开发指南—函数—窗口函数-阿里云开发者社区

开发指南—函数—窗口函数

2021-10-28 228

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

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

使用限制

窗口函数仅支持用于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。支持如下专用窗口函数： `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 |

开发指南—函数—窗口函数

使用限制

语法

使用示例

PolarDB 分布式版

热门文章

最新文章

相关电子书