数据清洗、统计分析

数据清洗、统计分析需要掌握SQL在数据清洗和统计分析这两个核心环节的实战用法，这是从原始数据到有效业务结论的关键步骤。我会结合真实业务场景，先讲数据清洗的常用技巧，再基于清洗后的数据做深度统计分析，所有代码都可直接复用。一、数据清洗（预处理：解决脏数据问题）数据清洗是分析的前提，核心目标是处理缺失值、重复值、异常值、格式不统一等问题。以下以电商用户订单表

orders

为例（表结构同前），补充脏数据场景：order_iduser_idorder_amountorder_statuscreate_timepay_time1101299已支付2026-01-0110:20:302026-01-0110:25:302102199已支付2026-01-0114:15:20NULL--缺失值2102199已支付2026-01-0114:15:20NULL--重复值3101-599未支付2026-01-0209:05:102026-01-0209:10:10--异常值（金额为负）4103399已支付2026-01-0216:30:002026/01/0216:35:00--格式不统一（时间格式）1.处理重复值（去重）sql--步骤1：先查询重复数据（确认重复规则：order_id唯一，重复则为脏数据）SELECTorder_id,COUNT(*)ASrepeat_countFROMordersGROUPBYorder_idHAVINGCOUNT(*)>1;--步骤2：删除重复数据（保留重复行中ID最小的一行）DELETEFROMordersWHEREorder_idIN(SELECTorder_idFROM(SELECTorder_id,ROW_NUMBER()OVER(PARTITIONBYorder_idORDERBYid)ASrn--按order_id分组加行号FROMorders)tWHEREt.rn>1--行号>1的是重复行);--或创建清洗后的新表（推荐：保留原始数据）CREATETABLEorders_cleanedASSELECTDISTINCT*FROMorders;--简单去重（所有字段完全重复）--精准去重（仅order_id重复时保留第一条）CREATETABLEorders_cleanedASSELECT*FROM(SELECT*,ROW_NUMBER()OVER(PARTITIONBYorder_idORDERBYcreate_time)ASrnFROMorders)tWHEREt.rn=1;2.处理缺失值（填充/过滤）sql--方式1：过滤缺失值（适用于缺失率低、关键字段缺失）--过滤pay_time为空且状态为“已支付”的异常订单（已支付但无支付时间）SELECT*FROMorders_cleanedWHERENOT(order_status='已支付'ANDpay_timeISNULL);--方式2：填充缺失值（适用于缺失率高、非关键字段）--将pay_time为空的填充为create_time（默认支付时间=下单时间）UPDATEorders_cleanedSETpay_time=create_timeWHEREpay_timeISNULL;--方式3：用默认值填充（如金额缺失填充0）UPDATEorders_cleanedSETorder_amount=COALESCE(order_amount,0);--COALESCE：返回第一个非NULL值3.处理异常值（修正/剔除）sql--步骤1：识别异常值（金额为负、金额过大/过小）SELECT*FROMorders_cleanedWHEREorder_amount<=0ORorder_amount>10000;--业务合理范围：0<金额≤10000--步骤2：修正异常值（负金额转为正）UPDATEorders_cleanedSETorder_amount=ABS(order_amount)--ABS：取绝对值WHEREorder_amount<0;--步骤3：剔除无法修正的异常值（如金额超10000的疑似测试数据）DELETEFROMorders_cleanedWHEREorder_amount>10000;4.统一数据格式sql--将非标准时间格式（2026/01/02）转为标准格式（2026-01-02）UPDATEorders_cleanedSETpay_time=STR_TO_DATE(pay_time,'%Y/%m/%d%H:%i:%s')--MySQL：STR_TO_DATE转换格式WHEREpay_timeLIKE'%/%';--匹配含“/”的非标准格式--Oracle语法：TO_DATE(pay_time,'YYYY/MM/DDHH24:MI:SS')5.数据清洗完整脚本（封装成可复用流程）sql--1.创建清洗表（保留原表）CREATETABLEorders_cleanedASSELECT*FROMorders;--2.去重DELETEFROMorders_cleanedWHEREidIN(SELECTidFROM(SELECTid,ROW_NUMBER()OVER(PARTITIONBYorder_idORDERBYcreate_time)ASrnFROMorders_cleaned)tWHERErn>1);--3.处理缺失值UPDATEorders_cleanedSETpay_time=create_timeWHEREpay_timeISNULL;--4.处理异常值UPDATEorders_cleanedSETorder_amount=ABS(order_amount)WHEREorder_amount<0;DELETEFROMorders_cleanedWHEREorder_amount>10000;--5.统一时间格式（MySQL）UPDATEorders_cleanedSETpay_time=STR_TO_DATE(pay_time,'%Y/%m/%d%H:%i:%s')WHEREpay_timeLIKE'%/%';--6.验证清洗结果SELECTCOUNT(*)AStotal_rows,--总行数SUM(CASEWHENpay_timeISNULLTHEN1ELSE0END)ASnull_pay_time,--剩余缺失值SUM(CASEWHENorder_amount<=0THEN1ELSE0END)ASabnormal_amount--剩余异常值FROMorders_cleaned;二、基于清洗后的数据做统计分析（业务价值挖掘）清洗后的数据可支撑多维度业务分析，以下结合用户、时间、金额三大核心维度，给出高频分析场景。场景1：用户维度分析（用户分层/复购）sql--1.用户消费频次分析（统计每个用户下单次数、总消费金额）SELECTuser_id,COUNT(order_id)ASorder_count,--下单次数SUM(order_amount)AStotal_consume,--总消费金额AVG(order_amount)ASavg_order_amount,--客单价MAX(order_amount)ASmax_single_amount--单笔最高消费FROMorders_cleanedGROUPBYuser_idORDERBYtotal_consumeDESC;--2.用户分层（按消费金额分档：高/中/低价值用户）SELECTuser_id,total_consume,CASEWHENtotal_consume>=1000THEN'高价值用户'WHENtotal_consume>=500THEN'中价值用户'ELSE'低价值用户'ENDASuser_levelFROM(SELECTuser_id,SUM(order_amount)AStotal_consumeFROMorders_cleanedGROUPBYuser_id)tORDERBYtotal_consumeDESC;--3.复购率分析（统计有多次下单的用户占比）SELECT--复购用户数（下单次数≥2）SUM(CASEWHENorder_count>=2THEN1ELSE0END)ASrepurchase_user,--总用户数COUNT(DISTINCTuser_id)AStotal_user,--复购率（保留2位小数）ROUND(SUM(CASEWHENorder_count>=2THEN1ELSE0END)/COUNT(DISTINCTuser_id),2)ASrepurchase_rateFROM(SELECTuser_id,COUNT(order_id)ASorder_countFROMorders_cleanedGROUPBYuser_id)t;场景2：时间维度分析（趋势/时段特征）sql--1.每日销售额/订单量趋势（按天统计）SELECTDATE(create_time)ASorder_date,COUNT(order_id)ASdaily_order_count,--日订单量SUM(order_amount)ASdaily_sales,--日销售额AVG(order_amount)ASdaily_avg_amount--日客单价FROMorders_cleanedGROUPBYDATE(create_time)ORDERBYorder_dateASC;--2.时段消费特征（按小时统计，看高峰时段）SELECTHOUR(create_time)ASorder_hour,--提取小时（0-23）COUNT(order_id)AShour_order_count,SUM(order_amount)AShour_salesFROMorders_cleanedGROUPBYHOUR(create_time)ORDERBYhour_order_countDESC;--3.周度/月度销售对比--周度：提取星期几（1=周一，7=周日）SELECTWEEKDAY(create_time)+1ASweek_day,--MySQL：WEEKDAY返回0=周一，+1转为1=周一SUM(order_amount)ASweek_day_salesFROMorders_cleanedGROUPBYWEEKDAY(create_time)+1ORDERBYweek_day;--月度：提取月份SELECTMONTH(create_time)ASorder_month,SUM(order_amount)ASmonth_salesFROMorders_cleanedGROUPBYMONTH(create_time)ORDERBYorder_month;场景3：订单状态维度分析（转化/流失）sql--1.支付转化率（已支付订单/总订单）SELECTCOUNT(order_id)AStotal_order,SUM(CASEWHENorder_status='已支付'THEN1ELSE0END)ASpaid_order,ROUND(SUM(CASEWHENorder_status='已支付'THEN1ELSE0END)/COUNT(order_id),2)ASpay_conversion_rateFROMorders_cleaned;--2.支付时长分析（下单到支付的平均时长，单位：分钟）SELECTROUND(AVG(TIMESTAMPDIFF(MINUTE,create_time,pay_time)),--TIMESTAMPDIFF：计算时间差（分钟）2)ASavg_pay_durationFROMorders_cleanedWHEREorder_status='已支付'ANDpay_timeISNOTNULL;三、高级分析：多维度交叉分析结合用户、时间、金额维度，挖掘更深度的业务结论：sql--分析不同价值用户的消费时段特征SELECTt1.user_level,t1.order_hour,COUNT(t2.order_id)ASorder_count,SUM(t2.order_amount)ASsalesFROM(--子查询1：用户分层+时段SELECTuser_id,CASEWHENtotal_consume>=1000THEN'高价值用户'WHENto