SQL中怎样通过GROUP BY消除ETL清洗过程中的冗余行-SQL-PHP中文网

GROUP BY 仅分组不删行，无法直接去重；真正清洗需结合聚合逻辑、子查询定位后用 DELETE/INSERT 落地，并注意 NULL、大小写、collation 等陷阱。

sql中怎样通过group by消除etl清洗过程中的冗余行

GROUP BY 不能直接“消除”冗余行，它只分组、不删行；想清理ETL中的重复数据，必须配合聚合逻辑或子查询定位，再用 DELETE / INSERT 替换等手段落地。

GROUP BY 后看起来“去重”了，其实是错觉

写 SELECT * FROM orders GROUP BY order_id 在 MySQL 5.7+ 会直接报错：Expression #2 of SELECT list is not in GROUP BY clause。即使关掉 ONLY_FULL_GROUP_BY，数据库也只是随机挑每组第一行返回——你看到的“唯一结果”，可能漏掉 status = 'paid' 那条，却保留了 status = 'pending' 的脏数据。

真正清洗前，先明确业务规则：重复行中，哪条算“有效”？依据是 created_at 最大？updated_at 最新？还是 version 字段最高？
如果所有字段完全一致（比如双写导致的全字段重复），MIN(id) 或 MAX(id) 可安全选一条
但若 amount 字段有微小差异（59.90 vs 59.9），MAX(amount) 可能掩盖精度问题，得先查清是否为录入误差

用 GROUP BY + 聚合函数“取代表行”，但仅限字段级

当只需保留关键字段（如主键、时间、金额）且允许合并时，GROUP BY 搭配聚合函数是轻量方案：

SELECT 
  order_id,
  user_id,
  MAX(created_at) AS created_at,
  MAX(amount) AS amount,
  MAX(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS has_paid
FROM raw_orders 
GROUP BY order_id, user_id;

MAX(created_at) 和 MAX(amount) 在完全重复行里结果一致，相对安全
用 CASE WHEN + MAX() 模拟布尔聚合（如“是否有已支付记录”），比 GROUP_CONCAT() 更可控
注意：这种写法无法还原原始整行——比如 payment_method 和 amount 可能来自不同物理行，业务上不可接受时就得换窗口函数

真正删冗余行，GROUP BY 只能辅助定位，不能直接 DELETE

SQL 标准不支持 DELETE ... GROUP BY。你得先用 GROUP BY 找出重复键，再通过自连接或 CTE 删除多余行：

DELETE o1 FROM raw_orders o1
INNER JOIN raw_orders o2 
  ON o1.order_id = o2.order_id 
  AND o1.user_id = o2.user_id
  AND o1.id > o2.id;

该语句依赖 id 是自增主键；若表无主键，需先加临时序号：ROW_NUMBER() OVER (PARTITION BY order_id, user_id ORDER BY updated_at DESC)
执行前务必加事务并备份：BEGIN; CREATE TABLE raw_orders_bak AS SELECT * FROM raw_orders;
千万级表上，没索引的 GROUP BY order_id, user_id 会触发 Using temporary; Using filesort，先跑 EXPLAIN 看执行计划

NULL 值、大小写、collation 是最容易被忽略的陷阱

ETL 中常见脏数据特征：空邮箱、大小写混用、前后空格、全角数字。这些都会让 GROUP BY 分组失效：

NULL 在分组中被当作同一值处理，但业务上 email IS NULL 和 email = '' 往往含义不同，需提前标准化
'O002' 和 'o002' 是否归为一组，取决于字段 collation（如 utf8mb4_0900_as_cs 区分大小写，utf8mb4_general_ci 不区分）
字符串字段建议在 GROUP BY 前显式清洗：TRIM(UPPER(email))，数值字段注意隐式类型转换（如字符串型 '123 ' 和整型 123 不会自动合并）