據(jù)技能賽數(shù)據(jù)清洗與分析：25分拿分策略與實戰(zhàn)技巧)
1. 25分到底考什么任務(wù)三第4子任務(wù)的定位與分值拆分1.1 賽項里數(shù)據(jù)清洗與分析為什么要單獨考2019年全國職業(yè)技能大賽大數(shù)據(jù)技術(shù)賽項的整套賽題是一個完整的離線數(shù)據(jù)業(yè)務(wù)鏈路數(shù)據(jù)產(chǎn)生之后進入采集通道落到存儲引擎然后交給清洗環(huán)節(jié)去粗取精最后再通過分析把數(shù)據(jù)變成能輔助決策的結(jié)果。任務(wù)三的第4個子任務(wù)數(shù)據(jù)清洗與分析恰恰卡在鏈路最關(guān)鍵的腰部——前面做好的數(shù)據(jù)表能不能用、后面分析出來的結(jié)論有沒有說服力都取決于這道題做得扎實不扎實。參加過這類賽事的選手都知道賽題里的任務(wù)編號有不少門道。任務(wù)三放在整套題的中間偏后位置意味著前幾個子任務(wù)已經(jīng)把原始數(shù)據(jù)準備好了到這一道題時你面對的往往是帶著缺失值、重復(fù)行、超范圍數(shù)值、格式不統(tǒng)一的大寬表。職業(yè)技能大賽的出題邏輯和學(xué)校里的課程作業(yè)完全不一樣課程作業(yè)里數(shù)據(jù)基本是干凈的大賽里的數(shù)據(jù)則是從真實業(yè)務(wù)場景里抽出來的臟得有多自然題目就有多真實。這道25分的題本質(zhì)上是考察兩件事你能不能把臟數(shù)據(jù)理順以及理順之后你能不能問出有價值的問題。1.2 分值是均勻分布還是集中給點凡是認真研究過這份賽題答案的人都能發(fā)現(xiàn)一個規(guī)律25分不是均勻撒在每一步操作上的。出題方會故意讓某個清洗動作占8分、某個分析結(jié)果占10分剩下的幾分留給過程和輸出格式這樣做是為了拉開區(qū)分度——只會點點鼠標的人拿不到高分真正理解數(shù)據(jù)的人能一路暢通。按照歷年備賽時對照題目答案的統(tǒng)計習(xí)慣這道題的分值在心里可以這樣預(yù)估數(shù)據(jù)清洗部分大約占812分集中在缺失值處理、重復(fù)行刪除、異常值修正這些硬性操作上數(shù)據(jù)分析部分占1012分通常要求輸出分組統(tǒng)計結(jié)果、同比環(huán)比或者排行前幾的明細最后還有23分給的是導(dǎo)出文件命名、字段命名、表頭規(guī)范這類看起來不起眼但評分時繞不開的細節(jié)。明確了分值分布再去答題策略就完全不一樣了清洗環(huán)節(jié)寧可多花時間也不能丟分分析環(huán)節(jié)要優(yōu)先寫清楚結(jié)論口徑輸出格式要留出最后5分鐘專門檢查而不是全部一股腦兒做完。很多第一次參賽的人最大的誤區(qū)是先把數(shù)據(jù)全清洗干凈再開始分析。事實上賽題并不會給你一條完美的清洗主線多數(shù)情況下清洗到能回答目標問題的程度就夠了不必追求理論上的絕對干凈。2. 數(shù)據(jù)清洗考試最穩(wěn)的拿分點也是翻車最集中的環(huán)節(jié)2.1 缺失值別一上來就刪先回答為什么空數(shù)據(jù)清洗的第一刀幾乎永遠落在缺失值上。賽題給的表很少有干干凈凈沒有空格的常見的情況有三種某個字段整列都是空、某幾行關(guān)鍵字段缺失、以及用空字符串或者null字符串冒充空值。這三種情況的處理策略完全不一樣混在一起處理就會翻車。先處理整列都空的情況。這一列如果確實是賽題故意留的空列它對你的分析目標沒有任何用處直接判斷不需要納入結(jié)果表即可。但如果題目后邊的分析題要用到這個字段那說明數(shù)據(jù)鏈路在更早的環(huán)節(jié)出了問題這時候就要回到前面幾個子任務(wù)的產(chǎn)出目錄里去找原始文件重新核對字段對應(yīng)關(guān)系。某個字段部分缺失時需要看缺失比例缺失比例不到5%且該字段是數(shù)值型時可以用分組中位數(shù)或者均值填充缺失比例高但它又是分析主鍵的一部分就要回到主鍵關(guān)聯(lián)邏輯去排查看看是不是上游join出了問題。另外要注意Excel或者文本文件里常見的null字符串這種看起來是空、實際上占著字符位置的值在數(shù)值計算時最坑人必須先統(tǒng)一替換成真正的空再走填充邏輯。拿SQL環(huán)境舉例一個穩(wěn)妥的處理順序是-- 1. 把字符串形式的NULL統(tǒng)一成真NULL UPDATE cleaned_sales SET quantity NULL WHERE quantity null; -- 2. 用分組中位數(shù)填充數(shù)值型缺失 UPDATE cleaned_sales t JOIN ( SELECT category, MEDIAN(price) AS m_price FROM cleaned_sales WHERE price IS NOT NULL GROUP BY category ) x ON t.category x.category SET t.price x.m_price WHERE t.price IS NULL;這里用中位數(shù)而不是平均值是因為大賽數(shù)據(jù)經(jīng)常有極端價格存在均值會被帶偏中位數(shù)更穩(wěn)健。這個細節(jié)很多人不知道但恰好在某些年份賽題答案的給分點上出現(xiàn)過。2.2 異常值用業(yè)務(wù)規(guī)則而不是統(tǒng)計規(guī)則去篩異常值處理是數(shù)據(jù)清洗里最容易引起爭議的部分因為異常的定義本身就模糊。賽題里最常出現(xiàn)的異常類型大概是這么幾類異常類型典型表現(xiàn)推薦處理方式范圍外數(shù)值年齡出現(xiàn)300、價格出現(xiàn)負數(shù)按業(yè)務(wù)字段合理范圍修正或剔除邏輯矛盾下單時間晚于發(fā)貨時間以主流程時間為準修正前后順序單位不一致部分記錄按元部分按萬元統(tǒng)一換算基準單位后再參與計算重復(fù)值同一主鍵出現(xiàn)多行內(nèi)容完全相同按主鍵去重保留第一條極端離群點個別值遠超正常范圍結(jié)合業(yè)務(wù)判斷是否保留并單獨標注針對范圍外數(shù)值建議直接按業(yè)務(wù)常識定規(guī)則年齡字段必須落在0到120之間價格字段必須大于等于0。有些選手遇到負價格第一反應(yīng)是刪行結(jié)果把有效記錄給刪了。更好的做法是先查一下這個字段在其他子任務(wù)里的口徑比如負價格可能是退款金額被混進了銷售明細這種情況應(yīng)該拆分到退款表而不是粗暴刪除。邏輯矛盾的處理方向要記住一條鐵律以主鏈路的時間節(jié)點為基準。下單時間晚于發(fā)貨時間這種矛盾通常是錄入時區(qū)不同導(dǎo)致的分析時要統(tǒng)一到發(fā)貨時間所在時區(qū)之后再判斷。如果賽題沒有特別說明時區(qū)問題就用任務(wù)一里元數(shù)據(jù)表給出的默認時區(qū)所有時間的清洗口徑保持一致保證后續(xù)分析不會因為時區(qū)混用而對不上數(shù)。2.3 重復(fù)值與格式統(tǒng)一最容易順手丟分的細節(jié)重復(fù)值處理聽起來簡單實際操作里面有個很隱蔽的坑很多表里看起來重復(fù)的行其實只是主鍵以外的字段完全一樣但主鍵字段并不重復(fù)。這類情況不能直接刪得先看主鍵有沒有唯一性約束。如果一張干凈的業(yè)務(wù)流水表里存在同一訂單號出現(xiàn)兩次的情況要么是上游抽數(shù)時發(fā)生了重復(fù)要么是訂單表本身含有多行明細后者并不算異常。正確做法是先做一次分組計數(shù)確認重復(fù)的粒度。SELECT order_id, COUNT(*) AS cnt FROM raw_sales GROUP BY order_id HAVING COUNT(*) 1;如果重復(fù)粒度確實是整行所有字段全部一樣那基本可以判斷是抽取環(huán)節(jié)帶出來的冗余保留任意一行即可。如果只有部分字段重復(fù)而其他字段不同則要看這些不同字段是否構(gòu)成分析所需的維度否則保留一行并標注疑似重復(fù)即可。這個先確認再動手的習(xí)慣在競賽里直接決定了你刪的是臟數(shù)據(jù)還是有效數(shù)據(jù)。格式統(tǒng)一的坑則更瑣碎日期字段有的帶時分秒、有的只有日期字符串字段里隱藏著肉眼看不見的全角空格數(shù)值字段被存成了文本導(dǎo)致排序異常。這些問題的共性解法是先探查后清洗。拿到表之后先做一輪字段級別的distinct抽樣用眼睛確認每個字段的真實形態(tài)再寫清洗腳本。競賽時間緊很多選手跳過探查直接寫清洗邏輯結(jié)果清洗腳本跑出來的結(jié)果比原始數(shù)據(jù)還要奇怪這類教訓(xùn)每年都有。3. 數(shù)據(jù)分析讓結(jié)果輸出向考點意圖靠攏3.1 從題目措辭反推分析目標數(shù)據(jù)清洗做完緊接著的分析題才是真正拉分的環(huán)節(jié)。歷年賽題里的分析問法其實翻來覆去就那么幾類但每年都有大量選手在分析上跑偏根源在于沒有仔細讀題。分析題的題干往往在措辭里藏著口型要求排名前10的一定要有排序和limit要求占比情況的一定要有分母要求同比/環(huán)比的一定要有時間維度做join。一個實用的方法是把題干里的動詞和名詞拆出來對照。比如題干寫統(tǒng)計每月各類別銷售總額那輸出表至少要包含月份、類別、銷售總額三個字段一個都不能少寫對比上半年與下半年的客單價差異則必須有上半年、下半年兩個分組且要明確客單價的分子和分母口徑。這些內(nèi)容在閱卷時都是按字段給分的少寫一個字段哪怕計算結(jié)果完全正確也給不了滿分。3.2 分組聚合的寫法和輸出口徑用SQL還是用Python做分析取決于賽題給你的數(shù)據(jù)形態(tài)和你更熟練的工具。但無論用哪個分組聚合的輸出口徑必須統(tǒng)一。所謂口徑就是每個指標的計算定義。比如銷售額到底是訂單原價還是實際支付價用戶數(shù)到底是去重用戶還是訂單用戶數(shù)。賽題后面如果有附屬說明表一般會寫明口徑如果沒有就按最符合業(yè)務(wù)直覺的規(guī)則來并且在答案里寫明自己的口徑選擇。閱卷時能看出你做了口徑判斷這本就是分析能力的一部分。以SQL為例子一個完整的分組分析往往長成這樣SELECT DATE_FORMAT(order_date, %Y-%m) AS month, category, ROUND(SUM(actual_amount), 2) AS total_amount, COUNT(DISTINCT user_id) AS user_cnt, ROUND(SUM(actual_amount) / COUNT(DISTINCT user_id), 2) AS per_user_avg FROM cleaned_sales WHERE order_status completed GROUP BY month, category ORDER BY month, total_amount DESC;這里有個關(guān)鍵習(xí)慣把where條件放在聚合之前先過濾再聚合別等聚合結(jié)果出來再過濾。這不僅是SQL執(zhí)行效率的問題更是避免邏輯錯誤的關(guān)鍵。很多選手喜歡在子查詢里先分組再在外面加where寫出來特別繞一不留神就把分組后的聚合結(jié)果當(dāng)成過濾條件最后對不上答案。3.3 比對的幾種常見形態(tài)環(huán)比、同比、占比、排名大賽分析題最愛考的四類輸出邏輯各有各的坑。環(huán)比分析一定會涉及上一個時間段的數(shù)據(jù)最常見的寫法是用lag窗口函數(shù)或者自關(guān)聯(lián)取上一周期值。用SQL的時候推薦先用窗口函數(shù)把上一周期值放到同一行再做差這樣邏輯清晰。自關(guān)聯(lián)寫法容易在多對多join時產(chǎn)生重復(fù)計數(shù)一旦基數(shù)對不上結(jié)果就亂了。同比分析則要特別注意時間窗口的天數(shù)差異比如2月的同比要處理閏年季度同比要處理季度天數(shù)不同。賽題如果沒特別說明默認按自然月/自然季對齊即可不需要引入工作日調(diào)整這種復(fù)雜邏輯。占比分析要先確認分子分母的層級比如某品類占全店銷售的比例分母應(yīng)該是全店整體而非當(dāng)前篩選范圍。還有個隱藏的坑分母里要不要包含退款訂單。這個要看題干的銷售額是否已經(jīng)做過退款剔除如果前面清洗時已經(jīng)把退款單刪掉了分母就直接用清洗后的數(shù)據(jù)不需要二次處理。排名分析反而最簡單最需要注意的就是并列排名怎么算。SQL里rownum、rank、dense_rank之間的差別賽題答案通常按rank并列會跳號來給分。如果只用order by加limit而完全避開排名函數(shù)遇到并列時輸出行數(shù)會跟答案對不上這也是一個高頻丟分點。4. 工具鏈選擇與比賽環(huán)境的隱形限制4.1 選SQL還是Python看賽題給的數(shù)據(jù)形態(tài)很多選手備賽時糾結(jié)到底用SQL還是Python做數(shù)據(jù)清洗分析其實這個問題在比賽現(xiàn)場根本不該糾結(jié)。判斷標準很簡單數(shù)據(jù)量級不大、字段結(jié)構(gòu)清晰的場景SQL的調(diào)試成本更低出結(jié)果更快數(shù)據(jù)有多表關(guān)聯(lián)、復(fù)雜清洗鏈路、自定義函數(shù)處理邏輯的場景Python的代碼可維護性更高不容易在中間步驟里迷路。我個人的建議是清洗階段以SQL為主、Python為輔分析階段反過來。原因是清洗階段的探查—修改—驗證循環(huán)很頻繁SQL里寫一句select再套一層update執(zhí)行力非常順滑到了分析階段需要連續(xù)做多層篩選、分組、合并Python里用pandas鏈式操作比SQL層層嵌套可讀性強得多。曾經(jīng)和我一起備賽的一位A同學(xué)在模擬題里堅持所有步驟都用SQL結(jié)果到第三層嵌套子查詢時把自己繞暈了最后在時間上吃了大虧。后來他改成清洗用SQL分析用pandas速度提升了至少一半。4.2 編碼與路徑比賽環(huán)境里的第一個坑正式比賽環(huán)境里最容易出現(xiàn)的問題根本不是分析邏輯跑不通而是文件讀不出來。數(shù)據(jù)文本的編碼格式是GBK還是UTF-8在Windows環(huán)境下經(jīng)常是重災(zāi)區(qū)。很多選手把csv文件拖進Excel或者Python里直接讀看到亂碼才意識到編碼不對這一來一回就是十幾分鐘。正確的做法是建表或讀取之前先看一遍任務(wù)說明里注明的編碼格式如果沒有注明就用記事本或者文本工具打開文件看一眼亂碼就切換到GBK沒亂碼就是UTF-8。Python讀取時的寫法是這樣的import pandas as pd df pd.read_csv(sales.csv, encodinggbk) # 如果還是亂碼可以換成 encodingutf-8 # 還可以用 errorsignore 先跑通再回來補編碼另一個坑是文件路徑不能帶中文。某些比賽環(huán)境里工具鏈對中文路徑支持不完善一旦工作目錄或者輸出路徑里有中文程序就會在某個莫名其妙的步驟里報錯。穩(wěn)妥的做法是開賽后第一件事就是在純英文路徑下建立自己的工作目錄把題目給到的數(shù)據(jù)文件全部拷貝進去后面所有的中間結(jié)果都往這個目錄里寫。4.3 結(jié)果表導(dǎo)出的格式約定這道題的最終答案通常要求以結(jié)果表文件的形式提交文件格式、命名規(guī)則、字段順序在題目里都會有明確要求。很多選手自以為看清楚了實際上漏看了字段順序這四個字。分析表字段的順序在閱卷程序里是嚴格匹配的——第一列必須對應(yīng)題目第一個要求的字段第二列第二個順序錯了哪怕值全對都會被判定為結(jié)構(gòu)不符。導(dǎo)出CSV時還要注意Excel打開后的數(shù)據(jù)精度問題。有些統(tǒng)計結(jié)果浮點數(shù)位數(shù)很長直接導(dǎo)出會顯示成科學(xué)計數(shù)法或者被Excel截斷。建議所有數(shù)值字段在導(dǎo)出前統(tǒng)一round到題目要求的小數(shù)位數(shù)沒有要求就統(tǒng)一保留2位小數(shù)。這個習(xí)慣還能避免另一個問題多個工具之間互相打開結(jié)果文件時因為浮點精度不一致導(dǎo)致看似同樣的數(shù)據(jù)對不上的情況。5. 答案怎么才能踩中給分點閱卷視角的答題組織5.1 過程文件比最終結(jié)果更重要這道題雖然只有25分但在閱卷時它看的往往不是唯一一張結(jié)果表而是一組過程文件。通常來說你要提交的包括清洗后的數(shù)據(jù)表、分析結(jié)果表、以及必要的說明文檔。有些年份的評分規(guī)則里甚至要求保留清洗前后的對照文件沒有中間過程文件直接扣分。很多選手不理解為什么答案要附中間過程覺得我最終結(jié)果是對的就行。但大賽閱卷有一個現(xiàn)實約束閱卷人員需要驗證結(jié)果是怎么來的只有最終結(jié)果沒有過程哪怕結(jié)果對了也沒法確認你是自己做出來的。反過來如果過程文件完整哪怕最終結(jié)果差了一點點往往能靠過程分撈回不少。所以答題時我習(xí)慣這么做每完成一個清洗動作就把當(dāng)前表導(dǎo)出一次快照命名按01原始數(shù)據(jù)、02去重、03填充、04分析結(jié)果這樣的節(jié)奏遞增。最后提交時整個過程鏈路一目了然閱卷時好感度直接拉滿。5.2 一張結(jié)果表的表頭決定一大半分結(jié)果表設(shè)計也是一門學(xué)問不是隨便把計算結(jié)果堆在一起就行。字段命名要清晰、直觀盡量跟題目里的用詞保持一致。題目寫銷售額你就別叫total_sale_amount題目寫商品類別你就別叫category_code。雖然語義等價但在機器判卷環(huán)節(jié)默認答案的字段名就是題目的原始措辭命名對不上很吃虧。表頭還要注意不要帶多余字段。分析結(jié)果表只保留題目要求輸出的字段別把清洗過程中用到的輔助字段比如填充標記、原始值備份也帶進結(jié)果表那只會讓閱卷程序產(chǎn)生疑惑。每個字段的類型也要盡量對齊百分比字段要明確是小數(shù)0.25還是25%日期字段要統(tǒng)一成同一種格式。這些細節(jié)在往年賽題答案的評分說明里幾乎每一項都對應(yīng)著一到兩個給分點。5.3 踩過坑后的自查順序交卷前的最后幾分鐘與其坐在那里干等不如按固定順序做一遍自查。我整理了一個清單每次比賽都用它檢查文件命名是否完全與題目一致注意大小寫和下劃線檢查結(jié)果表的字段名、字段順序、字段數(shù)量是否與題目一致抽樣驗證幾行數(shù)據(jù)看數(shù)值是否在合理范圍比如沒有負銷售額、日期都在合理區(qū)間用SQL或者Python重新跑一遍主結(jié)果表與原始表的關(guān)聯(lián)確認表間行數(shù)能對上如果有多個結(jié)果文件確認文件之間的主鍵能夠互相匹配這幾條全部過完通常不超過8分鐘但每次都能攔下一兩個低級錯誤。曾經(jīng)有位選手分析做得很漂亮結(jié)果提交時發(fā)現(xiàn)文件名少寫了一個下劃線直接扣掉了2分。這種分丟得太冤了而且賽場上完全可避免。6. 從備賽到正式賽時間分配與練習(xí)建議6.1 25分建議的時間預(yù)算和優(yōu)先級整個賽項通常持續(xù)數(shù)小時任務(wù)三內(nèi)部還有多個子任務(wù)所以落到數(shù)據(jù)清洗與分析這一題的時間非常有限。我的經(jīng)驗是給這道25分的題留出45到60分鐘的凈時間其中前10分鐘讀題和探查數(shù)據(jù)20分鐘做清洗20分鐘做分析最后5分鐘檢查輸出。時間如果再緊優(yōu)先保清洗環(huán)節(jié)因為清洗是后續(xù)所有分析的前置條件清洗錯了一步后面全白搭。分析環(huán)節(jié)如果出現(xiàn)了做不出來的情況不要死磕。先把能做的簡單分組統(tǒng)計結(jié)果寫上至少把字段結(jié)構(gòu)搭出來后面再回頭補復(fù)雜指標。賽場上一旦卡住最忌諱的就是在一個小坑上耗半小時那會把后面所有題目的時間預(yù)算全部吃掉。6.2 練到什么程度才算穩(wěn)備賽階段訓(xùn)練這道題不用追求題海戰(zhàn)術(shù)而是要把每一種數(shù)據(jù)臟類型和每一種分析輸出形態(tài)都練到肌肉記憶的程度。給自己定一個標準拿到任意一張帶臟數(shù)據(jù)的表15分鐘內(nèi)能完成探查和清洗方案設(shè)計再花15分鐘跑完清洗和基礎(chǔ)分析這就算入門了如果能在最后檢查時一眼掃出字段命名、格式、口徑的隱患那就穩(wěn)了。練習(xí)時還有一個重要習(xí)慣每練完一道題就把自己的輸出和參考答案做一次字段級對比不要只看數(shù)值對不對。找出自己漏掉的字段和寫錯的命名總結(jié)成一張個人錯題單。備賽后期這張錯題單比任何模擬題都值錢因為它記錄的是你個人的易錯點而不是通用知識點。6.3 復(fù)盤時最值得盯的幾類錯誤正式賽結(jié)束之后復(fù)盤我最關(guān)注四類錯誤運行環(huán)境類編碼、路徑、工具版本、邏輯類重復(fù)分組、過濾順序、輸出類字段命名、文件格式、時間類某個環(huán)節(jié)超時導(dǎo)致后面?zhèn)}促。這四類錯誤的性質(zhì)完全不一樣環(huán)境類錯誤靠提前踩點比賽場地可以大幅減少邏輯類錯誤靠大量練習(xí)來強化輸出類錯誤靠習(xí)慣性的自查清單來兜底時間類錯誤則需要平時就按正式賽的時長做整套模擬。如果要說一句最想告訴后來者的話這道25分的題表面上考的是工具操作實際考的是你有沒有一套穩(wěn)定可靠的工作流。工具隨時可以換數(shù)據(jù)臟的方式千奇百怪但只要你的處理流程是固定的、可重復(fù)的、每一步都留痕的遇到什么樣的臟數(shù)據(jù)都不會慌。備賽時我們習(xí)慣用同一套流程反復(fù)跑不同數(shù)據(jù)集跑到后來清洗和分析已經(jīng)不是做題而是一種自然的操作反射。到賽場上你會發(fā)現(xiàn)這份從容才是拿分最可靠的底氣。 大數(shù)據(jù)技能賽數(shù)據(jù)清洗與分析：25分拿分策略與實戰(zhàn)技巧
1. 25分到底考什么任務(wù)三第4子任務(wù)的定位與分值拆分1.1 賽項里數(shù)據(jù)清洗與分析為什么要單獨考2019年全國職業(yè)技能大賽大數(shù)據(jù)技術(shù)賽項的整套賽題是一個完整的離線數(shù)據(jù)業(yè)務(wù)鏈路數(shù)據(jù)產(chǎn)生之后進入采集通道落到存儲引擎然后交給清洗環(huán)節(jié)去粗取精最后再通過分析把數(shù)據(jù)變成能輔助決策的結(jié)果。任務(wù)三的第4個子任務(wù)數(shù)據(jù)清洗與分析恰恰卡在鏈路最關(guān)鍵的腰部——前面做好的數(shù)據(jù)表能不能用、后面分析出來的結(jié)論有沒有說服力都取決于這道題做得扎實不扎實。參加過這類賽事的選手都知道賽題里的任務(wù)編號有不少門道。任務(wù)三放在整套題的中間偏后位置意味著前幾個子任務(wù)已經(jīng)把原始數(shù)據(jù)準備好了到這一道題時你面對的往往是帶著缺失值、重復(fù)行、超范圍數(shù)值、格式不統(tǒng)一的大寬表。職業(yè)技能大賽的出題邏輯和學(xué)校里的課程作業(yè)完全不一樣課程作業(yè)里數(shù)據(jù)基本是干凈的大賽里的數(shù)據(jù)則是從真實業(yè)務(wù)場景里抽出來的臟得有多自然題目就有多真實。這道25分的題本質(zhì)上是考察兩件事你能不能把臟數(shù)據(jù)理順以及理順之后你能不能問出有價值的問題。1.2 分值是均勻分布還是集中給點凡是認真研究過這份賽題答案的人都能發(fā)現(xiàn)一個規(guī)律25分不是均勻撒在每一步操作上的。出題方會故意讓某個清洗動作占8分、某個分析結(jié)果占10分剩下的幾分留給過程和輸出格式這樣做是為了拉開區(qū)分度——只會點點鼠標的人拿不到高分真正理解數(shù)據(jù)的人能一路暢通。按照歷年備賽時對照題目答案的統(tǒng)計習(xí)慣這道題的分值在心里可以這樣預(yù)估數(shù)據(jù)清洗部分大約占812分集中在缺失值處理、重復(fù)行刪除、異常值修正這些硬性操作上數(shù)據(jù)分析部分占1012分通常要求輸出分組統(tǒng)計結(jié)果、同比環(huán)比或者排行前幾的明細最后還有23分給的是導(dǎo)出文件命名、字段命名、表頭規(guī)范這類看起來不起眼但評分時繞不開的細節(jié)。明確了分值分布再去答題策略就完全不一樣了清洗環(huán)節(jié)寧可多花時間也不能丟分分析環(huán)節(jié)要優(yōu)先寫清楚結(jié)論口徑輸出格式要留出最后5分鐘專門檢查而不是全部一股腦兒做完。很多第一次參賽的人最大的誤區(qū)是先把數(shù)據(jù)全清洗干凈再開始分析。事實上賽題并不會給你一條完美的清洗主線多數(shù)情況下清洗到能回答目標問題的程度就夠了不必追求理論上的絕對干凈。2. 數(shù)據(jù)清洗考試最穩(wěn)的拿分點也是翻車最集中的環(huán)節(jié)2.1 缺失值別一上來就刪先回答為什么空數(shù)據(jù)清洗的第一刀幾乎永遠落在缺失值上。賽題給的表很少有干干凈凈沒有空格的常見的情況有三種某個字段整列都是空、某幾行關(guān)鍵字段缺失、以及用空字符串或者null字符串冒充空值。這三種情況的處理策略完全不一樣混在一起處理就會翻車。先處理整列都空的情況。這一列如果確實是賽題故意留的空列它對你的分析目標沒有任何用處直接判斷不需要納入結(jié)果表即可。但如果題目后邊的分析題要用到這個字段那說明數(shù)據(jù)鏈路在更早的環(huán)節(jié)出了問題這時候就要回到前面幾個子任務(wù)的產(chǎn)出目錄里去找原始文件重新核對字段對應(yīng)關(guān)系。某個字段部分缺失時需要看缺失比例缺失比例不到5%且該字段是數(shù)值型時可以用分組中位數(shù)或者均值填充缺失比例高但它又是分析主鍵的一部分就要回到主鍵關(guān)聯(lián)邏輯去排查看看是不是上游join出了問題。另外要注意Excel或者文本文件里常見的null字符串這種看起來是空、實際上占著字符位置的值在數(shù)值計算時最坑人必須先統(tǒng)一替換成真正的空再走填充邏輯。拿SQL環(huán)境舉例一個穩(wěn)妥的處理順序是-- 1. 把字符串形式的NULL統(tǒng)一成真NULL UPDATE cleaned_sales SET quantity NULL WHERE quantity null; -- 2. 用分組中位數(shù)填充數(shù)值型缺失 UPDATE cleaned_sales t JOIN ( SELECT category, MEDIAN(price) AS m_price FROM cleaned_sales WHERE price IS NOT NULL GROUP BY category ) x ON t.category x.category SET t.price x.m_price WHERE t.price IS NULL;這里用中位數(shù)而不是平均值是因為大賽數(shù)據(jù)經(jīng)常有極端價格存在均值會被帶偏中位數(shù)更穩(wěn)健。這個細節(jié)很多人不知道但恰好在某些年份賽題答案的給分點上出現(xiàn)過。2.2 異常值用業(yè)務(wù)規(guī)則而不是統(tǒng)計規(guī)則去篩異常值處理是數(shù)據(jù)清洗里最容易引起爭議的部分因為異常的定義本身就模糊。賽題里最常出現(xiàn)的異常類型大概是這么幾類異常類型典型表現(xiàn)推薦處理方式范圍外數(shù)值年齡出現(xiàn)300、價格出現(xiàn)負數(shù)按業(yè)務(wù)字段合理范圍修正或剔除邏輯矛盾下單時間晚于發(fā)貨時間以主流程時間為準修正前后順序單位不一致部分記錄按元部分按萬元統(tǒng)一換算基準單位后再參與計算重復(fù)值同一主鍵出現(xiàn)多行內(nèi)容完全相同按主鍵去重保留第一條極端離群點個別值遠超正常范圍結(jié)合業(yè)務(wù)判斷是否保留并單獨標注針對范圍外數(shù)值建議直接按業(yè)務(wù)常識定規(guī)則年齡字段必須落在0到120之間價格字段必須大于等于0。有些選手遇到負價格第一反應(yīng)是刪行結(jié)果把有效記錄給刪了。更好的做法是先查一下這個字段在其他子任務(wù)里的口徑比如負價格可能是退款金額被混進了銷售明細這種情況應(yīng)該拆分到退款表而不是粗暴刪除。邏輯矛盾的處理方向要記住一條鐵律以主鏈路的時間節(jié)點為基準。下單時間晚于發(fā)貨時間這種矛盾通常是錄入時區(qū)不同導(dǎo)致的分析時要統(tǒng)一到發(fā)貨時間所在時區(qū)之后再判斷。如果賽題沒有特別說明時區(qū)問題就用任務(wù)一里元數(shù)據(jù)表給出的默認時區(qū)所有時間的清洗口徑保持一致保證后續(xù)分析不會因為時區(qū)混用而對不上數(shù)。2.3 重復(fù)值與格式統(tǒng)一最容易順手丟分的細節(jié)重復(fù)值處理聽起來簡單實際操作里面有個很隱蔽的坑很多表里看起來重復(fù)的行其實只是主鍵以外的字段完全一樣但主鍵字段并不重復(fù)。這類情況不能直接刪得先看主鍵有沒有唯一性約束。如果一張干凈的業(yè)務(wù)流水表里存在同一訂單號出現(xiàn)兩次的情況要么是上游抽數(shù)時發(fā)生了重復(fù)要么是訂單表本身含有多行明細后者并不算異常。正確做法是先做一次分組計數(shù)確認重復(fù)的粒度。SELECT order_id, COUNT(*) AS cnt FROM raw_sales GROUP BY order_id HAVING COUNT(*) 1;如果重復(fù)粒度確實是整行所有字段全部一樣那基本可以判斷是抽取環(huán)節(jié)帶出來的冗余保留任意一行即可。如果只有部分字段重復(fù)而其他字段不同則要看這些不同字段是否構(gòu)成分析所需的維度否則保留一行并標注疑似重復(fù)即可。這個先確認再動手的習(xí)慣在競賽里直接決定了你刪的是臟數(shù)據(jù)還是有效數(shù)據(jù)。格式統(tǒng)一的坑則更瑣碎日期字段有的帶時分秒、有的只有日期字符串字段里隱藏著肉眼看不見的全角空格數(shù)值字段被存成了文本導(dǎo)致排序異常。這些問題的共性解法是先探查后清洗。拿到表之后先做一輪字段級別的distinct抽樣用眼睛確認每個字段的真實形態(tài)再寫清洗腳本。競賽時間緊很多選手跳過探查直接寫清洗邏輯結(jié)果清洗腳本跑出來的結(jié)果比原始數(shù)據(jù)還要奇怪這類教訓(xùn)每年都有。3. 數(shù)據(jù)分析讓結(jié)果輸出向考點意圖靠攏3.1 從題目措辭反推分析目標數(shù)據(jù)清洗做完緊接著的分析題才是真正拉分的環(huán)節(jié)。歷年賽題里的分析問法其實翻來覆去就那么幾類但每年都有大量選手在分析上跑偏根源在于沒有仔細讀題。分析題的題干往往在措辭里藏著口型要求排名前10的一定要有排序和limit要求占比情況的一定要有分母要求同比/環(huán)比的一定要有時間維度做join。一個實用的方法是把題干里的動詞和名詞拆出來對照。比如題干寫統(tǒng)計每月各類別銷售總額那輸出表至少要包含月份、類別、銷售總額三個字段一個都不能少寫對比上半年與下半年的客單價差異則必須有上半年、下半年兩個分組且要明確客單價的分子和分母口徑。這些內(nèi)容在閱卷時都是按字段給分的少寫一個字段哪怕計算結(jié)果完全正確也給不了滿分。3.2 分組聚合的寫法和輸出口徑用SQL還是用Python做分析取決于賽題給你的數(shù)據(jù)形態(tài)和你更熟練的工具。但無論用哪個分組聚合的輸出口徑必須統(tǒng)一。所謂口徑就是每個指標的計算定義。比如銷售額到底是訂單原價還是實際支付價用戶數(shù)到底是去重用戶還是訂單用戶數(shù)。賽題后面如果有附屬說明表一般會寫明口徑如果沒有就按最符合業(yè)務(wù)直覺的規(guī)則來并且在答案里寫明自己的口徑選擇。閱卷時能看出你做了口徑判斷這本就是分析能力的一部分。以SQL為例子一個完整的分組分析往往長成這樣SELECT DATE_FORMAT(order_date, %Y-%m) AS month, category, ROUND(SUM(actual_amount), 2) AS total_amount, COUNT(DISTINCT user_id) AS user_cnt, ROUND(SUM(actual_amount) / COUNT(DISTINCT user_id), 2) AS per_user_avg FROM cleaned_sales WHERE order_status completed GROUP BY month, category ORDER BY month, total_amount DESC;這里有個關(guān)鍵習(xí)慣把where條件放在聚合之前先過濾再聚合別等聚合結(jié)果出來再過濾。這不僅是SQL執(zhí)行效率的問題更是避免邏輯錯誤的關(guān)鍵。很多選手喜歡在子查詢里先分組再在外面加where寫出來特別繞一不留神就把分組后的聚合結(jié)果當(dāng)成過濾條件最后對不上答案。3.3 比對的幾種常見形態(tài)環(huán)比、同比、占比、排名大賽分析題最愛考的四類輸出邏輯各有各的坑。環(huán)比分析一定會涉及上一個時間段的數(shù)據(jù)最常見的寫法是用lag窗口函數(shù)或者自關(guān)聯(lián)取上一周期值。用SQL的時候推薦先用窗口函數(shù)把上一周期值放到同一行再做差這樣邏輯清晰。自關(guān)聯(lián)寫法容易在多對多join時產(chǎn)生重復(fù)計數(shù)一旦基數(shù)對不上結(jié)果就亂了。同比分析則要特別注意時間窗口的天數(shù)差異比如2月的同比要處理閏年季度同比要處理季度天數(shù)不同。賽題如果沒特別說明默認按自然月/自然季對齊即可不需要引入工作日調(diào)整這種復(fù)雜邏輯。占比分析要先確認分子分母的層級比如某品類占全店銷售的比例分母應(yīng)該是全店整體而非當(dāng)前篩選范圍。還有個隱藏的坑分母里要不要包含退款訂單。這個要看題干的銷售額是否已經(jīng)做過退款剔除如果前面清洗時已經(jīng)把退款單刪掉了分母就直接用清洗后的數(shù)據(jù)不需要二次處理。排名分析反而最簡單最需要注意的就是并列排名怎么算。SQL里rownum、rank、dense_rank之間的差別賽題答案通常按rank并列會跳號來給分。如果只用order by加limit而完全避開排名函數(shù)遇到并列時輸出行數(shù)會跟答案對不上這也是一個高頻丟分點。4. 工具鏈選擇與比賽環(huán)境的隱形限制4.1 選SQL還是Python看賽題給的數(shù)據(jù)形態(tài)很多選手備賽時糾結(jié)到底用SQL還是Python做數(shù)據(jù)清洗分析其實這個問題在比賽現(xiàn)場根本不該糾結(jié)。判斷標準很簡單數(shù)據(jù)量級不大、字段結(jié)構(gòu)清晰的場景SQL的調(diào)試成本更低出結(jié)果更快數(shù)據(jù)有多表關(guān)聯(lián)、復(fù)雜清洗鏈路、自定義函數(shù)處理邏輯的場景Python的代碼可維護性更高不容易在中間步驟里迷路。我個人的建議是清洗階段以SQL為主、Python為輔分析階段反過來。原因是清洗階段的探查—修改—驗證循環(huán)很頻繁SQL里寫一句select再套一層update執(zhí)行力非常順滑到了分析階段需要連續(xù)做多層篩選、分組、合并Python里用pandas鏈式操作比SQL層層嵌套可讀性強得多。曾經(jīng)和我一起備賽的一位A同學(xué)在模擬題里堅持所有步驟都用SQL結(jié)果到第三層嵌套子查詢時把自己繞暈了最后在時間上吃了大虧。后來他改成清洗用SQL分析用pandas速度提升了至少一半。4.2 編碼與路徑比賽環(huán)境里的第一個坑正式比賽環(huán)境里最容易出現(xiàn)的問題根本不是分析邏輯跑不通而是文件讀不出來。數(shù)據(jù)文本的編碼格式是GBK還是UTF-8在Windows環(huán)境下經(jīng)常是重災(zāi)區(qū)。很多選手把csv文件拖進Excel或者Python里直接讀看到亂碼才意識到編碼不對這一來一回就是十幾分鐘。正確的做法是建表或讀取之前先看一遍任務(wù)說明里注明的編碼格式如果沒有注明就用記事本或者文本工具打開文件看一眼亂碼就切換到GBK沒亂碼就是UTF-8。Python讀取時的寫法是這樣的import pandas as pd df pd.read_csv(sales.csv, encodinggbk) # 如果還是亂碼可以換成 encodingutf-8 # 還可以用 errorsignore 先跑通再回來補編碼另一個坑是文件路徑不能帶中文。某些比賽環(huán)境里工具鏈對中文路徑支持不完善一旦工作目錄或者輸出路徑里有中文程序就會在某個莫名其妙的步驟里報錯。穩(wěn)妥的做法是開賽后第一件事就是在純英文路徑下建立自己的工作目錄把題目給到的數(shù)據(jù)文件全部拷貝進去后面所有的中間結(jié)果都往這個目錄里寫。4.3 結(jié)果表導(dǎo)出的格式約定這道題的最終答案通常要求以結(jié)果表文件的形式提交文件格式、命名規(guī)則、字段順序在題目里都會有明確要求。很多選手自以為看清楚了實際上漏看了字段順序這四個字。分析表字段的順序在閱卷程序里是嚴格匹配的——第一列必須對應(yīng)題目第一個要求的字段第二列第二個順序錯了哪怕值全對都會被判定為結(jié)構(gòu)不符。導(dǎo)出CSV時還要注意Excel打開后的數(shù)據(jù)精度問題。有些統(tǒng)計結(jié)果浮點數(shù)位數(shù)很長直接導(dǎo)出會顯示成科學(xué)計數(shù)法或者被Excel截斷。建議所有數(shù)值字段在導(dǎo)出前統(tǒng)一round到題目要求的小數(shù)位數(shù)沒有要求就統(tǒng)一保留2位小數(shù)。這個習(xí)慣還能避免另一個問題多個工具之間互相打開結(jié)果文件時因為浮點精度不一致導(dǎo)致看似同樣的數(shù)據(jù)對不上的情況。5. 答案怎么才能踩中給分點閱卷視角的答題組織5.1 過程文件比最終結(jié)果更重要這道題雖然只有25分但在閱卷時它看的往往不是唯一一張結(jié)果表而是一組過程文件。通常來說你要提交的包括清洗后的數(shù)據(jù)表、分析結(jié)果表、以及必要的說明文檔。有些年份的評分規(guī)則里甚至要求保留清洗前后的對照文件沒有中間過程文件直接扣分。很多選手不理解為什么答案要附中間過程覺得我最終結(jié)果是對的就行。但大賽閱卷有一個現(xiàn)實約束閱卷人員需要驗證結(jié)果是怎么來的只有最終結(jié)果沒有過程哪怕結(jié)果對了也沒法確認你是自己做出來的。反過來如果過程文件完整哪怕最終結(jié)果差了一點點往往能靠過程分撈回不少。所以答題時我習(xí)慣這么做每完成一個清洗動作就把當(dāng)前表導(dǎo)出一次快照命名按01原始數(shù)據(jù)、02去重、03填充、04分析結(jié)果這樣的節(jié)奏遞增。最后提交時整個過程鏈路一目了然閱卷時好感度直接拉滿。5.2 一張結(jié)果表的表頭決定一大半分結(jié)果表設(shè)計也是一門學(xué)問不是隨便把計算結(jié)果堆在一起就行。字段命名要清晰、直觀盡量跟題目里的用詞保持一致。題目寫銷售額你就別叫total_sale_amount題目寫商品類別你就別叫category_code。雖然語義等價但在機器判卷環(huán)節(jié)默認答案的字段名就是題目的原始措辭命名對不上很吃虧。表頭還要注意不要帶多余字段。分析結(jié)果表只保留題目要求輸出的字段別把清洗過程中用到的輔助字段比如填充標記、原始值備份也帶進結(jié)果表那只會讓閱卷程序產(chǎn)生疑惑。每個字段的類型也要盡量對齊百分比字段要明確是小數(shù)0.25還是25%日期字段要統(tǒng)一成同一種格式。這些細節(jié)在往年賽題答案的評分說明里幾乎每一項都對應(yīng)著一到兩個給分點。5.3 踩過坑后的自查順序交卷前的最后幾分鐘與其坐在那里干等不如按固定順序做一遍自查。我整理了一個清單每次比賽都用它檢查文件命名是否完全與題目一致注意大小寫和下劃線檢查結(jié)果表的字段名、字段順序、字段數(shù)量是否與題目一致抽樣驗證幾行數(shù)據(jù)看數(shù)值是否在合理范圍比如沒有負銷售額、日期都在合理區(qū)間用SQL或者Python重新跑一遍主結(jié)果表與原始表的關(guān)聯(lián)確認表間行數(shù)能對上如果有多個結(jié)果文件確認文件之間的主鍵能夠互相匹配這幾條全部過完通常不超過8分鐘但每次都能攔下一兩個低級錯誤。曾經(jīng)有位選手分析做得很漂亮結(jié)果提交時發(fā)現(xiàn)文件名少寫了一個下劃線直接扣掉了2分。這種分丟得太冤了而且賽場上完全可避免。6. 從備賽到正式賽時間分配與練習(xí)建議6.1 25分建議的時間預(yù)算和優(yōu)先級整個賽項通常持續(xù)數(shù)小時任務(wù)三內(nèi)部還有多個子任務(wù)所以落到數(shù)據(jù)清洗與分析這一題的時間非常有限。我的經(jīng)驗是給這道25分的題留出45到60分鐘的凈時間其中前10分鐘讀題和探查數(shù)據(jù)20分鐘做清洗20分鐘做分析最后5分鐘檢查輸出。時間如果再緊優(yōu)先保清洗環(huán)節(jié)因為清洗是后續(xù)所有分析的前置條件清洗錯了一步后面全白搭。分析環(huán)節(jié)如果出現(xiàn)了做不出來的情況不要死磕。先把能做的簡單分組統(tǒng)計結(jié)果寫上至少把字段結(jié)構(gòu)搭出來后面再回頭補復(fù)雜指標。賽場上一旦卡住最忌諱的就是在一個小坑上耗半小時那會把后面所有題目的時間預(yù)算全部吃掉。6.2 練到什么程度才算穩(wěn)備賽階段訓(xùn)練這道題不用追求題海戰(zhàn)術(shù)而是要把每一種數(shù)據(jù)臟類型和每一種分析輸出形態(tài)都練到肌肉記憶的程度。給自己定一個標準拿到任意一張帶臟數(shù)據(jù)的表15分鐘內(nèi)能完成探查和清洗方案設(shè)計再花15分鐘跑完清洗和基礎(chǔ)分析這就算入門了如果能在最后檢查時一眼掃出字段命名、格式、口徑的隱患那就穩(wěn)了。練習(xí)時還有一個重要習(xí)慣每練完一道題就把自己的輸出和參考答案做一次字段級對比不要只看數(shù)值對不對。找出自己漏掉的字段和寫錯的命名總結(jié)成一張個人錯題單。備賽后期這張錯題單比任何模擬題都值錢因為它記錄的是你個人的易錯點而不是通用知識點。6.3 復(fù)盤時最值得盯的幾類錯誤正式賽結(jié)束之后復(fù)盤我最關(guān)注四類錯誤運行環(huán)境類編碼、路徑、工具版本、邏輯類重復(fù)分組、過濾順序、輸出類字段命名、文件格式、時間類某個環(huán)節(jié)超時導(dǎo)致后面?zhèn)}促。這四類錯誤的性質(zhì)完全不一樣環(huán)境類錯誤靠提前踩點比賽場地可以大幅減少邏輯類錯誤靠大量練習(xí)來強化輸出類錯誤靠習(xí)慣性的自查清單來兜底時間類錯誤則需要平時就按正式賽的時長做整套模擬。如果要說一句最想告訴后來者的話這道25分的題表面上考的是工具操作實際考的是你有沒有一套穩(wěn)定可靠的工作流。工具隨時可以換數(shù)據(jù)臟的方式千奇百怪但只要你的處理流程是固定的、可重復(fù)的、每一步都留痕的遇到什么樣的臟數(shù)據(jù)都不會慌。備賽時我們習(xí)慣用同一套流程反復(fù)跑不同數(shù)據(jù)集跑到后來清洗和分析已經(jīng)不是做題而是一種自然的操作反射。到賽場上你會發(fā)現(xiàn)這份從容才是拿分最可靠的底氣。