窗口函数over()

窗口函数是什么？

核心作用：

在保留原始行数据的同时，对指定范围内的数据进行计算（如排名、累计、占比等）。

与普通聚合函数（如 SUM、AVG）的区别：

普通聚合函数：会合并多行数据，结果行数减少。

窗口函数：逐行计算，结果行数不变

基本语法

函数名() OVER (
  [PARTITION BY 字段]  -- 定义窗口的分组规则（类似 GROUP BY）
  [ORDER BY 字段]      -- 定义窗口内的排序规则
  [frame_clause]       -- 定义窗口范围（如 ROWS BETWEEN ...）
)

三、关键子句详解

1. PARTITION BY

作用：将数据按指定字段分组，窗口函数在每个分组内独立计算。

示例：计算每个部门的工资排名

  SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees;
  

2. ORDER BY

作用：定义窗口内的数据排序方式，常用于计算累计值或排名。

示例：计算累计销售额

  SELECT date, sales,
    SUM(sales) OVER (ORDER BY date) AS cumulative_sales
  FROM daily_sales;

3. 窗口范围 (ROWS/RANGE BETWEEN)

作用：精确控制窗口包含哪些行。

常用选项：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从第一行到当前行（默认）

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：前一行到后一行

示例：计算3天移动平均

  SELECT date, sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
  FROM daily_sales;

四、案例

案例1：超市销售数据（基础表）

日期	商品	销售额
2023-01-01	苹果	200
2023-01-01	香蕉	150
2023-01-02	苹果	180
2023-01-02	香蕉	170

需求一：计算 每天各商品销售额占当日总销售额的百分比

关键思路：

按日期分组：每天是一个独立计算单位

逐行计算：每行商品都要知道当天的总销售额

SQL 实现：

SELECT 日期, 商品, 销售额,
   销售额 * 100.0 / SUM(销售额) OVER(PARTITION BY 日期) AS 当日占比
FROM 销售表;

执行过程拆解：

1. PARTITION BY 日期 → 将数据按日期分成两个窗口：
   • 窗口1：2023-01-01（苹果200 + 香蕉150）
   • 窗口2：2023-01-02（苹果180 + 香蕉170）
2. 在每个窗口内计算 SUM(销售额) → 窗口1总和350，窗口2总和350
3. 逐行计算占比 → 苹果在1月1日的占比：200/350≈57.14%

结果：

日期	商品	销售额	当日占比
2023-01-01	苹果	200	57.14%
2023-01-01	香蕉	150	42.86%
2023-01-02	苹果	180	51.43%
2023-01-02	香蕉	170	48.57%

需求二：计算 每个商品的累计销售额（按日期排序）

关键思路：

按商品分组：苹果和香蕉分别累计

按日期排序：逐天累加

SQL 实现：

SELECT 日期, 商品, 销售额,
   SUM(销售额) OVER(
     PARTITION BY 商品
     ORDER BY 日期
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   ) AS 累计销售额
FROM 销售表;

执行过程拆解：

1. PARTITION BY 商品 → 分成苹果、香蕉两个窗口
2. ORDER BY 日期 → 在每个商品窗口内按日期排序
3. ROWS BETWEEN ... → 从第一行到当前行累计

苹果窗口：

1月1日：200 → 累计200

1月2日：180 → 累计200+180=380

香蕉窗口：

1月1日：150 → 累计150

1月2日：170 → 累计150+170=320

结果：

日期	商品	销售额	累计销售额
2023-01-01	苹果	200	200
2023-01-01	香蕉	150	380
2023-01-02	苹果	180	150
2023-01-02	香蕉	170	320

需求三：查看 每行数据的前后相邻销售额（按日期排序）

SQL 实现：

SELECT 日期, 商品, 销售额,
   LAG(销售额, 1) OVER(ORDER BY 日期) AS 前一天销售额,
   LEAD(销售额, 1) OVER(ORDER BY 日期) AS 后一天销售额
FROM 销售表;

结果解析：

日期	商品	销售额	前一天销售额	后一天销售额
2023-01-01	苹果	200	NULL	150	← 苹果第一天无前一天数据
2023-01-01	香蕉	150	200	180	← 香蕉的"前一天"其实是同一天的苹果
2023-01-02	苹果	180	150	170
2023-01-02	香蕉	170	180	NULL	← 最后一天无后一天数据

因为没有用 PARTITION BY 商品，导致苹果和香蕉的数据混在一起排序。修正方法见下方。

需求三（修正版）：按商品分组 查看相邻日期销售额

SELECT 日期, 商品, 销售额,
   LAG(销售额, 1) OVER(PARTITION BY 商品 ORDER BY 日期) AS 上期销售额,
   LEAD(销售额, 1) OVER(PARTITION BY 商品 ORDER BY 日期) AS 下期销售额
FROM 销售表;

修正后结果

日期	商品	销售额	上期销售额	下期销售额
2023-01-01	苹果	200	NULL	100	← 苹果只有两行数据
2023-01-02	苹果	180	200	NULL
2023-01-01	香蕉	150	NULL	170
2023-01-02	香蕉	170	150	NULL

五、核心技巧总结 

组合使用子句：

PARTITION BY + ORDER BY → 分组内排序计算（如累计值）

PARTITION BY + 窗口范围 → 分组内指定计算范围（如移动平均）

常用函数搭配：

| 函数 | 作用 | 典型场景 | |---------------|---------------------|-----------------------| | ROW_NUMBER()| 行号 | 生成唯一序号 | | RANK() | 排名（允许跳号） | 成绩排名 |

| SUM() | 求和 | 累计值、分组占比 | | LAG()/LEAD()| 获取前后行数据 | 环比分析 |

调试技巧：

先写 OVER() 的空括号，逐步添加 PARTITION BY 和 ORDER BY

用 SELECT * 临时查看窗口划分结果