【SQL】窗口函数

窗口函数是 SQL 中的一个高级功能，它可以在不对表的结果集进行分组的情况下，对查询结果中的每一行执行分析操作。窗口函数的作用范围是“窗口”（一组有逻辑意义的行）。通过窗口函数，可以实现排名、累计求和、移动平均等复杂的数据分析功能。

---

一、窗口函数的基本语法

窗口函数的标准结构如下：

<窗口函数> OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS|RANGE <窗口滑动的数据范围> )

• 窗口函数：用于对数据进行操作（如排名、累计求和等），常见的函数包括 RANK()、ROW_NUMBER()、SUM()、AVG() 等。

• OVER：窗口函数的关键字，表示窗口函数的计算范围。

• PARTITION BY：按照某些列对数据进行分组。它类似于 GROUP BY，但不会将数据聚合成一行，而是为每组创建独立的分析窗口。

• ORDER BY：定义数据的排序方式，通常是窗口范围内的数据需要按某列排序。

• RANGE|ROWS： 用来限定<窗口函数>所运用的数据的范围，具体有如下这些：

  关键字	解释
  UNBOUNDED PRECEDING	从当前窗口的开头到当前行
  UNBOUNDED FOLLOWING	从当前行到窗口的末尾
  CURRENT ROW	当前行
  PRECEDING	当前行往前的第 n 行（包含当前行）
  FOLLOWING	当前行往后的第 n 行（包含当前行）

  示例：
  取当前行和前五行：ROWS between 5 preceding and current row --共6行
  取当前行和后五行：ROWS between current row and 5 following --共6行
  取前五行和后五行：ROWS between 5 preceding and 5 following --共11行
  取当前行和前六行：ROWS 6 preceding（等价于between…and current row） --共7行
  这一天和前面6天：RANGE between interval 6 day preceding and current row --共7天
  这一天和前面6天：RANGE interval 6 day preceding（等价于between…and current row） --共7天
  字段值落在当前值-100到+200的区间：RANGE between 100 preceding and 200 following --共301个数值

---

二、窗口函数的特点

窗口函数与聚合函数的区别：

• 聚合函数（如 SUM()、AVG()）通常是对整个表或某一分组的行进行计算，然后将结果汇总到单行。
• 窗口函数保留了每一行的原始数据，但在此基础上对每一行计算出额外的分析数据，窗口函数不会“折叠”表中的行。

---

三、常见的窗口函数类型及用途

1. 排名相关函数

窗口函数的一大应用场景是排名操作。

函数	作用
ROW_NUMBER()	按顺序为每行分配一个唯一的编号
RANK()	按排序顺序为每行分配排名，存在并列时跳号
DENSE_RANK()	分配排名，存在并列时不跳号
NTILE(n)	将行分成 n 个大致相等的部分，并分配组号

实例：
假如有一张名为 sales 的表，包含以下几列：

id	department	sales_amount
1	A	500
2	A	300
3	B	800
4	B	600
5	A	700

使用 ROW_NUMBER()按照部门和销售额排序：

SELECT 
    id,
    department,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS rank
FROM sales;

结果：

id	department	sales_amount	rank
5	A	700	1
1	A	500	2
2	A	300	3
3	B	800	1
4	B	600	2

2. 累积计算函数

窗口函数也可以用于计算累计总和、平均值等。

函数	作用
SUM()	累加总和
AVG()	累计平均值
COUNT()	累计计数
MIN() / MAX()	当前窗口的最小/最大值

实例：
还是用前面的sales表，想按照部门计算销售额的累计求和：

SELECT 
    department,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY department ORDER BY sales_amount DESC) AS cumulative_sales
FROM sales;

结果：

department	sales_amount	cumulative_sales
A	700	700
A	500	1200
A	300	1500
B	800	800
B	600	1400

3. 滑动窗口函数

在一些分析场景中，希望计算某行周围的特定范围（如前 2 行、后 2 行）的数据，这时滑动窗口函数就派上用场了。

窗口范围关键字：
ROWS：基于绝对行号。
RANGE：基于逻辑值范围。

实例：
计算每一行的当前值和前两行的销售总和：

SELECT 
    id,
    department,
    sales_amount,
    SUM(sales_amount) OVER (
        PARTITION BY department 
        ORDER BY sales_amount 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS sliding_sum
FROM sales;

4. 比例分析函数

窗口函数也可以用于计算某行占某组总量的比例，如销售额占比。

实例：
计算每个部门中，每行销售额占整个部门销售额的百分比：

SELECT 
    department,
    sales_amount,
    ROUND(
        sales_amount * 1.0 / SUM(sales_amount) OVER (PARTITION BY department) * 100, 2
    ) AS percentage
FROM sales;

结果：

department	sales_amount	percentage
A	700	46.67
A	500	33.33
A	300	20.00
B	800	57.14
B	600	42.86

---

四、窗口函数使用的注意事项

PARTITION BY 和 ORDER BY 都是可选的。

如果没有 PARTITION BY，则整个数据集视为一个组。
如果没有 ORDER BY，则窗口函数不会进行任何排序。
窗口范围（ROWS/RANGE）的使用需要搭配 ORDER BY。

性能问题：
窗口函数的计算会对性能有一定影响，尤其是当 PARTITION BY 和 ORDER BY 的组合过于复杂时。尽量先将基础查询的结果保存为临时表，再针对结果应用窗口函数。