SQL(2) - 开窗函数

开窗函数（Window Functions）是SQL中一种强大的功能，用于在一组相关行（窗口）上执行聚合计算，同时保留每行的详细信息。与传统的聚合函数（如 SUM, AVG, COUNT 等）不同，开窗函数不会将结果聚合成单一的行，而是为每一行返回一个计算结果。

基本语法

开窗函数的基本语法如下：

function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC | DESC]]
    [ROWS BETWEEN start AND end]
)

主要组成部分

1. 函数名：常见的开窗函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG(), MIN(), MAX() 等。
2. OVER子句：定义窗口的范围。
   • PARTITION BY：将数据分成多个分区，每个分区独立计算。
   • ORDER BY：在每个分区内对数据进行排序。
   • ROWS BETWEEN：定义窗口的行范围。

常见的开窗函数

1. 行号函数

• ROW_NUMBER()：为每个分区内的行分配一个唯一的行号。

  ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)

• RANK()：为每个分区内的行分配一个排名，如果有相同的值，排名会跳过。

  RANK() OVER (PARTITION BY column1 ORDER BY column2)

• DENSE_RANK()：为每个分区内的行分配一个排名，如果有相同的值，排名不会跳过。

  DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2)

2. 前后行函数

• LEAD()：获取当前行之后的某一行的值。

  LEAD(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)

• LAG()：获取当前行之前的某一行的值。

  LAG(column, offset, default) OVER (PARTITION BY column1 ORDER BY column2)

3. 聚合函数

• SUM()：计算窗口内的总和。

  SUM(column) OVER (PARTITION BY column1 ORDER BY column2)

• AVG()：计算窗口内的平均值。

  AVG(column) OVER (PARTITION BY column1 ORDER BY column2)

• MIN()：计算窗口内的最小值。

  MIN(column) OVER (PARTITION BY column1 ORDER BY column2)

• MAX()：计算窗口内的最大值。

  MAX(column) OVER (PARTITION BY column1 ORDER BY column2)

示例

假设有一个销售数据表 sales，包含以下字段：id, product_id, sale_date, amount。

1. 行号函数示例

SELECT id, product_id, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) as row_num
FROM sales;

这个查询为每个产品的销售记录按日期排序，并分配一个行号。

2. 排名函数示例

SELECT id, product_id, sale_date, amount,
       RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) as rank
FROM sales;

这个查询为每个产品的销售记录按金额降序排序，并分配一个排名。

3. 前后行函数示例

SELECT id, product_id, sale_date, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount,
       LEAD(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) as next_amount
FROM sales;

这个查询为每个产品的销售记录按日期排序，并获取前一行和后一行的金额。

4. 聚合函数示例

SELECT id, product_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_sum
FROM sales;

这个查询为每个产品的销售记录按日期排序，并计算累计销售额。

窗口范围

• ROWS BETWEEN：定义窗口的行范围。

  SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)

  这个查询计算每个产品销售记录按日期排序时，当前行及其前后两行的总和。