分析函数
1. 排名函数
-
RANK(): 为结果集中的每一行分配一个唯一的排名，如果有相同的值（并列），则会跳过后续的排名数。 -
DENSE_RANK(): 类似于 RANK()，但它不会跳过任何排名数字；如果有相同值，则它们会有相同的排名，但下一个排名数字是连续的。 -
ROW_NUMBER(): 为分区内的每一行分配一个唯一的行号，从1开始递增。
2. 窗口偏移函数
-
LAG(): 返回当前行之前的一行或多行的数据。如果指定了偏移量大于可用行数，则返回默认值或NULL。 -
LEAD(): 返回当前行之后的一行或多行的数据。与 LAG()类似，可以指定偏移量和默认值。
3. 聚合函数
当聚合函数如SUM(), AVG(), MIN(), MAX()等结合OVER()子句使用时，它们就变成了分析函数，允许执行窗口计算而不是整个分组的汇总。
-
SUM() OVER (…): 可用于计算累积总和。 -
AVG() OVER (…): 可以用来计算移动平均。 -
MIN() / MAX() OVER (…): 可以找到窗口内数据的最小值/最大值。
4. 值函数
-
FIRST_VALUE(): 返回窗口帧的第一行的值。 -
LAST_VALUE(): 默认情况下，它返回窗口帧的最后一行的值。但是，需要注意的是，在没有明确指定窗口范围的情况下，它的行为可能不符合预期。通常建议显式定义窗口范围，例如 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING来获取真正的最后一行值。
实战案例
示例数据
假设我们有一个sales表，包含以下数据：
| product_id | sale_date | quantity | price |
|---|---|---|---|
| 100 | 2023-01-01 | 10 | 5.00 |
| 100 | 2023-01-02 | 15 | 5.00 |
| 100 | 2023-01-03 | 20 | 5.50 |
| 100 | 2023-01-04 | 25 | 5.50 |
| 101 | 2023-01-01 | 8 | 6.00 |
| 101 | 2023-01-02 | 12 | 6.00 |
| 101 | 2023-01-03 | 16 | 6.50 |
案例一：按销量排名
SELECT product_id, quantity,
RANK() OVER (ORDER BY quantity DESC) as rank,
DENSE_RANK() OVER (ORDER BY quantity DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY quantity DESC) as row_number
FROM sales;
查询结果
| product_id | quantity | rank | dense_rank | row_number |
|---|---|---|---|---|
| 100 | 25 | 1 | 1 | 1 |
| 100 | 20 | 2 | 2 | 2 |
| 100 | 15 | 3 | 3 | 3 |
| 101 | 16 | 4 | 4 | 4 |
| 100 | 10 | 5 | 5 | 5 |
| 101 | 12 | 6 | 6 | 6 |
| 101 | 8 | 7 | 7 | 7 |
案例二：计算累计销售额
SELECT product_id, sale_date, price * quantity as total_sale,
SUM(price * quantity) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_sales
FROM sales;
查询结果
| product_id | sale_date | total_sale | cumulative_sales |
|---|---|---|---|
| 100 | 2023-01-01 | 50.00 | 50.00 |
| 100 | 2023-01-02 | 75.00 | 125.00 |
| 100 | 2023-01-03 | 110.00 | 235.00 |
| 100 | 2023-01-04 | 137.50 | 372.50 |
| 101 | 2023-01-01 | 48.00 | 48.00 |
| 101 | 2023-01-02 | 72.00 | 120.00 |
| 101 | 2023-01-03 | 104.00 | 224.00 |
案例三：比较前后期销售额
SELECT product_id, sale_date, quantity,
LAG(quantity, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_quantity,
LEAD(quantity, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as next_quantity
FROM sales;
查询结果
| product_id | sale_date | quantity | prev_quantity | next_quantity |
|---|---|---|---|---|
| 100 | 2023-01-01 | 10 | 0 | 15 |
| 100 | 2023-01-02 | 15 | 10 | 20 |
| 100 | 2023-01-03 | 20 | 15 | 25 |
| 100 | 2023-01-04 | 25 | 20 | 0 |
| 101 | 2023-01-01 | 8 | 0 | 12 |
| 101 | 2023-01-02 | 12 | 8 | 16 |
| 101 | 2023-01-03 | 16 | 12 | 0 |
案例四：销售趋势分析
SELECT product_id, sale_date, quantity,
SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as sales_last_7_days,
AVG(quantity) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_sales_last_7_days
FROM sales
这个例子展示了如何计算过去7天的累计销量和平均销量，有助于识别销售趋势。
案例五：价格波动分析
SELECT product_id, sale_date, price,
LAG(price, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_price,
LEAD(price, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as next_price,
(price - LAG(price, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date)) as price_change
FROM sales
这里我们不仅比较了前后期的价格，还计算了价格的变化幅度。岂不美哉！
应邀创建了一个数仓人自己的社群 VIP 知识星球🪐，为大家提供一个数据人自己的知识共享平台。技术讨论、资源共享、面试指导、大厂内推、方案拉齐、人脉链接，欢迎数据同行的加入！
