窗口函数（常见）

窗口函数（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：定义窗口的范围，可以是当前行前后一定数量的行，也可以是基于值的范围

这是比较常见的窗口函数（人工智能真是人类的好帮手）