人生没有彩排
每一天都是现场直播
        找回密码
当前位置：枫言枫语 SQL 正文

SQL语法 - 窗口函数详解

2024-12-31 作者：夕枫 分类：SQL / 语法 阅读() 评论(0)

SQL窗口函数是一种在结果集的滑动窗口上执行计算的函数。与传统的聚合函数不同，窗口函数不会修改结果集的行数，而是返回每个窗口的计算结果。窗口函数的主要优势在于其能够在不改变原始数据的情况下，提供丰富的分析功能。

语法

使用窗口函数时的语法主要为：

window_function() OVER ([PARTITION BY column_name] [ORDER BY column_name] [ROWS|RANGE [BETWEEN] start_row [AND end_row]])
  • window_function() 指的是各种作用不同的窗口函数
  • OVER 语句定义了窗口的大小和位置
  • PARTITION BY 的作用为按指定字段进行分区
  • ORDER BY 的作用为在各个分区内按指定字段对窗口进行排序
  • RANGE| ROWS 语句的作用为控制窗口的大小，即定义窗口的起始行与结束行

其中，控制窗口大小的主要语法为：

ROWS|RANGE [BETWEEN] <start_expr> [AND <end_expr>]
或
ROWS|RANGE <start_expr>

第一种语法同时指定开始行和结束行，第二种语法仅指定开始行，结束行默认为当前行。注意：如果省略ROWS | RANGE语句，那么窗口的大小默认为从起始行到当前行。

ROWS与RANGE的区别如下：

1、ROWS: 表示按照行的范围定义窗口，根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。常用：rows n perceding表示从前n行到当前行（一共n+1行）

2、RANGE：表示按照值的范围定义窗口，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组，比如：

range between 4 preceding AND 7 following

-- 表示：如果当前行的值为10的话，就取前后的值在6到17之间的数据。

其中，start_expr和end_expr的边界可取值共有以下五种：

关键词 描述
UNBOUNDED PRECEDING 表示该分组的第一行
UNBOUNDED FOLLOWING 表示该分组的最后一行
CURRENT ROW 表示当前行
PRECEDING 表示从当前行往前数数量的行，RANGE选项禁用
FOLLOWING 表示从当前行往后数数量的行，RANGE选项禁用

另外，OVER语句中，如果有ORDER BY时，同行是指在ORDER BY排序时具有相同数值的行，不同行是指ORDER BY排序时具有不同的数值的行。 如果没有ORDER BY时，那么就是当前分区的所有行都包含在窗口中，因为所有行都会成为当前行。

总的来说，窗口函数一般使用在SELECT子句中，将在每一行中创建OVER语句所定义的窗口，并在该窗口中执行指定的函数以进行聚合或滑动取值。

窗口函数

1、聚合：

函数 作用
sum(expr) over 对[窗口内]指定列的expr值进行求和
count(expr) over 对[窗口内]指定列的expr值进行计数
avg(expr) over 对[窗口内]指定列的expr值进行平均值计算
max(expr) over 找到[窗口内]指定列的expr最大值
min(expr) over 找到[窗口内]指定列的expr最大值

2、排序：

函数 作用
row_number() 为每一行分配一个唯一的行号，即创建行索引
rank() 并列排序，返回有间隔重复的排名，例如1，1，3
dense_rank() 并列排序，返回无间隔重复的排名，例如1，1，2

3、滑动取值：

函数 作用
first_value(expr) 返回[窗口内]指定列中第一行的expr值
last_value(expr) 返回[窗口内]指定列中最后一行的expr值
nth_value(expr,n) 返回[窗口内]指定列中第n行的expr值
lag(expr,n) 返回[窗口内]指定列中当前行向前第n行的expr值，n可省略，默认n为1
lead(expr,n) 返回[窗口内]指定列中当前行向后第n行的expr值，n可省略，默认n为1

示例

下面将基于以下数据进行演示：

Sales表：

id category product amount
1 X A 100
2 X B 200
3 Y A 300
4 Y B 300
5 X A 200
6 X B 100

1、聚合函数示例：

SUM：

SELECT id, category, product, amount, 
       SUM(amount) OVER (PARTITION BY category ORDER BY id) AS cumulative_sum
FROM sales;
id category product amount cumulative_sum
1 X A 100 100
2 X B 200 300
5 X A 200 500
6 X B 100 600
3 Y A 300 300
4 Y B 300 600

COUNT：

SELECT id, category, product, amount, 
       COUNT(amount) OVER (PARTITION BY category ORDER BY id) AS cumulative_count
FROM sales;
id category product amount cumulative_count
1 X A 100 1
2 X B 200 2
5 X A 200 3
6 X B 100 4
3 Y A 300 1
4 Y B 300 2

AVG：

SELECT id, category, product, amount, 
       AVG(amount) OVER (PARTITION BY category ORDER BY id) AS cumulative_avg
FROM sales;
id category product amount cumulative_avg
1 X A 100 100.0
2 X B 200 150.0
5 X A 200 166.67
6 X B 100 150.0
3 Y A 300 300.0
4 Y B 300 300.0

MAX：

SELECT id, category, product, amount, 
       MAX(amount) OVER (PARTITION BY category ORDER BY id) AS cumulative_max
FROM sales;
id category product amount cumulative_max
1 X A 100 100
2 X B 200 200
5 X A 200 200
6 X B 100 200
3 Y A 300 300
4 Y B 300 300

MIN：

SELECT id, category, product, amount, 
       MIN(amount) OVER (PARTITION BY category ORDER BY id) AS cumulative_min
FROM sales;
id category product amount cumulative_min
1 X A 100 100
2 X B 200 100
5 X A 200 100
6 X B 100 100
3 Y A 300 300
4 Y B 300 300

2、排序函数示例：

ROW_NUMBER：

SELECT id, category, product, amount, 
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS row_num
FROM sales;
id category product amount row_num
2 X B 200 1
5 X A 200 2
1 X A 100 3
6 X B 100 4
3 Y A 300 1
4 Y B 300 2

RANK：

SELECT id, category, product, amount, 
       RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rank
FROM sales;
id category product amount rank
2 X B 200 1
5 X A 200 1
1 X A 100 3
6 X B 100 3
3 Y A 300 1
4 Y B 300 1

DENSE_RANK：

SELECT id, category, product, amount, 
       DENSE_RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS dense_rank
FROM sales;
id category product amount dense_rank
2 X B 200 1
5 X A 200 1
1 X A 100 2
6 X B 100 2
3 Y A 300 1
4 Y B 300 1

3、滑动取值函数示例

FIRST_VALUE：

SELECT id, category, product, amount, 
       FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY id) AS first_amount
FROM sales;
id category product amount first_amount
1 X A 100 100
2 X B 200 100
5 X A 200 100
6 X B 100 100
3 Y A 300 300
4 Y B 300 300

LAST_VALUE：

SELECT id, category, product, amount, 
       LAST_VALUE(amount) OVER (PARTITION BY category ORDER BY id 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM sales;
id category product amount last_amount
1 X A 100 100
2 X B 200 100
5 X A 200 100
6 X B 100 100
3 Y A 300 300
4 Y B 300 300

NTH_VALUE：

SELECT id, category, product, amount, 
       NTH_VALUE(amount, 2) OVER (PARTITION BY category ORDER BY id) AS second_amount
FROM sales;
id category product amount second_amount
1 X A 100 NULL
2 X B 200 200
5 X A 200 200
6 X B 100 200
3 Y A 300 NULL
4 Y B 300 300

LAG：

SELECT id, category, product, amount, 
       LAG(amount, 1) OVER (PARTITION BY category ORDER BY id) AS previous_amount
FROM sales;
id category product amount previous_amount
1 X A 100 NULL
2 X B 200 100
5 X A 200 200
6 X B 100 200
3 Y A 300 NULL
4 Y B 300 300

LEAD：

SELECT id, category, product, amount, 
       LEAD(amount, 1) OVER (PARTITION BY category ORDER BY id) AS next_amount
FROM sales;
id category product amount next_amount
1 X A 100 200
2 X B 200 200
5 X A 200 100
6 X B 100 NULL
3 Y A 300 300
4 Y B 300 NULL

总结

SQL窗口函数是一种强大的数据处理工具，能够在不改变原始数据的情况下，提供丰富的分析功能。在实际应用中，合理使用窗口函数可以大大提高数据处理的效率和准确性。

AD：【必看】万字长文，带你了解PowerBI窗口函数的所有秘密
赞(0) 打赏
版权声明：本文为夕枫的原创文章，著作权归作者所有，未经允许不得转载
文章名称：《SQL语法 - 窗口函数详解》
文章链接：https://www.ximaple.com/posts/823.html
分享到
夕枫

夕枫

贤者以其昭昭，使人昭昭

相关推荐

订阅评论
提醒
guest
0 评论
最新
最久 最赞
内联反馈
查看所有评论

热门文章

知识标签

PowerBI(86)DAX(61)UDF(58)自定义函数(58)SQL(42)实践案例(37)进阶秘籍(36)原理篇(28)进阶篇(23)案例(20)语法(19)应用技巧(12)表函数(9)基础篇(8)博客搭建(7)筛选调节器(6)MySQL(6)Hexo(6)时间智能函数(5)筛选上下文(4)Linux(4)

觉得文章有用的话就支持一下吧~

感谢您的打赏支持，我将持续输出有价值的内容！

支付宝扫一扫

微信扫一扫

登录

找回密码

注册

Operation don't support