PolarDB-X 1.0-SQL 手册-函数-窗口函数

2021-07-19 21:44:28

传统的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实例，支持如下专用窗口函数： `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 |

码农公寓

前提条件

使用限制

语法

使用示例

相关文章