1. 從業(yè)務(wù)場(chǎng)景說起為什么需要“逐月累加”在數(shù)據(jù)分析和報(bào)表開發(fā)中我們經(jīng)常會(huì)遇到一類需求不僅要看每個(gè)月的獨(dú)立業(yè)績(jī)還要看截止到某個(gè)月份的累計(jì)業(yè)績(jī)。比如銷售部門需要看“截至3月底的年度累計(jì)銷售額”產(chǎn)品運(yùn)營(yíng)需要看“用戶從上線至今的月度累計(jì)留存”財(cái)務(wù)需要看“本年度各月的累計(jì)利潤(rùn)”。這種“按月統(tǒng)計(jì)并逐月累加”的需求在SQL中通常被稱為“Running Total”或“累計(jì)求和”。在MySQL里實(shí)現(xiàn)它乍一看似乎很簡(jiǎn)單不就是先GROUP BY月份然后再把前面的月份數(shù)據(jù)加起來嗎但當(dāng)你真正動(dòng)手去寫尤其是在處理海量數(shù)據(jù)、考慮性能、處理空月份或者需要多維度組合統(tǒng)計(jì)時(shí)就會(huì)發(fā)現(xiàn)里面有不少門道。不同的寫法在邏輯清晰度、執(zhí)行效率和場(chǎng)景適應(yīng)性上差異巨大。今天我就結(jié)合自己多年在報(bào)表系統(tǒng)和數(shù)據(jù)分析后臺(tái)的實(shí)戰(zhàn)經(jīng)驗(yàn)系統(tǒng)梳理一下在MySQL中實(shí)現(xiàn)按月累計(jì)統(tǒng)計(jì)的幾種典型寫法。我們會(huì)從最基礎(chǔ)的子查詢和自連接開始講到更現(xiàn)代的窗口函數(shù)最后再聊聊在特殊業(yè)務(wù)場(chǎng)景下的變量技巧和預(yù)聚合優(yōu)化思路。無論你是剛接觸SQL不久的新手還是希望優(yōu)化現(xiàn)有報(bào)表性能的老手相信都能從中找到有用的東西。2. 基礎(chǔ)數(shù)據(jù)準(zhǔn)備與問題定義在深入各種寫法之前我們先明確一下要解決的問題并構(gòu)造一個(gè)標(biāo)準(zhǔn)的數(shù)據(jù)集用于后續(xù)所有示例的演示。清晰的問題定義是寫好SQL的第一步。假設(shè)我們有一張銷售記錄表sales_records它記錄了每一筆訂單的詳細(xì)信息。為了聚焦于“按月累計(jì)”這個(gè)核心問題我們只關(guān)心其中三個(gè)字段id: 訂單唯一標(biāo)識(shí)無關(guān)緊要僅用于區(qū)分記錄。sale_amount: 銷售額這是我們要求和的數(shù)值。sale_date: 銷售日期我們需要從這個(gè)日期中提取出“年月”來進(jìn)行分組。我們的核心目標(biāo)是統(tǒng)計(jì)出每個(gè)月的總銷售額并計(jì)算出從最早有記錄的月份開始到當(dāng)前月份的累計(jì)銷售額。首先創(chuàng)建測(cè)試表并插入一些數(shù)據(jù)-- 創(chuàng)建銷售記錄表 CREATE TABLE sales_records ( id INT PRIMARY KEY AUTO_INCREMENT, sale_amount DECIMAL(10, 2) NOT NULL, sale_date DATE NOT NULL, INDEX idx_sale_date (sale_date) -- 為日期字段建立索引這對(duì)后續(xù)查詢性能至關(guān)重要 ); -- 插入示例數(shù)據(jù)覆蓋多個(gè)年份和月份并讓數(shù)據(jù)量有一定規(guī)模 INSERT INTO sales_records (sale_amount, sale_date) VALUES (100.00, 2023-01-05), (150.00, 2023-01-15), (200.00, 2023-02-10), (250.00, 2023-02-20), (300.00, 2023-03-08), (120.00, 2023-04-12), (180.00, 2023-04-25), -- 4月數(shù)據(jù)稍多 (90.00, 2023-05-30), -- 5月數(shù)據(jù)較少 -- 模擬2024年數(shù)據(jù) (400.00, 2024-01-15), (350.00, 2024-01-25), (500.00, 2024-02-05), (150.00, 2024-03-20), (250.00, 2024-03-28), -- 故意插入一些跨年數(shù)據(jù)測(cè)試邏輯是否健壯 (600.00, 2022-12-28);現(xiàn)在如果我們只做簡(jiǎn)單的月度統(tǒng)計(jì)SQL很簡(jiǎn)單SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month;查詢結(jié)果可能如下monthmonthly_amount2022-12600.002023-01250.002023-02450.002023-03300.002023-04300.002023-0590.002024-01750.002024-02500.002024-03400.00接下來我們的任務(wù)就是在這個(gè)結(jié)果的基礎(chǔ)上新增一列cumulative_amount它應(yīng)該是這樣計(jì)算的2022-12: 600.00 (第一個(gè)月累計(jì)就是本月)2023-01: 600.00 250.00 850.002023-02: 850.00 450.00 1300.00... 以此類推。下面我們就開始逐一拆解實(shí)現(xiàn)這個(gè)目標(biāo)的幾種方法。3. 方法一使用關(guān)聯(lián)子查詢這是最直觀、最容易理解的一種方法尤其適合SQL初學(xué)者來理解“累計(jì)”的本質(zhì)。它的核心思想是對(duì)于結(jié)果集中的每一行代表一個(gè)月份都去計(jì)算所有“日期小于等于該月份”的記錄的總和。3.1 基礎(chǔ)寫法與原理SELECT a.month, a.monthly_amount, ( SELECT SUM(b.monthly_amount) FROM ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) b WHERE b.month a.month ) AS cumulative_amount FROM ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) a ORDER BY a.month;原理解析最內(nèi)層的子查詢我們稱之為基礎(chǔ)聚合子查詢先計(jì)算出每個(gè)月的獨(dú)立銷售額monthly_amount。這個(gè)子查詢被執(zhí)行了一次生成了月度匯總的中間結(jié)果。外層查詢從這個(gè)中間結(jié)果a中取出每一行數(shù)據(jù)。對(duì)于a中的每一行關(guān)聯(lián)子查詢(SELECT SUM(b.monthly_amount) ... WHERE b.month a.month)都會(huì)執(zhí)行一次。它的作用是從同樣的月度匯總中間結(jié)果b中篩選出所有月份編號(hào)b.month小于等于當(dāng)前行月份編號(hào)a.month的記錄然后對(duì)這些記錄的monthly_amount再次求和。這個(gè)“再次求和”的結(jié)果就是截止到當(dāng)前月份的累計(jì)銷售額。注意這里為什么用b.month a.month因?yàn)閙onth字段是‘%Y-%m’格式的字符串如‘2023-01’。在字符串比較時(shí)‘2023-01’ ‘2023-02’ 是成立的這正好符合時(shí)間順序。這是該方法成立的關(guān)鍵前提。3.2 性能分析與適用場(chǎng)景這種寫法的優(yōu)點(diǎn)是邏輯極其清晰一眼就能看懂“累計(jì)”是怎么算出來的。但它有一個(gè)致命的缺點(diǎn)性能差。假設(shè)月度匯總結(jié)果有N行例如100個(gè)月那么外層查詢需要處理N行。對(duì)于外層每一行關(guān)聯(lián)子查詢都要幾乎遍歷整個(gè)月度匯總結(jié)果平均N/2行來進(jìn)行求和。總的計(jì)算復(fù)雜度大約是 O(N2)。當(dāng)N很大時(shí)比如統(tǒng)計(jì)過去10年有120個(gè)月查詢速度會(huì)急劇下降。所以這種方法的適用場(chǎng)景非常有限數(shù)據(jù)量極小比如只統(tǒng)計(jì)最近幾個(gè)月或者只是臨時(shí)在開發(fā)環(huán)境驗(yàn)證一下邏輯。邏輯驗(yàn)證與教學(xué)作為理解累計(jì)求和概念的入門示例是極好的。MySQL版本過低 8.0且無法使用變量在沒有窗口函數(shù)的舊版本中如果不想用變量變量寫法有坑后面會(huì)講這可能是一種備選但必須嚴(yán)格限制數(shù)據(jù)量。實(shí)操心得在早期的項(xiàng)目中我曾用這種方式生成過一份年度報(bào)表當(dāng)時(shí)數(shù)據(jù)只有幾十行跑起來很快。后來業(yè)務(wù)數(shù)據(jù)增長(zhǎng)到幾百行頁面加載時(shí)間就從1秒變成了10秒以上直接導(dǎo)致了超時(shí)。這是一個(gè)典型的“開發(fā)時(shí)跑得通上線后扛不住”的坑。記住只要你的月度數(shù)據(jù)可能超過100行就絕對(duì)不要在生產(chǎn)環(huán)境使用這種關(guān)聯(lián)子查詢寫法。4. 方法二使用自連接自連接是關(guān)聯(lián)子查詢的一種“展開”形式它通過將表與自身連接來顯式地表達(dá)行與行之間的關(guān)系。對(duì)于累計(jì)求和我們可以通過連接條件將“當(dāng)前月”與所有“過去月”關(guān)聯(lián)起來。4.1 通過笛卡爾積與條件過濾實(shí)現(xiàn)SELECT a.month, a.monthly_amount, SUM(b.monthly_amount) AS cumulative_amount FROM ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) a JOIN ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) b ON b.month a.month GROUP BY a.month, a.monthly_amount ORDER BY a.month;原理解析我們創(chuàng)建了兩個(gè)完全相同的月度匯總子查詢別名為a和b。通過ON b.month a.month進(jìn)行連接。這意味著對(duì)于a表中的每一行當(dāng)前月b表中所有月份小于等于它的行都會(huì)與之匹配。例如a表是‘2023-03’時(shí)b表會(huì)匹配‘2022-12’ ‘2023-01’ ‘2023-02’ ‘2023-03’。最后我們按照a.month和a.monthly_amount進(jìn)行分組并對(duì)所有匹配到的b.monthly_amount進(jìn)行求和(SUM(b.monthly_amount))從而得到累計(jì)值。4.2 與關(guān)聯(lián)子查詢的對(duì)比這種方法在邏輯上和關(guān)聯(lián)子查詢是完全等價(jià)的可以看作是它的另一種表達(dá)。但在大多數(shù)MySQL版本的實(shí)際執(zhí)行中它的性能通常比關(guān)聯(lián)子查詢還要差。為什么因?yàn)殛P(guān)聯(lián)子查詢雖然次數(shù)多但每次子查詢操作的數(shù)據(jù)集是明確的整個(gè)b表。而自連接特別是帶有不等條件()的連接很容易生成一個(gè)巨大的中間結(jié)果集笛卡爾積的過濾版這個(gè)中間結(jié)果集的行數(shù)大約是 N*(N1)/2然后再對(duì)這個(gè)巨大的集合做分組聚合對(duì)內(nèi)存和CPU都是巨大的考驗(yàn)。性能排序從差到更差關(guān)聯(lián)子查詢 自連接。一個(gè)重要的注意事項(xiàng)注意GROUP BY子句是GROUP BY a.month, a.monthly_amount。這里必須把a(bǔ).monthly_amount也加進(jìn)去。因?yàn)樵赟QL標(biāo)準(zhǔn)中SELECT列表里出現(xiàn)的非聚合列這里就是a.monthly_amount必須出現(xiàn)在GROUP BY子句中否則結(jié)果可能不確定取決于數(shù)據(jù)庫的SQL模式。雖然在某些MySQL配置下只寫a.month可能也能運(yùn)行但為了代碼的嚴(yán)謹(jǐn)性和可移植性強(qiáng)烈建議將SELECT中所有非聚合列都進(jìn)行分組。適用場(chǎng)景理論上它可以用于所有關(guān)聯(lián)子查詢適用的場(chǎng)景。但在實(shí)踐中除非有特殊原因比如某些古老數(shù)據(jù)庫優(yōu)化器對(duì)自連接有神秘優(yōu)化否則不推薦使用。它既沒有關(guān)聯(lián)子查詢直觀性能又更差屬于“兩頭不討好”的寫法。5. 方法三使用用戶變量在MySQL 8.0引入窗口函數(shù)之前用戶變量是高性能實(shí)現(xiàn)累計(jì)求和的主流“黑科技”。它的思路是模仿程序中的循環(huán)按順序遍歷排好序的月度數(shù)據(jù)用一個(gè)變量來保存運(yùn)行中的累計(jì)值并逐行輸出。5.1 經(jīng)典變量累加寫法SELECT month, monthly_amount, cumulative : cumulative monthly_amount AS cumulative_amount FROM ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month -- 排序至關(guān)重要 ) t CROSS JOIN (SELECT cumulative : 0) vars ORDER BY month;原理解析CROSS JOIN (SELECT cumulative : 0) vars這是一個(gè)初始化用戶變量cumulative的技巧。通過交叉連接確保主查詢的每一行都能訪問到這個(gè)已初始化為0的變量。子查詢t首先按月份順序獲取每個(gè)月的銷售額。這里的ORDER BY month是靈魂必須保證數(shù)據(jù)按照時(shí)間順序處理累計(jì)才有意義。主查詢SELECT對(duì)于t中的每一行按順序執(zhí)行cumulative : cumulative monthly_amount。這是一個(gè)賦值表達(dá)式它先計(jì)算當(dāng)前變量值加上本月銷售額然后將結(jié)果賦回給cumulative并作為cumulative_amount列輸出。這個(gè)過程就像在遍歷一個(gè)有序數(shù)組并累加。5.2 變量的巨大隱患與嚴(yán)格使用規(guī)范變量寫法性能極高復(fù)雜度是O(N)因?yàn)樗粧呙枇伺藕眯虻脑露葦?shù)據(jù)一次。但是它充滿了陷阱在MySQL官方文檔中對(duì)用戶變量在SELECT語句中的求值順序有明確的警告指出其順序是“未定義的”。這意味著即使你寫了ORDER BYMySQL優(yōu)化器也可能在最終組合結(jié)果集之前以它認(rèn)為更優(yōu)的順序來求值cumulative從而導(dǎo)致累計(jì)結(jié)果錯(cuò)亂。雖然在很多簡(jiǎn)單查詢中它“看起來”工作正常但一旦查詢變得復(fù)雜例如包含JOIN、UNION或子查詢或者M(jìn)ySQL版本/優(yōu)化器策略發(fā)生變化結(jié)果就可能出錯(cuò)。安全使用變量的“鐵律”如果一定要用必須遵循最保守、最明確的寫法將計(jì)算過程完全封裝在一個(gè)確定順序的派生表中SELECT month, monthly_amount, cumulative : cumulative monthly_amount AS cumulative_amount FROM ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month ) t, (SELECT cumulative : 0) vars;注意這里使用了老式的逗號(hào)連接語法,它和CROSS JOIN是等價(jià)的。關(guān)鍵點(diǎn)在于變量初始化和累加計(jì)算必須在同一個(gè)查詢層級(jí)、同一條語句中完成避免優(yōu)化器打亂順序。即便如此仍然不推薦在新項(xiàng)目中使用。它的可讀性差維護(hù)成本高且存在潛在風(fēng)險(xiǎn)。僅在MySQL 5.7等舊版本環(huán)境中且對(duì)性能有極端要求并經(jīng)過充分測(cè)試的情況下方可謹(jǐn)慎使用。踩坑實(shí)錄我曾維護(hù)過一個(gè)舊系統(tǒng)報(bào)表SQL用了變量計(jì)算累計(jì)值一直運(yùn)行良好。后來為了優(yōu)化另一個(gè)部分給表增加了一個(gè)復(fù)合索引。就是這個(gè)索引改變了查詢的執(zhí)行計(jì)劃導(dǎo)致變量累加的順序發(fā)生了微妙變化報(bào)表數(shù)字連續(xù)幾天對(duì)不上排查了整整一天才找到這個(gè)原因。從此以后我對(duì)變量寫法敬而遠(yuǎn)之。6. 方法四使用窗口函數(shù)MySQL 8.0 終于引入了標(biāo)準(zhǔn)的窗口函數(shù)這徹底改變了復(fù)雜報(bào)表SQL的寫法。對(duì)于累計(jì)求和我們可以使用SUM(...) OVER (ORDER BY ...)這種簡(jiǎn)潔、強(qiáng)大且標(biāo)準(zhǔn)的方式。6.1 基礎(chǔ)窗口函數(shù)寫法SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount, SUM(SUM(sale_amount)) OVER ( ORDER BY DATE_FORMAT(sale_date, %Y-%m) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month;原理解析SUM(sale_amount) ... GROUP BY ...這部分和之前一樣先計(jì)算每個(gè)月的獨(dú)立銷售額。SUM(SUM(sale_amount)) OVER (...)這是窗口函數(shù)的核心。外層的SUM()是一個(gè)窗口聚合函數(shù)它不是在分組后計(jì)算一次而是為每一行計(jì)算一個(gè)值。它的參數(shù)是內(nèi)層的SUM(sale_amount)也就是每月的銷售額。OVER子句定義了窗口的范圍ORDER BY DATE_FORMAT(sale_date, %Y-%m)指定了數(shù)據(jù)按月份排序。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW指定了窗口的框架從結(jié)果集的第一行UNBOUNDED PRECEDING到當(dāng)前行CURRENT ROW。這個(gè)框架內(nèi)的所有行的monthly_amount值將被用于這次窗口求和計(jì)算。因此對(duì)于每一行cumulative_amount的計(jì)算方式就是從最早月份開始到當(dāng)前月份為止所有月份的monthly_amount之和。完美實(shí)現(xiàn)了累計(jì)。6.2 窗口函數(shù)的優(yōu)勢(shì)與細(xì)節(jié)探討優(yōu)勢(shì)聲明式易讀易維護(hù)你直接告訴數(shù)據(jù)庫“我要從開頭累加到當(dāng)前行”而不是教數(shù)據(jù)庫如何一步步去連接或循環(huán)。意圖清晰。高性能MySQL優(yōu)化器會(huì)對(duì)窗口函數(shù)進(jìn)行專門優(yōu)化通常比關(guān)聯(lián)子查詢和自連接快幾個(gè)數(shù)量級(jí)與變量寫法性能相當(dāng)甚至更優(yōu)且沒有變量的風(fēng)險(xiǎn)。標(biāo)準(zhǔn)SQL這是ANSI SQL標(biāo)準(zhǔn)語法可移植性強(qiáng)。功能強(qiáng)大窗口函數(shù)不止能做累計(jì)求和還能做移動(dòng)平均、排名、前后行對(duì)比等學(xué)會(huì)這一個(gè)解決一大片問題。關(guān)于窗口框架ROWS BETWEEN ...在上面的例子中我們顯式指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。實(shí)際上對(duì)于SUM/AVG等聚合窗口函數(shù)當(dāng)OVER子句中只有ORDER BY而沒有指定框架時(shí)默認(rèn)的框架就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。RANGE和ROWS在處理并列值同一個(gè)月有多條匯總記錄在我們場(chǎng)景里不會(huì)因?yàn)镚ROUP BY了時(shí)有所不同。為了絕對(duì)清晰和避免歧義尤其是在處理金額、數(shù)量等需要精確累計(jì)的場(chǎng)景我建議總是顯式地寫上ROWS BETWEEN ...框架。因此上面的SQL可以簡(jiǎn)化為依賴默認(rèn)框架但更推薦顯式寫法-- 簡(jiǎn)化寫法依賴默認(rèn)框架 SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount, SUM(SUM(sale_amount)) OVER (ORDER BY DATE_FORMAT(sale_date, %Y-%m)) AS cumulative_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month;處理跨年或多維度累計(jì)窗口函數(shù)的強(qiáng)大之處在于可以輕松處理復(fù)雜需求。比如我們想要每年重新開始累計(jì)即年度累計(jì)只需要在OVER子句中加入PARTITION BYSELECT YEAR(sale_date) AS year, DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount, SUM(SUM(sale_amount)) OVER ( PARTITION BY YEAR(sale_date) -- 按年分區(qū)每年獨(dú)立累計(jì) ORDER BY DATE_FORMAT(sale_date, %Y-%m) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount_in_year FROM sales_records GROUP BY YEAR(sale_date), DATE_FORMAT(sale_date, %Y-%m) ORDER BY year, month;適用場(chǎng)景只要你的MySQL是8.0或以上版本窗口函數(shù)就是實(shí)現(xiàn)累計(jì)統(tǒng)計(jì)的首選和唯一推薦方案。它平衡了性能、可讀性、安全性和功能性。7. 方法五使用CTE與窗口函數(shù)組合公共表表達(dá)式本身不提供新的累計(jì)計(jì)算能力但它能讓復(fù)雜的窗口函數(shù)查詢變得更加清晰、易于調(diào)試和復(fù)用。特別是當(dāng)你的累計(jì)邏輯需要基于一個(gè)已經(jīng)比較復(fù)雜的查詢結(jié)果時(shí)CTE的優(yōu)勢(shì)就體現(xiàn)出來了。7.1 利用CTE增強(qiáng)可讀性回顧一下方法四中直接使用的窗口函數(shù)SUM(SUM(sale_amount))這種嵌套聚合可能讓一些人覺得有點(diǎn)繞。我們可以用CTE將其分步拆解WITH monthly_sales AS ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) SELECT month, monthly_amount, SUM(monthly_amount) OVER ( ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM monthly_sales ORDER BY month;原理解析WITH monthly_sales AS (...)定義了一個(gè)名為monthly_sales的CTE。這個(gè)CTE就像一個(gè)臨時(shí)的視圖它只做一件事——計(jì)算每個(gè)月的銷售額。這一步邏輯獨(dú)立且清晰。主查詢直接從monthly_sales這個(gè)“干凈”的中間結(jié)果中選取數(shù)據(jù)。在主查詢中我們使用窗口函數(shù)SUM(monthly_amount) OVER (...)進(jìn)行累計(jì)。因?yàn)閿?shù)據(jù)源已經(jīng)是聚合好的月度數(shù)據(jù)所以窗口函數(shù)直接對(duì)monthly_amount列操作即可不再需要嵌套聚合邏輯更直白。7.2 CTE在復(fù)雜累計(jì)場(chǎng)景下的威力CTE的真正價(jià)值體現(xiàn)在多步驟、多層次的復(fù)雜統(tǒng)計(jì)中。假設(shè)我們有一個(gè)更變態(tài)的需求先按銷售員和月份統(tǒng)計(jì)銷售額然后計(jì)算每個(gè)銷售員自己月度銷售額的累計(jì)最后再列出所有累計(jì)額超過10萬的記錄。不用CTE的寫法會(huì)非常嵌套和混亂。而用CTE可以寫成WITH salesperson_monthly AS ( SELECT salesperson_id, DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY salesperson_id, DATE_FORMAT(sale_date, %Y-%m) ), salesperson_cumulative AS ( SELECT salesperson_id, month, monthly_amount, SUM(monthly_amount) OVER ( PARTITION BY salesperson_id ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS personal_cumulative FROM salesperson_monthly ) SELECT * FROM salesperson_cumulative WHERE personal_cumulative 100000 ORDER BY salesperson_id, month;通過兩個(gè)CTE我們將問題分解成了三個(gè)清晰的步驟salesperson_monthly: 計(jì)算每人每月銷售額。salesperson_cumulative: 基于上一步計(jì)算每人各自的累計(jì)額。主查詢從累計(jì)結(jié)果中篩選。這種寫法不僅易于編寫和閱讀也更易于調(diào)試。你可以單獨(dú)運(yùn)行每一個(gè)CTE來驗(yàn)證中間結(jié)果。適用場(chǎng)景查詢邏輯復(fù)雜當(dāng)累計(jì)計(jì)算需要基于一個(gè)多表JOIN、多層過濾或復(fù)雜聚合的結(jié)果時(shí)。需要代碼復(fù)用同一個(gè)中間結(jié)果如月度匯總可能被后續(xù)多個(gè)查詢用到。追求代碼清晰度和可維護(hù)性對(duì)于團(tuán)隊(duì)協(xié)作或長(zhǎng)期維護(hù)的項(xiàng)目清晰的邏輯分層至關(guān)重要。實(shí)操心得在處理一個(gè)涉及用戶行為鏈路的漏斗分析報(bào)表時(shí)我需要先計(jì)算每個(gè)步驟的日UV然后計(jì)算步驟間的轉(zhuǎn)化率最后再計(jì)算轉(zhuǎn)化率的7日移動(dòng)平均。如果不用CTE一條SQL會(huì)寫成“俄羅斯套娃”根本沒法維護(hù)。我果斷使用了三層CTE每一層只做一個(gè)明確的轉(zhuǎn)換最后主查詢簡(jiǎn)單明了。后來需求變更只需要改其中一個(gè)CTE的邏輯非常方便。CTE是編寫復(fù)雜分析SQL的“最佳伴侶”。8. 高級(jí)話題性能優(yōu)化與邊緣情況處理掌握了核心寫法我們還需要關(guān)注生產(chǎn)環(huán)境中可能遇到的實(shí)際問題數(shù)據(jù)量大了怎么辦月份不連續(xù)怎么辦如何應(yīng)對(duì)更復(fù)雜的業(yè)務(wù)邏輯8.1 面對(duì)海量數(shù)據(jù)的優(yōu)化策略當(dāng)原始表sales_records有上億行記錄時(shí)即使使用窗口函數(shù)直接GROUP BY DATE_FORMAT(sale_date, %Y-%m)也可能很慢因?yàn)樾枰頀呙璨⒂?jì)算哈希聚合。策略一利用索引與預(yù)聚合最好的優(yōu)化是從數(shù)據(jù)源頭減少計(jì)算量。確保sale_date上有索引這能加速分組和排序。對(duì)于我們的查詢一個(gè)(sale_date, sale_amount)的復(fù)合索引可能效果更好因?yàn)樗饕采w了查詢所需的所有列。使用預(yù)聚合表如果實(shí)時(shí)性要求不是秒級(jí)可以建立一張“月度匯總表”在每天或每小時(shí)通過定時(shí)任務(wù)如Event或調(diào)度系統(tǒng)更新。這樣累計(jì)查詢就直接基于這張只有幾百行的小表進(jìn)行性能飛升。-- 創(chuàng)建月度匯總表 CREATE TABLE sales_monthly_summary ( year_month CHAR(7) PRIMARY KEY, -- 格式‘YYYY-MM’ total_amount DECIMAL(15, 2) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- 定時(shí)任務(wù)更新語句增量更新示例 INSERT INTO sales_monthly_summary (year_month, total_amount) SELECT DATE_FORMAT(sale_date, %Y-%m), SUM(sale_amount) FROM sales_records WHERE sale_date CURDATE() - INTERVAL 1 DAY -- 僅處理新增數(shù)據(jù) GROUP BY DATE_FORMAT(sale_date, %Y-%m) ON DUPLICATE KEY UPDATE total_amount VALUES(total_amount), last_updated CURRENT_TIMESTAMP; -- 基于預(yù)聚合表的累計(jì)查詢 SELECT year_month AS month, total_amount AS monthly_amount, SUM(total_amount) OVER (ORDER BY year_month) AS cumulative_amount FROM sales_monthly_summary ORDER BY year_month;策略二分階段計(jì)算如果必須實(shí)時(shí)查詢大表可以嘗試將窗口函數(shù)的計(jì)算拆解。先通過子查詢或CTE利用索引快速完成月度聚合這個(gè)階段數(shù)據(jù)量已大幅減少再將這個(gè)小型結(jié)果集交給窗口函數(shù)處理。我們之前寫的CTE版本其實(shí)就隱含了這種思想。8.2 處理缺失月份與自定義起始點(diǎn)業(yè)務(wù)數(shù)據(jù)可能有月份缺失如某個(gè)月沒有任何銷售。我們的查詢結(jié)果中就不會(huì)出現(xiàn)這個(gè)月導(dǎo)致累計(jì)曲線在時(shí)間軸上“跳躍”。有時(shí)業(yè)務(wù)方希望看到連續(xù)的月份即使銷售額為0。生成連續(xù)月份序列我們可以利用遞歸CTEMySQL 8.0或數(shù)字輔助表生成一個(gè)連續(xù)的日期序列再左聯(lián)我們的銷售數(shù)據(jù)。WITH RECURSIVE date_series AS ( SELECT DATE(2022-01-01) AS month_start -- 起始日期 UNION ALL SELECT DATE_ADD(month_start, INTERVAL 1 MONTH) FROM date_series WHERE month_start CURDATE() -- 結(jié)束日期 ), monthly_sales AS ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS year_month, SUM(sale_amount) AS monthly_amount FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) SELECT DATE_FORMAT(ds.month_start, %Y-%m) AS month, COALESCE(ms.monthly_amount, 0) AS monthly_amount, -- 處理空值 SUM(COALESCE(ms.monthly_amount, 0)) OVER ( ORDER BY ds.month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM date_series ds LEFT JOIN monthly_sales ms ON DATE_FORMAT(ds.month_start, %Y-%m) ms.year_month ORDER BY ds.month_start;自定義累計(jì)起始點(diǎn)有時(shí)累計(jì)不是從最早數(shù)據(jù)開始而是從財(cái)年開始、從活動(dòng)開始日等。這可以通過在窗口函數(shù)中調(diào)整ORDER BY和框架的起始點(diǎn)來實(shí)現(xiàn)但更簡(jiǎn)單的方法是在生成基礎(chǔ)數(shù)據(jù)時(shí)進(jìn)行過濾。-- 只計(jì)算從‘2023-04-01’開始的累計(jì) WITH filtered_sales AS ( SELECT sale_date, sale_amount FROM sales_records WHERE sale_date 2023-04-01 ), monthly_sales AS ( SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount FROM filtered_sales GROUP BY DATE_FORMAT(sale_date, %Y-%m) ) SELECT ... -- 窗口函數(shù)累計(jì)邏輯同上8.3 多維度組合累計(jì)與條件累計(jì)累計(jì)不僅可以對(duì)“所有歷史”進(jìn)行還可以在多個(gè)維度上靈活組合。按維度分區(qū)累計(jì)前面已經(jīng)提到過PARTITION BY它可以實(shí)現(xiàn)按銷售員、按產(chǎn)品類別、按地區(qū)等多個(gè)維度的獨(dú)立累計(jì)。SELECT sales_region, DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount, SUM(SUM(sale_amount)) OVER ( PARTITION BY sales_region -- 每個(gè)區(qū)域獨(dú)立累計(jì) ORDER BY DATE_FORMAT(sale_date, %Y-%m) ) AS region_cumulative, SUM(SUM(sale_amount)) OVER ( -- 不分區(qū)全局累計(jì) ORDER BY DATE_FORMAT(sale_date, %Y-%m) ) AS global_cumulative FROM sales_records GROUP BY sales_region, DATE_FORMAT(sale_date, %Y-%m) ORDER BY sales_region, month;條件累計(jì)例如我們只想累計(jì)“銷售額大于100的月份”。這需要在窗口函數(shù)內(nèi)部使用條件聚合。SELECT DATE_FORMAT(sale_date, %Y-%m) AS month, SUM(sale_amount) AS monthly_amount, SUM( CASE WHEN SUM(sale_amount) 100 THEN SUM(sale_amount) ELSE 0 END ) OVER ( ORDER BY DATE_FORMAT(sale_date, %Y-%m) ) AS cumulative_amount_gt_100 FROM sales_records GROUP BY DATE_FORMAT(sale_date, %Y-%m) ORDER BY month;注意這里CASE WHEN判斷的是內(nèi)層聚合函數(shù)SUM(sale_amount)的結(jié)果即月銷售額然后窗口函數(shù)SUM再對(duì)這個(gè)條件結(jié)果進(jìn)行累計(jì)。這種嵌套聚合需要仔細(xì)理解執(zhí)行順序。9. 總結(jié)與最終選擇建議走過了從古老低效的關(guān)聯(lián)子查詢到危險(xiǎn)但快速的變量再到現(xiàn)代優(yōu)雅的窗口函數(shù)我們看到了實(shí)現(xiàn)同一個(gè)需求的技術(shù)演進(jìn)。最后我們來做個(gè)清晰的對(duì)比并給出最直接的選型建議。方法對(duì)比一覽表特性/方法關(guān)聯(lián)子查詢自連接用戶變量窗口函數(shù) (MySQL 8.0)CTE 窗口函數(shù)邏輯清晰度高中低高極高代碼可讀性中低低高高執(zhí)行性能差 (O(N2))極差 (O(N2))優(yōu) (O(N))優(yōu) (O(N))優(yōu) (O(N))結(jié)果確定性高高低 (有風(fēng)險(xiǎn))高高SQL標(biāo)準(zhǔn)是是否 (MySQL特性)是 (SQL:2003)是 (SQL:1999/2003)功能擴(kuò)展性差差差強(qiáng)強(qiáng)推薦指數(shù)?不推薦?? (僅限舊版本)?????????? (復(fù)雜時(shí))最終選擇建議一句話版如果你的MySQL版本 8.0無腦選擇【窗口函數(shù)】。如果查詢邏輯復(fù)雜用【CTE 窗口函數(shù)】拆解。詳細(xì)決策路徑MySQL 8.0 或更新版本簡(jiǎn)單累計(jì)直接使用SUM(...) OVER (ORDER BY ...)。這是標(biāo)準(zhǔn)、高效、安全的首選。復(fù)雜邏輯或多步驟使用CTE將中間結(jié)果命名化再結(jié)合窗口函數(shù)。這大幅提升了代碼的可讀性、可調(diào)試性和可維護(hù)性。MySQL 5.7 或更舊版本首要任務(wù)強(qiáng)烈建議推動(dòng)升級(jí)到MySQL 8.0。窗口函數(shù)帶來的開發(fā)效率和運(yùn)行性能提升是全方位的。無法升級(jí)時(shí)如果數(shù)據(jù)量很小比如后臺(tái)管理頁面查看少量數(shù)據(jù)可以考慮使用關(guān)聯(lián)子查詢但務(wù)必清楚其性能瓶頸。如果對(duì)性能有苛刻要求且能承擔(dān)潛在風(fēng)險(xiǎn)可以極其謹(jǐn)慎地使用用戶變量并必須遵循“單語句內(nèi)初始化與計(jì)算”的鐵律并進(jìn)行充分測(cè)試。任何表結(jié)構(gòu)、索引或優(yōu)化器版本的變動(dòng)都可能引入風(fēng)險(xiǎn)。探索是否能在應(yīng)用層Java, Python等進(jìn)行累計(jì)計(jì)算將復(fù)雜的累計(jì)邏輯從數(shù)據(jù)庫轉(zhuǎn)移到業(yè)務(wù)代碼中。個(gè)人經(jīng)驗(yàn)與避坑指南索引是基礎(chǔ)無論用哪種方法在sale_date以及分組字段上建立合適的索引是保證性能的底線。理解業(yè)務(wù)邊界累計(jì)是從何時(shí)開始是否按財(cái)年重置是否包含未發(fā)生的未來月份是否處理數(shù)據(jù)缺失在寫SQL前務(wù)必和業(yè)務(wù)方確認(rèn)清楚這些邊界條件。測(cè)試空數(shù)據(jù)你的SQL在沒有任何銷售數(shù)據(jù)的月份或者整張表為空時(shí)會(huì)返回什么是空結(jié)果集還是0確保行為符合預(yù)期。窗口函數(shù)框架是細(xì)節(jié)記住ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW這個(gè)框架顯式寫出它避免因默認(rèn)行為 (RANGE) 在遇到相同排序值時(shí)產(chǎn)生的意外情況。按月累計(jì)求和是一個(gè)經(jīng)典的SQL問題它像一把鑰匙打開了一扇通往更高級(jí)數(shù)據(jù)分析的大門。從最初的蠻力計(jì)算到利用變量的小聰明再到窗口函數(shù)的降維打擊我們不僅看到了SQL語法的發(fā)展更看到了思維模式的轉(zhuǎn)變從“如何命令數(shù)據(jù)庫一步步操作”到“如何聲明我想要的結(jié)果”。掌握窗口函數(shù)無疑是現(xiàn)代數(shù)據(jù)分析師和后臺(tái)開發(fā)工程師的一項(xiàng)核心技能。