上周和一個做電商運營的朋友聊天。他說團隊打算上一套“高端數(shù)據(jù)分析平臺”第一步是讓技術(shù)部把 MySQL 里的訂單表、用戶表、商品表導到數(shù)據(jù)倉庫再上 BI 看板。結(jié)果光是字段口徑就對了三天支付金額到底要不要包含取消訂單用戶 id 到底按賬號算還是按手機號去重報表剛上線一周業(yè)務(wù)部門又開始抱怨數(shù)據(jù)不對。技術(shù)負責人最后說了一句讓我印象很深的話問題根本不在平臺在數(shù)據(jù)源頭。這個場景太常見了。很多數(shù)據(jù)分析項目一上來就談架構(gòu)、談大數(shù)據(jù)組件卻忽略了一個事實絕大多數(shù)企業(yè)里的核心業(yè)務(wù)數(shù)據(jù)仍然躺在 MySQL 這類關(guān)系型數(shù)據(jù)庫里。MySQL 承載的不僅是一筆筆線上交易更是后續(xù)所有分析、報表、決策的事實基礎(chǔ)。想搭建一套真正能落地的企業(yè)數(shù)據(jù)分析架構(gòu)繞開 MySQL 去談 Spark、Flink、數(shù)據(jù)湖基本等于蓋樓不打地基。我不推銷任何課程也不想羅列工具清單。這篇文章想結(jié)合這幾年在業(yè)務(wù)數(shù)據(jù)項目里的真實體會聊聊以 MySQL 作為核心驅(qū)動時數(shù)據(jù)分析架構(gòu)怎么搭、SQL 怎么用、踩坑怎么排查以及所謂“高級數(shù)據(jù)分析實訓”到底應(yīng)該訓練什么。1. 為什么企業(yè)數(shù)據(jù)分析架構(gòu)繞不開 MySQL1.1 大多數(shù)業(yè)務(wù)系統(tǒng)的數(shù)據(jù)源頭仍然是 MySQL不管是在電商、CRM、ERP還是內(nèi)容管理系統(tǒng)里很多業(yè)務(wù)方選擇的第一代數(shù)據(jù)庫都是 MySQL。原因很直接開發(fā)門檻低、生態(tài)完善、運維資料多、硬成本友好。哪怕公司后來引入了微服務(wù)架構(gòu)、分布式中間件核心的交易、會員、商品、庫存這類數(shù)據(jù)往往仍然落在 MySQL 里。數(shù)據(jù)分析不等于直接對業(yè)務(wù)庫做查詢但任何分析項目都得先和這些源頭數(shù)據(jù)打交道。你可以用 Python 讀取數(shù)據(jù)也可以用 BI 工具直連更可以把數(shù)據(jù)同步到數(shù)倉里但前提是你得理解這些源頭表訂單表里的狀態(tài)字段有哪些枚舉值支付時間和創(chuàng)建時間到底誰先誰后用戶表是單主鍵還是有多套 id如果這些源頭的數(shù)據(jù)理解錯了后面所有加工都會把錯誤放大。很多人以為“高端數(shù)據(jù)分析架構(gòu)”是拿復(fù)雜組件堆出來的但真實企業(yè)里最值錢的分析師往往是那個能把 MySQL 業(yè)務(wù)庫講明白的人。他不需要寫多少高深的算法但每當業(yè)務(wù)問“這個數(shù)為什么變了”他能很快定位到是哪個表、哪個字段、哪個狀態(tài)出了變化。1.2 “核心驅(qū)動”的真實含義“核心驅(qū)動”不是指 MySQL 永遠站在 C 位而是說它承擔了數(shù)據(jù)接入、基礎(chǔ)清洗、口徑固化、輕度聚合這一連串又臟又累的活。在常見的企業(yè)數(shù)據(jù)鏈路里MySQL 是這樣參與數(shù)據(jù)分析的業(yè)務(wù)系統(tǒng)寫入 MySQLMySQL 是一手數(shù)據(jù)的存儲層。數(shù)據(jù)分析師寫 SQL從 MySQL 里做取數(shù)、刷數(shù)、核數(shù)。報表系統(tǒng)或 BI 工具直接連接 MySQL展示實時或準實時的業(yè)務(wù)看板。數(shù)據(jù)同步工具把 MySQL 數(shù)據(jù)搬進數(shù)倉供全公司做更重的分析。也就是說MySQL 是所有數(shù)據(jù)鏈路的入口。它像一個“數(shù)據(jù)收費站”決定了進入下一層的數(shù)據(jù)是否干凈、口徑是否一致、字段是否可信。如果第一公里沒走好后面數(shù)倉再規(guī)范也難彌補源頭數(shù)據(jù)質(zhì)量問題。這也是為什么我始終認為企業(yè)數(shù)據(jù)分析架構(gòu)最先要設(shè)計好的不是大數(shù)據(jù)平臺而是 MySQL 里的表結(jié)構(gòu)、字段規(guī)范、指標口徑和訪問路徑。1.3 不是所有分析都要用大數(shù)據(jù)平臺很多團隊有一種慣性思維報表慢就上大數(shù)據(jù)數(shù)據(jù)量大就搞分布式。但真實場景里有相當一部分分析完全可以在 MySQL 里完成甚至 MySQL 是更合適的選擇。可以參考這幾個判斷標準數(shù)據(jù)量級百萬行以內(nèi)MySQL 配合合理索引完全可以高效分析千萬行只要查詢模式不匪夷所思也能穩(wěn)定跑。真正到上億行、還要做全量復(fù)雜聚合時才需要考慮數(shù)倉或大數(shù)據(jù)引擎。查詢復(fù)雜度如果是多表 join、窗口函數(shù)、分組聚合MySQL 8.0 能覆蓋大多數(shù)日常分析。如果要做全量 ETL、海量日志分析、機器學習特征工程那是另一個領(lǐng)域。時效要求T1 日報、后臺管理報表、運營取數(shù)MySQL 可以直接應(yīng)對。秒級實時大屏、復(fù)雜實時推薦才需要時效性更強的組件。團隊能力如果團隊只有 MySQL 和 BI 基礎(chǔ)硬上一套 Hadoop 生態(tài)只會讓運維和開發(fā)都陷入泥潭。場景MySQL 直接分析數(shù)倉 / 大數(shù)據(jù)組件數(shù)據(jù)量百萬到千萬級查詢可控TB 級以上需要全量計算核心訴求快速看數(shù)、日常報表、輕量聚合復(fù)雜 ETL、全域建模、算法特征時效要求T1 或分鐘級秒級實時或大規(guī)模并行計算團隊基礎(chǔ)熟悉 SQL 和業(yè)務(wù)有專門數(shù)據(jù)研發(fā)和運維團隊這不是說大數(shù)據(jù)平臺沒用而是說“先想清楚問題再選工具”。很多號稱高并發(fā)的分析場景實際數(shù)據(jù)量還不到一百萬行真正的瓶頸是索引缺失、SQL 寫法不合理而不是數(shù)據(jù)庫選型。2. 用 MySQL 搭數(shù)據(jù)底座先解決模型和口徑2.1 數(shù)據(jù)模型不是 DBA 的事分析者也必須懂我見過不少分析師SQL 寫得很溜但拿到表之后完全不明白業(yè)務(wù)過程。他能寫復(fù)雜的子查詢卻不知道“支付金額有負數(shù)”是因為有退款也不知道“訂單狀態(tài)為 5”到底表示已發(fā)貨還是已完成。數(shù)據(jù)分析的本質(zhì)是回答業(yè)務(wù)問題而業(yè)務(wù)問題最終要落到一張張表上。所以哪怕不會做完整的數(shù)倉建模也必須理解兩個基礎(chǔ)概念事實表和維度表。事實表記錄發(fā)生了什么訂單、支付流水、退款記錄、日志訪問。它的特點是一行對應(yīng)一次事件有大量可度量字段比如數(shù)量、金額、時間。維度表描述這個發(fā)生的主體是什么用戶、商品、門店、渠道。它的特點是相對穩(wěn)定提供名稱、分類、屬性等描述信息。簡單說事實表是流水賬維度表是字典。分析一個指標時先想清楚它來自哪張事實表需要按哪些維度描述再決定怎么 join、怎么聚合。2.2 明細表、匯總表、寬表的定位在 MySQL 里做數(shù)據(jù)分析最容易犯的錯是“一張表走天下”。有些剛開始做數(shù)據(jù)的同學恨不得把所有字段都塞進一張大表里結(jié)果表變得異常臃腫查詢慢、更新難、權(quán)限也難控制。實際項目中我更建議把表拆成幾類職責表類型定位使用建議明細表保留每行原始事實是最底層的依據(jù)盡量不刪改按業(yè)務(wù)日期分區(qū)匯總表按日、周、月預(yù)聚合用于固定報表速度快適合高頻查詢寬表把頻繁 join 的維度字段冗余進來面向分析主題減少重復(fù) join舉例來說一套電商分析庫可以這樣組織order_detail訂單明細表一行一個訂單明細。sku_daily_summary商品每日匯總表由定時任務(wù)生成。user_info_extend用戶寬表包含用戶基礎(chǔ)屬性、最近一次下單時間、累計消費金額等。寬表不是不能用而是不要一開始就追求“萬能大寬表”。正確的思路是底層明細表保持相對規(guī)范分析層再按主題適度冗余。這樣既保證數(shù)據(jù)一致性又提升查詢效率。2.3 指標口徑要前置用視圖固化下來口徑不統(tǒng)一是所有數(shù)據(jù)分析團隊最痛苦的問題。同一個銷售額有人統(tǒng)計下單金額有人統(tǒng)計支付金額還有人統(tǒng)計扣除退款后的凈額。大家各寫各的 SQL到月底一對數(shù)誰也說服不了誰。解決口徑問題技術(shù)手段只是輔助關(guān)鍵是把定義先定清楚。每個團隊都應(yīng)該維護一份指標字典明確指標名稱、定義、計算公式、統(tǒng)計維度、有效時間。定義清楚了再用數(shù)據(jù)庫手段固化。我最推薦的做法是把一些高頻并且口徑穩(wěn)定的指標封裝成視圖。CREATE VIEW v_gmv_daily AS SELECT DATE(pay_time) AS stat_date, SUM(order_amount) AS gmv FROM orders WHERE status paid AND refund_status 0 GROUP BY DATE(pay_time);這是一個很典型的示意把所有已支付且未退款的訂單按支付日期匯總成 GMV。以后業(yè)務(wù)要查日 GMV直接SELECT * FROM v_gmv_daily不需要每個人重新寫一遍判斷邏輯。口徑不統(tǒng)一時所有高級分析都是空中樓閣。先統(tǒng)一“銷售額怎么算”再談“銷售額預(yù)測”。3. 從 SQL 查詢到分析能力七成場景靠這些手段3.1 分析型 SQL 和普通業(yè)務(wù) CRUD 不是一回事業(yè)務(wù)開發(fā)寫 SQL 常常是“按主鍵查一行”或者“更新某個狀態(tài)字段”。分析型 SQL 面對的是全量數(shù)據(jù)要做聚合、分組、排序、比例、去重、時間對比。很多從 CRUD 轉(zhuǎn)過來的開發(fā)者剛接觸數(shù)據(jù)分析時會有一種“會語法但不會解題”的感覺。核心原因在于分析型任務(wù)更看重怎么把業(yè)務(wù)問題翻譯成數(shù)據(jù)操作。比如怎么算每個用戶最近一次下單時間怎么算相鄰兩筆訂單的時間間隔怎么算每個商品被多少個用戶復(fù)購怎么算某個月的新客在次月留存率這些問題單獨看都不難但組合在一起就涉及 join、聚合、窗口函數(shù)、時間函數(shù)、子查詢的綜合運用。打好這個底子比背 100 個函數(shù)更重要。3.2 窗口函數(shù)復(fù)雜分析場景的真正分水嶺不少分析任務(wù)用GROUP BY能做到但代價是丟失明細。遇到“用戶最近一單”“商品排名”“累計銷售額”這類需求窗口函數(shù)是更優(yōu)雅的方案。窗口函數(shù)和普通聚合最大的區(qū)別是它在不減少行數(shù)的前提下對每一行做計算。常用的包括ROW_NUMBER()按分組生成序號。RANK()/DENSE_RANK()排名。LAG()/LEAD()取當前行前一行或后一行的值。SUM(...) OVER(PARTITION BY ...)分組累計。看一個真實高頻的例子求每個用戶最近一次下單時間。SELECT user_id, order_time FROM ( SELECT user_id, order_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;這段 SQL 的邏輯是先把每個用戶的訂單按時間倒序編號再取出編號為 1 的那一行。它保留了所有明細只是把“最近一次”這個概念表達出來了。一定要留意版本MySQL 8.0 才支持窗口函數(shù)。如果還在用 5.7這類需求通常要改寫成用戶變量或自連接實現(xiàn)更繞也更容易出錯。所以做數(shù)據(jù)分析實訓第一件事就是確認數(shù)據(jù)庫版本。3.3 用視圖和存儲過程把重復(fù)分析固化成模板日常取數(shù)里會有大量重復(fù)工作。比如每周都要跑一次銷售周報每個月都要統(tǒng)計一次用戶增長。如果每次都從頭寫 SQL不僅慢而且容易因為一個過濾條件的差異導致結(jié)果對不上。更推薦的做法是把穩(wěn)定的分析邏輯固化成視圖或存儲過程。視圖適合“查詢模板”比如前面說的日 GMV 視圖、周復(fù)購率視圖。業(yè)務(wù)或分析師只需要查視圖不需要理解背后的表關(guān)系。存儲過程適合“定時計算”比如每天凌晨把前一天的匯總結(jié)果寫入?yún)R總表形成日報數(shù)據(jù)。一個簡單的存儲過程示意DELIMITER // CREATE PROCEDURE sp_sales_daily_report() BEGIN INSERT INTO sales_daily_summary(stat_date, gmv, order_cnt, user_cnt) SELECT DATE(pay_time) AS stat_date, SUM(order_amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS user_cnt FROM orders WHERE status paid AND pay_time CURDATE() - INTERVAL 1 DAY AND pay_time CURDATE() GROUP BY DATE(pay_time); END // DELIMITER ;我這里只是給了個整體結(jié)構(gòu)真實環(huán)境里還要處理冪等防止重復(fù)跑任務(wù)、日志記錄、失敗告警。存儲過程不是銀彈復(fù)雜業(yè)務(wù)邏輯放進應(yīng)用層更易調(diào)試。但簡單的、高頻率的匯總?cè)蝿?wù)用存儲過程確實能減少很多重復(fù)勞動。3.4 用 EXPLAIN 驗證性能別只憑感覺SQL 寫出來能跑只是第一步。數(shù)據(jù)分析師如果處理的數(shù)據(jù)量上來還得有性能意識。MySQL 里最直接的調(diào)優(yōu)入口就是EXPLAIN。EXPLAIN SELECT user_id, order_time FROM orders WHERE status paid ORDER BY pay_time DESC;執(zhí)行后重點看幾個字段type是否走了索引至少要達到ref級別ALL說明全表掃描。key實際用到的索引。rows預(yù)估掃描多少行數(shù)字越大越危險。Extra出現(xiàn)Using filesort或Using temporary時要想想能否通過索引優(yōu)化。很多人調(diào) SQL 只看執(zhí)行時間但執(zhí)行時間會受數(shù)據(jù)量、CPU、網(wǎng)絡(luò)影響。用EXPLAIN能看到更本質(zhì)的東西這條 SQL 是不是在用一種高效的方式獲取數(shù)據(jù)。4. 從單表到集群數(shù)據(jù)量上來以后怎么演進4.1 先判斷 MySQL 是否需要拆分有些團隊一聊到“架構(gòu)升級”第一反應(yīng)就是分庫分表、上分布式中間件。但分庫分表是一種把復(fù)雜度從 SQL 層轉(zhuǎn)移到架構(gòu)層的決策成本很高遠不是慢查詢的銀彈。要不要拆分應(yīng)該先看四個指標單表數(shù)據(jù)量是否持續(xù)突破億級歸檔和分區(qū)是否已經(jīng)失效連接數(shù)數(shù)據(jù)庫連接是否頻繁打滿連接池是否存在大量等待慢查詢占比優(yōu)化 SQL 和索引后慢查詢是否仍然居高不下備份恢復(fù)時長單庫備份或恢復(fù)時間是否已經(jīng)長到不可接受如果只是為了跑一個報表優(yōu)先嘗試優(yōu)化 SQL、加索引、建匯總表而不是拆庫。很多項目的實際數(shù)據(jù)量不到百萬行問題出在一個LIKE %xxx%導致全表掃描或者日期字段上用了函數(shù)導致索引失效。這種時候談分布式屬于用大炮打蚊子。用分庫分表解決慢查詢是最昂貴的優(yōu)化方式。它把問題從“SQL 怎么寫”變成了“架構(gòu)怎么維護”。4.2 主從復(fù)制與讀寫分離當 MySQL 承擔了在線業(yè)務(wù)又有數(shù)據(jù)分析需求時最自然的演進是主從復(fù)制與讀寫分離。主庫負責增刪改從庫負責查詢分析報表盡量走從庫避免把線上業(yè)務(wù)庫壓垮。讀寫分離的架構(gòu)并不復(fù)雜主庫開啟二進制日志從庫通過 I/O 線程拉取日志并回放保持和主庫一致。日常查詢連接從庫寫操作連接主庫。但這個方案有一個繞不開的問題主從延遲。從庫同步是異步進行的在寫入量大的時刻從庫可能滯后主庫幾百毫秒甚至幾秒。如果業(yè)務(wù)要求“我剛下單馬上能在報表里看到”從庫不一定能滿足。實際落地通常是這樣區(qū)分實時性要求高的關(guān)鍵查詢走主庫。運營看板、后臺報表、分析查詢走從庫。對延遲極度敏感的場景考慮半同步復(fù)制或引入緩存。用 GTID 復(fù)制比傳統(tǒng)基于日志位點的復(fù)制更好維護切換主從時更安全新建從庫也會簡單不少。4.3 分庫分表和分布式架構(gòu)的邊界分庫分表一般是在數(shù)據(jù)量或?qū)懭雺毫Φ搅艘欢ǔ潭群蟛趴紤]。真正的難點不是把數(shù)據(jù)拆開而是拆完之后的查詢邏輯變得復(fù)雜。舉個例子訂單表按user_id分片后單用戶的訂單查詢很快天然解決了數(shù)據(jù)量大的問題。但如果你想分析“全平臺近 30 天銷量 Top 100 商品”數(shù)據(jù)分散在幾十個分片里就必須聚合所有分片再排序。MySQL 本身不擅長跨庫聚合于是你要引入中間件、匯總層或者干脆把數(shù)據(jù)同步到數(shù)倉里去算。所以在搭建數(shù)據(jù)分析架構(gòu)時一定要提前想清楚分片的邊界按什么鍵分片決定了高頻查詢能不能命中單個分片。跨分片 join 會變得極其困難要盡量避免。全量分析、多維分析不適合在分片后的 MySQL 上做交給數(shù)倉或 OLAP 引擎更合理。如果已經(jīng)走到分庫分表這一步MySQL 的角色就開始從“分析引擎”變成“業(yè)務(wù)源庫”了。真正的分析計算應(yīng)該由后面更擅長批量聚合的組件承擔。4.4 MySQL 在數(shù)據(jù)鏈路里的真實位置一個相對完整的數(shù)據(jù)分析架構(gòu)可以簡化成這樣的鏈路業(yè)務(wù)應(yīng)用 - MySQL業(yè)務(wù)庫- 同步工具 - 數(shù)倉/數(shù)據(jù)湖ODS/DWD/ADS- BI/API這里的同步工具常見的有基于 Binlog 的實時同步組件也有離線批量同步工具。但不管用哪種MySQL 都是數(shù)據(jù)可信度的第一道防線。源頭的字段類型、枚舉值、時間格式、主外鍵關(guān)系會一路傳遞到數(shù)倉最終影響報表和決策。這也是“核心驅(qū)動”的另一層含義MySQL 不只是數(shù)據(jù)倉庫的上游它定義的業(yè)務(wù)邏輯和字段語義決定了整個分析架構(gòu)能走多遠。5. 真正落地時最容易踩的坑與排查鏈路5.1 數(shù)據(jù)分析報錯別急著改 SQL先按鏈路排查很多新人遇到報錯第一反應(yīng)是百度或改 SQL。但實際問題可能根本不在 SQL而在環(huán)境、配置或數(shù)據(jù)本身。我建議遇到任何分析異常都按這個順序排查層級要檢查什么典型現(xiàn)象現(xiàn)象具體報錯卡住結(jié)果不對SQL 返回空、執(zhí)行超時、統(tǒng)計與業(yè)務(wù)對不上輸入源表結(jié)構(gòu)、字段類型、join 基數(shù)、時間范圍字段不存在、join 后行數(shù)翻倍、時間差 8 小時環(huán)境MySQL 版本、字符集、時區(qū)、權(quán)限窗口函數(shù)不支持、中文亂碼、遠程連不上參數(shù)連接超時、批量大小、排序內(nèi)存大批量查詢斷開、limit 深分頁慢工具邊界語法兼容、驅(qū)動認證、調(diào)度平臺老客戶端報認證不支持、存儲過程權(quán)限受限排查順序不要反過來。我曾經(jīng)幫同事查一個“統(tǒng)計結(jié)果多了一倍”的問題他一直在改 SQL最后發(fā)現(xiàn)是 join 的表里存在一對多關(guān)系導致訂單主表被放大。問題不在 SQL 語法而在輸入層的數(shù)據(jù)關(guān)系理解。5.2 SQL 編寫中的高頻錯誤有幾個 SQL 錯誤幾乎每周都會看到。第一類UPDATE忘了加WHERE。UPDATE users SET status 1;這條 SQL 會把全表的狀態(tài)都改成 1。MySQL 里可以通過sql_safe_updates來約束開啟后不帶WHERE或沒有走索引的UPDATE/DELETE會被拒絕。SET sql_safe_updates 1; UPDATE users SET status 1 WHERE id 100;這只是一個客戶端會話設(shè)置生產(chǎn)環(huán)境更推薦從賬號權(quán)限和應(yīng)用規(guī)范上約束。第二類排序和分頁性能差。ORDER BY字段沒有索引時MySQL 會做filesort。分頁越深OFFSET越大性能下降越明顯。更穩(wěn)妥的做法是用“上一頁最后一條記錄 ID”做條件查詢而不是不斷加深OFFSET。第三類字段類型隱式轉(zhuǎn)換。當字段是VARCHAR你用數(shù)字條件去查MySQL 可能放棄索引。比如SELECT * FROM users WHERE phone 13800000000;如果phone是VARCHAR這里的數(shù)字會轉(zhuǎn)成字符串但有時候索引就是不走。更穩(wěn)妥的做法是優(yōu)先保證類型一致。第四類JOIN條件不完整。分析結(jié)果突然翻倍時先檢查是不是一對多 join 導致的而不是急著加過濾條件。所有 join 都應(yīng)該清楚左表一行右表可能匹配多少行。第五類聚合和明細混用。在沒有ONLY_FULL_GROUP_BY模式的 MySQL 中SELECT非聚合列但沒在GROUP BY里能執(zhí)行但結(jié)果不確定。建議在數(shù)據(jù)庫配置里打開ONLY_FULL_GROUP_BY從機制上避免這類問題。5.3 環(huán)境、安裝和客戶端連接坑數(shù)據(jù)分析環(huán)境里安裝 MySQL 和連接 MySQL 也是高頻問題。在 Windows 上安裝 MySQL 時服務(wù)啟動失敗常見原因有端口被占用、配置文件寫錯、data 目錄沒有初始化權(quán)限。如果安裝時遇到服務(wù)無法啟動先看錯誤日志通常比卸載重裝有效得多。MySQL 8 默認使用caching_sha2_password認證插件。一些老版本客戶端工具或驅(qū)動在連接時會報認證協(xié)議不支持的錯。解決思路有兩種升級客戶端驅(qū)動或者把對應(yīng)用戶的認證插件改回mysql_native_password。在本地做實驗時也可以直接用 Docker 起一個 MySQL。這是我個人比較推薦的方式因為干凈、易清理、可以隨時換版本。docker run -d \ --name mysql \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDyourpassword \ -e TZAsia/Shanghai \ -v /data/mysql:/var/lib/mysql \ mysql:8注意兩點一是掛載數(shù)據(jù)目錄防止容器刪除后數(shù)據(jù)丟失二是設(shè)置時區(qū)避免 MySQL 內(nèi)部時間和業(yè)務(wù)時間相差 8 小時。這個命令只適合本地實驗生產(chǎn)環(huán)境還要考慮參數(shù)文件、備份策略和資源限制。5.4 數(shù)據(jù)結(jié)果驗證分析報告的最后一道關(guān)口分析結(jié)果是要支撐決策的不能“看著差不多”就提交。我給自己定過幾條鐵律抽樣核對隨機抽 50 到 100 條明細手工算一遍關(guān)鍵字段。交叉驗證同一個指標用兩種不同 SQL 實現(xiàn)方式去算看結(jié)果是否一致。對照業(yè)務(wù)和分析系統(tǒng)的導出、財務(wù)系統(tǒng)的報表、運營后臺的計數(shù)做對比。波動檢查和上一周期對比任何不合理的大幅波動都要先解釋清楚再往下發(fā)報告。運行成功不等于結(jié)果正確。做數(shù)據(jù)分析必須把“結(jié)果驗證”當作流程的一部分而不是額外工作。6. 把“高級實訓”落到日常比堆課時更重要6.1 搭建一個最小實驗環(huán)境不管你是剛開始學數(shù)據(jù)分析還是想補 MySQL 這塊短板我都會建議先花一下午搭一個最小實驗環(huán)境而不是光看視頻或讀文檔。步驟很簡單本地安裝 MySQL 8或用 Docker 起一個實例。準備一份盡量接近真實業(yè)務(wù)的樣例數(shù)據(jù)比如電商訂單、用戶、商品表。通過命令行或 Workbench 連上數(shù)據(jù)庫先跑通最基本的增刪改查。mysql -h127.0.0.1 -uroot -p SHOW DATABASES;如果本機沒有歷史數(shù)據(jù)可以用 Python 生成模擬訂單也可以導入開源練習數(shù)據(jù)集。關(guān)鍵是數(shù)據(jù)本身要有一點復(fù)雜度至少包含多張有關(guān)聯(lián)的表否則后面很難練習 join 和聚合。6.2 一個可復(fù)用的五步分析法當手里有一份數(shù)據(jù)面對一個開放的分析問題時怎么下手我總結(jié)過一個五步法后面做任何分析項目都可以先按這個流程過一遍。第一步理解業(yè)務(wù)問題。業(yè)務(wù)方問“復(fù)購率怎么樣”你要先搞清楚他關(guān)心的是哪個商品、哪個時間周期、哪個用戶群體。第二步定義口徑。復(fù)購率是復(fù)購用戶數(shù)除以購買用戶數(shù)還是復(fù)購訂單數(shù)除以總訂單數(shù)一個用戶買同一商品兩次算不算復(fù)購這些必須提前確認。第三步設(shè)計 SQL。從哪些表取數(shù)主表和從表按什么鍵 join在哪里過濾、在哪里聚合第四步實現(xiàn)并驗證。執(zhí)行 SQL 后抽樣核對和業(yè)務(wù)系統(tǒng)交叉驗證。第五步展示與迭代。用 BI 或 Python 可視化拿到業(yè)務(wù)反饋后再修正。拿電商復(fù)購率舉例核心 SQL 大致長這樣SELECT product_id, COUNT(DISTINCT user_id) AS buy_users, SUM(CASE WHEN buy_cnt 2 THEN 1 ELSE 0 END) AS repurchase_users FROM ( SELECT product_id, user_id, COUNT(*) AS buy_cnt FROM order_items WHERE order_time NOW() - INTERVAL 30 DAY GROUP BY product_id, user_id ) t GROUP BY product_id;這個寫法里內(nèi)層子查詢算出每個用戶在每個商品上的購買次數(shù)外層按商品統(tǒng)計購買人數(shù)和復(fù)購人數(shù)。真實業(yè)務(wù)里還要剔除測試訂單、退款訂單、特殊渠道等但框架就是這樣。6.3 把經(jīng)驗沉淀成指標字典和 SQL 模板獨立做完一個分析項目后最該產(chǎn)出的不是結(jié)果表而是三樣能被反復(fù)使用的東西。第一是指標字典。每個指標一句定義寫清楚分子、分母、統(tǒng)計維度、特殊規(guī)則。以后任何人問起“這個指標怎么算”都只需要查字典。第二是 SQL 模板。把高頻查詢整理成模板包括基礎(chǔ)表