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