SQL 分析函数
介绍
SQL分析函数(Analytic Functions)是SQL中一类强大的工具,用于在查询结果中执行复杂的计算和分析。与聚合函数(如SUM
、AVG
)不同,分析函数不会将多行数据合并为一行,而是为每一行返回一个计算结果。这使得分析函数非常适合用于排名、窗口计算、累积求和等场景。
分析函数通常与OVER
子句一起使用,OVER
子句定义了计算的范围(称为“窗口”)。通过分析函数,你可以在不改变原始数据的情况下,对数据进行深入分析。
分析函数的基本语法
分析函数的基本语法如下:
sql
分析函数名(参数) OVER (
[PARTITION BY 列名]
[ORDER BY 列名]
[窗口子句]
)
- 分析函数名:如
ROW_NUMBER()
、RANK()
、SUM()
等。 - PARTITION BY:将数据分组,类似于
GROUP BY
,但不会合并行。 - ORDER BY:指定窗口内的排序方式。
- 窗口子句:定义计算的范围,如
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。
常用的分析函数
以下是SQL中常用的分析函数:
- ROW_NUMBER():为每一行分配一个唯一的序号。
- RANK():为每一行分配一个排名,相同值会有相同的排名,后续排名会跳过。
- DENSE_RANK():与
RANK()
类似,但不会跳过后续排名。 - SUM():计算窗口内的累积和。
- AVG():计算窗口内的平均值。
- LEAD() 和 LAG():访问当前行的前一行或后一行的数据。
实际案例
案例1:使用ROW_NUMBER()
为数据分配序号
假设我们有一个sales
表,包含以下数据:
id | salesperson | sale_amount |
---|---|---|
1 | Alice | 1000 |
2 | Bob | 1500 |
3 | Alice | 2000 |
4 | Bob | 500 |
5 | Alice | 3000 |
我们希望为每个销售人员的销售记录分配一个序号:
sql
SELECT
id,
salesperson,
sale_amount,
ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_amount DESC) AS row_num
FROM
sales;
输出结果:
id | salesperson | sale_amount | row_num |
---|---|---|---|
5 | Alice | 3000 | 1 |
3 | Alice | 2000 | 2 |
1 | Alice | 1000 | 3 |
2 | Bob | 1500 | 1 |
4 | Bob | 500 | 2 |
提示
ROW_NUMBER()
为每个销售人员的销售记录分配了唯一的序号,按销售金额从高到低排序。
案例2:使用SUM()
计算累积销售额
继续使用sales
表,我们希望计算每个销售人员的累积销售额:
sql
SELECT
id,
salesperson,
sale_amount,
SUM(sale_amount) OVER (PARTITION BY salesperson ORDER BY id) AS cumulative_sales
FROM
sales;
输出结果:
id | salesperson | sale_amount | cumulative_sales |
---|---|---|---|
1 | Alice | 1000 | 1000 |
3 | Alice | 2000 | 3000 |
5 | Alice | 3000 | 6000 |
2 | Bob | 1500 | 1500 |
4 | Bob | 500 | 2000 |
备注
SUM()
函数计算了每个销售人员的累积销售额,按id
顺序累加。
案例3:使用LEAD()
和LAG()
访问前后行数据
我们希望查看每个销售人员的当前销售额与前一次销售额的差异:
sql
SELECT
id,
salesperson,
sale_amount,
LAG(sale_amount) OVER (PARTITION BY salesperson ORDER BY id) AS previous_sale,
sale_amount - LAG(sale_amount) OVER (PARTITION BY salesperson ORDER BY id) AS sale_difference
FROM
sales;
输出结果:
id | salesperson | sale_amount | previous_sale | sale_difference |
---|---|---|---|---|
1 | Alice | 1000 | NULL | NULL |
3 | Alice | 2000 | 1000 | 1000 |
5 | Alice | 3000 | 2000 | 1000 |
2 | Bob | 1500 | NULL | NULL |
4 | Bob | 500 | 1500 | -1000 |
警告
LAG()
函数用于访问前一行的数据,如果当前行是分区的第一行,则返回NULL
。
总结
SQL分析函数是处理复杂数据分析任务的强大工具。通过OVER
子句,你可以定义计算的范围和顺序,从而实现排名、累积计算、前后行数据访问等功能。掌握这些函数将极大地提升你的SQL查询能力。
附加资源与练习
- 练习1:使用
RANK()
和DENSE_RANK()
函数,为sales
表中的销售人员按销售额排名,并比较两者的区别。 - 练习2:使用
AVG()
函数,计算每个销售人员的平均销售额。 - 推荐阅读:SQL官方文档中关于分析函数的部分,深入理解窗口函数的高级用法。
通过不断练习和实践,你将能够熟练运用SQL分析函数解决实际问题!