計實戰(zhàn)：從CASE WHEN到空桶補齊的完整解法與踩坑指南)
前陣子幫團隊里的同學(xué)做 SQL 面試復(fù)盤聊到一道數(shù)據(jù)庫題名字叫做“制作會話柱狀圖”。題號在某個在線題庫里排在第 1435 位表面上看是個統(tǒng)計聚合題結(jié)果不少人一上來就卡住。問題拆開來看其實非常典型一張保存會話時長的表要求按時間區(qū)間分桶輸出每個區(qū)間的會話數(shù)量。區(qū)間是[0-5)、[5-30)、[30-60)、[60)而且哪怕某個區(qū)間一條數(shù)據(jù)都沒有也得輸出 0。這個“空桶也輸出”的要求就是整道題的分水嶺。說實話這道題我在面試候選人和幫忙查業(yè)務(wù) SQL 時都碰到過同款問題。很多人第一反應(yīng)就是CASE WHEN然后GROUP BY一跑發(fā)現(xiàn)少了行才知道 SQL 里“把連續(xù)值切成離散標簽”和“保證標簽維度完整”是兩碼事。這篇文章就圍繞這個題目展開從表結(jié)構(gòu)、邊界定義、三種解法到真實業(yè)務(wù)里的分桶實踐完整拆一遍。給準備數(shù)據(jù)庫面試的朋友也給平時天天寫報表的兄弟做個參考。1. 讀懂題目Sessions 表和四個時間桶1.1 表結(jié)構(gòu)、字段與業(yè)務(wù)含義這道題給的表結(jié)構(gòu)很簡潔一張Sessions表主要字段就是session_id和duration。用表格看就是這樣列名類型含義session_idint會話唯一編號durationint會話持續(xù)時長單位通常是分鐘題目要求的輸出很簡單把會話按照持續(xù)時間分成四個區(qū)間然后統(tǒng)計每個區(qū)間里有多少條會話記錄。最終結(jié)果要有兩列一列是區(qū)間名一列是數(shù)量?，F(xiàn)實中這張表對應(yīng)的場景非常常見一個 App 記錄用戶每次登錄的時長或者在線客服系統(tǒng)記錄每次會話的接待時長。在做產(chǎn)品分析的時候我們很少關(guān)心“平均時長是 7.3 分鐘”這種數(shù)字反而更想知道“有多少用戶連 5 分鐘都沒待夠”“有多少用戶待了超過半小時”。把連續(xù)時長切成幾個區(qū)間再數(shù)每個區(qū)間的人數(shù)這個過程就是分桶統(tǒng)計也就是柱狀圖背后的數(shù)據(jù)基礎(chǔ)。這里先提醒一句題目里duration給的是分鐘數(shù)但如果你在真實業(yè)務(wù)里遇到的數(shù)據(jù)源給的是秒、毫秒或者直接給了開始時間和結(jié)束時間兩個字段那就得先換算單位。別拿起 SQL 就寫先確認字段語義這是做數(shù)據(jù)查詢的第一步也是很多人容易忽略的一步。1.2 分桶邊界半開區(qū)間和“等于邊界歸誰”的問題四個桶的劃分規(guī)則如下桶名條件含義[0-5duration 0 且 duration 5短會話不足 5 分鐘[5-30duration 5 且 duration 30中等會話5 到 30 分鐘之間[30-60duration 30 且 duration 60較長會話30 到 60 分鐘之間[60duration 60長會話超過 1 小時這種寫法叫半開區(qū)間左邊閉右邊開。為什么業(yè)界都這么設(shè)計因為可以保證每一個值只有一個歸屬不會出現(xiàn)“duration 等于 5 到底算上一個桶還是下一個桶”的爭執(zhí)。柱狀圖的橫軸刻度也不會重疊數(shù)據(jù)不會重復(fù)計算。我在真實項目里見過有人用閉區(qū)間寫結(jié)果[0-5]和[5-30]都包含 5同一個數(shù)被算進兩個區(qū)間報表里的總數(shù)和明細對不上查了半天才發(fā)現(xiàn)是邊界條件寫重了。從那以后我給自己定了個規(guī)矩凡是分桶一律用半開區(qū)間。這道題里[0-5這個寫法其實就是半開區(qū)間的標準表示左邊閉右邊開數(shù)學(xué)上很嚴謹寫 SQL 的時候也方便。另外有一個細節(jié)在CASE WHEN里寫條件時建議按照從小到大的順序判斷寫成duration 5、duration 30、duration 60這樣邏輯上最不容易出錯。因為CASE WHEN是順序匹配的只要命中了第一個條件就會進入對應(yīng)桶后面的條件根本不會判斷。如果你寫成duration 0 AND duration 5這種完整寫法也沒問題但順序判斷更簡潔也不容易漏條件。2. 核心知識點拆解這道題不是簡單 GROUP BY2.1 條件聚合的兩種打開方式這道題看起來簡單但它實際上考了三個容易被忽略的 SQL 知識點條件聚合、結(jié)果集完整性和排序控制。第一個知識點是條件聚合。條件聚合有兩種寫法。第一種是在SELECT后面用CASE WHEN給每條記錄打上桶標簽然后GROUP BY這個標簽第二種是在SELECT里用SUM(CASE WHEN ... THEN 1 ELSE 0 END)直接把符合條件的記錄數(shù)加出來不需要GROUP BY。兩種寫法都能算出結(jié)果但適用場景不太一樣。第一種寫法的問題是GROUP BY能不能直接引用SELECT里定義的別名這個在不同數(shù)據(jù)庫里行為不一樣。MySQL 和部分分支版本允許GROUP BY引用別名這是對標準 SQL 的擴展但標準 SQL 的執(zhí)行順序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY理論上在GROUP BY階段還看不到SELECT里的別名。所以有些嚴格模式或者數(shù)倉引擎會直接報錯比如我們把CASE WHEN的結(jié)果命名為bin然后寫GROUP BY bin在嚴格模式下是過不去的。穩(wěn)妥的做法是先寫個子查詢在內(nèi)層把分桶標簽用CASE WHEN算好直接作為一列輸出外層再對那一列做GROUP BY。這個習(xí)慣建議直接刻進腦子里能避免很多莫名其妙的兼容性問題。我自己過去就吃過這個虧本地 MySQL 寫得好好的換到某云數(shù)據(jù)倉庫一跑就報錯找半天原因才發(fā)現(xiàn)是GROUP BY引用別名的問題。2.2 空桶的哲學(xué)柱狀圖的橫軸必須有刻度第二個知識點是結(jié)果集完整性也就是空桶問題。直接用GROUP BY出來的結(jié)果只有那些出現(xiàn)過記錄的分組。如果測試數(shù)據(jù)里恰好沒有 30 到 60 分鐘之間的會話那么查詢結(jié)果就只有三行[30-60這個桶直接消失不見。但題目要求很明確四個區(qū)間都必須輸出缺失的區(qū)間要補 0。這個需求背后是有實際意義的柱狀圖的橫軸是一個固定的刻度體系你不能讓柱子憑空少一根否則看圖的人會以為系統(tǒng)出錯或者數(shù)據(jù)被截斷了。哪怕某個區(qū)間一根柱子都沒有刻度也得留在那里數(shù)值顯示為 0。在 SQL 里實現(xiàn)“維度補齊”套路幾乎是固定的先構(gòu)造一個包含全部區(qū)間的維度表再拿它去LEFT JOIN統(tǒng)計數(shù)據(jù)。這是做 BI 報表的人每天都要用的基本功。行業(yè)里也管這個叫“日期維度補齊”或者“類別維度補齊”本質(zhì)就是讓結(jié)果集的行數(shù)不受數(shù)據(jù)本身影響始終和定義好的維度保持一致。拿生活里的例子類比一個月有 30 天就算某天完全沒有訂單日報上也要把這天列出來銷售額寫 0。否則到了月底做匯總天數(shù)對不上數(shù)據(jù)完整性就崩了。LeetCode 這道題其實就是這個場景的簡化版只不過把日期換成了會話時長區(qū)間。2.3 時間函數(shù)不是重點單位才是重點第三個知識點也是很多人容易誤解的地方這道題雖然是數(shù)據(jù)庫分類下的題目但它考的不是時間差計算函數(shù)。duration字段已經(jīng)是一個算好的數(shù)字不需要你用DATEDIFF或者TIMESTAMPDIFF去推導(dǎo)。真正該留意的是數(shù)據(jù)單位的一致性。如果某天你的數(shù)據(jù)源給的不是分鐘而是秒、毫秒或者直接給了start_time和end_time兩個DATETIME字段你需要先做換算。比如用TIMESTAMPDIFF(SECOND, start_time, end_time) / 60.0得出帶小數(shù)的分鐘數(shù)再參與分桶邏輯。換算完了之后所有條件都基于同一個單位去判斷避免一個 SQL 里一會兒秒一會兒分鐘把自己都繞暈。順帶提一下兩個常見時間函數(shù)的區(qū)別DATEDIFF只能返回兩個日期之間相差的天數(shù)精度太低TIMESTAMPDIFF可以指定單位SECOND、MINUTE、HOUR都行。做時長類分析的時候優(yōu)先用TIMESTAMPDIFF。3. 三種可落地的解法從簡到繁3.1 解法 A先統(tǒng)計、后補維度推薦先給出我認為最標準的解法。核心思路分兩步第一步在子查詢里把每條記錄分到對應(yīng)桶然后按桶統(tǒng)計數(shù)量第二步用UNION ALL構(gòu)造一個包含全部桶名的維度表左連接統(tǒng)計結(jié)果缺失的桶用 0 補齊。SELECT b.bin, IFNULL(t.total, 0) AS total FROM ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ) b LEFT JOIN ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin, COUNT(*) AS total FROM Sessions GROUP BY bin ) t ON b.bin t.bin ORDER BY CASE b.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;拆開來看這個 SQL 的每一部分。最內(nèi)層的子查詢t做的事情是掃描Sessions表用CASE WHEN把每條記錄歸入對應(yīng)桶然后GROUP BY bin統(tǒng)計每個桶的會話數(shù)量。這里有一個問題需要注意內(nèi)層子查詢里的GROUP BY bin引用了CASE WHEN表達式產(chǎn)生的別名。在 MySQL 里這樣寫沒問題但如果想做到嚴格兼容建議再包一層子查詢把CASE WHEN的結(jié)果先作為獨立的列算出來外層再分組比如FROM ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) s GROUP BY s.bin外面的b子查詢就是標準的“維度構(gòu)造”用UNION ALL把四個桶名豎排成四行。之所以用UNION ALL而不是UNION是因為不會出現(xiàn)重復(fù)行沒必要去重UNION ALL性能也更好。左連接之后沒有數(shù)據(jù)的桶對應(yīng)的t.total就是NULL包一層IFNULL轉(zhuǎn)成 0。最后是排序。因為bin是文本如果直接ORDER BY bin會按照字典序排成[0-5、[30-60、[5-30、[60這不是柱狀圖從左到右的自然順序。所以要用CASE再映射一次手動指定順序。這個細節(jié)在題目測試用例里可能不會觸發(fā)嚴格檢查但實際做報表一定會被產(chǎn)品經(jīng)理問“為什么順序是亂的”。3.2 解法 B四個 UNION ALL邏輯最樸素第二種解法思路完全不同不分組直接用四個SELECT分別統(tǒng)計每個桶的數(shù)量再用UNION ALL拼起來SELECT [0-5 AS bin, SUM(CASE WHEN duration 5 THEN 1 ELSE 0 END) AS total FROM Sessions UNION ALL SELECT [5-30, SUM(CASE WHEN duration 5 AND duration 30 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [30-60, SUM(CASE WHEN duration 30 AND duration 60 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [60, SUM(CASE WHEN duration 60 THEN 1 ELSE 0 END) FROM Sessions;這個方案的優(yōu)點非常明顯完全沒有GROUP BY不存在別名引用問題也不存在空桶丟失問題。四個區(qū)間的條件寫得很明確任何一條數(shù)據(jù)只會命中其中一個SUM條件互斥性有保證。缺點也相當突出表會被全表掃描四次。在這個題目里數(shù)據(jù)量很小完全無所謂但在生產(chǎn)環(huán)境里如果Sessions表有幾百萬甚至上億行這個寫法會讓數(shù)據(jù)庫多扛三倍的壓力。所以我的建議是這個解法適合作為理解題意的入門方案不適合作為生產(chǎn)環(huán)境的最終方案。如果面試官問你“還有什么優(yōu)化空間”你最好能主動提到這一點。3.3 解法 CCTE 把分桶和補齊拆開第三種解法是解法 A 的現(xiàn)代寫法用公共表表達式CTE把“分桶”和“維度補齊”兩個步驟拆得清清楚楚。在可讀性上是最好的適合支持 CTE 的現(xiàn)代數(shù)據(jù)庫環(huán)境WITH dim AS ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ), bucketed AS ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) SELECT d.bin, COUNT(b.bin) AS total FROM dim d LEFT JOIN bucketed b ON d.bin b.bin GROUP BY d.bin ORDER BY CASE d.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;這里有一個小技巧值得專門講外層用的是COUNT(b.bin)而不是IFNULL(SUM(...), 0)。因為LEFT JOIN之后如果bucketed里沒有匹配的行b.bin的值就是NULL而COUNT本身會忽略NULL、只數(shù)非空值所以COUNT(b.bin)正好可以實現(xiàn)“沒有數(shù)據(jù)就輸出 0”的效果。這個寫法比先JOIN再包IFNULL要簡潔而且執(zhí)行效率也不差。三種解法說到底核心思想是一樣的先把維度補全再把數(shù)據(jù)對齊到維度上。區(qū)別只在于“補維度”用的是顯式的UNION ALL構(gòu)造還是隱式的分組聚合。筆試做題用哪種都能過但如果我們站在工程角度選解法 A 和解法 C 明顯比解法 B 更值得推廣。4. 實操中容易踩的坑和排查心得4.1 邊界值到底歸哪個桶這是最典型的問題。duration 5到底算[0-5還是[5-30按照半開區(qū)間定義左邊的閉區(qū)間包含邊界值所以 5 算進[5-30。在CASE WHEN里如果寫的是duration 5那么等于 5 的行會自然落到下一個條件結(jié)果完全正確。怕的是有人寫成duration 5這樣 5 就會被算進第一個桶兩個桶的語義就被破壞了。排查這個問題的技巧很簡單構(gòu)造邊界測試數(shù)據(jù)。在測試環(huán)境里插入duration分別為 4、5、29、30、59、60 的會話記錄然后跑一遍查詢檢查每個值落在哪個桶。如果 4 落在[0-5、5 落在[5-30、29 落在[5-30、30 落在[30-60、59 落在[30-60、60 落在[60那分桶邏輯就是對的。這一步能幫你把邊界值的隱患提前掐死在搖籃里。4.2 GROUP BY 別名在不同數(shù)據(jù)庫的行為差異我在前面已經(jīng)提過一次GROUP BY別名的問題這里再展開說透。標準 SQL 的執(zhí)行順序里GROUP BY在SELECT之前所以理論上不能引用SELECT里的別名。但 MySQL 做了擴展允許這樣寫這就導(dǎo)致很多人在本地開發(fā)環(huán)境沒問題一換到嚴格要求標準的數(shù)倉引擎就報錯。實際工作中我遇到過不止一次同樣一條 SQL在 A 平臺跑得好好的到了 B 平臺直接拋Unknown column異常。排查方法很簡單把GROUP BY后面的別名替換成完整的CASE WHEN表達式問題立刻消失。最穩(wěn)妥的寫法永遠是在子查詢里先算好分桶列外層再分組不要依賴任何數(shù)據(jù)庫的擴展特性。4.3 性能隱患條件聚合里的子查詢和大表全掃有些人在真實業(yè)務(wù)里會寫出類似SUM(CASE WHEN duration (SELECT AVG(duration) FROM Sessions) THEN 1 ELSE 0 END)這種 SQL。問題在于CASE WHEN里如果嵌入了標量子查詢每一行都要執(zhí)行一次子查詢性能會非常差。這道題雖然沒有復(fù)雜到那個程度但它引出了一個原則分桶條件必須是簡單表達式不能帶子查詢不能帶自定義函數(shù)否則大表場景下一定會拖垮數(shù)據(jù)庫。另外解法 B 里提到的“全表掃描四次”也是一個隱患。如果Sessions表數(shù)據(jù)量大建議優(yōu)先使用只掃描一次表的寫法。判斷標準很簡單看執(zhí)行計劃里Sessions被訪問了幾次。出現(xiàn)多次大表掃描就值得重新設(shè)計查詢結(jié)構(gòu)了。4.4 排序和 NULL 的隱藏坑排序的問題前面已經(jīng)講過了文本序不是我們想要的自然序必須手動映射。這里再補充一個容易忽略的坑duration字段如果允許為NULL那么在CASE WHEN里所有比較條件都會返回FALSE最終會被ELSE分支捕獲被算進[60。這在語義上是錯的未知時長怎么能算作“超過一小時”呢處理方式取決于業(yè)務(wù)定義。如果NULL表示數(shù)據(jù)缺失比較合理的做法是在子查詢最前面加WHERE duration IS NOT NULL把這些記錄排除在統(tǒng)計之外或者用COALESCE(duration, 0)給個默認值讓它們落到最短桶。不管選哪種都要顯式處理而不是任由ELSE兜底。下面把常見的坑整理成一張速查表現(xiàn)象可能原因解決思路輸出的桶缺少某一行直接GROUP BY導(dǎo)致空桶消失先構(gòu)造維度表再LEFT JOIN統(tǒng)計數(shù)據(jù)5 分鐘被算進[0-5分桶條件寫成duration 5統(tǒng)一用半開區(qū)間邊界值歸入右區(qū)間排序結(jié)果變成[0-5、[30-60文本字典序不符合預(yù)期用CASE手動映射桶的順序在數(shù)倉引擎里GROUP BY報錯引用了SELECT別名標準 SQL 不允許子查詢先算列外層再分組NULL時長被算進最長桶ELSE兜底捕獲了NULLWHERE過濾或COALESCE顯式處理5. 從這道題延展出去分桶統(tǒng)計的真實應(yīng)用場景5.1 用戶活躍時長分析LeetCode 這道題簡化了很多真實業(yè)務(wù)細節(jié)但分桶統(tǒng)計這個思路在實際數(shù)據(jù)分析里是繞不開的。我舉個例子。某個內(nèi)容類產(chǎn)品要分析用戶單次訪問時長原始表里有l(wèi)ogin_time和logout_time兩個字段。我們先用TIMESTAMPDIFF(MINUTE, login_time, logout_time)算出訪問時長然后分桶統(tǒng)計[0-1、[1-5、[5-30、[30。這樣一看就能知道自己是“流量型產(chǎn)品”大量用戶待幾秒鐘就走還是“黏性產(chǎn)品”大量用戶停留超過半小時。后續(xù)的產(chǎn)品策略完全不同。這里的關(guān)鍵點和題目一模一樣先把時間差換算成統(tǒng)一單位再分桶最后補齊維度。不管字段多復(fù)雜這個套路是固定的。5.2 訂單金額區(qū)間與人群結(jié)構(gòu)分析分桶統(tǒng)計在電商場景里同樣常見。訂單金額是典型的連續(xù)值分析時通常切成0-50、50-100、100-500、500-2000、2000幾個區(qū)間。同樣是半開區(qū)間邊界寫法和語義要提前定義好。在這個基礎(chǔ)上還可以再加一維統(tǒng)計每個金額桶里的下單人數(shù)用COUNT(DISTINCT user_id)。這樣就可以看出高價值金額區(qū)間的人群密度運營團隊據(jù)此決定往哪個價位段傾斜資源。和 LeetCode 原題相比只是多了一個去重計數(shù)的維度底層的 SQL 結(jié)構(gòu)沒有本質(zhì)區(qū)別。5.3 分桶設(shè)計原則總結(jié)做了這么多年數(shù)據(jù)分析我總結(jié)了幾條分桶設(shè)計的經(jīng)驗寫在這里供參考區(qū)間互斥且窮盡行話叫 MECE。任何一條數(shù)據(jù)必須且只能落進一個桶不允許有漏網(wǎng)之魚也不允許有重復(fù)計入。區(qū)間數(shù)量控制在 5 到 8 個。太少會掩蓋分布規(guī)律太多則人眼沒法快速看出結(jié)構(gòu)。超過十幾個桶的柱狀圖基本只能靠程序讀人已經(jīng)看不過來了。邊界值必須在文檔里寫清楚。比如“5 分鐘歸入 5 到 30 分鐘桶”或者在下發(fā) SQL 時用注釋寫明白避免同事之間理解不一致。桶標簽盡量無歧義。[0-5這種寫法數(shù)學(xué)上很嚴謹?shù)珮I(yè)務(wù)同事可能看不懂。實際報表里可以寫成“0 到 5 分鐘不含 5”或者直接用“不足 5 分鐘”“5 到 30 分鐘”。原則是讓看圖的人不需要額外解釋就明白含義?；氐?LeetCode 這道題本身它的價值不在于難度而在于它把分桶統(tǒng)計里最容易翻車的幾個坑全部暴露出來了邊界值定義、空桶維度的保留、結(jié)果排序、單位換算。如果能在不看答案的情況下獨立寫出滿足全部測試用例的 SQL那說明你真的理解了聚合查詢的底層邏輯。我在實際帶人的時候經(jīng)常用這道題做入職摸底測試。不是因為它的難度而是因為它真的能反映一個人是不是有做數(shù)據(jù)報表的底層素養(yǎng)。最后送大家一句話分桶容易桶全難。把這道題吃透以后遇到各類“補齊維度”的統(tǒng)計需求你會感謝自己現(xiàn)在花的時間。 SQL分桶統(tǒng)計實戰(zhàn)：從CASE WHEN到空桶補齊的完整解法與踩坑指南
前陣子幫團隊里的同學(xué)做 SQL 面試復(fù)盤聊到一道數(shù)據(jù)庫題名字叫做“制作會話柱狀圖”。題號在某個在線題庫里排在第 1435 位表面上看是個統(tǒng)計聚合題結(jié)果不少人一上來就卡住。問題拆開來看其實非常典型一張保存會話時長的表要求按時間區(qū)間分桶輸出每個區(qū)間的會話數(shù)量。區(qū)間是[0-5)、[5-30)、[30-60)、[60)而且哪怕某個區(qū)間一條數(shù)據(jù)都沒有也得輸出 0。這個“空桶也輸出”的要求就是整道題的分水嶺。說實話這道題我在面試候選人和幫忙查業(yè)務(wù) SQL 時都碰到過同款問題。很多人第一反應(yīng)就是CASE WHEN然后GROUP BY一跑發(fā)現(xiàn)少了行才知道 SQL 里“把連續(xù)值切成離散標簽”和“保證標簽維度完整”是兩碼事。這篇文章就圍繞這個題目展開從表結(jié)構(gòu)、邊界定義、三種解法到真實業(yè)務(wù)里的分桶實踐完整拆一遍。給準備數(shù)據(jù)庫面試的朋友也給平時天天寫報表的兄弟做個參考。1. 讀懂題目Sessions 表和四個時間桶1.1 表結(jié)構(gòu)、字段與業(yè)務(wù)含義這道題給的表結(jié)構(gòu)很簡潔一張Sessions表主要字段就是session_id和duration。用表格看就是這樣列名類型含義session_idint會話唯一編號durationint會話持續(xù)時長單位通常是分鐘題目要求的輸出很簡單把會話按照持續(xù)時間分成四個區(qū)間然后統(tǒng)計每個區(qū)間里有多少條會話記錄。最終結(jié)果要有兩列一列是區(qū)間名一列是數(shù)量?，F(xiàn)實中這張表對應(yīng)的場景非常常見一個 App 記錄用戶每次登錄的時長或者在線客服系統(tǒng)記錄每次會話的接待時長。在做產(chǎn)品分析的時候我們很少關(guān)心“平均時長是 7.3 分鐘”這種數(shù)字反而更想知道“有多少用戶連 5 分鐘都沒待夠”“有多少用戶待了超過半小時”。把連續(xù)時長切成幾個區(qū)間再數(shù)每個區(qū)間的人數(shù)這個過程就是分桶統(tǒng)計也就是柱狀圖背后的數(shù)據(jù)基礎(chǔ)。這里先提醒一句題目里duration給的是分鐘數(shù)但如果你在真實業(yè)務(wù)里遇到的數(shù)據(jù)源給的是秒、毫秒或者直接給了開始時間和結(jié)束時間兩個字段那就得先換算單位。別拿起 SQL 就寫先確認字段語義這是做數(shù)據(jù)查詢的第一步也是很多人容易忽略的一步。1.2 分桶邊界半開區(qū)間和“等于邊界歸誰”的問題四個桶的劃分規(guī)則如下桶名條件含義[0-5duration 0 且 duration 5短會話不足 5 分鐘[5-30duration 5 且 duration 30中等會話5 到 30 分鐘之間[30-60duration 30 且 duration 60較長會話30 到 60 分鐘之間[60duration 60長會話超過 1 小時這種寫法叫半開區(qū)間左邊閉右邊開。為什么業(yè)界都這么設(shè)計因為可以保證每一個值只有一個歸屬不會出現(xiàn)“duration 等于 5 到底算上一個桶還是下一個桶”的爭執(zhí)。柱狀圖的橫軸刻度也不會重疊數(shù)據(jù)不會重復(fù)計算。我在真實項目里見過有人用閉區(qū)間寫結(jié)果[0-5]和[5-30]都包含 5同一個數(shù)被算進兩個區(qū)間報表里的總數(shù)和明細對不上查了半天才發(fā)現(xiàn)是邊界條件寫重了。從那以后我給自己定了個規(guī)矩凡是分桶一律用半開區(qū)間。這道題里[0-5這個寫法其實就是半開區(qū)間的標準表示左邊閉右邊開數(shù)學(xué)上很嚴謹寫 SQL 的時候也方便。另外有一個細節(jié)在CASE WHEN里寫條件時建議按照從小到大的順序判斷寫成duration 5、duration 30、duration 60這樣邏輯上最不容易出錯。因為CASE WHEN是順序匹配的只要命中了第一個條件就會進入對應(yīng)桶后面的條件根本不會判斷。如果你寫成duration 0 AND duration 5這種完整寫法也沒問題但順序判斷更簡潔也不容易漏條件。2. 核心知識點拆解這道題不是簡單 GROUP BY2.1 條件聚合的兩種打開方式這道題看起來簡單但它實際上考了三個容易被忽略的 SQL 知識點條件聚合、結(jié)果集完整性和排序控制。第一個知識點是條件聚合。條件聚合有兩種寫法。第一種是在SELECT后面用CASE WHEN給每條記錄打上桶標簽然后GROUP BY這個標簽第二種是在SELECT里用SUM(CASE WHEN ... THEN 1 ELSE 0 END)直接把符合條件的記錄數(shù)加出來不需要GROUP BY。兩種寫法都能算出結(jié)果但適用場景不太一樣。第一種寫法的問題是GROUP BY能不能直接引用SELECT里定義的別名這個在不同數(shù)據(jù)庫里行為不一樣。MySQL 和部分分支版本允許GROUP BY引用別名這是對標準 SQL 的擴展但標準 SQL 的執(zhí)行順序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY理論上在GROUP BY階段還看不到SELECT里的別名。所以有些嚴格模式或者數(shù)倉引擎會直接報錯比如我們把CASE WHEN的結(jié)果命名為bin然后寫GROUP BY bin在嚴格模式下是過不去的。穩(wěn)妥的做法是先寫個子查詢在內(nèi)層把分桶標簽用CASE WHEN算好直接作為一列輸出外層再對那一列做GROUP BY。這個習(xí)慣建議直接刻進腦子里能避免很多莫名其妙的兼容性問題。我自己過去就吃過這個虧本地 MySQL 寫得好好的換到某云數(shù)據(jù)倉庫一跑就報錯找半天原因才發(fā)現(xiàn)是GROUP BY引用別名的問題。2.2 空桶的哲學(xué)柱狀圖的橫軸必須有刻度第二個知識點是結(jié)果集完整性也就是空桶問題。直接用GROUP BY出來的結(jié)果只有那些出現(xiàn)過記錄的分組。如果測試數(shù)據(jù)里恰好沒有 30 到 60 分鐘之間的會話那么查詢結(jié)果就只有三行[30-60這個桶直接消失不見。但題目要求很明確四個區(qū)間都必須輸出缺失的區(qū)間要補 0。這個需求背后是有實際意義的柱狀圖的橫軸是一個固定的刻度體系你不能讓柱子憑空少一根否則看圖的人會以為系統(tǒng)出錯或者數(shù)據(jù)被截斷了。哪怕某個區(qū)間一根柱子都沒有刻度也得留在那里數(shù)值顯示為 0。在 SQL 里實現(xiàn)“維度補齊”套路幾乎是固定的先構(gòu)造一個包含全部區(qū)間的維度表再拿它去LEFT JOIN統(tǒng)計數(shù)據(jù)。這是做 BI 報表的人每天都要用的基本功。行業(yè)里也管這個叫“日期維度補齊”或者“類別維度補齊”本質(zhì)就是讓結(jié)果集的行數(shù)不受數(shù)據(jù)本身影響始終和定義好的維度保持一致。拿生活里的例子類比一個月有 30 天就算某天完全沒有訂單日報上也要把這天列出來銷售額寫 0。否則到了月底做匯總天數(shù)對不上數(shù)據(jù)完整性就崩了。LeetCode 這道題其實就是這個場景的簡化版只不過把日期換成了會話時長區(qū)間。2.3 時間函數(shù)不是重點單位才是重點第三個知識點也是很多人容易誤解的地方這道題雖然是數(shù)據(jù)庫分類下的題目但它考的不是時間差計算函數(shù)。duration字段已經(jīng)是一個算好的數(shù)字不需要你用DATEDIFF或者TIMESTAMPDIFF去推導(dǎo)。真正該留意的是數(shù)據(jù)單位的一致性。如果某天你的數(shù)據(jù)源給的不是分鐘而是秒、毫秒或者直接給了start_time和end_time兩個DATETIME字段你需要先做換算。比如用TIMESTAMPDIFF(SECOND, start_time, end_time) / 60.0得出帶小數(shù)的分鐘數(shù)再參與分桶邏輯。換算完了之后所有條件都基于同一個單位去判斷避免一個 SQL 里一會兒秒一會兒分鐘把自己都繞暈。順帶提一下兩個常見時間函數(shù)的區(qū)別DATEDIFF只能返回兩個日期之間相差的天數(shù)精度太低TIMESTAMPDIFF可以指定單位SECOND、MINUTE、HOUR都行。做時長類分析的時候優(yōu)先用TIMESTAMPDIFF。3. 三種可落地的解法從簡到繁3.1 解法 A先統(tǒng)計、后補維度推薦先給出我認為最標準的解法。核心思路分兩步第一步在子查詢里把每條記錄分到對應(yīng)桶然后按桶統(tǒng)計數(shù)量第二步用UNION ALL構(gòu)造一個包含全部桶名的維度表左連接統(tǒng)計結(jié)果缺失的桶用 0 補齊。SELECT b.bin, IFNULL(t.total, 0) AS total FROM ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ) b LEFT JOIN ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin, COUNT(*) AS total FROM Sessions GROUP BY bin ) t ON b.bin t.bin ORDER BY CASE b.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;拆開來看這個 SQL 的每一部分。最內(nèi)層的子查詢t做的事情是掃描Sessions表用CASE WHEN把每條記錄歸入對應(yīng)桶然后GROUP BY bin統(tǒng)計每個桶的會話數(shù)量。這里有一個問題需要注意內(nèi)層子查詢里的GROUP BY bin引用了CASE WHEN表達式產(chǎn)生的別名。在 MySQL 里這樣寫沒問題但如果想做到嚴格兼容建議再包一層子查詢把CASE WHEN的結(jié)果先作為獨立的列算出來外層再分組比如FROM ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) s GROUP BY s.bin外面的b子查詢就是標準的“維度構(gòu)造”用UNION ALL把四個桶名豎排成四行。之所以用UNION ALL而不是UNION是因為不會出現(xiàn)重復(fù)行沒必要去重UNION ALL性能也更好。左連接之后沒有數(shù)據(jù)的桶對應(yīng)的t.total就是NULL包一層IFNULL轉(zhuǎn)成 0。最后是排序。因為bin是文本如果直接ORDER BY bin會按照字典序排成[0-5、[30-60、[5-30、[60這不是柱狀圖從左到右的自然順序。所以要用CASE再映射一次手動指定順序。這個細節(jié)在題目測試用例里可能不會觸發(fā)嚴格檢查但實際做報表一定會被產(chǎn)品經(jīng)理問“為什么順序是亂的”。3.2 解法 B四個 UNION ALL邏輯最樸素第二種解法思路完全不同不分組直接用四個SELECT分別統(tǒng)計每個桶的數(shù)量再用UNION ALL拼起來SELECT [0-5 AS bin, SUM(CASE WHEN duration 5 THEN 1 ELSE 0 END) AS total FROM Sessions UNION ALL SELECT [5-30, SUM(CASE WHEN duration 5 AND duration 30 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [30-60, SUM(CASE WHEN duration 30 AND duration 60 THEN 1 ELSE 0 END) FROM Sessions UNION ALL SELECT [60, SUM(CASE WHEN duration 60 THEN 1 ELSE 0 END) FROM Sessions;這個方案的優(yōu)點非常明顯完全沒有GROUP BY不存在別名引用問題也不存在空桶丟失問題。四個區(qū)間的條件寫得很明確任何一條數(shù)據(jù)只會命中其中一個SUM條件互斥性有保證。缺點也相當突出表會被全表掃描四次。在這個題目里數(shù)據(jù)量很小完全無所謂但在生產(chǎn)環(huán)境里如果Sessions表有幾百萬甚至上億行這個寫法會讓數(shù)據(jù)庫多扛三倍的壓力。所以我的建議是這個解法適合作為理解題意的入門方案不適合作為生產(chǎn)環(huán)境的最終方案。如果面試官問你“還有什么優(yōu)化空間”你最好能主動提到這一點。3.3 解法 CCTE 把分桶和補齊拆開第三種解法是解法 A 的現(xiàn)代寫法用公共表表達式CTE把“分桶”和“維度補齊”兩個步驟拆得清清楚楚。在可讀性上是最好的適合支持 CTE 的現(xiàn)代數(shù)據(jù)庫環(huán)境WITH dim AS ( SELECT [0-5 AS bin UNION ALL SELECT [5-30 UNION ALL SELECT [30-60 UNION ALL SELECT [60 ), bucketed AS ( SELECT CASE WHEN duration 5 THEN [0-5 WHEN duration 30 THEN [5-30 WHEN duration 60 THEN [30-60 ELSE [60 END AS bin FROM Sessions ) SELECT d.bin, COUNT(b.bin) AS total FROM dim d LEFT JOIN bucketed b ON d.bin b.bin GROUP BY d.bin ORDER BY CASE d.bin WHEN [0-5 THEN 1 WHEN [5-30 THEN 2 WHEN [30-60 THEN 3 ELSE 4 END;這里有一個小技巧值得專門講外層用的是COUNT(b.bin)而不是IFNULL(SUM(...), 0)。因為LEFT JOIN之后如果bucketed里沒有匹配的行b.bin的值就是NULL而COUNT本身會忽略NULL、只數(shù)非空值所以COUNT(b.bin)正好可以實現(xiàn)“沒有數(shù)據(jù)就輸出 0”的效果。這個寫法比先JOIN再包IFNULL要簡潔而且執(zhí)行效率也不差。三種解法說到底核心思想是一樣的先把維度補全再把數(shù)據(jù)對齊到維度上。區(qū)別只在于“補維度”用的是顯式的UNION ALL構(gòu)造還是隱式的分組聚合。筆試做題用哪種都能過但如果我們站在工程角度選解法 A 和解法 C 明顯比解法 B 更值得推廣。4. 實操中容易踩的坑和排查心得4.1 邊界值到底歸哪個桶這是最典型的問題。duration 5到底算[0-5還是[5-30按照半開區(qū)間定義左邊的閉區(qū)間包含邊界值所以 5 算進[5-30。在CASE WHEN里如果寫的是duration 5那么等于 5 的行會自然落到下一個條件結(jié)果完全正確。怕的是有人寫成duration 5這樣 5 就會被算進第一個桶兩個桶的語義就被破壞了。排查這個問題的技巧很簡單構(gòu)造邊界測試數(shù)據(jù)。在測試環(huán)境里插入duration分別為 4、5、29、30、59、60 的會話記錄然后跑一遍查詢檢查每個值落在哪個桶。如果 4 落在[0-5、5 落在[5-30、29 落在[5-30、30 落在[30-60、59 落在[30-60、60 落在[60那分桶邏輯就是對的。這一步能幫你把邊界值的隱患提前掐死在搖籃里。4.2 GROUP BY 別名在不同數(shù)據(jù)庫的行為差異我在前面已經(jīng)提過一次GROUP BY別名的問題這里再展開說透。標準 SQL 的執(zhí)行順序里GROUP BY在SELECT之前所以理論上不能引用SELECT里的別名。但 MySQL 做了擴展允許這樣寫這就導(dǎo)致很多人在本地開發(fā)環(huán)境沒問題一換到嚴格要求標準的數(shù)倉引擎就報錯。實際工作中我遇到過不止一次同樣一條 SQL在 A 平臺跑得好好的到了 B 平臺直接拋Unknown column異常。排查方法很簡單把GROUP BY后面的別名替換成完整的CASE WHEN表達式問題立刻消失。最穩(wěn)妥的寫法永遠是在子查詢里先算好分桶列外層再分組不要依賴任何數(shù)據(jù)庫的擴展特性。4.3 性能隱患條件聚合里的子查詢和大表全掃有些人在真實業(yè)務(wù)里會寫出類似SUM(CASE WHEN duration (SELECT AVG(duration) FROM Sessions) THEN 1 ELSE 0 END)這種 SQL。問題在于CASE WHEN里如果嵌入了標量子查詢每一行都要執(zhí)行一次子查詢性能會非常差。這道題雖然沒有復(fù)雜到那個程度但它引出了一個原則分桶條件必須是簡單表達式不能帶子查詢不能帶自定義函數(shù)否則大表場景下一定會拖垮數(shù)據(jù)庫。另外解法 B 里提到的“全表掃描四次”也是一個隱患。如果Sessions表數(shù)據(jù)量大建議優(yōu)先使用只掃描一次表的寫法。判斷標準很簡單看執(zhí)行計劃里Sessions被訪問了幾次。出現(xiàn)多次大表掃描就值得重新設(shè)計查詢結(jié)構(gòu)了。4.4 排序和 NULL 的隱藏坑排序的問題前面已經(jīng)講過了文本序不是我們想要的自然序必須手動映射。這里再補充一個容易忽略的坑duration字段如果允許為NULL那么在CASE WHEN里所有比較條件都會返回FALSE最終會被ELSE分支捕獲被算進[60。這在語義上是錯的未知時長怎么能算作“超過一小時”呢處理方式取決于業(yè)務(wù)定義。如果NULL表示數(shù)據(jù)缺失比較合理的做法是在子查詢最前面加WHERE duration IS NOT NULL把這些記錄排除在統(tǒng)計之外或者用COALESCE(duration, 0)給個默認值讓它們落到最短桶。不管選哪種都要顯式處理而不是任由ELSE兜底。下面把常見的坑整理成一張速查表現(xiàn)象可能原因解決思路輸出的桶缺少某一行直接GROUP BY導(dǎo)致空桶消失先構(gòu)造維度表再LEFT JOIN統(tǒng)計數(shù)據(jù)5 分鐘被算進[0-5分桶條件寫成duration 5統(tǒng)一用半開區(qū)間邊界值歸入右區(qū)間排序結(jié)果變成[0-5、[30-60文本字典序不符合預(yù)期用CASE手動映射桶的順序在數(shù)倉引擎里GROUP BY報錯引用了SELECT別名標準 SQL 不允許子查詢先算列外層再分組NULL時長被算進最長桶ELSE兜底捕獲了NULLWHERE過濾或COALESCE顯式處理5. 從這道題延展出去分桶統(tǒng)計的真實應(yīng)用場景5.1 用戶活躍時長分析LeetCode 這道題簡化了很多真實業(yè)務(wù)細節(jié)但分桶統(tǒng)計這個思路在實際數(shù)據(jù)分析里是繞不開的。我舉個例子。某個內(nèi)容類產(chǎn)品要分析用戶單次訪問時長原始表里有l(wèi)ogin_time和logout_time兩個字段。我們先用TIMESTAMPDIFF(MINUTE, login_time, logout_time)算出訪問時長然后分桶統(tǒng)計[0-1、[1-5、[5-30、[30。這樣一看就能知道自己是“流量型產(chǎn)品”大量用戶待幾秒鐘就走還是“黏性產(chǎn)品”大量用戶停留超過半小時。后續(xù)的產(chǎn)品策略完全不同。這里的關(guān)鍵點和題目一模一樣先把時間差換算成統(tǒng)一單位再分桶最后補齊維度。不管字段多復(fù)雜這個套路是固定的。5.2 訂單金額區(qū)間與人群結(jié)構(gòu)分析分桶統(tǒng)計在電商場景里同樣常見。訂單金額是典型的連續(xù)值分析時通常切成0-50、50-100、100-500、500-2000、2000幾個區(qū)間。同樣是半開區(qū)間邊界寫法和語義要提前定義好。在這個基礎(chǔ)上還可以再加一維統(tǒng)計每個金額桶里的下單人數(shù)用COUNT(DISTINCT user_id)。這樣就可以看出高價值金額區(qū)間的人群密度運營團隊據(jù)此決定往哪個價位段傾斜資源。和 LeetCode 原題相比只是多了一個去重計數(shù)的維度底層的 SQL 結(jié)構(gòu)沒有本質(zhì)區(qū)別。5.3 分桶設(shè)計原則總結(jié)做了這么多年數(shù)據(jù)分析我總結(jié)了幾條分桶設(shè)計的經(jīng)驗寫在這里供參考區(qū)間互斥且窮盡行話叫 MECE。任何一條數(shù)據(jù)必須且只能落進一個桶不允許有漏網(wǎng)之魚也不允許有重復(fù)計入。區(qū)間數(shù)量控制在 5 到 8 個。太少會掩蓋分布規(guī)律太多則人眼沒法快速看出結(jié)構(gòu)。超過十幾個桶的柱狀圖基本只能靠程序讀人已經(jīng)看不過來了。邊界值必須在文檔里寫清楚。比如“5 分鐘歸入 5 到 30 分鐘桶”或者在下發(fā) SQL 時用注釋寫明白避免同事之間理解不一致。桶標簽盡量無歧義。[0-5這種寫法數(shù)學(xué)上很嚴謹?shù)珮I(yè)務(wù)同事可能看不懂。實際報表里可以寫成“0 到 5 分鐘不含 5”或者直接用“不足 5 分鐘”“5 到 30 分鐘”。原則是讓看圖的人不需要額外解釋就明白含義?；氐?LeetCode 這道題本身它的價值不在于難度而在于它把分桶統(tǒng)計里最容易翻車的幾個坑全部暴露出來了邊界值定義、空桶維度的保留、結(jié)果排序、單位換算。如果能在不看答案的情況下獨立寫出滿足全部測試用例的 SQL那說明你真的理解了聚合查詢的底層邏輯。我在實際帶人的時候經(jīng)常用這道題做入職摸底測試。不是因為它的難度而是因為它真的能反映一個人是不是有做數(shù)據(jù)報表的底層素養(yǎng)。最后送大家一句話分桶容易桶全難。把這道題吃透以后遇到各類“補齊維度”的統(tǒng)計需求你會感謝自己現(xiàn)在花的時間。