今天这道题是我在实际业务里被问过很多次的一个需求——订单表去重。热搜榜上“SQL Server 2022下载”这类安装词固然多但真正卡住人的往往是“清洗---sql语句去重”“sql语句去重查询”这种日常操作。做数据分析、写报表、搞数据清洗的基本都遇到过说明大家是真被这问题绊过。先把话说清楚这不是一道简单的SELECT DISTINCT题它牵扯到你对SQL执行逻辑的理解、窗口函数的掌握以及一条SQL在不同数据量下的性能表现。题目本身不难但值得把它彻底讲透。适合正在学SQL入门和基础的同学也适合写了好几年SQL但一直靠“背写法”干活的人看完可以顺手自查一下自己的认知。1. 先看题目一张订单表里藏着什么问题1.1 表结构和样例数据业务背景有一张订单主表记录用户的下单行为。同一个用户可能下多笔订单但因为上游数仓同步过程偶发重试部分订单会被同步两次最终导致主键列出现重复值。这种脏数据在数据清洗环节太常见了。建表语句如下CREATE TABLE orders ( order_id VARCHAR(32), user_id INT, order_time DATETIME, amount DECIMAL(10,2) );插入样例数据INSERT INTO orders VALUES (O001, 1001, 2024-06-01 09:30:00, 199.00), (O002, 1001, 2024-06-02 10:00:00, 59.90), (O002, 1001, 2024-06-02 10:00:00, 59.90), (O003, 1002, 2024-06-03 11:20:00, 899.00), (O004, 1003, 2024-06-03 14:45:00, 39.90), (O004, 1003, 2024-06-03 14:45:00, 39.90);注意观察O002和O004出现了完全重复的两行。用户1001有三条订单记录实际只有两笔用户1002和1003的订单也都存在重复。这里我刻意没有建主键和唯一约束这是有意的。真实场景里如果主键存在这种问题根本不会发生既然发生了就说明表结构已经失控。你在处理这类问题时第一个动作不是写SQL而是确认能不能从源头避免重复别只顾着在数据下游打补丁。1.2 需求拆解“去重”这个词其实有三种含义写SQL之前必须把需求问清楚。很多同学上来就写SELECT DISTINCT但“去重”在不同场景下含义完全不一样。第一种是整行去重两行所有字段都一样只保留一行。O002和O004就属于这种DISTINCT可以干净利落地处理。第二种是按某列去重同一个用户只保留一条记录这其实是“分组取一条”你得先明确保留哪一条——是最新下单的还是金额最大的需求不说清楚SQL没法写。第三种是统计去重数一数有多少个不同用户用COUNT(DISTINCT user_id)就行根本不涉及保留明细。我故意把这三种需求混在一个题目里是想让看题的人先判断光靠DISTINCT在这里远远不够。需求类型核心问题常用手段DISTINCT够用吗整行去重全字段重复SELECT DISTINCT够用按列去重并取某一条组内排序选一窗口函数不够统计去重不同值计数COUNT(DISTINCT)够用1.3 这道题到底在考什么它表面是个SQL练习题实际上考察三个能力第一能不能准确识别需求类型第二知不知道GROUP BY和窗口函数的分工边界第三写完SQL之后有没有性能意识。这三个能力分别对应热搜词里的“sql语句去重查询”“sql窗口函数”“慢sql优化”不是巧合是大家工作中真实卡住的地方。顺带提一句热搜词里还有“sql转er”“grafana sql”这种工具需求但工具再多核心的SQL功底还是得靠这类基础题打底。能从一张乱表里干净利索地抽出想要的数据比装十个工具都实在。2. 第一反应GROUP BY 去重为什么总差一口气2.1 用 GROUP BY 能查出来但字段拿不全我见过大量新人第一版写出来的SQL长这样SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id;结果确实让每个用户只出现一次了但除了user_id和COUNT(*)之外什么字段都拿不到。如果把需求改成“把每个用户最新一笔订单的完整信息拿出来”这个写法直接失效。有些同学会再加一步写成MAX(amount)SELECT user_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id;这样确实能拿到金额但拿的是“最大金额”不是“最新一笔的金额”。用户1001两笔订单金额分别是199.00和59.90最新一笔是59.90MAX(amount)返回的却是199.00。业务上这俩经常不是一回事。2.2 GROUP BY 的适用边界它是一台榨汁机GROUP BY的语义是“分组聚合”分组后每一行代表一个组组内细节被彻底压扁。你要想拿某个字段的非聚合值标准SQL根本不支持这也是SQL Server报错“列在选择列表中无效因为它既不包含在聚合函数中也不包含在GROUP BY子句中”的原因。我经常用榨汁机做类比GROUP BY把一筐水果榨成一杯汁营养浓缩了但果肉本身没了。窗口函数是分拣员把一筐水果按大小分好每个都还保留在原地。这个区别在面试里也经常被追问COUNT(*)配合GROUP BY和窗口函数配合PARTITION BY都能实现“每个组一个编号”但前者改变结果集粒度后者不改变。能把这个边界讲清楚SQL基础基本是过关的。2.3 有人会问能不能用子查询替代确实不借助窗口函数也能实现“每组取最新一条”。比如相关子查询SELECT o1.* FROM orders o1 WHERE o1.order_time ( SELECT MAX(o2.order_time) FROM orders o2 WHERE o2.user_id o1.user_id );这种写法在小表上没问题但它在逻辑上是逐行扫描、每行执行一次子查询数据量一大性能就崩。而且如果同一个人有两笔完全相同的下单时间这个查询会返回多行需要再套一层去重逻辑。窗口函数在这里的价值体现得很明显一次扫描、打完编号、筛出来完事。3. 正解窗口函数 ROW_NUMBER 的完整推导3.1 窗口函数到底在算什么窗口函数的基本语法是函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)PARTITION BY把数据切成一堆相互独立的小组ORDER BY在每个小组内指定顺序外面的函数对每个小组分别计算。前面说过它和GROUP BY最大的区别就是行数不减少每一行原始信息都在只是旁边附加了一个计算出来的编号或聚合值。对“每组取一条”这个需求我们需要的是给每一行编个号然后捞出编号为1的行。这个场景最常用的窗口函数就是ROW_NUMBER()。3.2 从编号到筛选两步得到最终结果第一步给每个用户内部的订单按下单时间倒序编号SELECT order_id, user_id, order_time, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders;执行结果示意如下order_iduser_idorder_timeamountrnO00210012024-06-02 10:00:0059.901O00210012024-06-02 10:00:0059.902O00110012024-06-01 09:30:00199.003O00310022024-06-03 11:20:00899.001O00410032024-06-03 14:45:0039.901O00410032024-06-03 14:45:0039.902注意完全重复的两行O002会得到rn1和rn2两个编号。这是好事你先拿到全部信息下一步用条件筛选就好。第二步把rn1的行捞出来WITH ranked AS ( SELECT order_id, user_id, order_time, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) SELECT order_id, user_id, order_time, amount FROM ranked WHERE rn 1;这样就拿到了每个用户最新一笔订单的完整信息而且因为rn1在每个分区内唯一完全重复的脏数据也一并被处理掉。这里有个关键点为什么不能直接写WHERE ROW_NUMBER() OVER(...) 1因为窗口函数在SQL的语义执行顺序中是在WHERE过滤之后才计算的你无法在WHERE里引用一个还没算出来的东西。标准SQL的执行顺序大致是FROM、WHERE、GROUP BY、HAVING、窗口函数、SELECT、ORDER BY所以必须包一层子查询或CTE。这一步是窗口函数入门时最容易卡住的地方。3.3 CTE、子查询还是临时表我习惯用CTE来写因为可读性好后面接WHERE或JOIN都很清晰。如果你的数据库版本不支持CTE比如很老的MySQL用派生表也行SELECT order_id, user_id, order_time, amount FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;临时表方案适合后面还要对结果多次加工的场景但会多一次写盘性能上不一定划算。能用一条SQL表达清楚的逻辑我倾向不引入临时表。另外还有CROSS APPLY这类写法可以处理“每组取Top N”等以后遇到再单独展开这次先把窗口函数讲透。3.4 ROW_NUMBER、RANK、DENSE_RANK 的区别什么时候用谁这三个函数放一起对比最有价值函数相同排序值的编号典型场景ROW_NUMBER顺序编号排序值相同也强制分先后取固定条数RANK相同值同号后面跳跃Top N允许并列DENSE_RANK相同值同号后面不跳跃竞赛排名、取所有并列举个例子找“每个用户金额前三的订单”如果用户1001有三笔金额完全相同的订单且正好卡在第三名ROW_NUMBER只会返回三条中的任意两条加一条RANK会返回比三条更多第四名也算并列第三DENSE_RANK也会返回并列条数但不会像RANK那样跳过排名。你选哪个取决于产品需求里“前三”允不允许并列。没人能替你回答这个问题但你要知道这三个函数给的是不同语义。4. 性能视角小表靠直觉大表靠执行计划4.1 三种写法的执行计划差异我在热搜词里看到“dbeaver连接sql server怎么查看执行计划”和“慢sql优化”说明大家写完SQL之后普遍不看执行计划。这里直接给结论SSMS里按CtrlM开启实际执行计划DBeaver里按快捷键或右键查看执行计划方法都很简单难的是读懂。回到我们的场景假设orders表有1000万行user_id有索引order_time没有索引。此时执行计划里最容易出现大开销的三个运算符Table Scan表扫描、Sort排序、Compute Scalar计算编号列。其中表扫描说明WHERE没有走索引Sort说明窗口函数的ORDER BY被迫在内存里重新排序。很多人以为窗口函数写出来之后执行计划一定很复杂其实SQL Server处理ROW_NUMBER的流程通常是先按PARTITION BY加ORDER BY需要的顺序扫描数据然后依次编号最后对外层WHERE做过滤。如果基础扫描和排序都顺整个计划非常清爽如果排序要额外做数据量一大就可能把Sort溢出到tempdb查询立刻变慢。4.2 索引设计让排序从计划里消失最有效的优化通常不是改SQL而是建一个复合索引CREATE INDEX IX_orders_user_time ON orders(user_id, order_time DESC);索引建好后数据库可以按user_id和order_time的有序结构直接扫描PARTITION BY和ORDER BY都能用上索引顺序Sort运算符直接从计划里消失。这个优化带来的性能提升经常比任何SQL改写都明显。这里有个常见的误区只在user_id上建单列索引以为PARTITION BY走索引就够了。实际上order_time还需要额外排序Sort不会消失。复合索引设计有两条朴素原则等值条件放前面排序条件放后面顺序要和查询里的PARTITION BY加ORDER BY一致。知道了窗口函数的执行逻辑索引就好设计了二者是同一件事的正反面。还要提醒一点如果这张表本身有频繁的写操作建太多索引会拖慢插入和更新。要不要为一条报表SQL建复合索引得先看执行频率和数据量。那种一个月跑一次的跑批任务全表扫描几次也无所谓天天跑的线上查询才值得认真优化。4.3 慢SQL优化的通用排查路径从实操中总结的路径我见过很多次都适用先看表扫描WHERE条件能过滤大量数据却没走索引多半是索引缺失或统计信息过期。再看Sort排序开销大优先想能不能用索引顺序替代。再看预估行数与实际行数偏差超过一个数量级优先更新统计信息或者处理参数嗅探问题。最后才考虑改写业务逻辑比如把相关子查询改成JOIN或窗口函数。这四步的顺序很重要。很多人一上来就改SQL写法结果执行计划显示瓶颈在表扫描改成什么写法都白搭。有一次我帮同事优化一个报表应用层改了半天效果不大后来发现就是少了一个覆盖索引加上之后查询从20秒降到1秒以内。执行计划不会骗人优先级是索引、统计信息、SQL写法。5. 从热搜词里捞出来的高频坑位去重、NULL与安全边界5.1 去重时的NULL值陷阱热搜词里有“sql去除空值”和“sql语句去重查询”放一起看特别有意思。DISTINCT会把NULL当成一个独立的值两行都是NULL时DISTINCT会合并成一行。这听起来合理但业务上不一定对订单金额为NULL表示未支付两笔未支付订单可能是完全不同的两笔订单去重时却会被判为重复。所以在做数据清洗时建议先在去重前问一句这个字段的NULL有没有业务含义。如果有就不要在NULL上直接做去重判断可以先用COALESCE把NULL替换成一个不可能出现在正常业务里的默认值或者用IS NOT NULL过滤掉空值再对非空部分处理。无脑去重可能把不同类型的数据悄悄合并掉。5.2 窗口函数里ORDER BY不写的后果SQL标准允许窗口函数不写ORDER BY比如SUM(amount) OVER (PARTITION BY user_id)表示“按用户分组但顺序未定义的累计求和”。这种写法在只关心总和时不影响结果因为加法顺序无所谓。但放在ROW_NUMBER里就出问题了组内顺序不确定谁会拿到1号完全看数据库心情。我专门试过SQL Server在没有ORDER BY时会按物理存储顺序编号看起来稳定可一旦并行执行计划生效或者索引变化顺序就可能变。同样一条SQL今天跑和明天跑结果不一样这种线上事故我见过不是一次两次。铁律就一句话用ROW_NUMBER、RANK、DENSE_RANK这类位置敏感函数ORDER BY必须写不关心位置时才允许不写。5.3 字符串拼接、参数化与SQL注入的边界热搜词里挂着“sql注入万能密码绕过”“sql注入 访问后台”这类词必须说一句。任何把用户输入直接拼进SQL字符串的行为都是给攻击者留后门这和你是做报表、做数据清洗还是做后台系统没有区别。攻击者可以在输入框里传一个精心构造的字符串让拼接出来的SQL改变语义轻则绕过登录重则拖库。防御的正确姿势只有一条使用参数化查询或存储过程让SQL的语义和参数值彻底分离。靠过滤关键字防注入基本等于漏筛我不推荐。既然热搜词里能看到它说明关注SQL的人确实会遇到我就顺手放进避坑清单里。5.4 多列去重与查重思路最后分享一个实际工作中很实用的场景。如果要去重的“唯一键”由多个字段组成比如user_id加product_id窗口函数的PARTITION BY可以直接写多列ROW_NUMBER() OVER (PARTITION BY user_id, product_id ORDER BY order_time DESC) AS rn如果需要先找出重复组合用GROUP BY配合HAVINGSELECT user_id, product_id, COUNT(*) AS cnt FROM orders GROUP BY user_id, product_id HAVING COUNT(*) 1;“先查重再清洗”的思路比直接DELETE安全得多。我处理脏数据的惯例是先用SELECT把疑似重复的行全部捞出来确认确认无误再执行删除或合并绝不会一上来就落地DELETE。删除容易误删之后想恢复就难了。说实话这道题我第一次带新人的时候对方也写了GROUP BY版本被我追问“那最新一笔订单的金额是多少”之后愣了半天。后来我把这题总结成三句话发给他去重之前先问保留哪一条取某一条就上窗口函数性能不行先看执行计划和索引再改SQL。这三句话看着简单够用很久。如果大家在这个系列里有什么建议或者工作中遇到更刁钻的SQL问题下一期就挑一个来拆。
