窗口函数(Window Functions)是在SQL中一种强大的分析工具,允许你在数据集中进行复杂的计算,而无需进行额外的子查询或者自连接操作。窗口函数可以在结果集的每一行上运行,同时考虑到一组相关行(即“窗口”)的数据。这使得它们非常适合用于执行排名、移动平均、累积总和等操作。(很好用)
窗口函数主要分为三类:排名函数、分析函数和聚合函数。
1.排名函数
这些函数用于在指定的窗口中对行进行排序和排名。
ROW_NUMBER():为每一行分配一个唯一的整数,从1开始。
示例:找出每个部门薪水最高的员工
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num
FROM employees;
RANK():对每一行进行排名,当遇到相同值时,排名会跳过一些数字。
示例:计算员工在部门中的薪水排名。
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;
DENSE_RANK():对每一行进行排名,但不会跳过排名数字,即使遇到相同值。
示例:计算员工在部门中的薪水密集排名
SELECT employee_id, department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dense_rank
FROM employees;
2.分析函数
这类函数通常用于计算累计值或偏移值。
LAG() 和 LEAD():分别获取当前行前一行或后一行的值。
示例:计算销售额的逐月增长率
SELECT sale_date, sales_amount,
LAG(sales_amount) OVER (ORDER BY sale_date) as prev_sales
FROM sales;
FIRST_VALUE() 和 LAST_VALUE():获取窗口中第一个或最后一个非空值。
示例:找到每个部门第一个入职的员工
SELECT employee_id, department_id, hire_date, FIRST_VALUE(employee_id) OVER (PARTITION BY department_id ORDER BY hire_date) as first_hire
FROM employees;
3.聚合函数
这些函数在窗口内执行聚合操作,如求和、平均值、最小值、最大值等。
SUM() OVER ():窗口内的累计总和。
示例:每月累计销售额
SELECT sale_date, sales_amount, SUM(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales FROM sales;
AVG() OVER ():窗口内的平均值。
示例:计算最近三个月的平均销售额
SELECT sale_date, sales_amount,
AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average
FROM sales;
MIN() OVER ():窗口内的最小值。
示例:查找每个类别中价格最低的产品
SELECT product_id, category, price,
MIN(price) OVER (PARTITION BY category) as min_price
FROM products;
MAX() OVER ():窗口内的最大值。
示例:查找每个类别中价格最高的产品
SELECT product_id, category, price,
MAX(price) OVER (PARTITION BY category) as min_price
FROM products;
COUNT() OVER ():窗口内的计数。
示例:每个季度的订单数量
SELECT order_date, COUNT(*) OVER (PARTITION BY TO_CHAR(order_date, 'YYYY-Q')) as quarterly_orders
FROM orders;
窗口定义通常包含以下三个部分:
PARTITION BY:将数据集分割成多个分区,每个分区独立进行计算。
ORDER BY:在每个分区内部对行进行排序。
ROWS BETWEEN 或 RANGE BETWEEN:定义窗口的范围,可以是当前行前后一定数量的行,也可以是基于值的范围
这是比较常见的窗口函数(人工智能真是人类的好帮手)