跳到主要内容

SQL 分析函数

介绍

SQL分析函数(Analytic Functions)是SQL中一类强大的工具,用于在查询结果中执行复杂的计算和分析。与聚合函数(如SUMAVG)不同,分析函数不会将多行数据合并为一行,而是为每一行返回一个计算结果。这使得分析函数非常适合用于排名、窗口计算、累积求和等场景。

分析函数通常与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中常用的分析函数:

  1. ROW_NUMBER():为每一行分配一个唯一的序号。
  2. RANK():为每一行分配一个排名,相同值会有相同的排名,后续排名会跳过。
  3. DENSE_RANK():与RANK()类似,但不会跳过后续排名。
  4. SUM():计算窗口内的累积和。
  5. AVG():计算窗口内的平均值。
  6. LEAD()LAG():访问当前行的前一行或后一行的数据。

实际案例

案例1:使用ROW_NUMBER()为数据分配序号

假设我们有一个sales表,包含以下数据:

idsalespersonsale_amount
1Alice1000
2Bob1500
3Alice2000
4Bob500
5Alice3000

我们希望为每个销售人员的销售记录分配一个序号:

sql
SELECT 
id,
salesperson,
sale_amount,
ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_amount DESC) AS row_num
FROM
sales;

输出结果:

idsalespersonsale_amountrow_num
5Alice30001
3Alice20002
1Alice10003
2Bob15001
4Bob5002
提示

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;

输出结果:

idsalespersonsale_amountcumulative_sales
1Alice10001000
3Alice20003000
5Alice30006000
2Bob15001500
4Bob5002000
备注

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;

输出结果:

idsalespersonsale_amountprevious_salesale_difference
1Alice1000NULLNULL
3Alice200010001000
5Alice300020001000
2Bob1500NULLNULL
4Bob5001500-1000
警告

LAG()函数用于访问前一行的数据,如果当前行是分区的第一行,则返回NULL


总结

SQL分析函数是处理复杂数据分析任务的强大工具。通过OVER子句,你可以定义计算的范围和顺序,从而实现排名、累积计算、前后行数据访问等功能。掌握这些函数将极大地提升你的SQL查询能力。


附加资源与练习

  • 练习1:使用RANK()DENSE_RANK()函数,为sales表中的销售人员按销售额排名,并比较两者的区别。
  • 练习2:使用AVG()函数,计算每个销售人员的平均销售额。
  • 推荐阅读:SQL官方文档中关于分析函数的部分,深入理解窗口函数的高级用法。

通过不断练习和实践,你将能够熟练运用SQL分析函数解决实际问题!