传统的Group By函数会按照分组后的查询结果进行聚合计算,且每个分组只输出一条数据。但与传统的Group By函数不同,窗口函数(也称OLAP函数)可以为每个分组返回多个值,且不会影响记录的数量。本文介绍如何使用窗口函数。

前提条件

PolarDB-X实例需满足如下条件:
  • PolarDB-X 1.0实例版本需为5.4.8及以上。关于如何查看实例版本,请参见查看实例版本
  • PolarDB-X 2.0实例版本需为5.4.9及以上。关于如何查看实例版本,请参见查看实例版本

使用限制

  • 窗口函数仅支持用于SELECT语句中。
  • 窗口函数禁止与单独的聚合函数混合使用。

    例如,在如下语句中,SUM为聚合函数,且未与OVER关键字组合,因此您无法使用如下语句进行查询:

    SELECT SUM(NAME),COUNT() OVER(...) FROM SOME_TABLE

    若需实现如上查询,您可以使用如下语句代替:

    SELECT SUM(NAME),WIN1 FROM (SELECT NAME,COUNT() OVER(...) AS WIN1 FROM SOME_TABLE) alias

语法

function OVER ([[partition by column_some1] [order by column_some2] [RANGE|ROWS BETWEEN start AND end]])
参数 说明
function 该部分指定了窗口函数中支持的函数,取值范围如下:
  • 可以在窗口函数中结合OVER关键字使用如下聚合函数:
    • SUM()
    • COUNT()
    • AVG()
    • MAX()
    • MIN()
  • 专用窗口函数如下:
    • ROW_NUMBER()
    • RANK()
    • DENSE_RANK()
    • PERCENT_RANK()
    • CUME_DIST()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LAG()
    • LEAD()
    • NTH_VALUE()
说明
  • 当使用专用窗口函数RANK()DENSE_RANK()时,窗口函数中的order by部分不可省略。更多专用窗口函数的介绍,请参见Window Function Descriptions
  • 仅实例版本为5.4.9或以上(若您的实例版本低于5.4.9,请升级版本)的PolarDB-X实例,支持如下专用窗口函数:
    • PERCENT_RANK()
    • CUME_DIST()
    • FIRST_VALUE()
    • LAST_VALUE()
    • LAG()
    • LEAD()
    • NTH_VALUE()
[partition by column_some1] 该部分指定了窗口函数的分区规范,用于将输入行分散到不同的分区中,过程和GROUP BY子句的分散过程相似。
说明 partition by部分不支持引用复杂表达式,如您可以引用column_some1,但不可以引用column_some1 + 1
[order by column_some2] 该部分指定了窗口函数的排序规范,用于确定输入数据行在窗口函数中执行的顺序。
说明 order by部分不支持引用复杂表达式,如您可以引用column_some2,但不可以引用column_some2 + 1
[RANGE|ROWS BETWEEN start AND end] 该部分指定了窗口函数的窗口区间,支持按照计算列值的范围(即RANGE)或计算列的行数(即ROWS)等两种模式来定义区间。

您可以使用BETWEEN start AND end指定边界的可取值,其中:

  • start取值范围如下:
    • CURRENT ROW:当前行
    • N PRECEDING:前N行
    • UNBOUNDED PRECEDING:直到第1行
  • end取值范围如下:
    • CURRENT ROW:当前行
    • N FOLLOWING:后N行
    • UNBOUNDED FOLLOWING:直到最后1行

使用示例

假设已有如下原始数据:

| year | country | product    | profit |
|------|---------|------------|--------|
| 2001 | Finland | Phone      |     10 |
| 2000 | Finland | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2001 | India   | Calculator |     79 |
  • 您可以使用如下聚合函数来统计每个国家的总利润:
    select
        country,
        sum(profit) over (partition by country) sum_profit
    from test_window;
    返回结果如下:
    | country | sum_profit |
    |---------|------------|
    | India   |        229 |
    | India   |        229 |
    | India   |        229 |
    | USA     |       1550 |
    | USA     |       1550 |
    | Finland |       1510 |
    | Finland |       1510 |
  • 您可以使用如下专用窗口函数将数据按照国家分组,并将国家内的产品按利润由小到大排名:
    select
        'year',
        country,
        product,
        profit,
        rank() over (partition by country order by profit) as rank
    from test_window;
    返回结果如下:
    | year | country | product    | profit | rank |
    |------|---------|------------|--------|------|
    | 2001 | Finland | Phone      |     10 |    1 |
    | 2000 | Finland | Computer   |   1500 |    2 |
    | 2001 | USA     | Calculator |     50 |    1 |
    | 2001 | USA     | Computer   |   1500 |    2 |
    | 2000 | India   | Calculator |     75 |    1 |
    | 2000 | India   | Calculator |     75 |    1 |
    | 2001 | India   | Calculator |     79 |    3 |
  • 您可以使用如下带有ROWS命令的语句,查询根据当前窗口的每行数据计算利润部分的总和:
    select 
        'year',
        country,
        profit,
        sum(profit) over (partition by country order by 'year' ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as sum_win 
    from test_window;
    返回结果如下:
    +------+---------+--------+-------------+
    | year | country | profit |   sum_win   |
    +------+---------+--------+-------------+
    | 2001 | USA     |     50 |          50 |
    | 2001 | USA     |   1500 |        1550 |
    | 2000 | India   |     75 |          75 |
    | 2000 | India   |     75 |         150 |
    | 2001 | India   |     79 |         229 |
    | 2000 | Finland |   1500 |        1500 |
    | 2001 | Finland |     10 |        1510 |