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

1. ROW_NUMBER()：为每一行分配一个唯一的序号。
2. RANK()：为每一行分配一个排名，相同值会有相同的排名，后续排名会跳过。
3. DENSE_RANK()：与RANK()类似，但不会跳过后续排名。
4. SUM()：计算窗口内的累积和。
5. AVG()：计算窗口内的平均值。
6. 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分析函数解决实际问题！