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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 传统的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开源数据库
本实验环境已内置PostgreSQL数据库以及PolarDB开源数据库:PolarDB PostgreSQL版和PolarDB分布式版,支持一键拉起使用,方便各位开发者学习使用。
相关文章
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(二)
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(二)
|
6月前
|
SQL 关系型数据库 MySQL
MySQL UPDATE 更新
MySQL UPDATE 更新
|
SQL JSON Java
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(一)
Hive学习---4、函数(单行函数、高级聚合函数、炸裂函数、窗口函数)(一)
开发指南—函数—窗口函数
传统的Group By函数会按照分组后的查询结果进行聚合计算,且每个分组只输出一条数据。但与传统的Group By函数不同,窗口函数(也称OLAP函数)可以为每个分组返回多个值,且不会影响记录的数量。本文介绍如何使用窗口函数
143 0
开发指南—函数—聚合函数
本文介绍了PolarDB-X支持及不支持的聚合函数。
开发指南—函数—比较函数
本文介绍了PolarDB-X支持的比较函数。
开发指南—函数—位函数
本文介绍了PolarDB-X支持的位函数。 位函数分为两类,一是标量函数,二是聚合函数。
|
网络协议
开发指南—函数—其他函数
本文介绍了PolarDB-X支持的其他函数。
|
SQL 存储 关系型数据库
全面了解mysql锁机制(InnoDB)与问题排查
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高 ,并发度最低。 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
1233 0
全面了解mysql锁机制(InnoDB)与问题排查
|
SQL 关系型数据库 流计算