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

简介: 传统的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 |
相关文章
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(二)
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(二)
|
5月前
|
存储 Prometheus 并行计算
10倍性能提升-SLS Prometheus 时序存储技术演进
本文将介绍近期SLS Prometheus存储引擎的技术更新,在兼容 PromQL 的基础上实现 10 倍以上的性能提升。同时技术升级带来的成本红利也将回馈给使用SLS 时序引擎的上万内外部客户。
158599 7
|
SQL 分布式计算 Unix
阿里云-DataWorks- ODPS SQL开发3-日期与字符、数学运算、聚合函数函数
阿里云-DataWorks- ODPS SQL开发3 本文主要讲解日常大量会接触到的一些常用的日期与字符、数学运算、聚合函数函数。
|
SQL JSON Java
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(一)
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(一)
如何快速体验通义千问全系列模型能力
体验通义千问全系列模型能力,需在阿里云开通百炼服务。访问阿里云百炼控制台的“模型广场”,可选择包括通义系列在内的多种模型。按照指南进行训练、部署和评测。详情参阅官方文档。
|
5月前
|
人工智能 弹性计算 运维
聚焦六大典型应用场景,博云金融行业容器解决方案更新发布!
【4月更文挑战第5天】博云作为领先的私有云厂商,其金融行业容器云解决方案应对了金融企业在数字化转型中的挑战,提供敏捷开发、快速部署、弹性伸缩及全生命周期管理。解决方案涵盖六大应用场景:分布式金融PaaS、核心系统下移、信创云建设、互金业务管理、金融AI训练和老旧应用上云。通过统一的开发运维平台、一致的环境、高效的资源管理和安全保障,实现技术、管理及业务层面的价值提升。博云已成功服务近百家金融机构,助力金融行业的云原生转型。
110 1
开发指南—函数—窗口函数
传统的Group By函数会按照分组后的查询结果进行聚合计算,且每个分组只输出一条数据。但与传统的Group By函数不同,窗口函数(也称OLAP函数)可以为每个分组返回多个值,且不会影响记录的数量。本文介绍如何使用窗口函数
开发指南—函数—聚合函数
本文介绍了PolarDB-X支持及不支持的聚合函数。
|
5月前
|
传感器 监控 网络协议
WebSocket 实战:构建高效的实时应用
WebSocket 实战:构建高效的实时应用
WebSocket 实战:构建高效的实时应用
开发指南—函数—数学函数
本文介绍了PolarDB-X支持的数学函数。