SQL 窗口函数指南：深入解析窗口函数的神奇之处

引言

在这篇文章里，我们将揭开SQL世界的一大秘密——窗口函数！它像一把神奇的钥匙，让你在数据中开启新的视角。你不仅能轻松排名、统计、计算，还能实现数据滑窗和分组操作。无需复杂的子查询，窗口函数助你事半功倍！快跟我一起探索这个强大而有趣的功能，让你的SQL技能更上一层楼！

什么是窗口函数？

在SQL中，窗口函数是一组用于执行分析和计算的特殊函数。它们能够让你在不改变查询结果的情况下，对结果集中的行进行排序、分组和计算，从而获得更深入的数据分析。

窗口函数语法

窗口函数语法表格

函数	功能	语法
ROW_NUMBER()	对结果集中的行进行编号	ROW_NUMBER() OVER(ORDER BY column1[ASC/DESC])
RANK()	根据排序列对结果集中的行进行排名	RANK() OVER(ORDER BY column1 [ASC/DESC])
DENSE_RANK()	根据排序列对结果集中的行进行排名	DENSE_RANK() OVER(ORDER BY column1 [ASC/DESC])
NTILE()	将结果集划分为指定数量的桶	NTILE(n) OVER(ORDER BY column1[ASC/DESC])
SUM()	计算指定列的累计和	SUM(cloumn1) OVER(PARTITION BY column2 ORDER BY column3)
AVG()	计算指定列的累计和	AVG(column1) OVER(PARTITION BY column2 ORDER BY column3)
COUNT()	计算指定列的行数	COUNT(column1) OVER(PARTITION BY column2 ORDER BY column3)
FIRST_VALUE()	获取分组中第一行的指定列的值	FIRST_VALUE(column1) OVER(PARTITION BY column2 ORDER BY column3)
LAST_VALUE()	获取分组中最后一行的指定列的值	LAST_VALUE(column1) OVER(PARTITION BY column2 ORDER BY column3)
LEAD()	获取指定列下一行的值	LEAD(column1,offset,default) OVER(PARTITION BY column2 ORDER BY column3)
LAG()	获取指定列上一行的值	LAG(column1,offset,default) OVER(PARTITION BY column2 ORDER BY column3)
PERCENT_RANK()	计算指定列值的百分位排名	PERCENT_RANK() OVER(ORDER BY column1 [ASC/DESC])
CUME_DIST()	计算指定列值的累积分布	CUME_DIST() OVER(ORDER BY column1 [ASC/DESC])

窗口函数的妙用

数据表如下：

students表

id	name	age
1	Alice	20
2	Bob	22
3	Carol	19

scores表

student_id	course	score
1	Math	85
1	English	78
1	Science	92
2	Math	90
2	English	88
2	Science	80
3	Math	76
3	English	85
3	Science	78

排名函数（Ranking Functions）

在排名函数组中，有三个常见的函数：ROW_NUMBER()、RANK()和DENSE_RANK()。这些函数用于对结果集中的行进行排名和排序。

1.ROW_NUMBER()函数

ROW_NUMBER()函数为每一行分配一个唯一的编号，无视重复值。它在排序后的结果集中对每一行进行标记，以便唯一标识该行在排序结果中的位置

SELECT id, name, ROW_NUMBER() OVER (ORDER BY age) AS row_number
FROM students;

运行结果

id	name	row_number
3	Carol	1
1	Alice	2
2	Bob	3

2.RANK()函数

RANK()函数根据排序列对结果集中的行进行排名，并可以处理并列排名。如果有多个行具有相同的排序值，则它们将被分配相同的排名，而下一个排名将会跳过相应的行数。

SELECT id, name, RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

运行结果

id	name	rank
1	Science	1
2	Math	2
3	English	3

3.DENSE_RANK()函数

DENSE_RANK()函数与RANK()函数类似，也是根据排序列对结果集中的行进行排名。不同之处在于，DENSE_RANK()函数处理并列排名时不会跳过相应的行数，而是连续分配排名。

SELECT id, name, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

运行结果

id	name	dense_rank
1	Science	1
2	Math	2
3	English	3

分位数函数（Quantile Functions）

在分位数函数组中，有一个常见的函数：NTILE()。这个函数将结果集划分为指定数量的桶，每个桶内的行数尽量平均。

4.NTILE()函数

NTILE()函数将结果集划分为指定数量的桶，每个桶内的行数尽量平均。该函数将结果集中的行分成n个部分，每个部分包含大致相同数量的行，除非不能完全平均分配。

SELECT id, name, score, NTILE(3) OVER (ORDER BY score DESC) AS ntile
FROM scores;

运行结果

id	name	score	ntile
1	Science	92	1
2	Math	90	1
3	English	88	1

窗口聚合函数（Window Aggregate Functions）

在窗口聚合函数组中，有三个常见的函数：SUM()、AVG()和COUNT()。这些函数通过窗口来计算聚合值，而不是在整个结果集上进行计算。

5. SUM()函数

SUM()函数用于计算指定列的累计和。

SELECT s.id, s.name, sc.score,
       SUM(sc.score) OVER (PARTITION BY s.id) AS total_score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	total_score
1	Alice	255
2	Bob	258
3	Carol	239

6. AVG()函数

AVG()函数用于计算指定列的平均值。

SELECT s.id, s.name,
       AVG(sc.score) AS avg_score
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name;

运行结果

id	name	avg_score
1	Alice	85.0
2	Bob	86.0
3	Carol	19.666

7. COUNT()函数

COUNT()函数用于计算指定列的行数。

SELECT s.id, s.name,
       COUNT(sc.score) AS num_courses
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name;

运行结果

id	name	num_courses
1	Alice	3
2	Bob	3
3	Carol	3

其他窗口函数

在其他窗口函数组中，有一些常见的函数，比如FIRST_VALUE()、LAST_VALUE()、LEAD()、LAG()、PERCENT_RANK()和CUME_DIST()。这些函数在数据分析中非常有用。

8. FIRST_VALUE()函数

FIRST_VALUE()函数用于获取窗口中指定列的第一个值。

SELECT DISTINCT s.id, s.name,
       FIRST_VALUE(sc.score) OVER (PARTITION BY s.id, s.name ORDER BY sc.score) AS first_score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	first_score
1	Alice	78
2	Bob	80
3	Carol	76

9. LAST_VALUE()函数

LAST_VALUE()函数用于获取窗口中指定列的最后一个值。

SELECT DISTINCT s.id, s.name,
       LAST_VALUE(sc.score) OVER (PARTITION BY s.id, s.name ORDER BY sc.score
                                ROWS BETWEEN UNBOUNDED preceding AND UNBOUNDED following) AS last_score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	last_score
1	Alice	92
2	Bob	80
3	Carol	78

10. LEAD()函数

LEAD()函数用于在窗口中获取指定列的后续行的值。

SELECT s.id, s.name, sc.course, sc.score,
       LEAD(sc.score) OVER (PARTITION BY s.id ORDER BY sc.course) AS next_score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

 11. LAG()函数

 LAG()函数用于获取窗口中指定列的前面行的值。它可以用来获取当前行前的任意行的值。

SELECT DISTINCT s.id, s.name, sc.course,
       LAG(sc.score) OVER (PARTITION BY s.id ORDER BY sc.course) AS previous_score
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	course	previous_score
1	Alice	Math	NULL
1	Alice	English	85
1	Alice	Science	78
2	Bob	Math	NULL
2	Bob	English	90
2	Bob	Science	88
3	Carol	Math	NULL
3	Carol	English	76
3	Carol	Science	85

12. PERCENT_RANK()函数

PERCENT_RANK()函数用于计算每行相对于窗口的百分比排名值。它返回一个介于0和1之间的值，表示当前行在窗口中的百分比排名。

SELECT DISTINCT s.id, s.name, sc.course,
       PERCENT_RANK() OVER (PARTITION BY s.id ORDER BY sc.score) AS percent_rank_value
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	course	percent_rank_value
1	Alice	English	0.0
1	Alice	Math	0.5
1	Alice	Science	1.0
2	Bob	English	0.0
2	Bob	Math	0.5
2	Bob	Science	1.0
3	Carol	English	0.0
3	Carol	Math	0.5
3	Carol	Science	1.0

13. CUME_DIST()函数

CUME_DIST()函数用于计算每行相对于窗口的累积分布值。它返回一个介于0和1之间的值，表示当前行在窗口中的累积分布百分比

SELECT DISTINCT s.id, s.name, sc.course,
       CUME_DIST() OVER (PARTITION BY s.id ORDER BY sc.score) AS cumulative_distribution
FROM students s
JOIN scores sc ON s.id = sc.student_id;

运行结果

id	name	course	cumulative_distribution
1	Alice	English	0.6667
1	Alice	Science	1.0
1	Alice	Math	0.3333
2	Bob	English	0.6667
2	Bob	Science	1.0
2	Bob	Math	0.3333
3	Carol	English	0.6667
3	Carol	Science	1.0
3	Carol	Math	0.3333

结论

窗口函数为SQL查询提供了更加灵活和强大的功能。通过对结果集中的行进行排序、分组和计算，我们能够轻松实现复杂的数据分析和处理任务。希望这篇文章能够帮助你深入理解窗口函数的神奇之处，并在你的SQL学习之旅中探索更多可能性！