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