1. sum() + over()? 做組內數據累加
? ? ? ?在 SQL 中想實現不同分組內數據累加，可以通過?sum() + over()?+?PARTITION BY + ORDER BY? 結合實現。這種方式能同時滿足多維度分組且組內累加的需求，示例如下：
假設我們有一張?sales?表，表中存儲著不同區域、不同產品的每日銷售額，數據如下：
| sale_id | region | product | sale_date | amount |
|---|---|---|---|---|
| 1 | 華北 | 手表 | 2023-10-02 | 1000 |
| 2 | 華北 | 手表 | 2023-10-03 | 1500 |
| 3 | 華北 | 平板 | 2023-10-02 | 3000 |
| 4 | 華東 | 手表 | 2023-10-03 | 3000 |
| 5 | 華東 | 手表 | 2023-10-03 | 3500 |
| 6 | 華東 | 平板 | 2023-10-03 | 4000 |
需求：按?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?的區別
| 特性 | GROUP BY | OVER()?窗口函數 |
|---|---|---|
| 結果行數 | 每組返回一行 | 保留原表所有行 |
| 計算范圍 | 整組數據 | 可自定義窗口范圍（如前 N 行） |
| 適用場景 | 統計各組匯總值 | 需保留明細行的分析場景 |
總結
OVER()?函數的核心價值是：在不減少原表行數的前提下，實現對 "相關數據組" 的靈活計算，特別適合需要同時展示明細數據和聚合分析結果的場景（如報表中的累計占比、排名、移動平均等）。
