SQL窗口函数

SQL窗口函数

1. 简介

说明：窗口函数用于对数据进行复杂的分析和聚合，窗口函数可以在一组行（通常是基于某种排序的行集）上执行计算，窗口函数类似于聚合函数，但是不会将多行合并成单行。

使用：窗口函数的语法通常包含一个OVER子句，用来定义窗口的范围。OVER子句可以包含以下选项：

• PARTITION BY: 用于定义分区，即对数据进行分组，每个分组独立进行窗口函数计算。
• ORDER BY: 用于定义窗口内的排序规则。
• ROWS BETWEEN 或 RANGE BETWEEN: 用于定义窗口的大小和范围。

2. 分类

1. 排名函数
   • ROW_NUMBER(): 根据排序规则，为每一行分配一个唯一的排名。类似：类似：1 2 3 4 5 6。
   • RANK(): 根据排序规则，为每一行分配一个排名，相同值的行会有相同的排名，会跳过排名值。类似：1 1 3 4 4 6。
   • DENSE_RANK(): 类似于RANK()，但是没有跳过的排名值。类似：1 1 2 3 4 4 5。
2. 聚合函数:
   • SUM() OVER (): 在窗口内求和。
   • AVG() OVER (): 在窗口内计算平均值。
   • COUNT() OVER (): 在窗口内计数。
   • MIN() OVER (): 在窗口内找到最小值。
   • MAX() OVER (): 在窗口内找到最大值。
3. 其它函数
   • LAG(column, offset, default_value) OVER()：返回当前行之前的行的指定列的值，offset是偏移量，default是默认值
   • LEAD(column, offset, default) OVER()：返回当前行之后的行的指定列的值，offset是偏移量，default是默认值
   • NTILE(): 将结果集分割成指定数量的组。
   • FIRST_VALUE(): 返回窗口内第一行的值。
   • LAST_VALUE(): 返回窗口内最后一行的值。

3. 用法

3.1 排名

ROW_NUMBER，RANK，DENSE_RANK的应用：
假如你有一张员工表，你需要获取每个部门的最高薪资，此时你就可以使用相关的排名函数：

1）中间表写法

• 因为要查询部门内，所以PARTITION BY 后面要跟department_id；
• 因为要查薪资，所以对窗口内的salary进行排序。

-- 中间表
WITH mid_tab AS(
SELECT employee_id, name, department_id, salary, 
ROW_NUMBER ( ) OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rank 
FROM employee)
-- 查询
SELECT employee_id, name, department_id,salary,rank
FROM mid_tab 
WHERE rank = 1;

结果：

2）子查询写法

SELECT
	department_id,MAX(salary) max_salary
FROM
	( SELECT employee_id, name, department_id, salary, 
	ROW_NUMBER ( ) OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rank 
	FROM employee ) mid_tab
GROUP BY department_id

结果：

3.2 库存入库分析

FIRST_VALUE, LAST_VALUE的应用：
假如你有一张库存表，你需要获取一个仓库内最早入库和最晚入库的库存。

SELECT warehouse_id, 
MAX(first_product_id) first_product_id,
MAX(first_entry_date) first_entry_date, 
MAX(last_product_id) last_product_id,
MAX(last_entry_date) last_entry_date
FROM
(SELECT  
    warehouse_id,  
    FIRST_VALUE(product_id) OVER (PARTITION BY warehouse_id ORDER BY entry_date ASC) AS first_product_id,  
    FIRST_VALUE(entry_date) OVER (PARTITION BY warehouse_id ORDER BY entry_date ASC) AS first_entry_date,  
    LAST_VALUE(product_id) OVER (PARTITION BY warehouse_id ORDER BY entry_date DESC) AS last_product_id,  
    LAST_VALUE(entry_date) OVER (PARTITION BY warehouse_id ORDER BY entry_date DESC) AS last_entry_date  
	FROM  inventory ) mid_tab 
GROUP BY warehouse_id

结果：

3.3 商品变化分析

LEAD,LAG的应用：
假如你有一张商品表，你需要查看某种商品前一天的价格后一天的价格以及价格的变化。

SELECT  
    date,  
    goods,  
    price,  
    LAG(price) OVER (PARTITION BY goods ORDER BY date) AS pre_price,  
    price - LAG(price) OVER (PARTITION BY goods ORDER BY date) AS price_change,  
    LEAD(price) OVER (PARTITION BY goods ORDER BY date) AS next_price  
FROM  
    goods
WHERE  
    goods = 'mouse'  
ORDER BY date;

结果：