SQL 常用 OVER() 窗口函数介绍

1. sum() + over()  做组内数据累加

       在 SQL 中想实现不同分组内数据累加，可以通过 sum() + over() + PARTITION BY + ORDER BY  结合实现。这种方式能同时满足多维度分组且组内累加的需求，示例如下：

假设我们有一张 sales 表，表中存储着不同区域、不同产品的每日销售额，数据如下：

需求：按 region（区域）和 product（产品）分组，在每个分组内按时间顺序累加金额。

实现方法：SUM()+OVER()+PARTITION BY+ORDER BY

SELECT 
  sale_id,
  region,
  product,
  sale_date,
  amount,
  -- 按区域和产品分组，组内按日期排序累加金额
  SUM(amount) OVER (
    PARTITION BY region, product  -- 多列分组：区域+产品
    ORDER BY sale_date            -- 组内按日期排序
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 累加范围：从组内第一条到当前行
  ) AS cumulative_amount
FROM sales
ORDER BY region, product, sale_date;

结果:

sale_id | region  | product | sale_date  | amount | cumulative_amount
---------|---------|---------|------------|--------|------------------
1       | 华北    | 手表    | 2023-10-02 | 1000   | 1000     -- 华北手表：1000
2       | 华北    | 手表    | 2023-10-02 | 1500   | 2500     -- 华北手表：1000+1500
3       | 华北    | 手表    | 2023-10-03 | 3000   | 5500     -- 华北手表：1000+1500+3000
4       | 华东    | 手表    | 2023-10-03 | 3500   | 9000     -- 华东手表：1000+1500+3000+3500
5       | 华东    | 平板    | 2023-10-02 | 3000   | 3000     -- 华东平板：3000
6       | 华东    | 平板    | 2023-10-03 | 4000   | 7000     -- 华东平板：3000+4000

 关键语法: 

  1. PARTITION BY col1, col2：按多列组合进行分组，所有列相同的数据会被分到同一组。

  2. ORDER BY：指定组内的排序规则，决定累加的顺序。

      3.  窗口函数支持所有主流数据库（MySQL 8.0+、PostgreSQL、Kingbase、Orecle 等）

2. OVER() 窗口函数介绍

       SELECT  sale_id, region, product, sale_date,  amount, SUM(amount) OVER (
               PARTITION BY region, product   ORDER BY sale_date  
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_amount  FROM sales ORDER BY region, product, sale_date;

        在上面的 sql 中，OVER()窗口函数核心语法是定义一个数据窗口，可以理解为定义组内其它数据与当前行处理逻辑，例如上面 sql 的逻辑是，同一个分组内从第一行开始将数据累加到当前行。         注意：上面 sql 看似与 GROUP BY 处理相同，其实所有区别，窗口函数不会将多行数据合并为一行，而是在原有行的数据基础上为每行添加一个计算结果。

     2.1 基本使用语法

函数名(列名) OVER (窗口定义)

        1. 函数名：可以是聚合函数（SUM、COUNT、AVG 等）或专用窗口函数（ROW_NUMBER、RANK、DENSE_RANK 等）。

        2. 窗口定义：通过 PARTITION BY、ORDER BY 等子句定义数据窗口对数据处理范围和规则。

    2.2  关键字说明

        1. PARTITION BY: 分组划分规则(可选)，将数据按指定列分组，每个分组形成一个独立的空间，函数只在组内计算。

SELECT 
  sale_date,
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS 区域累计销售额
FROM sales;

        2. ORDER BY：窗口内排序(可选)，指定窗口内数据的排序规则

SELECT 
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS 全局累计销售额
FROM sales;

       3. 处理逻辑范围(可选)，通过 ROWS 或 RANGE 精确指定窗口包含的行范围（默认是从组内第一行到当前行）：

    3.1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW :从组内第一行到当前行

    3.2 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING :从当前行到组内最后一行

    3.3 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ;当前行、前一行、后一行（共 3 行）

SELECT 
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 近3行平均值
FROM sales;

3. 常见使用场景

  3.1  累计求和 / 平均值（聚合函数 +OVER()）

SELECT 
  id,
  amount,
  -- 累计求和
  SUM(amount) OVER (ORDER BY id) AS 累计金额,
  -- 移动平均值（前1行+当前行+后1行）
  AVG(amount) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 移动平均
FROM sales;

3.2 组内排名（ROW_NUMBER/RANK/DENSE_RANK）

SELECT 
  region,
  amount,
  -- 组内按金额排名（不重复）
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS 排名_不重复,
  -- 组内按金额排名（允许并列，跳过后续名次）
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS 排名_跳号,
  -- 组内按金额排名（允许并列，不跳过后续名次）
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS 排名_连续
FROM sales;

3.3 组内首尾值 / 占比

SELECT 
  region,
  amount,
  -- 组内最大金额
  MAX(amount) OVER (PARTITION BY region) AS 区域最高金额,
  -- 当前金额占组内总金额的比例
  amount / SUM(amount) OVER (PARTITION BY region) AS 区域占比
FROM sales;

与 GROUP BY 的区别

总结

OVER() 函数的核心价值是：在不减少原表行数的前提下，实现对 "相关数据组" 的灵活计算，特别适合需要同时展示明细数据和聚合分析结果的场景（如报表中的累计占比、排名、移动平均等）。