SQL 分析功能
SQL分析功能是SQL语言中的高级特性，允许你在查询结果中执行复杂的计算和分析。这些功能通常用于处理分组数据、计算累计值、排名、移动平均值等。通过使用分析功能，你可以更高效地处理和分析大规模数据集。
什么是SQL分析功能？
SQL分析功能是一组特殊的函数，允许你在查询结果中执行复杂的计算，而不会改变原始数据的行数。这些函数通常与OVER子句一起使用，以定义计算的范围和顺序。常见的分析功能包括窗口函数、排名函数和聚合函数。
窗口函数
窗口函数是SQL分析功能的核心。它们允许你在查询结果中定义一个“窗口”，并在该窗口内执行计算。窗口函数不会改变查询结果的行数，而是为每一行返回一个计算结果。
示例：计算累计销售额
假设我们有一个销售数据表sales，包含以下列：date、product、amount。我们可以使用窗口函数计算每个产品的累计销售额。
SELECT
date,
product,
amount,
SUM(amount) OVER (PARTITION BY product ORDER BY date) AS cumulative_sales
FROM
sales;
输入：
| date | product | amount |
|---|---|---|
| 2023-01-01 | A | 100 |
| 2023-01-02 | A | 150 |
| 2023-01-03 | A | 200 |
| 2023-01-01 | B | 50 |
| 2023-01-02 | B | 75 |
输出：
| date | product | amount | cumulative_sales |
|---|---|---|---|
| 2023-01-01 | A | 100 | 100 |
| 2023-01-02 | A | 150 | 250 |
| 2023-01-03 | A | 200 | 450 |
| 2023-01-01 | B | 50 | 50 |
| 2023-01-02 | B | 75 | 125 |
在这个例子中，SUM(amount) OVER (PARTITION BY product ORDER BY date)计算了每个产品的累计销售额。
排名函数
排名函数用于为查询结果中的行分配排名。常见的排名函数包括ROW_NUMBER()、RANK()和DENSE_RANK()。
示例：为销售额排名
我们可以使用RANK()函数为每个产品的销售额排名。
SELECT
product,
amount,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS sales_rank
FROM
sales;
输入：
| product | amount |
|---|---|
| A | 100 |
| A | 150 |
| A | 200 |
| B | 50 |
| B | 75 |
输出：
| product | amount | sales_rank |
|---|---|---|
| A | 200 | 1 |
| A | 150 | 2 |
| A | 100 | 3 |
| B | 75 | 1 |
| B | 50 | 2 |
在这个例子中，RANK()函数为每个产品的销售额分配了排名。
聚合函数与窗口函数结合
聚合函数（如SUM()、AVG()、COUNT()等）也可以与窗口函数结合使用，以在查询结果中执行复杂的计算。
示例：计算移动平均值
我们可以使用AVG()函数计算每个产品的移动平均值。
SELECT
date,
product,
amount,
AVG(amount) OVER (PARTITION BY product ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
sales;
输入：
| date | product | amount |
|---|---|---|
| 2023-01-01 | A | 100 |
| 2023-01-02 | A | 150 |
| 2023-01-03 | A | 200 |
| 2023-01-04 | A | 250 |
| 2023-01-01 | B | 50 |
| 2023-01-02 | B | 75 |
输出：
| date | product | amount | moving_avg |
|---|---|---|---|
| 2023-01-01 | A | 100 | 100.00 |
| 2023-01-02 | A | 150 | 125.00 |
| 2023-01-03 | A | 200 | 150.00 |
| 2023-01-04 | A | 250 | 200.00 |
| 2023-01-01 | B | 50 | 50.00 |
| 2023-01-02 | B | 75 | 62.50 |
在这个例子中，AVG(amount) OVER (PARTITION BY product ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)计算了每个产品的移动平均值。
实际应用场景
SQL分析功能在实际应用中有广泛的用途，例如：
- 销售分析：计算累计销售额、移动平均值、排名等。
- 财务分析：计算累计利润、增长率等。
- 用户行为分析：计算用户活跃度、留存率等。
案例：计算用户留存率
假设我们有一个用户登录表user_logins，包含以下列：user_id、login_date。我们可以使用窗口函数计算每个用户的留存率。
SELECT
user_id,
login_date,
COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date) AS retention_rate
FROM
user_logins;
输入：
| user_id | login_date |
|---|---|
| 1 | 2023-01-01 |
| 1 | 2023-01-02 |
| 1 | 2023-01-03 |
| 2 | 2023-01-01 |
| 2 | 2023-01-02 |
输出：
| user_id | login_date | retention_rate |
|---|---|---|
| 1 | 2023-01-01 | 1 |
| 1 | 2023-01-02 | 2 |
| 1 | 2023-01-03 | 3 |
| 2 | 2023-01-01 | 1 |
| 2 | 2023-01-02 | 2 |
在这个例子中，COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date)计算了每个用户的留存率。
总结
SQL分析功能是处理和分析大规模数据集的强大工具。通过使用窗口函数、排名函数和聚合函数，你可以执行复杂的计算，如累计值、排名、移动平均值等。这些功能在实际应用中有广泛的用途，如销售分析、财务分析和用户行为分析。
练习：
- 尝试使用
ROW_NUMBER()函数为每个产品的销售额分配唯一的行号。 - 使用
SUM()函数计算每个产品的累计销售额，并尝试使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW来定义窗口范围。
通过掌握SQL分析功能，你将能够更高效地处理和分析数据，提升你的SQL技能。