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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 传统的Group By函数会按照分组后的查询结果进行聚合计算,且每个分组只输出一条数据。但与传统的Group By函数不同,窗口函数(也称OLAP函数)可以为每个分组返回多个值,且不会影响记录的数量。本文介绍如何使用窗口函数。

传统的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 |
相关实践学习
跟我学:如何一键安装部署 PolarDB-X
《PolarDB-X 动手实践》系列第一期,体验如何一键安装部署 PolarDB-X。
相关文章
|
11天前
|
SQL 人工智能 关系型数据库
PostgreSQL 常用SQL(持续更新...)
PostgreSQL 常用SQL(持续更新...)
|
3天前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
10天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
2天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
15 1
|
11天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
20 2
|
11天前
T-sql 高级查询( 5*函数 联接 分组 子查询)
T-sql 高级查询( 5*函数 联接 分组 子查询)
|
11天前
|
SQL 关系型数据库 数据库
SQL 42501: Postgresql查询中的权限不足错误
SQL 42501: Postgresql查询中的权限不足错误
|
11天前
|
SQL 关系型数据库 MySQL
【MySQL】:探秘主流关系型数据库管理系统及SQL语言
【MySQL】:探秘主流关系型数据库管理系统及SQL语言
36 0

相关产品

  • 云原生分布式数据库 PolarDB-X