開(kāi)頭直接切入SQL聚合是數(shù)據(jù)分析最常用的能力也是高頻面試題。分享自己刷“高頻SQL 50題”中聚合部分的經(jīng)驗(yàn)拆解考點(diǎn)、常見(jiàn)場(chǎng)景、性能陷阱并附實(shí)戰(zhàn)SQL。注意每段150字以上總5000字以上。需要認(rèn)真擴(kuò)展。 換一個(gè)技術(shù)圈里比較有意思的現(xiàn)象刷過(guò)LeetCode SQL題庫(kù)或者各種“高頻SQL 50題”的人十有八九都會(huì)卡在“聚合”這一塊。不是因?yàn)樗y而是因?yàn)槠綍r(shí)寫(xiě)業(yè)務(wù)SQL時(shí)大多數(shù)情況只是拿COUNT、SUM、AVG來(lái)糊弄個(gè)報(bào)表數(shù)字真到了面試那種“給你一張表、一個(gè)業(yè)務(wù)場(chǎng)景、讓你寫(xiě)出統(tǒng)計(jì)結(jié)果”的題目才會(huì)發(fā)現(xiàn)聚合這玩意兒藏著大量細(xì)節(jié)。聚合查詢表面上是SQL里最“基礎(chǔ)”的功能但恰恰是它決定了你能不能高效處理數(shù)據(jù)。我見(jiàn)過(guò)不少工作三五年的開(kāi)發(fā)寫(xiě)復(fù)雜報(bào)表時(shí)還在用“一張臨時(shí)表多個(gè)子查詢各種JOIN”硬編效率低且容易出錯(cuò)。這篇文章就把我刷“高頻SQL 50題”里聚合部分的一些心得整理出來(lái)同時(shí)結(jié)合日常工作踩過(guò)的坑把聚合函數(shù)、分組邏輯、窗口函數(shù)、性能優(yōu)化一次性講透。不管你是準(zhǔn)備面試、在做數(shù)據(jù)分析還是每天跟報(bào)表打交道的后端開(kāi)發(fā)這篇內(nèi)容應(yīng)該都能幫你在聚合這條路上少走彎路。1. 聚合SQL到底考什么從高頻50題看考點(diǎn)分布先把“高頻SQL 50題”里聚合相關(guān)的題拉出來(lái)看一眼你會(huì)發(fā)現(xiàn)考點(diǎn)非常集中。拿我自己刷過(guò)的版本來(lái)說(shuō)聚合相關(guān)的題目大概占了三成主要分布在幾個(gè)方向1.1 最基礎(chǔ)的三件套COUNT、SUM、AVG這三個(gè)函數(shù)看起來(lái)簡(jiǎn)單實(shí)際上每個(gè)都有坑。COUNT()和COUNT(列)的區(qū)別多少人面試第一問(wèn)就被問(wèn)倒了COUNT()是統(tǒng)計(jì)所有行數(shù)包括NULL值的行COUNT(列)是統(tǒng)計(jì)該列非NULL值的行數(shù)。SUM(列)會(huì)忽略NULL但如果整列都是NULLSUM返回NULL而不是0。AVG同樣忽略NULL計(jì)算時(shí)不會(huì)把NULL當(dāng)作0去參與總和這跟很多人的直覺(jué)不一樣。高頻題里常見(jiàn)套路是給你一張訂單表、一張用戶表讓你統(tǒng)計(jì)每個(gè)用戶的訂單數(shù)、訂單總額、平均訂單金額。這題看似簡(jiǎn)單但能考察的點(diǎn)很多需不需要保留沒(méi)有訂單的用戶如果用LEFT JOIN那COUNT字段時(shí)要不要做空值處理深挖下去就能看出你到底是在背SQL還是在理解SQL。我建議入門者先把這三件套吃透尤其搞清楚它們的NULL語(yǔ)義。別小看這個(gè)很多線上統(tǒng)計(jì)Bug就是從這里來(lái)的。1.2 分組聚合與HAVING的隱藏細(xì)節(jié)有了聚合函數(shù)自然會(huì)引出一個(gè)經(jīng)典問(wèn)題如果要在聚合之后再過(guò)濾用WHERE還是HAVING這個(gè)考點(diǎn)幾乎每套題都有。標(biāo)準(zhǔn)答案是WHERE在分組前過(guò)濾HAVING在分組后過(guò)濾。很多人背下來(lái)了但真寫(xiě)的時(shí)候還是會(huì)搞混。比如“查出訂單數(shù)大于5的用戶”正確的寫(xiě)法一定是GROUP BY user_id之后用HAVING COUNT(*) 5。如果你把條件寫(xiě)在WHERE里SQL會(huì)直接報(bào)錯(cuò)“聚合函數(shù)不能出現(xiàn)在WHERE子句中”嗎有些數(shù)據(jù)庫(kù)會(huì)報(bào)錯(cuò)有些數(shù)據(jù)庫(kù)語(yǔ)法上允許但結(jié)果完全不對(duì)。除了這個(gè)基礎(chǔ)點(diǎn)高頻題里還會(huì)考“GROUP BY多個(gè)字段”、“GROUP BY與DISTINCT的區(qū)別”、“分組后再排序取Top N”等變形。說(shuō)實(shí)話如果能把HAVING的過(guò)濾邏輯、GROUP BY的字段順序、以及聚合函數(shù)作用于分組后的結(jié)果集這三件事理清楚你基本就能PK掉80%的面試者。2. 面試最愛(ài)出的聚合場(chǎng)景從“按部門統(tǒng)計(jì)”到“連續(xù)問(wèn)題”刷題刷多了你會(huì)發(fā)現(xiàn)聚合題從來(lái)不直接說(shuō)“請(qǐng)用分組聚合”它總是包裝成一個(gè)業(yè)務(wù)需求。這時(shí)候能不能把需求翻譯成SQL邏輯就是核心能力。2.1 經(jīng)典“分組統(tǒng)計(jì)”題的完整拆解看一個(gè)最常見(jiàn)也最容易被問(wèn)的題目有一張員工表Employee字段包括emp_id, emp_name, dept_id, hire_date, salary?，F(xiàn)在要統(tǒng)計(jì)每個(gè)部門的員工人數(shù)、平均工資、最高工資和最低工資并且只顯示平均工資大于5000的部門按平均工資降序排列。第一眼看過(guò)去這不就是Hello World等級(jí)嗎很多人的寫(xiě)法是這樣SELECT dept_id, COUNT(*) AS emp_cnt, AVG(salary) AS avg_sal, MAX(salary) AS max_sal, MIN(salary) AS min_sal FROM employee GROUP BY dept_id HAVING AVG(salary) 5000 ORDER BY avg_sal DESC;這確實(shí)能跑但有一個(gè)隱藏問(wèn)題如果只想統(tǒng)計(jì)在職員工或者只統(tǒng)計(jì)工資非空的員工你要不要加WHERE更關(guān)鍵的是如果dept_id在另一張部門表里你想顯示部門名稱而不是編號(hào)就要JOIN部門表。而JOIN之后會(huì)不會(huì)因?yàn)椴块T表里沒(méi)有匹配記錄導(dǎo)致數(shù)據(jù)變少這些都是面試官希望你能主動(dòng)提到的。我的習(xí)慣是先把業(yè)務(wù)拆成三層數(shù)據(jù)范圍WHERE、分組維度GROUP BY、聚合后過(guò)濾HAVING。每一步都問(wèn)自己一個(gè)問(wèn)題這一層過(guò)濾會(huì)不會(huì)改變聚合的基數(shù)這樣寫(xiě)出來(lái)的SQL才不容易翻車。2.2 結(jié)合窗口函數(shù)累計(jì)求和與移動(dòng)平均聚合題做到后面一定會(huì)遇到窗口函數(shù)。因?yàn)槠胀℅ROUP BY會(huì)把多行壓縮成一行而很多業(yè)務(wù)要的是“既能保留明細(xì)行又能看到分組統(tǒng)計(jì)值”這時(shí)候就需要SUM() OVER()這類窗口語(yǔ)法。窗口函數(shù)的核心邏輯是它不減少行數(shù)只是在每一行后面帶上一個(gè)“窗口范圍”的計(jì)算結(jié)果。比如計(jì)算每個(gè)部門內(nèi)每個(gè)員工的工資占部門總工資的比例SELECT emp_id, emp_name, dept_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary, ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY dept_id), 2) AS pct FROM employee;寫(xiě)到這里你就能感受到同一個(gè)SUM函數(shù)在GROUP BY里是“聚合行”在窗口里是“計(jì)算列”。很多人分不清這兩者寫(xiě)報(bào)表時(shí)就容易出現(xiàn)“行數(shù)變少”或者“重復(fù)統(tǒng)計(jì)”的尷尬。再升級(jí)一點(diǎn)就是“求每個(gè)用戶連續(xù)登錄天數(shù)”。這題幾乎是所有聚合高頻題里最經(jīng)典的題解法也很有意思先用ROW_NUMBER()按用戶分組、按日期排序得到一個(gè)序號(hào)然后用登錄日期減去序號(hào)得到一組“日期差值”。只要用戶連續(xù)登錄日期減去序號(hào)后的值就是一樣的一旦斷簽差值就會(huì)變。最后再按用戶和差值分組聚合就能統(tǒng)計(jì)出連續(xù)天數(shù)。這個(gè)套路我在后面實(shí)戰(zhàn)部分會(huì)再展開(kāi)這里先記住一句話窗口函數(shù)是聚合思維從“整體”走向“局部”的關(guān)鍵工具。3. 聚合查詢的性能陷阱與優(yōu)化思路學(xué)習(xí)聚合不能只盯著語(yǔ)法性能同樣重要。工作里我經(jīng)常在慢SQL排查現(xiàn)場(chǎng)看到類似“SELECT COUNT(*) FROM 大表”這種直接把數(shù)據(jù)庫(kù)CPU打滿的查詢。聚合操作天然要掃描大量數(shù)據(jù)如果底子沒(méi)打好一張千萬(wàn)級(jí)表就能讓你體驗(yàn)什么叫“卡死”。3.1 為什么COUNT(*)比COUNT(列)快很多老開(kāi)發(fā)會(huì)推薦“能寫(xiě)COUNT()就別寫(xiě)COUNT(列)”這背后不是迷信而是有索引層面的道理。在多數(shù)數(shù)據(jù)庫(kù)里如果一張表沒(méi)有定義主鍵或合適的二級(jí)索引COUNT(列)需要判斷每個(gè)值是否為NULL而COUNT()是直接數(shù)行數(shù)并不關(guān)心具體列的值。在InnoDB引擎MySQL為例里COUNT(*)的優(yōu)化空間更大尤其當(dāng)表上有覆蓋索引時(shí)它可以直接走索引掃描而不需要回到聚簇索引去讀整行數(shù)據(jù)。反過(guò)來(lái)如果你COUNT一個(gè)很小的非索引列確實(shí)可能要全表一遍但即便全表也還是比COUNT(列)多一層“判斷NULL”的開(kāi)銷。所以我的習(xí)慣是統(tǒng)計(jì)總行數(shù)用COUNT()統(tǒng)計(jì)某個(gè)字段有值的數(shù)量才用COUNT(字段)。很多人用COUNT(id)來(lái)數(shù)行數(shù)如果id列非空結(jié)果一樣但邏輯上不如COUNT()清晰。3.2 聚合查詢慢的常見(jiàn)原因與優(yōu)化手段慢SQL優(yōu)化是一個(gè)很大的話題但聚合查詢的優(yōu)化方向其實(shí)很固定。第一先看能不能下推過(guò)濾條件。WHERE過(guò)濾要盡早執(zhí)行讓進(jìn)入聚合的數(shù)據(jù)量最小化。千萬(wàn)不要在聚合前的子查詢里把所有明細(xì)查出來(lái)再在外面套一層聚合。一些新手寫(xiě)“SELECT COUNT(*) FROM (SELECT * FROM big_table) t”這種完全可以避免。第二合理利用索引。GROUP BY字段上如果建有索引數(shù)據(jù)庫(kù)就不需要額外排序。MySQL里GROUP BY默認(rèn)會(huì)做排序操作如果分組字段能用索引覆蓋性能會(huì)有質(zhì)的提升。不過(guò)要注意聯(lián)合索引的字段順序要匹配GROUP BY的順序否則依然要文件排序。第三監(jiān)視執(zhí)行計(jì)劃。無(wú)論是MySQL還是PostgreSQL都能用EXPLAIN看到聚合階段是不是出現(xiàn)了Using temporary或者Using filesort。出現(xiàn)臨時(shí)表不一定是壞事但數(shù)據(jù)量一大就容易寫(xiě)磁盤這時(shí)候可以考慮通過(guò)改寫(xiě)SQL結(jié)構(gòu)或者調(diào)整數(shù)據(jù)庫(kù)參數(shù)比如tmp_table_size、max_heap_table_size來(lái)緩解。第四如果實(shí)時(shí)聚合實(shí)在跑不動(dòng)可以考慮用物化視圖或者定時(shí)匯總表。這不是SQL語(yǔ)法層面的事但卻是實(shí)際工作中最常用的手段。常見(jiàn)做法是每天凌晨跑一個(gè)離線任務(wù)把前一天的聚合結(jié)果存到中間表前端查詢直接查匯總表。這樣雖然會(huì)有一定程度的數(shù)據(jù)延遲但換來(lái)了查詢速度的穩(wěn)定。4. 那些年我踩過(guò)的聚合坑6個(gè)必看注意事項(xiàng)我最近一次“翻車”是在做銷售報(bào)表時(shí)某個(gè)產(chǎn)品線的銷售額怎么算都對(duì)不上。后來(lái)排查了半天發(fā)現(xiàn)是SUM函數(shù)把NULL值直接忽略了而那個(gè)字段在月初的很多訂單里確實(shí)錄的是NULL導(dǎo)致計(jì)算結(jié)果差了一截。從此以后我對(duì)聚合函數(shù)的“隱性行為”特別敏感。4.1 NULL值對(duì)聚合結(jié)果的影響先列一個(gè)我自己整理的規(guī)則表建議刻在腦子里場(chǎng)景結(jié)果COUNT(*)統(tǒng)計(jì)所有行不考慮NULLCOUNT(字段)只統(tǒng)計(jì)字段非NULL的行SUM(字段)忽略NULL全NULL則返回NULLAVG(字段)忽略NULL全NULL則返回NULLMAX/MIN忽略NULL全NULL則返回NULLGROUP BY 某列NULL會(huì)單獨(dú)成為一組實(shí)際開(kāi)發(fā)中前三種坑最致命。比如SUM返回NULL這件事如果你是在Java后臺(tái)直接用sumResult字段很可能會(huì)因?yàn)閚ull導(dǎo)致NPE正確做法是用COALESCE(SUM(列), 0)包一層。AVG也一樣如果表里恰好沒(méi)有符合條件的數(shù)據(jù)返回NULL而不是0前端一展示就變成“空白”用戶還以為系統(tǒng)壞了。4.2 浮點(diǎn)數(shù)求和的精度問(wèn)題SQL聚合不只處理整數(shù)更多時(shí)候處理的是金額、百分比、匯率這類浮點(diǎn)數(shù)。很多數(shù)據(jù)庫(kù)的FLOAT/DOUBLE類型在計(jì)算二進(jìn)制小數(shù)時(shí)會(huì)有誤差比如0.1加0.2得到0.30000000000000004。如果你直接拿這個(gè)結(jié)果跟0.3比較結(jié)果是不相等的報(bào)表上則可能看到一連串奇怪的小數(shù)。金融計(jì)算一律用DECIMAL/NUMERIC類型比如DECIMAL(10,2)或者DECIMAL(20,4)。MySQL里SUM(DECIMAL)的精度也是可控的但仍然建議在最終輸出前用ROUND處理一下。另外等值時(shí)不要用“0.3”而是用ABS(SUM(x) - 0.3) 0.000001這種比較方式。4.3 去重聚合與GROUP BY的配合COUNT(DISTINCT 字段)是另一個(gè)高頻陷阱。它確實(shí)能統(tǒng)計(jì)唯一值個(gè)數(shù)但性能很差因?yàn)閿?shù)據(jù)庫(kù)需要去重后才能計(jì)數(shù)。當(dāng)數(shù)據(jù)量大時(shí)這幾乎是所有聚合操作里最慢的。優(yōu)化手段主要有兩種一是先GROUP BY去重再在外面COUNTSELECT COUNT(*) FROM ( SELECT DISTINCT user_id FROM event_log WHERE create_date 2024-01-01 AND user_id IS NOT NULL ) t;這種方式在數(shù)據(jù)量較大時(shí)往往比直接COUNT(DISTINCT user_id)要快。原因在于子查詢里可以先走索引/分組把結(jié)果集縮小后再在外層計(jì)數(shù)。二是如果業(yè)務(wù)中經(jīng)常需要統(tǒng)計(jì)唯一用戶數(shù)干脆在ETL階段就把唯一用戶ID對(duì)應(yīng)的明細(xì)表單獨(dú)拉出來(lái)維護(hù)查詢時(shí)直接查一張已經(jīng)去重的表。這也是“用空間換時(shí)間”的典型場(chǎng)景。4.4 小心GROUP BY的隱式排序MySQL 5.7和8.0的行為差異特別值得留意。5.7及之前GROUP BY默認(rèn)會(huì)按照分組字段排序很多人寫(xiě)“GROUP BY dept_id LIMIT 1”就順手取到了第一條。但8.0里默認(rèn)排序行為變了如果依賴這種隱式順序很可能得到完全不同的結(jié)果。正確做法是需要排序就明確寫(xiě)ORDER BY不要依賴任何隱式行為。同理HAVING也不要跟WHERE混淆。我曾經(jīng)見(jiàn)過(guò)一個(gè)同事把租期在WHERE里寫(xiě)了“租期5年”又在HAVING里寫(xiě)了“租期5年”結(jié)果當(dāng)然是沒(méi)報(bào)錯(cuò)但多了一次過(guò)濾數(shù)據(jù)沒(méi)問(wèn)題但SQL讀起來(lái)很別扭維護(hù)成本很高。4.5 聚合大小與行轉(zhuǎn)列/列轉(zhuǎn)行的坑用聚合做行轉(zhuǎn)列Pivot時(shí)很多人會(huì)寫(xiě)成多個(gè)SUM加CASE WHEN。比如統(tǒng)計(jì)每個(gè)月的銷售額把12個(gè)月變成12列。寫(xiě)法上沒(méi)錯(cuò)但列一多SQL就會(huì)很臃腫動(dòng)態(tài)月份更是難以維護(hù)。更通用的做法是用CASE WHEN配合GROUP BY但如果你用的數(shù)據(jù)庫(kù)支持FILTER子句PostgreSQL那語(yǔ)法會(huì)簡(jiǎn)潔很多SELECT user_id, COUNT(*) FILTER (WHERE status success) AS success_cnt, COUNT(*) FILTER (WHERE status fail) AS fail_cnt FROM orders GROUP BY user_id;MySQL沒(méi)有FILTER只能寫(xiě)CASE WHEN加NULL或0。注意SUM(CASE WHEN ... THEN 1 ELSE 0 END)和COUNT(CASE WHEN ... THEN 1 END)效果一樣但后者如果CASE條件不滿足就會(huì)返回NULLCOUNT會(huì)忽略NULL所以等價(jià)有人會(huì)寫(xiě)成COUNT(CASE WHEN ... THEN 1 ELSE NULL END)也沒(méi)問(wèn)題但不推薦直接用COUNT(CASE WHEN ... THEN 1 END)更簡(jiǎn)潔。4.6 聚合的無(wú)限延伸ROLLUP與GROUPING SETS做報(bào)表時(shí)經(jīng)常遇到“既要每個(gè)部門總?cè)藬?shù)又要所有部門合計(jì)”的情況。傳統(tǒng)寫(xiě)法是兩個(gè)查詢用UNION ALL拼在一起后來(lái)我發(fā)現(xiàn)如果數(shù)據(jù)庫(kù)支持GROUP BY WITH ROLLUP一行搞定SELECT dept_id, COUNT(*) AS emp_cnt FROM employee GROUP BY dept_id WITH ROLLUP;結(jié)果里會(huì)多出一行dept_id為NULL的記錄那就是全部門合計(jì)。PostgreSQL和SQL Server支持GROUPING SETS能更靈活地控制組合維度。這些都是聚合題里比較少直接考但實(shí)際工作卻很加分的點(diǎn)。5. 高頻聚合SQL實(shí)戰(zhàn)從需求到SQL的完整推演紙上談兵再多不如直接上手跑幾個(gè)完整需求。這里我用自己整理過(guò)的三個(gè)高頻案例帶大家完整走一遍從需求到SQL的推演過(guò)程你會(huì)發(fā)現(xiàn)聚合的核心不外乎“邏輯拆分”和“語(yǔ)法細(xì)節(jié)”。5.1 需求1統(tǒng)計(jì)各部門各職位平均薪資表結(jié)構(gòu)很簡(jiǎn)單employee(emp_id, emp_name, dept_id, job_title, salary)。需求是統(tǒng)計(jì)每個(gè)部門、每個(gè)職位的平均薪資并顯示平均薪資排名前3的記錄按平均薪資降序。第一步先寫(xiě)基礎(chǔ)分組SELECT dept_id, job_title, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id, job_title;第二步加排名。SQL Server支持DENSE_RANK()MySQL 8.0、PostgreSQL也支持。我們直接把上面的結(jié)果作為子查詢外面套一個(gè)窗口排名SELECT dept_id, job_title, avg_salary FROM ( SELECT dept_id, job_title, AVG(salary) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) DESC) AS rk FROM employee GROUP BY dept_id, job_title ) t WHERE rk 3;看到這里你會(huì)發(fā)現(xiàn)其實(shí)“每個(gè)部門內(nèi)排名前3”這個(gè)需求核心是先按部門分組算出平均薪資再在部門內(nèi)部做窗口排序兩個(gè)維度分工明確。有人會(huì)問(wèn)能不能不用子查詢直接在原表上用窗口函數(shù)可以但寫(xiě)法會(huì)重復(fù)聚合邏輯。比如SELECT dept_id, job_title, avg_salary, rk FROM ( SELECT dept_id, job_title, AVG(salary) OVER (PARTITION BY dept_id, job_title) AS avg_salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY AVG(salary) OVER (PARTITION BY dept_id, job_title) DESC) AS rk FROM employee ) t WHERE rk 3 GROUP BY dept_id, job_title, avg_salary, rk;這種寫(xiě)法雖然也能跑但窗口函數(shù)嵌套窗口函數(shù)可讀性極差而且有些數(shù)據(jù)庫(kù)對(duì)“ORDER BY后面直接跟窗口聚合函數(shù)”的寫(xiě)法支持并不好。我個(gè)人的習(xí)慣是優(yōu)先用GROUP BY做好聚合再用子查詢包一層處理排名邏輯清晰也方便加WHERE條件。5.2 需求2求每個(gè)用戶連續(xù)登錄天數(shù)這題在面試題里出鏡率極高。給一張login_log(user_id, login_date)一天可能有多條登錄記錄求每個(gè)用戶的最大連續(xù)登錄天數(shù)。解題套路分三步第一步按用戶和日期去重。同一天登錄多次只算一天有效所以要先DISTINCTSELECT DISTINCT user_id, login_date FROM login_log;第二步用ROW_NUMBER()給每個(gè)用戶按日期編號(hào)SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM ( SELECT DISTINCT user_id, login_date FROM login_log ) t;第三步核心公式login_date - rn。因?yàn)閞n是連續(xù)遞增的如果登錄日期也是連續(xù)的那么日期減序號(hào)得到的值一定相同。所以WITH daily AS ( SELECT DISTINCT user_id, login_date FROM login_log ), numbered AS ( SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM daily ), groups AS ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL rn DAY) AS grp FROM numbered ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM groups GROUP BY user_id, grp ORDER BY user_id, start_date;想要最大連續(xù)天數(shù)就再用MAX(consecutive_days)按用戶分組取一次。我在講這個(gè)題時(shí)總會(huì)強(qiáng)調(diào)一個(gè)點(diǎn)SQL解連續(xù)問(wèn)題本質(zhì)上就是“把連續(xù)的日期歸到同一個(gè)組”。日期減去行號(hào)這個(gè)技巧是理解“組的概念”最直觀的案例。一旦理解了碰到“連續(xù)3天活躍”“連續(xù)簽到7天”這類問(wèn)題都能舉一反三。5.3 需求3同比環(huán)比計(jì)算用LAG/LEAD業(yè)務(wù)方常要求“本月銷售額相比上月增長(zhǎng)多少、相比去年同期增長(zhǎng)多少”。如果只有一張按月匯總的銷售表sales_monthly(month_date, sales_amount)用LAG函數(shù)非常方便。SELECT month_date, sales_amount, LAG(sales_amount) OVER (ORDER BY month_date) AS prev_month_sales, sales_amount - LAG(sales_amount) OVER (ORDER BY month_date) AS mom_growth, ROUND( (sales_amount - LAG(sales_amount) OVER (ORDER BY month_date)) / LAG(sales_amount) OVER (ORDER BY month_date) * 100, 2 ) AS mom_growth_rate FROM sales_monthly ORDER BY month_date;同比需要往前推12個(gè)月就用LAG(sales_amount, 12)。但這里有個(gè)常見(jiàn)大坑如果某個(gè)月份沒(méi)有數(shù)據(jù)LAG會(huì)直接跳到再往前一行而不是跳過(guò)空洞找12個(gè)月前的值。要更嚴(yán)謹(jǐn)你得先用ORDER BY month_date保證每月一行如果原表缺月份要先通過(guò)LEFT JOIN日期維度表補(bǔ)齊。另外LAG取不到值時(shí)返回NULL所以計(jì)算增長(zhǎng)率時(shí)一定要用COALESCE或NULLIF避免除零。NULLIF(prev_month_sales, 0)這個(gè)函數(shù)很實(shí)用我一般會(huì)配合COALESCE輸出NULL再在展示層處理。用窗口函數(shù)做同比環(huán)比本質(zhì)上是聚合查詢的另一種延續(xù)聚合是“把多行壓成一行”窗口是“讓每一行走讀相鄰行的值”。理解了這層關(guān)系你就不容易再把窗口函數(shù)和GROUP BY混為一談。6. 刷題與實(shí)戰(zhàn)之間的最后一公里最后聊一點(diǎn)很多人不太重視、但我覺(jué)得很關(guān)鍵的內(nèi)容。刷“高頻SQL 50題”時(shí)我們通常關(guān)注的是“答案對(duì)不對(duì)”。但在實(shí)際工作里SQL的好壞除了正確性還要看可讀性、執(zhí)行效率、可維護(hù)性。同一個(gè)需求有人寫(xiě)出的SQL一眼看懂有人寫(xiě)出的SQL嵌套八層子查詢跑起來(lái)倒是不慢但三個(gè)月后沒(méi)人敢動(dòng)。我個(gè)人的實(shí)踐是在本地建一套和線上結(jié)構(gòu)相同的測(cè)試庫(kù)專門用來(lái)跑各種SQL實(shí)驗(yàn)。刷題的時(shí)候也不要只滿足于通過(guò)測(cè)試用例多問(wèn)問(wèn)自己如果這個(gè)表有1000萬(wàn)行這條SQL會(huì)不會(huì)掛如果業(yè)務(wù)方突然要加一個(gè)過(guò)濾條件我改起來(lái)方不方便如果把這段邏輯做成一個(gè)視圖別人看代碼能不能看懂聚合是所有SQL能力中最不能“只背不練”的一部分。你可以在網(wǎng)上找到無(wú)數(shù)條“語(yǔ)法正確”的SQL但只有真正處理過(guò)臟數(shù)據(jù)、NULL和浮點(diǎn)精度之后才會(huì)明白為什么很多老手會(huì)堅(jiān)持用COALESCE包裹可能為NULL的聚合結(jié)果為什么統(tǒng)計(jì)用戶數(shù)要用COUNT(DISTINCT user_id)而不是COUNT(user_id)。如果你正準(zhǔn)備面試建議把聚合相關(guān)的50題按上面說(shuō)的幾個(gè)分類去刷函數(shù)語(yǔ)義、分組過(guò)濾、窗口排序、連續(xù)問(wèn)題、行轉(zhuǎn)列。每類找到2到3個(gè)代表性題目徹底吃透比刷滿100題更有用。最后再分享一個(gè)小習(xí)慣每次寫(xiě)完一條聚合SQL我都會(huì)強(qiáng)制自己多看一眼那兩樣?xùn)|西——執(zhí)行計(jì)劃里有沒(méi)有出現(xiàn)Using temporary/Using filesort以及統(tǒng)計(jì)結(jié)果里有沒(méi)有意外的NULL值。這兩個(gè)檢查點(diǎn)救了我非常多線上事故。