大数据技能赛数据清洗与分析：25分拿分策略与实战技巧

1. 25分到底考什么任务三第4子任务的定位与分值拆分1.1 赛项里数据清洗与分析为什么要单独考2019年全国职业技能大赛大数据技术赛项的整套赛题是一个完整的离线数据业务链路数据产生之后进入采集通道落到存储引擎然后交给清洗环节去粗取精最后再通过分析把数据变成能辅助决策的结果。任务三的第4个子任务数据清洗与分析恰恰卡在链路最关键的腰部——前面做好的数据表能不能用、后面分析出来的结论有没有说服力都取决于这道题做得扎实不扎实。参加过这类赛事的选手都知道赛题里的任务编号有不少门道。任务三放在整套题的中间偏后位置意味着前几个子任务已经把原始数据准备好了到这一道题时你面对的往往是带着缺失值、重复行、超范围数值、格式不统一的大宽表。职业技能大赛的出题逻辑和学校里的课程作业完全不一样课程作业里数据基本是干净的大赛里的数据则是从真实业务场景里抽出来的脏得有多自然题目就有多真实。这道25分的题本质上是考察两件事你能不能把脏数据理顺以及理顺之后你能不能问出有价值的问题。1.2 分值是均匀分布还是集中给点凡是认真研究过这份赛题答案的人都能发现一个规律25分不是均匀撒在每一步操作上的。出题方会故意让某个清洗动作占8分、某个分析结果占10分剩下的几分留给过程和输出格式这样做是为了拉开区分度——只会点点鼠标的人拿不到高分真正理解数据的人能一路畅通。按照历年备赛时对照题目答案的统计习惯这道题的分值在心里可以这样预估数据清洗部分大约占812分集中在缺失值处理、重复行删除、异常值修正这些硬性操作上数据分析部分占1012分通常要求输出分组统计结果、同比环比或者排行前几的明细最后还有23分给的是导出文件命名、字段命名、表头规范这类看起来不起眼但评分时绕不开的细节。明确了分值分布再去答题策略就完全不一样了清洗环节宁可多花时间也不能丢分分析环节要优先写清楚结论口径输出格式要留出最后5分钟专门检查而不是全部一股脑儿做完。很多第一次参赛的人最大的误区是先把数据全清洗干净再开始分析。事实上赛题并不会给你一条完美的清洗主线多数情况下清洗到能回答目标问题的程度就够了不必追求理论上的绝对干净。2. 数据清洗考试最稳的拿分点也是翻车最集中的环节2.1 缺失值别一上来就删先回答为什么空数据清洗的第一刀几乎永远落在缺失值上。赛题给的表很少有干干净净没有空格的常见的情况有三种某个字段整列都是空、某几行关键字段缺失、以及用空字符串或者null字符串冒充空值。这三种情况的处理策略完全不一样混在一起处理就会翻车。先处理整列都空的情况。这一列如果确实是赛题故意留的空列它对你的分析目标没有任何用处直接判断不需要纳入结果表即可。但如果题目后边的分析题要用到这个字段那说明数据链路在更早的环节出了问题这时候就要回到前面几个子任务的产出目录里去找原始文件重新核对字段对应关系。某个字段部分缺失时需要看缺失比例缺失比例不到5%且该字段是数值型时可以用分组中位数或者均值填充缺失比例高但它又是分析主键的一部分就要回到主键关联逻辑去排查看看是不是上游join出了问题。另外要注意Excel或者文本文件里常见的null字符串这种看起来是空、实际上占着字符位置的值在数值计算时最坑人必须先统一替换成真正的空再走填充逻辑。拿SQL环境举例一个稳妥的处理顺序是-- 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;这里用中位数而不是平均值是因为大赛数据经常有极端价格存在均值会被带偏中位数更稳健。这个细节很多人不知道但恰好在某些年份赛题答案的给分点上出现过。2.2 异常值用业务规则而不是统计规则去筛异常值处理是数据清洗里最容易引起争议的部分因为异常的定义本身就模糊。赛题里最常出现的异常类型大概是这么几类异常类型典型表现推荐处理方式范围外数值年龄出现300、价格出现负数按业务字段合理范围修正或剔除逻辑矛盾下单时间晚于发货时间以主流程时间为准修正前后顺序单位不一致部分记录按元部分按万元统一换算基准单位后再参与计算重复值同一主键出现多行内容完全相同按主键去重保留第一条极端离群点个别值远超正常范围结合业务判断是否保留并单独标注针对范围外数值建议直接按业务常识定规则年龄字段必须落在0到120之间价格字段必须大于等于0。有些选手遇到负价格第一反应是删行结果把有效记录给删了。更好的做法是先查一下这个字段在其他子任务里的口径比如负价格可能是退款金额被混进了销售明细这种情况应该拆分到退款表而不是粗暴删除。逻辑矛盾的处理方向要记住一条铁律以主链路的时间节点为基准。下单时间晚于发货时间这种矛盾通常是录入时区不同导致的分析时要统一到发货时间所在时区之后再判断。如果赛题没有特别说明时区问题就用任务一里元数据表给出的默认时区所有时间的清洗口径保持一致保证后续分析不会因为时区混用而对不上数。2.3 重复值与格式统一最容易顺手丢分的细节重复值处理听起来简单实际操作里面有个很隐蔽的坑很多表里看起来重复的行其实只是主键以外的字段完全一样但主键字段并不重复。这类情况不能直接删得先看主键有没有唯一性约束。如果一张干净的业务流水表里存在同一订单号出现两次的情况要么是上游抽数时发生了重复要么是订单表本身含有多行明细后者并不算异常。正确做法是先做一次分组计数确认重复的粒度。SELECT order_id, COUNT(*) AS cnt FROM raw_sales GROUP BY order_id HAVING COUNT(*) 1;如果重复粒度确实是整行所有字段全部一样那基本可以判断是抽取环节带出来的冗余保留任意一行即可。如果只有部分字段重复而其他字段不同则要看这些不同字段是否构成分析所需的维度否则保留一行并标注疑似重复即可。这个先确认再动手的习惯在竞赛里直接决定了你删的是脏数据还是有效数据。格式统一的坑则更琐碎日期字段有的带时分秒、有的只有日期字符串字段里隐藏着肉眼看不见的全角空格数值字段被存成了文本导致排序异常。这些问题的共性解法是先探查后清洗。拿到表之后先做一轮字段级别的distinct抽样用眼睛确认每个字段的真实形态再写清洗脚本。竞赛时间紧很多选手跳过探查直接写清洗逻辑结果清洗脚本跑出来的结果比原始数据还要奇怪这类教训每年都有。3. 数据分析让结果输出向考点意图靠拢3.1 从题目措辞反推分析目标数据清洗做完紧接着的分析题才是真正拉分的环节。历年赛题里的分析问法其实翻来覆去就那么几类但每年都有大量选手在分析上跑偏根源在于没有仔细读题。分析题的题干往往在措辞里藏着口型要求排名前10的一定要有排序和limit要求占比情况的一定要有分母要求同比/环比的一定要有时间维度做join。一个实用的方法是把题干里的动词和名词拆出来对照。比如题干写统计每月各类别销售总额那输出表至少要包含月份、类别、销售总额三个字段一个都不能少写对比上半年与下半年的客单价差异则必须有上半年、下半年两个分组且要明确客单价的分子和分母口径。这些内容在阅卷时都是按字段给分的少写一个字段哪怕计算结果完全正确也给不了满分。3.2 分组聚合的写法和输出口径用SQL还是用Python做分析取决于赛题给你的数据形态和你更熟练的工具。但无论用哪个分组聚合的输出口径必须统一。所谓口径就是每个指标的计算定义。比如销售额到底是订单原价还是实际支付价用户数到底是去重用户还是订单用户数。赛题后面如果有附属说明表一般会写明口径如果没有就按最符合业务直觉的规则来并且在答案里写明自己的口径选择。阅卷时能看出你做了口径判断这本就是分析能力的一部分。以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执行效率的问题更是避免逻辑错误的关键。很多选手喜欢在子查询里先分组再在外面加where写出来特别绕一不留神就把分组后的聚合结果当成过滤条件最后对不上答案。3.3 比对的几种常见形态环比、同比、占比、排名大赛分析题最爱考的四类输出逻辑各有各的坑。环比分析一定会涉及上一个时间段的数据最常见的写法是用lag窗口函数或者自关联取上一周期值。用SQL的时候推荐先用窗口函数把上一周期值放到同一行再做差这样逻辑清晰。自关联写法容易在多对多join时产生重复计数一旦基数对不上结果就乱了。同比分析则要特别注意时间窗口的天数差异比如2月的同比要处理闰年季度同比要处理季度天数不同。赛题如果没特别说明默认按自然月/自然季对齐即可不需要引入工作日调整这种复杂逻辑。占比分析要先确认分子分母的层级比如某品类占全店销售的比例分母应该是全店整体而非当前筛选范围。还有个隐藏的坑分母里要不要包含退款订单。这个要看题干的销售额是否已经做过退款剔除如果前面清洗时已经把退款单删掉了分母就直接用清洗后的数据不需要二次处理。排名分析反而最简单最需要注意的就是并列排名怎么算。SQL里rownum、rank、dense_rank之间的差别赛题答案通常按rank并列会跳号来给分。如果只用order by加limit而完全避开排名函数遇到并列时输出行数会跟答案对不上这也是一个高频丢分点。4. 工具链选择与比赛环境的隐形限制4.1 选SQL还是Python看赛题给的数据形态很多选手备赛时纠结到底用SQL还是Python做数据清洗分析其实这个问题在比赛现场根本不该纠结。判断标准很简单数据量级不大、字段结构清晰的场景SQL的调试成本更低出结果更快数据有多表关联、复杂清洗链路、自定义函数处理逻辑的场景Python的代码可维护性更高不容易在中间步骤里迷路。我个人的建议是清洗阶段以SQL为主、Python为辅分析阶段反过来。原因是清洗阶段的探查—修改—验证循环很频繁SQL里写一句select再套一层update执行力非常顺滑到了分析阶段需要连续做多层筛选、分组、合并Python里用pandas链式操作比SQL层层嵌套可读性强得多。曾经和我一起备赛的一位A同学在模拟题里坚持所有步骤都用SQL结果到第三层嵌套子查询时把自己绕晕了最后在时间上吃了大亏。后来他改成清洗用SQL分析用pandas速度提升了至少一半。4.2 编码与路径比赛环境里的第一个坑正式比赛环境里最容易出现的问题根本不是分析逻辑跑不通而是文件读不出来。数据文本的编码格式是GBK还是UTF-8在Windows环境下经常是重灾区。很多选手把csv文件拖进Excel或者Python里直接读看到乱码才意识到编码不对这一来一回就是十几分钟。正确的做法是建表或读取之前先看一遍任务说明里注明的编码格式如果没有注明就用记事本或者文本工具打开文件看一眼乱码就切换到GBK没乱码就是UTF-8。Python读取时的写法是这样的import pandas as pd df pd.read_csv(sales.csv, encodinggbk) # 如果还是乱码可以换成 encodingutf-8 # 还可以用 errorsignore 先跑通再回来补编码另一个坑是文件路径不能带中文。某些比赛环境里工具链对中文路径支持不完善一旦工作目录或者输出路径里有中文程序就会在某个莫名其妙的步骤里报错。稳妥的做法是开赛后第一件事就是在纯英文路径下建立自己的工作目录把题目给到的数据文件全部拷贝进去后面所有的中间结果都往这个目录里写。4.3 结果表导出的格式约定这道题的最终答案通常要求以结果表文件的形式提交文件格式、命名规则、字段顺序在题目里都会有明确要求。很多选手自以为看清楚了实际上漏看了字段顺序这四个字。分析表字段的顺序在阅卷程序里是严格匹配的——第一列必须对应题目第一个要求的字段第二列第二个顺序错了哪怕值全对都会被判定为结构不符。导出CSV时还要注意Excel打开后的数据精度问题。有些统计结果浮点数位数很长直接导出会显示成科学计数法或者被Excel截断。建议所有数值字段在导出前统一round到题目要求的小数位数没有要求就统一保留2位小数。这个习惯还能避免另一个问题多个工具之间互相打开结果文件时因为浮点精度不一致导致看似同样的数据对不上的情况。5. 答案怎么才能踩中给分点阅卷视角的答题组织5.1 过程文件比最终结果更重要这道题虽然只有25分但在阅卷时它看的往往不是唯一一张结果表而是一组过程文件。通常来说你要提交的包括清洗后的数据表、分析结果表、以及必要的说明文档。有些年份的评分规则里甚至要求保留清洗前后的对照文件没有中间过程文件直接扣分。很多选手不理解为什么答案要附中间过程觉得我最终结果是对的就行。但大赛阅卷有一个现实约束阅卷人员需要验证结果是怎么来的只有最终结果没有过程哪怕结果对了也没法确认你是自己做出来的。反过来如果过程文件完整哪怕最终结果差了一点点往往能靠过程分捞回不少。所以答题时我习惯这么做每完成一个清洗动作就把当前表导出一次快照命名按01原始数据、02去重、03填充、04分析结果这样的节奏递增。最后提交时整个过程链路一目了然阅卷时好感度直接拉满。5.2 一张结果表的表头决定一大半分结果表设计也是一门学问不是随便把计算结果堆在一起就行。字段命名要清晰、直观尽量跟题目里的用词保持一致。题目写销售额你就别叫total_sale_amount题目写商品类别你就别叫category_code。虽然语义等价但在机器判卷环节默认答案的字段名就是题目的原始措辞命名对不上很吃亏。表头还要注意不要带多余字段。分析结果表只保留题目要求输出的字段别把清洗过程中用到的辅助字段比如填充标记、原始值备份也带进结果表那只会让阅卷程序产生疑惑。每个字段的类型也要尽量对齐百分比字段要明确是小数0.25还是25%日期字段要统一成同一种格式。这些细节在往年赛题答案的评分说明里几乎每一项都对应着一到两个给分点。5.3 踩过坑后的自查顺序交卷前的最后几分钟与其坐在那里干等不如按固定顺序做一遍自查。我整理了一个清单每次比赛都用它检查文件命名是否完全与题目一致注意大小写和下划线检查结果表的字段名、字段顺序、字段数量是否与题目一致抽样验证几行数据看数值是否在合理范围比如没有负销售额、日期都在合理区间用SQL或者Python重新跑一遍主结果表与原始表的关联确认表间行数能对上如果有多个结果文件确认文件之间的主键能够互相匹配这几条全部过完通常不超过8分钟但每次都能拦下一两个低级错误。曾经有位选手分析做得很漂亮结果提交时发现文件名少写了一个下划线直接扣掉了2分。这种分丢得太冤了而且赛场上完全可避免。6. 从备赛到正式赛时间分配与练习建议6.1 25分建议的时间预算和优先级整个赛项通常持续数小时任务三内部还有多个子任务所以落到数据清洗与分析这一题的时间非常有限。我的经验是给这道25分的题留出45到60分钟的净时间其中前10分钟读题和探查数据20分钟做清洗20分钟做分析最后5分钟检查输出。时间如果再紧优先保清洗环节因为清洗是后续所有分析的前置条件清洗错了一步后面全白搭。分析环节如果出现了做不出来的情况不要死磕。先把能做的简单分组统计结果写上至少把字段结构搭出来后面再回头补复杂指标。赛场上一旦卡住最忌讳的就是在一个小坑上耗半小时那会把后面所有题目的时间预算全部吃掉。6.2 练到什么程度才算稳备赛阶段训练这道题不用追求题海战术而是要把每一种数据脏类型和每一种分析输出形态都练到肌肉记忆的程度。给自己定一个标准拿到任意一张带脏数据的表15分钟内能完成探查和清洗方案设计再花15分钟跑完清洗和基础分析这就算入门了如果能在最后检查时一眼扫出字段命名、格式、口径的隐患那就稳了。练习时还有一个重要习惯每练完一道题就把自己的输出和参考答案做一次字段级对比不要只看数值对不对。找出自己漏掉的字段和写错的命名总结成一张个人错题单。备赛后期这张错题单比任何模拟题都值钱因为它记录的是你个人的易错点而不是通用知识点。6.3 复盘时最值得盯的几类错误正式赛结束之后复盘我最关注四类错误运行环境类编码、路径、工具版本、逻辑类重复分组、过滤顺序、输出类字段命名、文件格式、时间类某个环节超时导致后面仓促。这四类错误的性质完全不一样环境类错误靠提前踩点比赛场地可以大幅减少逻辑类错误靠大量练习来强化输出类错误靠习惯性的自查清单来兜底时间类错误则需要平时就按正式赛的时长做整套模拟。如果要说一句最想告诉后来者的话这道25分的题表面上考的是工具操作实际考的是你有没有一套稳定可靠的工作流。工具随时可以换数据脏的方式千奇百怪但只要你的处理流程是固定的、可重复的、每一步都留痕的遇到什么样的脏数据都不会慌。备赛时我们习惯用同一套流程反复跑不同数据集跑到后来清洗和分析已经不是做题而是一种自然的操作反射。到赛场上你会发现这份从容才是拿分最可靠的底气。