1. 從業(yè)務(wù)場(chǎng)景說(shuō)起為什么需要“逐月累加”在數(shù)據(jù)分析和報(bào)表開(kāi)發(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ù)加起來(lái)嗎但當(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ǔ)的子查詢和自連接開(kāi)始講到更現(xiàn)代的窗口函數(shù)最后再聊聊在特殊業(yè)務(wù)場(chǎng)景下的變量技巧和預(yù)聚合優(yōu)化思路。無(wú)論你是剛接觸SQL不久的新手還是希望優(yōu)化現(xiàn)有報(bào)表性能的老手相信都能從中找到有用的東西。2. 基礎(chǔ)數(shù)據(jù)準(zhǔn)備與問(wèn)題定義在深入各種寫法之前我們先明確一下要解決的問(wèn)題并構(gòu)造一個(gè)標(biāo)準(zhǔn)的數(shù)據(jù)集用于后續(xù)所有示例的演示。清晰的問(wèn)題定義是寫好SQL的第一步。假設(shè)我們有一張銷售記錄表sales_records它記錄了每一筆訂單的詳細(xì)信息。為了聚焦于“按月累計(jì)”這個(gè)核心問(wèn)題我們只關(guān)心其中三個(gè)字段id: 訂單唯一標(biāo)識(shí)無(wú)關(guān)緊要僅用于區(qū)分記錄。sale_amount: 銷售額這是我們要求和的數(shù)值。sale_date: 銷售日期我們需要從這個(gè)日期中提取出“年月”來(lái)進(jìn)行分組。我們的核心目標(biāo)是統(tǒng)計(jì)出每個(gè)月的總銷售額并計(jì)算出從最早有記錄的月份開(kāi)始到當(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接下來(lái)我們的任務(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... 以此類推。下面我們就開(kāi)始逐一拆解實(shí)現(xiàn)這個(gè)目標(biāo)的幾種方法。3. 方法一使用關(guān)聯(lián)子查詢這是最直觀、最容易理解的一種方法尤其適合SQL初學(xué)者來(lái)理解“累計(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ì)”是怎么算出來(lái)的。但它有一個(gè)致命的缺點(diǎn)性能差。假設(shè)月度匯總結(jié)果有N行例如100個(gè)月那么外層查詢需要處理N行。對(duì)于外層每一行關(guān)聯(lián)子查詢都要幾乎遍歷整個(gè)月度匯總結(jié)果平均N/2行來(lái)進(jìn)行求和?？偟挠?jì)算復(fù)雜度大約是 O(N2)。當(dāng)N很大時(shí)比如統(tǒng)計(jì)過(guò)去10年有120個(gè)月查詢速度會(huì)急劇下降。所以這種方法的適用場(chǎng)景非常有限數(shù)據(jù)量極小比如只統(tǒng)計(jì)最近幾個(gè)月或者只是臨時(shí)在開(kāi)發(fā)環(huán)境驗(yàn)證一下邏輯。邏輯驗(yàn)證與教學(xué)作為理解累計(jì)求和概念的入門示例是極好的。MySQL版本過(guò)低 8.0且無(wú)法使用變量在沒(méi)有窗口函數(shù)的舊版本中如果不想用變量變量寫法有坑后面會(huì)講這可能是一種備選但必須嚴(yán)格限制數(shù)據(jù)量。實(shí)操心得在早期的項(xiàng)目中我曾用這種方式生成過(guò)一份年度報(bào)表當(dāng)時(shí)數(shù)據(jù)只有幾十行跑起來(lái)很快。后來(lái)業(yè)務(wù)數(shù)據(jù)增長(zhǎng)到幾百行頁(yè)面加載時(shí)間就從1秒變成了10秒以上直接導(dǎo)致了超時(shí)。這是一個(gè)典型的“開(kāi)發(fā)時(shí)跑得通上線后扛不住”的坑。記住只要你的月度數(shù)據(jù)可能超過(guò)100行就絕對(duì)不要在生產(chǎn)環(huán)境使用這種關(guān)聯(lián)子查詢寫法。4. 方法二使用自連接自連接是關(guān)聯(lián)子查詢的一種“展開(kāi)”形式它通過(guò)將表與自身連接來(lái)顯式地表達(dá)行與行之間的關(guān)系。對(duì)于累計(jì)求和我們可以通過(guò)連接條件將“當(dāng)前月”與所有“過(guò)去月”關(guān)聯(lián)起來(lái)。4.1 通過(guò)笛卡爾積與條件過(guò)濾實(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。通過(guò)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é)果集笛卡爾積的過(guò)濾版這個(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ù)庫(kù)的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ù)庫(kù)優(yōu)化器對(duì)自連接有神秘優(yōu)化否則不推薦使用。它既沒(méi)有關(guān)聯(lián)子查詢直觀性能又更差屬于“兩頭不討好”的寫法。5. 方法三使用用戶變量在MySQL 8.0引入窗口函數(shù)之前用戶變量是高性能實(shí)現(xiàn)累計(jì)求和的主流“黑科技”。它的思路是模仿程序中的循環(huán)按順序遍歷排好序的月度數(shù)據(jù)用一個(gè)變量來(lái)保存運(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的技巧。通過(guò)交叉連接確保主查詢的每一行都能訪問(wèn)到這個(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è)過(guò)程就像在遍歷一個(gè)有序數(shù)組并累加。5.2 變量的巨大隱患與嚴(yán)格使用規(guī)范變量寫法性能極高復(fù)雜度是O(N)因?yàn)樗粧呙枇伺藕眯虻脑露葦?shù)據(jù)一次。但是它充滿了陷阱在MySQL官方文檔中對(duì)用戶變量在SELECT語(yǔ)句中的求值順序有明確的警告指出其順序是“未定義的”。這意味著即使你寫了ORDER BYMySQL優(yōu)化器也可能在最終組合結(jié)果集之前以它認(rèn)為更優(yōu)的順序來(lái)求值cumulative從而導(dǎo)致累計(jì)結(jié)果錯(cuò)亂。雖然在很多簡(jiǎn)單查詢中它“看起來(lái)”工作正常但一旦查詢變得復(fù)雜例如包含JOIN、UNION或子查詢或者M(jìn)ySQL版本/優(yōu)化器策略發(fā)生變化結(jié)果就可能出錯(cuò)。安全使用變量的“鐵律”如果一定要用必須遵循最保守、最明確的寫法將計(jì)算過(guò)程完全封裝在一個(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)連接語(yǔ)法,它和CROSS JOIN是等價(jià)的。關(guān)鍵點(diǎn)在于變量初始化和累加計(jì)算必須在同一個(gè)查詢層級(jí)、同一條語(yǔ)句中完成避免優(yōu)化器打亂順序。即便如此仍然不推薦在新項(xiàng)目中使用。它的可讀性差維護(hù)成本高且存在潛在風(fēng)險(xiǎn)。僅在MySQL 5.7等舊版本環(huán)境中且對(duì)性能有極端要求并經(jīng)過(guò)充分測(cè)試的情況下方可謹(jǐn)慎使用。踩坑實(shí)錄我曾維護(hù)過(guò)一個(gè)舊系統(tǒng)報(bào)表SQL用了變量計(jì)算累計(jì)值一直運(yùn)行良好。后來(lái)為了優(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ì)算方式就是從最早月份開(kāi)始到當(dāng)前月份為止所有月份的monthly_amount之和。完美實(shí)現(xiàn)了累計(jì)。6.2 窗口函數(shù)的優(yōu)勢(shì)與細(xì)節(jié)探討優(yōu)勢(shì)聲明式易讀易維護(hù)你直接告訴數(shù)據(jù)庫(kù)“我要從開(kāi)頭累加到當(dāng)前行”而不是教數(shù)據(jù)庫(kù)如何一步步去連接或循環(huán)。意圖清晰。高性能MySQL優(yōu)化器會(huì)對(duì)窗口函數(shù)進(jìn)行專門優(yōu)化通常比關(guān)聯(lián)子查詢和自連接快幾個(gè)數(shù)量級(jí)與變量寫法性能相當(dāng)甚至更優(yōu)且沒(méi)有變量的風(fēng)險(xiǎn)。標(biāo)準(zhǔn)SQL這是ANSI SQL標(biāo)準(zhǔn)語(yǔ)法可移植性強(qiáng)。功能強(qiáng)大窗口函數(shù)不止能做累計(jì)求和還能做移動(dòng)平均、排名、前后行對(duì)比等學(xué)會(huì)這一個(gè)解決一大片問(wèn)題。關(guān)于窗口框架ROWS BETWEEN ...在上面的例子中我們顯式指定了ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。實(shí)際上對(duì)于SUM/AVG等聚合窗口函數(shù)當(dāng)OVER子句中只有ORDER BY而沒(méi)有指定框架時(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ù)雜需求。比如我們想要每年重新開(kāi)始累計(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)出來(lái)了。7.1 利用CTE增強(qiáng)可讀性回顧一下方法四中直接使用的窗口函數(shù)SUM(SUM(sale_amount))這種嵌套聚合可能讓一些人覺(jué)得有點(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ì)額超過(guò)10萬(wàn)的記錄。不用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;通過(guò)兩個(gè)CTE我們將問(wèn)題分解成了三個(gè)清晰的步驟salesperson_monthly: 計(jì)算每人每月銷售額。salesperson_cumulative: 基于上一步計(jì)算每人各自的累計(jì)額。主查詢從累計(jì)結(jié)果中篩選。這種寫法不僅易于編寫和閱讀也更易于調(diào)試。你可以單獨(dú)運(yùn)行每一個(gè)CTE來(lái)驗(yàn)證中間結(jié)果。適用場(chǎng)景查詢邏輯復(fù)雜當(dāng)累計(jì)計(jì)算需要基于一個(gè)多表JOIN、多層過(guò)濾或復(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ì)寫成“俄羅斯套娃”根本沒(méi)法維護(hù)。我果斷使用了三層CTE每一層只做一個(gè)明確的轉(zhuǎn)換最后主查詢簡(jiǎn)單明了。后來(lái)需求變更只需要改其中一個(gè)CTE的邏輯非常方便。CTE是編寫復(fù)雜分析SQL的“最佳伴侶”。8. 高級(jí)話題性能優(yōu)化與邊緣情況處理掌握了核心寫法我們還需要關(guān)注生產(chǎn)環(huán)境中可能遇到的實(shí)際問(wèn)題數(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í)通過(guò)定時(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ù)更新語(yǔ)句增量更新示例 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ì)算拆解。先通過(guò)子查詢或CTE利用索引快速完成月度聚合這個(gè)階段數(shù)據(jù)量已大幅減少再將這個(gè)小型結(jié)果集交給窗口函數(shù)處理。我們之前寫的CTE版本其實(shí)就隱含了這種思想。8.2 處理缺失月份與自定義起始點(diǎn)業(yè)務(wù)數(shù)據(jù)可能有月份缺失如某個(gè)月沒(méi)有任何銷售。我們的查詢結(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ù)開(kāi)始而是從財(cái)年開(kāi)始、從活動(dòng)開(kāi)始日等。這可以通過(guò)在窗口函數(shù)中調(diào)整ORDER BY和框架的起始點(diǎn)來(lái)實(shí)現(xiàn)但更簡(jiǎn)單的方法是在生成基礎(chǔ)數(shù)據(jù)時(shí)進(jìn)行過(guò)濾。-- 只計(jì)算從‘2023-04-01’開(kāi)始的累計(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)提到過(guò)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ò)了從古老低效的關(guān)聯(lián)子查詢到危險(xiǎn)但快速的變量再到現(xiàn)代優(yōu)雅的窗口函數(shù)我們看到了實(shí)現(xiàn)同一個(gè)需求的技術(shù)演進(jìn)。最后我們來(lái)做個(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無(wú)腦選擇【窗口函數(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ù)帶來(lái)的開(kāi)發(fā)效率和運(yùn)行性能提升是全方位的。無(wú)法升級(jí)時(shí)如果數(shù)據(jù)量很小比如后臺(tái)管理頁(yè)面查看少量數(shù)據(jù)可以考慮使用關(guān)聯(lián)子查詢但務(wù)必清楚其性能瓶頸。如果對(duì)性能有苛刻要求且能承擔(dān)潛在風(fēng)險(xiǎn)可以極其謹(jǐn)慎地使用用戶變量并必須遵循“單語(yǔ)句內(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ù)庫(kù)轉(zhuǎn)移到業(yè)務(wù)代碼中。個(gè)人經(jīng)驗(yàn)與避坑指南索引是基礎(chǔ)無(wú)論用哪種方法在sale_date以及分組字段上建立合適的索引是保證性能的底線。理解業(yè)務(wù)邊界累計(jì)是從何時(shí)開(kāi)始是否按財(cái)年重置是否包含未發(fā)生的未來(lái)月份是否處理數(shù)據(jù)缺失在寫SQL前務(wù)必和業(yè)務(wù)方確認(rèn)清楚這些邊界條件。測(cè)試空數(shù)據(jù)你的SQL在沒(méi)有任何銷售數(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問(wèn)題它像一把鑰匙打開(kāi)了一扇通往更高級(jí)數(shù)據(jù)分析的大門。從最初的蠻力計(jì)算到利用變量的小聰明再到窗口函數(shù)的降維打擊我們不僅看到了SQL語(yǔ)法的發(fā)展更看到了思維模式的轉(zhuǎn)變從“如何命令數(shù)據(jù)庫(kù)一步步操作”到“如何聲明我想要的結(jié)果”。掌握窗口函數(shù)無(wú)疑是現(xiàn)代數(shù)據(jù)分析師和后臺(tái)開(kāi)發(fā)工程師的一項(xiàng)核心技能。