OVER (PARTITION BY xx ORDER BY xx) 窗口函数理解

SUM(sale_amount) OVER (PARTITION BY salesperson ORDER BY sale_date) 这段 SQL 窗口函数的详细解释和它在执行过程中所发生的具体细节如下：

解析步骤

1. 窗口函数的基础定义：
   • SUM(sale_amount)：表示我们要对 sale_amount 列应用 SUM 聚合函数。
   • OVER 子句：指定窗口函数的范围和计算顺序。
   • PARTITION BY salesperson：将数据按 salesperson 列分组，即为每个销售人员独立计算。
   • ORDER BY sale_date：指定计算的顺序，即按 sale_date 列排序，按日期先后进行累积计算。

执行的详细步骤

1. 分区：

   • SQL 查询首先根据 PARTITION BY salesperson 子句将 sales 表的数据按 salesperson 列进行分区。每个销售人员的数据会被单独分开，形成多个独立的小组（partition）。

2. 排序：

   • 对每个分区内的数据根据 ORDER BY sale_date 子句进行排序，即按照 sale_date 列从小到大的顺序排列。

3. 累积计算：

   • 对于每个分区内的每一行，按照排序后的顺序逐行计算 SUM(sale_amount)。这意味着对前 N 行的 sale_amount 进行累加，并将结果赋值给当前行。

具体示例

考虑以下 sales 表数据：

id	salesperson	sale_amount	sale_date
1	Alice	500	2023-01-01
2	Bob	300	2023-01-01
3	Alice	700	2023-01-02
4	Bob	200	2023-01-02
5	Alice	100	2023-01-03
6	Bob	400	2023-01-03

我们应用窗口函数 SUM(sale_amount) OVER (PARTITION BY salesperson ORDER BY sale_date) 来计算累积销售额。执行过程如下：

分区与排序

• Alice 的分区（已按日期排序）：

  id	salesperson	sale_amount	sale_date
  1	Alice	500	2023-01-01
  3	Alice	700	2023-01-02
  5	Alice	100	2023-01-03

• Bob 的分区（已按日期排序）：

  id	salesperson	sale_amount	sale_date
  2	Bob	300	2023-01-01
  4	Bob	200	2023-01-02
  6	Bob	400	2023-01-03

累积计算

• Alice 的分区累积计算：

  id	salesperson	sale_amount	sale_date	cumulative_sales
  1	Alice	500	2023-01-01	500
  3	Alice	700	2023-01-02	500 + 700 = 1200
  5	Alice	100	2023-01-03	1200 + 100 = 1300

• Bob 的分区累积计算：

  id	salesperson	sale_amount	sale_date	cumulative_sales
  2	Bob	300	2023-01-01	300
  4	Bob	200	2023-01-02	300 + 200 = 500
  6	Bob	400	2023-01-03	500 + 400 = 900

结果

最终的查询结果如下：

id	salesperson	sale_amount	sale_date	cumulative_sales
1	Alice	500	2023-01-01	500
3	Alice	700	2023-01-02	1200
5	Alice	100	2023-01-03	1300
2	Bob	300	2023-01-01	300
4	Bob	200	2023-01-02	500
6	Bob	400	2023-01-03	900

总结

通过使用窗口函数，SQL 能够在不影响原始行的情况下计算出累积值。这使得它们非常适用于复杂的分析任务，如累积求和、移动平均和排名等。窗口函数的强大之处在于它能够灵活地应用于不同的分组和排序条件，从而在数据分析中提供了强大的支持。