sql 中 sum () over 用法_sql 中 sum () over 分組求和詳解

sum() over() 是 sql 中的窗口函數，用于在不減少行數的前提下進行分組聚合計算。1. 它通過 partition by 定義分組，在每行保留原始明細的同時顯示組內聚合值；2. 結合 order by 可實現滾動求和；3. 與 group by 的核心區別在于 sum() over() 保持行數不變并保留明細；4. 可用于復雜場景如移動平均、占比計算等；5. 使用時需注意性能問題，可通過索引、數據過濾、預聚合等方式優化。

SUM() OVER() 在 SQL 中，是一個非常強大的窗口函數，它允許你在一個“窗口”內對數據進行聚合計算，而這個“窗口”是基于你定義的分區（PARTITION BY）和排序（ORDER BY）來確定的。與傳統的 GROUP BY 聚合不同，SUM() OVER() 不會減少你查詢結果的行數，它會在每一行上都顯示聚合結果，這對于需要保留原始明細數據，同時又想看到匯總信息的場景來說，簡直是神來之筆。

解決方案

要深入理解 SUM() OVER()，我們不妨從最常見的應用場景入手：分組求和。它主要通過 PARTITION BY 子句來定義分組，然后在這個分組內進行 SUM 操作。

舉個例子，假設我們有一個銷售明細表 sales，包含 product_category（產品類別）、sale_date（銷售日期）和 amount（銷售金額）。如果我們想知道每個產品類別下的總銷售額，并且希望在每一行銷售記錄旁邊都顯示這個總額，而不是像 GROUP BY 那樣把所有記錄聚合為一行，SUM() OVER() 就派上用場了。

-- 假設表結構 -- CREATE table sales ( --     sale_id INT PRIMARY KEY, --     product_category VARCHAR(50), --     sale_date DATE, --     amount DECIMAL(10, 2) -- );  -- 插入一些示例數據 -- INSERT INTO sales (sale_id, product_category, sale_date, amount) VALUES -- (1, 'Electronics', '2023-01-01', 100.00), -- (2, 'Books', '2023-01-02', 50.00), -- (3, 'Electronics', '2023-01-03', 150.00), -- (4, 'Books', '2023-01-04', 75.00), -- (5, 'Electronics', '2023-01-05', 200.00), -- (6, 'Clothing', '2023-01-06', 120.00);  select     sale_id,     product_category,     sale_date,     amount,     SUM(amount) OVER (PARTITION BY product_category) AS total_category_sales FROM     sales;

這段 SQL 會為每一筆銷售記錄都計算出其所屬產品類別的總銷售額。你可以看到，即使是同一類別的多筆銷售，它們各自的行都會被保留，但 total_category_sales 列的值對于同一類別是相同的。這在分析時非常有用，比如你想計算每筆銷售占其所在類別總銷售的百分比，就非常方便了。

當然，OVER() 子句里還可以加入 ORDER BY，這會使得 SUM 成為一個“滾動求和”或“累計求和”。例如，如果你想看每個產品類別每天的累計銷售額：

SELECT     sale_id,     product_category,     sale_date,     amount,     SUM(amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_category_sales FROM     sales;

這里的 ORDER BY sale_date 意味著 SUM(amount) 會按照 sale_date 的順序，在每個 product_category 內部進行累加。這比你手動寫子查詢或者用游標去實現累計求和，效率和代碼簡潔度都高出不止一個檔次。

SUM() OVER() 與 GROUP BY 的核心區別是什么？

這是我第一次接觸 SUM() OVER() 時，腦子里蹦出的第一個問題。直觀感受上，它們都涉及“求和”，但實際使用場景和結果卻大相徑庭。

GROUP BY 是一種聚合操作，它的核心目的是將多行數據“壓縮”成少量的分組行，每個分組行代表了原數據中符合某個條件的聚合結果。例如，SELECT product_category, SUM(amount) FROM sales GROUP BY product_category; 這條語句，會把所有 ‘Electronics’ 類的銷售記錄合并成一行，顯示 ‘Electronics’ 的總銷售額。原始的 sale_id、sale_date 等明細信息，在 GROUP BY 結果中是無法直接看到的。它改變了結果集的行數，通常是減少行數。

而 SUM() OVER()，作為窗口函數，它的哲學完全不同。它在計算聚合值時，不會“折疊”你的數據行。它就像給你的數據集打開了一個“窗口”，在這個窗口里進行計算，然后把計算結果“貼”回每一行原始數據旁邊。這意味著，無論你如何定義你的 PARTITION BY 或 ORDER BY，最終查詢返回的行數與你原始表的行數（在沒有 WHERE 條件過濾的情況下）是一致的。每一行都有其原始的明細數據，同時附帶了在特定“窗口”內計算出的聚合值。

所以，核心區別在于：

• 行數變化： GROUP BY 減少行數；SUM() OVER() 保持行數不變。
• 數據粒度： GROUP BY 聚合后丟失明細；SUM() OVER() 保留明細。
• 應用場景： GROUP BY 用于獲取分組匯總結果；SUM() OVER() 用于在保留明細的同時，獲取基于特定上下文（窗口）的聚合值，常用于排名、累計、占比等分析。

理解了這一點，你就能在不同場景下，選擇最合適的工具。有時候，我甚至會先用 SUM() OVER() 得到每行的上下文聚合值，再對結果進行 GROUP BY，實現更復雜的二次聚合。

如何在 SQL 中利用 SUM() OVER() 實現復雜分組求和場景？

當我們談論復雜場景，通常意味著不僅僅是簡單的按一列分組求和。SUM() OVER() 的強大之處在于它對 PARTITION BY 和 ORDER BY 的靈活運用，以及結合窗口幀（ROWS BETWEEN 或 RANGE BETWEEN）。

一個常見的復雜場景是計算“移動平均”或“滾動總和”，比如在電商平臺，你可能想看每個用戶過去7天的消費總額，或者每個商品類別在特定時間段內的累計銷售額。

我們來一個稍微復雜點的例子，假設我們想計算每個產品類別，每天往前3天的累計銷售額（包括當天）。

SELECT     sale_id,     product_category,     sale_date,     amount,     SUM(amount) OVER (         PARTITION BY product_category         ORDER BY sale_date         ROWS BETWEEN 3 PRECEDING AND CURRENT ROW     ) AS rolling_3_day_sales_in_category FROM     sales ORDER BY     product_category, sale_date;

這里 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW 定義了窗口幀。它告訴 SQL，對于當前行，它的窗口包括當前行以及它前面（按 sale_date 排序）的3行。這個窗口會隨著當前行的移動而移動。如果前面不足3行，就只計算已有的行。

再比如，你可能需要計算每個產品類別中，每筆銷售占該類別總銷售額的百分比。

SELECT     sale_id,     product_category,     sale_date,     amount,     SUM(amount) OVER (PARTITION BY product_category) AS total_category_sales,     (amount / SUM(amount) OVER (PARTITION BY product_category)) * 100 AS percentage_of_category_sales FROM     sales;

這里我們兩次使用了 SUM(amount) OVER (PARTITION BY product_category)，一次是獲取類別總額，另一次是直接用于百分比計算。這種方式讓代碼非常簡潔直觀。

我個人覺得，理解 PARTITION BY 是如何定義“獨立計算單元”的關鍵。它就像把你的數據集切分成互不干擾的小塊，每個小塊里再按照 ORDER BY 進行排序，最后在每個小塊的每個“窗口”里執行聚合。一旦你掌握了這種思維，很多之前覺得棘手的報表和分析需求，都會變得迎刃而解。

使用 SUM() OVER() 可能遇到的性能問題及優化策略？

雖然 SUM() OVER() 強大且優雅，但它并非沒有代價。尤其是在處理海量數據時，性能問題是我們需要特別關注的。我曾經在處理一個上億行日志數據時，就因為窗口函數的使用不當，導致查詢時間長得令人發指。

潛在的性能瓶頸通常在于：

1. 大數據量下的排序和分區： PARTITION BY 和 ORDER BY 子句都需要對數據進行排序操作。當涉及的數據量非常大時，這個排序過程會消耗大量的內存和 CPU 資源，甚至可能導致磁盤溢出（tempdb 使用量暴增）。
2. 復雜窗口幀的計算： 像 ROWS BETWEEN 這樣的窗口幀，雖然靈活，但在某些數據庫實現中，計算起來可能比簡單的全分區聚合（不帶 ORDER BY）更耗資源。

那么，該如何優化呢？

• 索引是你的第一道防線：
  • 為 PARTITION BY 子句中使用的列創建索引。這能幫助數據庫系統更快地定位和分組數據，減少全表掃描。
  • 如果 OVER() 子句中還包含 ORDER BY，那么在 PARTITION BY 列和 ORDER BY 列上創建復合索引，并且 ORDER BY 的列放在索引的后面，效果會更好。例如，CREATE INDEX IX_Sales_Category_Date ON sales (product_category, sale_date);。這樣，數據庫在進行分區和排序時，可以直接利用索引的有序性，避免額外的排序操作。
• 縮小數據范圍： 在執行窗口函數之前，盡可能通過 WHERE 子句過濾掉不需要的數據。數據量越小，窗口函數的計算負擔就越輕。這是最直接有效的優化手段。
• 考慮數據預聚合或物化視圖： 對于那些需要頻繁查詢且計算復雜的窗口函數結果，如果數據更新頻率不高，可以考慮預先計算好結果并存儲在一個新的表中（即數據預聚合），或者創建物化視圖（Materialized View）。這樣，用戶查詢時直接從預計算好的結果中讀取，大大提升查詢速度。
• 優化 SQL 語句結構：
  • 避免在 OVER() 子句中進行復雜的表達式計算，盡量將計算移到 SELECT 列表之外，或者先計算好再作為列使用。
  • 如果可以，將多個獨立的窗口函數拆分為 CTE (Common Table Expressions) 或子查詢，有時可以幫助優化器更好地理解和執行查詢計劃，但也要注意過度拆分可能帶來的復雜性。
• 理解數據庫的執行計劃： 學習如何查看和理解你所用數據庫的執行計劃（如 SQL Server 的 Execution Plan，postgresql 的 EXPLaiN ANALYZE）。通過執行計劃，你可以看到哪個操作是性能瓶頸，是排序、掃描還是其他步驟，從而有針對性地進行優化。

記住，沒有銀彈。最好的優化策略往往是根據具體的數據量、查詢頻率和業務需求，進行權衡和實驗。但通常情況下，索引和數據過濾是見效最快、最值得優先考慮的手段。