窗口函数是 SQL 中非常强大的工具，用于在查询结果中执行复杂的计算和分析。与聚合函数不同，窗口函数不会将多行合并为一行，而是保留每一行的独立性，同时基于一组相关的行（称为“窗口”）进行计算。
一、窗口函数的基本概念
- 窗口函数：对一组相关的行（窗口）执行计算，同时保留每一行的独立性。
- 窗口：通过 OVER 子句定义，指定计算的范围和顺序。
- 常见用途：排名、累计计算、移动平均、分组计算等。
二、 窗口函数的语法
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[frame_clause]
)
- function_name：窗口函数的名称（如 ROW_NUMBER()、SUM() 等）。
- OVER：定义窗口。
- PARTITION BY：将数据分组，类似于 GROUP BY，但不会合并行。
- ORDER BY：指定窗口内的排序方式。
- frame_clause：定义窗口的范围（如 ROWS BETWEEN ... AND ...）。
三、 常见的窗口函数
3.1 排名函数
- ROW_NUMBER()：为每一行分配一个唯一的序号。
例： SELECT
customer_id,
order_amount,
ROW_NUMBER() OVER (ORDER BY order_amount DESC) AS row_num
FROM orders;
- RANK()：为每一行分配排名，相同值会有相同的排名，后续排名会跳过。
例： SELECT
customer_id,
order_amount,
RANK() OVER (ORDER BY order_amount DESC) AS rank
FROM orders;
- DENSE_RANK()：为每一行分配排名，相同值会有相同的排名，后续排名不会跳过。
例： SELECT
customer_id,
order_amount,
DENSE_RANK() OVER (ORDER BY order_amount DESC) AS dense_rank
FROM orders;
3.2 聚合函数
- SUM()：计算窗口内的总和。
例： SELECT
customer_id,
order_amount,
SUM(order_amount) OVER (PARTITION BY customer_id) AS total_amount
FROM orders;
- AVG()：计算窗口内的平均值。
例： SELECT
customer_id,
order_amount,
AVG(order_amount) OVER (PARTITION BY customer_id) AS avg_amount
FROM orders;
- COUNT()：计算窗口内的行数。
例： SELECT
customer_id,
order_amount,
COUNT() OVER (PARTITION BY customer_id) AS order_count
FROM orders;
3.3 分析函数
-LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...) ，获取当前行之前的某一行的值。
（- column：要获取值的列。
- offset：偏移量（默认为 1，表示前一行）。
- default_value：如果偏移量超出范围，返回的默认值（可选））
例： SELECT
customer_id,
order_date,
order_amount,
LAG(order_amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount
FROM orders;
-LEAD(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)，获取当前行之后的某一行的值。
- 参数与 LAG()相同。
例： SELECT
customer_id,
order_date,
order_amount,
LEAD(order_amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;
- FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)，获取窗口内的第一个值。
例： SELECT
customer_id,
order_date,
order_amount,
FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_amount
FROM orders;
- LAST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)，获取窗口内的最后一个值。
例： SELECT
customer_id,
order_date,
order_amount,
LAST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM orders;
四、窗口的范围（Frame Clause）
- ROWS BETWEEN ... AND ...：定义窗口的物理范围。
（- UNBOUNDED PRECEDING：从窗口的第一行开始。
- UNBOUNDED FOLLOWING：到窗口的最后一行结束。
- CURRENT ROW：当前行。）
示例：
SELECT
customer_id,
order_date,
order_amount,
SUM(order_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
五、 窗口函数的应用场景
- 排名和分组：计算每个分组内的排名。
- 累计计算：计算累计总和、平均值等。
- 移动平均：计算时间序列数据的移动平均值。
- 数据对比：使用 LAG() 和 LEAD() 对比当前行与前后行的数据。
六、 窗口函数的注意事项
- 性能：窗口函数可能会影响查询性能，尤其是在大数据集上。
- 数据库支持：不同数据库对窗口函数的支持可能有所不同，需参考具体数据库的文档。
总结
窗口函数是 SQL 中非常强大的工具，适用于复杂的分析和计算任务。通过掌握窗口函数，可以更高效地处理数据分析和报表生成的需求。如果你有具体的问题或需要进一步的示例，请随时告诉我！