DAY3--SQL单字段去重查询

SQL基础入门电商用户数据单字段去重查询实操这一章能解决什么电商工作问题前两章我们学了SELECT *全量看数据和SELECT 字段列表精准取字段。这一章讲的是另一个高频操作去重。我讲一个自己刚入职第一年踩过的坑。618大促结束后运营总监让我统计“这次活动有多少独立用户参与了”。我直接SELECT COUNT(*) FROM order_detail WHERE activity_id 618_promotion结果是35820条订单。我兴冲冲汇报说“有3.5万用户参与”。总监反问“你确定是用户数不是订单数同一个用户下了3单你算了几次”我当场愣住——对算错了。订单表里一个人可能有多条记录我需要的是去重后的独立用户数。后来我改成SELECT COUNT(DISTINCT user_id) FROM ...结果是12450个独立用户。总监说“这才是对的下次注意。”从那天起我记住了一个道理电商数据分析里90%的“人数”“商家数”“商品数”“渠道数”都需要去重。这一章的核心场景就是用户来源渠道/高校/地域的枚举值排查运营问“我们的用户都来自哪些大学”你需要返回一份不重复的高校名单而不是把每个用户的记录都列出来。活动独立参与用户统计大促后复盘计算UV独立访客数必须用COUNT(DISTINCT user_id)。商品类目/品牌的全量去重盘点平台有多少个一级类目多少个品牌都需要去重。订单支付渠道去重梳理系统接入了微信、支付宝、银行卡等渠道你要枚举出所有已产生订单的渠道。合规审计中的敏感字段去重校验检查用户表中device_id是否有重复理论上应唯一或者枚举所有已收集的用户年龄段。对比前两章SELECT *是全量拉取SELECT 字段列表是只取需要的列而SELECT DISTINCT是在此基础上按行去重只保留不重复的记录。基础认知DISTINCT去重的核心逻辑语法本质SELECT DISTINCT 字段名 FROM 表名;DISTINCT关键字放在SELECT后面、字段名前面告诉数据库返回这个字段的所有不重复值。重复的只保留一条。执行逻辑用人话翻译数据库会扫描该字段的所有行把值一个个放进一个“集合”里——如果值已经在集合里了就跳过如果是新值就加进去。最后把这个集合返回给你。所以SELECT DISTINCT university FROM user_profile如果表里有5行但university只有4个不同的值北京大学出现2次最终结果只有4行。和普通SELECT的核心区别操作返回行数典型场景SELECT university全部行含重复导出每个用户的原始数据用于明细核对SELECT DISTINCT university仅不重复的值回答“有哪些高校”“一共有多少种渠道”电商工作中必须用DISTINCT的场景回答“有多少个”独立用户数、独立商品数、独立渠道数、独立类目数。回答“有哪些”平台有哪些高校的用户、哪些省份有订单、哪些品牌的商品在售。数据质量校验检查某个字段是否包含重复值比如理论上device_id应唯一如果SELECT DISTINCT device_id的行数不等于总行数说明有重复数据。生成枚举值下拉列表前端或BI工具需要一份不重复的维度值列表供用户筛选。新手最容易混淆的误区误区1认为DISTINCT只能放在第一列。正解SELECT DISTINCT作用于它后面所有字段的组合。单字段去重就只针对该字段多字段去重会按字段组合去重后面章节会讲。误区2把DISTINCT写在字段名后面比如SELECT university DISTINCT。正解DISTINCT必须紧跟在SELECT之后字段名之前。误区3认为DISTINCT会改变原始数据。正解它只是查询时去重不修改表里的真实数据。分步实操从建表到高校字段去重查询业务需求运营要查看平台用户来自于哪些高校用于校园拉新渠道的覆盖规划。只需要一份不重复的高校名单不要重复数据不要其他字段。步骤1建表与测试数据导入修正原素材语法错误操作动作执行以下修正后的建表语句。-- 创建用户画像表彻底修正原素材的语法问题 CREATE TABLE user_profile ( id INT NOT NULL COMMENT 用户唯一ID, device_id INT NOT NULL COMMENT 用户设备ID, -- 原素材写成了device id有空格已修正 gender VARCHAR(14) NOT NULL COMMENT 用户性别, age INT COMMENT 用户年龄, university VARCHAR(32) NOT NULL COMMENT 用户所在高校, province VARCHAR(32) NOT NULL COMMENT 用户所在省份 );原素材错误点逐条标注与修正原因复习巩固字段名包含空格原素材device id中间有空格MySQL无法识别。统一用下划线device_id。数据类型写法错误原素材写varchar14缺少括号和长度。标准为VARCHAR(14)。NOT NULL位置混乱有些字段写成了NOT NULL int。标准顺序是数据类型 NOT NULL。表名拼写错误原素材某处把user_profile写成了user_profile少了下划线或user profile空格已统一。缺少注释实际工作强烈建议加COMMENT我顺手加上了。插入5条测试数据其中北京大学对应2条用户用于去重校验INSERT INTO user_profile (id, device_id, gender, age, university, province) VALUES (1, 2138, male, 21, 北京大学, 北京), (2, 3214, female, 22, 复旦大学, 上海), (3, 6543, male, 20, 浙江大学, 浙江), (4, 2315, female, NULL, 南京大学, 江苏), (5, 5432, male, 23, 北京大学, 北京); -- 注意第5条university也是北京大学原素材INSERT语句的错误点原素材把INSERT INTO写成了INSERT INOT或缺少INTO。字符串值漏引号如male写成了male无引号。NULL值写成了NULL带引号的字符串或空字符串。第5条数据的university原素材写成了北京大學繁体或拼写错误已统一为北京大学。预期结果执行成功表里有5行数据其中university字段有2行是“北京大学”。常见问题处理如果提示Duplicate entry 1 for key PRIMARY先执行TRUNCATE TABLE user_profile;清空表再插入。如果提示Data too long for column university检查字符串长度是否超过32个字符。VARCHAR(32)够用了。步骤2编写单字段去重查询SQL语句操作动作输入以下SQL。SELECT DISTINCT university FROM user_profile;逐句通俗讲解SELECT DISTINCT这是一个固定搭配意思是“我要去重查询”。DISTINCT必须写在SELECT之后、字段名之前。university只返回高校这一列不关心其他字段。FROM user_profile从用户表取。对比不加DISTINCT如果写成SELECT university FROM user_profile;会返回5行北京大学出现2次。加了DISTINCT后重复的北京大学只保留1行最终返回4行。字段顺序说明DISTINCT只作用于它后面的所有字段。这里只有一个字段所以只对该字段去重。步骤3执行查询与结果校验预期结果university北京大学复旦大学浙江大学南京大学校验重点去重效果原表有2条“北京大学”结果集只有1条。✅字段数量只有1列没有多余的id、device_id等。✅行数4行不是5行。✅NULL值处理如果某行university为NULLDISTINCT也会保留一个NULL但本例中没有NULL因为university字段设置了NOT NULL。顺序默认按数据库内部存储顺序返回不保证排序。如果要求固定顺序需要加ORDER BY后续章节讲。验证方法你也可以先执行SELECT university FROM user_profile;看原始5行再执行SELECT DISTINCT university看去重后的4行对比差异。步骤4查询结果的电商场景基础应用场景一导出给运营做校园拉新渠道的覆盖规划运营拿到这份不重复的高校名单北京大学、复旦大学、浙江大学、南京大学可以检查当前已覆盖的高校是否和业务目标一致比如目标覆盖20所985高校现在只有4所。针对未覆盖的高校制定拉新计划。给名单上的高校学生推送专属活动需要配合WHERE条件下一章讲。场景二统计不同高校的用户占比虽然DISTINCT本身只返回高校名称但你可以基于它继续分析SELECT university, COUNT(*) AS user_count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user_profile), 2) AS percentage FROM user_profile GROUP BY university ORDER BY user_count DESC;结果会是北京大学2人40%其他高校各1人20%这就是典型的“用户画像分布”分析运营可以用这个决定在哪个高校加大投放。场景三拉新渠道的投放效果复盘假设你的user_profile还有一个source_channel字段注册来源运营想知道“哪些渠道带来了新用户”。你可以先SELECT DISTINCT source_channel FROM user_profile枚举出所有渠道再对比各渠道的用户质量。实操避坑提醒新手高频踩坑点坑1统计独立用户数时忘了去重报出离谱数据错误场景我前面讲的618活动复盘直接COUNT(*)把订单数当用户数报给总监。幸好总监经验丰富当场指出问题。如果是报给老板或客户信任度直接打折扣。后果数据错误业务决策依据失效。比如按照3.5万用户准备奖品预算实际只有1.2万用户会造成奖品积压或预算浪费。解决办法形成肌肉记忆任何时候听到“用户数”“独立访客”“参与人数”“购买人数”脑子里立刻跳出COUNT(DISTINCT user_id)。先验证再汇报对关键指标先用SELECT COUNT(DISTINCT user_id)和SELECT COUNT(*)分别跑一遍对比差异。如果差异很大比如差了几倍说明有大量重复需要用去重后的值。在SQL注释里写明统计口径-- 统计独立用户数去重避免自己或他人以后误解。坑2把DISTINCT写错位置导致语法错误错误场景新手期我写过SELECT university DISTINCT FROM user_profile;报错You have an error in your SQL syntax。我还纳闷“不就是去重吗为什么不行”。还有一次我写SELECT DISTINCT FROM user_profile;忘了写字段名也报错。后果浪费时间排查打击信心。解决办法记住固定模板SELECT DISTINCT 字段1, 字段2, ... FROM 表名;。DISTINCT永远在SELECT之后、字段列表之前。使用SQL客户端的自动补全输入SELECT DISTINCT后客户端会提示减少拼错概率。如果要对多列去重SELECT DISTINCT university, province FROM ...DISTINCT作用于两列的组合。坑3误以为DISTINCT会影响ORDER BY或WHERE的执行顺序错误场景有同事写SELECT DISTINCT age FROM user_profile ORDER BY age;他认为DISTINCT只对age去重排序也是按age这没问题。但他后来写SELECT DISTINCT university, age FROM user_profile ORDER BY age;想按年龄排序结果报错或排序结果不是他想要的——因为DISTINCT作用于(university, age)组合ORDER BY的字段必须在SELECT列表里这个规则没问题但他没理解DISTINCT后的行数变少了排序逻辑依然正确只是他期望的“先按年龄排再去重”是不成立的因为SQL的执行顺序是FROM → WHERE → SELECT → DISTINCT → ORDER BY。所以DISTINCT发生在排序之前。后果对执行顺序理解错误导致结果集不符合预期。解决办法记住SQL逻辑执行顺序这个后面会有专题先有个印象FROM→WHERE→SELECT→DISTINCT→ORDER BY→LIMIT。DISTINCT不是在排序之后去重而是先去重、再排序。所以如果你想按某个字段排序这个字段必须在SELECT DISTINCT的字段列表里。新手阶段建议先写不带DISTINCT的查询确认字段和排序确认无误后再加DISTINCT。电商数据合规/法律避坑提示《个人信息保护法》中的匿名化与去重这一章的知识点——DISTINCT去重——在数据合规领域有一个重要的关联概念匿名化。《个人信息保护法》第四条定义个人信息时有一个除外情形“匿名化处理后的信息”。法律第七十三条明确匿名化是指个人信息经过处理无法识别特定自然人且不能复原的过程。匿名化的数据不属于个人信息可以自由使用、共享、分析不再受个保法的严格限制。这和DISTINCT有什么关系电商工作中如果你需要导出用户数据做统计分析但又不想触碰敏感信息一个常见做法是只导出去重后的非敏感维度值。举个例子运营问“平台用户都来自哪些省份”你不需要导出每个用户的device_id和province只需要SELECT DISTINCT province FROM user_profile。这份省份列表不涉及任何个人标识不属于个人信息可以放心发邮件、存网盘、甚至给外部合作方看。运营问“不同高校的用户分布”你不需要给出device_id只需要给出university和对应的用户数聚合后的统计结果也属于匿名化信息。我的合规操作铁律能去重就不出明细凡是回答“有哪些”的问题优先用DISTINCT返回枚举值列表而不是返回明细行。能聚合就不出明细回答“有多少”的问题用COUNT(DISTINCT ...)返回数字而不是导出用户ID列表。导出前做最小化字段检查如果确实需要导出明细比如给运营做精准推送必须只包含业务必需的字段并且走数据导出审批流程。真实案例我前同事接到一个需求——“导出所有有购买记录的用户的高校信息用于校园活动定向推送”。他直接用SELECT device_id, university FROM user_profile WHERE order_count 0导出了Excel包含了device_id设备标识属于个人信息。虽然目的是正当的但他没有经过数据安全审批也没有脱敏直接发到了工作群里。合规部监测到敏感数据外发给了他一个书面警告。正确的做法应该是先确认推送活动是否真的需要device_id如果是那必须走审批并且限定接收人、限定使用用途。如果只需要统计“哪些高校有购买用户”那就用SELECT DISTINCT university FROM ...完全不涉及个人信息。总结DISTINCT不仅是SQL语法也是你落实数据最小化和匿名化原则的好帮手。能用去重的地方就不要把明细带出去。我的实操小技巧 值得你思考的问题小技巧1用DISTINCT快速检查字段的唯一性想确认device_id是否唯一理论上每个用户一个设备ID可以跑SELECT COUNT(*) AS total_rows, COUNT(DISTINCT device_id) AS distinct_devices, COUNT(*) - COUNT(DISTINCT device_id) AS duplicate_count FROM user_profile;如果duplicate_count大于0说明有重复的device_id需要排查数据质量问题比如同一个设备注册了多个账号。小技巧2组合使用DISTINCT和COUNT统计平台覆盖了多少个高校SELECT COUNT(DISTINCT university) AS university_count FROM user_profile;返回4。这个数字可以直接写在日报里。小技巧3DISTINCT和GROUP BY的直觉选择很多新手会纠结去重用DISTINCT还是GROUP BY简单原则只去重、不聚合用SELECT DISTINCT更直观。需要聚合比如按高校统计用户数用GROUP BY功能更强。两者在只去重的场景下性能差不多优先用DISTINCT让代码更易懂。值得你思考的问题电商运营给你一个需求“帮我拉一下今年618活动的独立用户数。”你写了SELECT COUNT(DISTINCT user_id) FROM orders WHERE date BETWEEN 2024-06-01 AND 2024-06-18。运营又问“这些用户里男女比例是多少”这时候你需要写一个新的查询要同时用到去重和分组。你会怎么写去重在这里还适用吗我的答案是统计男女比例需要每个用户的性别但一个用户可能有多笔订单所以你需要先对user_id去重再关联性别。有两种写法子查询SELECT gender, COUNT(*) FROM (SELECT DISTINCT user_id, gender FROM orders JOIN user_profile ON ...) tmp GROUP BY gender;或者直接用COUNT(DISTINCT user_id)配合GROUP BY gender不行因为COUNT(DISTINCT user_id)会按gender分组后分别去重逻辑是对的SELECT gender, COUNT(DISTINCT user_id) FROM orders JOIN user_profile ON ... GROUP BY gender;这个问题留给你思考。在那之前请你把DISTINCT用到日常每一个“有多少种”“有哪些”的问题里。