文档

窗口函数

更新时间:

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

前提条件

PolarDB-X 1.0实例版本需为5.4.8及以上。关于如何查看实例版本,请参见查看实例版本

使用限制

  • 窗口函数仅支持用于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 1.0实例,支持如下专用窗口函数:

    • 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 | Singapore   | Calculator |     75 |
| 2000 | Singapore   | Calculator |     75 |
| 2001 | Singapore   | Calculator |     79 |
  • 您可以使用如下聚合函数来统计每个国家的总利润:

    select
        country,
        sum(profit) over (partition by country) sum_profit
    from test_window;

    返回结果如下:

    | country | sum_profit |
    |---------|------------|
    | Singapore   |        229 |
    | Singapore   |        229 |
    | Singapore   |        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 | Singapore   | Calculator |     75 |    1 |
    | 2000 | Singapore   | Calculator |     75 |    1 |
    | 2001 | Singapore   | 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 | Singapore   |     75 |          75 |
    | 2000 | Singapore   |     75 |         150 |
    | 2001 | Singapore   |     79 |         229 |
    | 2000 | Finland |   1500 |        1500 |
    | 2001 | Finland |     10 |        1510 |