数据库窗口函数是什么？窗口函数的类型、语法及使用详解

数据库窗口函数是SQL中用于对一组相关行进行计算的工具，它保留原始行并为每行返回计算结果，与GROUP BY不同，不会将行合并成一行。

窗口函数类型及应用场景

聚合窗口函数：
常见函数：SUM()、AVG()、COUNT()、MAX()、MIN()。
应用场景：
累计计算：计算运行总和，如销售额的每日累计。
移动平均：计算某段时间内的平均值，如股票价格的5日移动平均。
分组统计：计算每个学生在班级内的平均分，同时显示具体分数。
示例：

SELECT employee_name, department, salary, SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_department_salaryFROM employees;

排名窗口函数：
常见函数：
ROW_NUMBER()：为分区内的每一行分配一个唯一的连续整数，没有并列。
RANK()：为分区内的每一行分配一个排名，相同值得到相同排名，但下一个不同值会跳过相应数量的排名。
DENSE_RANK()：类似于RANK()，但下一个不同值会得到紧邻的下一个排名，不会跳过。
NTILE(n)：将分区内的行分成n个组，并为每行分配其所属组的编号。
应用场景：
Top N问题：找出每个部门工资最高的3名员工。
竞赛排名：根据分数对选手进行排名，处理并列情况。
数据分桶：将客户按消费额分成高、中、低三档。
示例：

SELECT * FROM ( SELECT employee_name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM employees) AS ranked_employeesWHERE rnk <= 3;

值窗口函数：
常见函数：
LAG(expression, offset, default)：获取当前行之前指定偏移量的行的expression值。
LEAD(expression, offset, default)：获取当前行之后指定偏移量的行的expression值。
FIRST_VALUE(expression)：获取分区内第一行的expression值。
LAST_VALUE(expression)：获取分区内最后一行的expression值。
应用场景：
环比/同比分析：比较当前月份与上个月份的销售额差异。
数据填充：用前一个有效值填充空值。
区间比较：比较当前记录与分区内首尾记录的差异。
示例：

SELECT sale_month, monthly_sales, LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month) AS previous_month_sales, (monthly_sales - LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month)) AS sales_growthFROM sales_data;

窗口函数语法详解

窗口函数通过OVER()子句定义窗口，其内部包含PARTITION BY、ORDER BY和ROWS/RANGE子句，共同确定计算的范围和顺序。

PARTITION BY子句：
作用：将数据集逻辑上分割成若干个独立的、不重叠的子集（即“分区”）。每个分区内的计算都是独立的，互不影响。
示例：PARTITION BY department表示所有后续的窗口函数计算都只会在同一个部门内部进行。
缺失情况：如果省略PARTITION BY，那么整个结果集将被视为一个单一的“窗口”，所有计算都针对整个结果集进行。
ORDER BY子句：
作用：在PARTITION BY划分好的每个分区内部，规定了行的处理顺序。这对于依赖顺序的窗口函数（如排名函数、累计函数、LAG/LEAD）至关重要。
示例：在计算累计销售额时，需要按日期进行排序；在排名时，需要按分数进行排序。
缺失情况：如果省略ORDER BY，并且没有指定帧（ROWS/RANGE），那么窗口函数的行为可能会变得不确定，因为数据库可能会以任意顺序处理分区内的行。
ROWS或RANGE子句（帧规范）：
ROWS：基于物理行数来定义帧。
示例：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从分区开始到当前行（这是ORDER BY存在时的默认帧，用于累计和）。
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：包含当前行、前一行和后一行（用于移动平均）。
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：整个分区（如果ORDER BY存在，通常用于计算分区总和，与PARTITION BY单独使用效果类似）。
RANGE：基于逻辑值范围来定义帧。它通常用于数值或日期类型，帧内的行是那些在ORDER BY列上与当前行值相差在指定范围内的行。如果存在重复值，RANGE会将所有相同值的行都包含在帧内。
示例：
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：类似ROWS，但会包含所有与当前行ORDER BY值相同的行。
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW：包含当前行以及其前7天内的所有行。
作用：精确控制计算的“范围”。它让你可以实现复杂的滑动窗口计算，比如计算过去7天的平均值，或者某个特定值范围内的统计。
注意事项：RANGE通常要求ORDER BY子句中只有一个表达式。如果ORDER BY省略，且没有指定帧，那么默认帧是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这意味着整个分区。

您可能感兴趣问答

Collapsible

热门标签

热点问答