华为大数据面试SQL-ODS层数据清洗去重

一、题目

已知ODS层有一张订单表t1_ods_order，由于上游系统CDC（变更数据捕获）同步策略问题，同一订单可能有多条记录（每次更新都会写入一条新记录）。表中包含字段：order_id（订单ID）、order_amount（订单金额）、order_status（订单状态）、update_time（更新时间）。

请对数据进行清洗去重，每个订单只保留更新时间最新的一条记录。同时需要统计清洗前后的数据行数，以验证去重效果。

样例数据

+----------+---------------+--------------+----------------------+
| order_id | order_amount  | order_status |     update_time      |
+----------+---------------+--------------+----------------------+
| 1001     | 100.00        | pending      | 2024-06-01 08:00:00  |
| 1001     | 100.00        | confirmed    | 2024-06-01 09:00:00  |
| 1001     | 100.00        | shipped      | 2024-06-01 10:00:00  |
| 1002     | 200.00        | pending      | 2024-06-01 08:30:00  |
| 1002     | 200.00        | cancelled    | 2024-06-01 08:35:00  |
| 1003     | 150.00        | confirmed    | 2024-06-01 09:00:00  |
| 1003     | 150.00        | confirmed    | 2024-06-01 09:00:00  |
| 1004     | 300.00        | pending      | 2024-06-01 10:00:00  |
+----------+---------------+--------------+----------------------+

三、思路分析

本题是数仓建设中ODS层数据清洗的经典场景。核心步骤简单直接：

• ROW_NUMBER去重：使用ROW_NUMBER()按order_id分组、按update_time降序排序，rn=1即为每个订单的最新记录。这是数仓开发中最常用的去重手段。

• 特殊情况处理：示例中order_id=1003存在两条update_time完全相同的记录，此时ROW_NUMBER的排序存在不确定性。在真实场景中应增加唯一排序字段（如自增主键或数据源的时间戳）确保结果稳定。

• 清洗前后对比：可以通过COUNT(*)和COUNT(DISTINCT order_id)来快速对比清洗前后的数据行数，验证去重是否合理。

在华为的数据中台建设中，ODS层每日接入大量上游业务系统数据，去重是ETL的第一步，后续还会做数据质量校验、空值处理等。

维度	评分
题目难度	⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

1.查看清洗前数据统计

先统计原始表的行数和去重后的订单数，了解重复情况。

执行SQL

select
    count(*) as total_rows,
    count(distinct order_id) as distinct_orders,
    count(*) - count(distinct order_id) as duplicate_rows
from t1_ods_order

执行结果

+------------+------------------+----------------+
| total_rows | distinct_orders  | duplicate_rows |
+------------+------------------+----------------+
| 8          | 4                | 4              |
+------------+------------------+----------------+

2.使用ROW_NUMBER标记每个订单的最新记录

按order_id分组，按update_time降序排序，用ROW_NUMBER生成行号。rn=1即为最新记录。

执行SQL

select
    order_id,
    order_amount,
    order_status,
    update_time,
    row_number() over (partition by order_id order by update_time desc) as rn
from t1_ods_order

执行结果

+----------+---------------+--------------+----------------------+----+
| order_id | order_amount  | order_status |     update_time      | rn |
+----------+---------------+--------------+----------------------+----+
| 1001     | 100.00        | shipped      | 2024-06-01 10:00:00  | 1  |
| 1001     | 100.00        | confirmed    | 2024-06-01 09:00:00  | 2  |
| 1001     | 100.00        | pending      | 2024-06-01 08:00:00  | 3  |
| 1002     | 200.00        | cancelled    | 2024-06-01 08:35:00  | 1  |
| 1002     | 200.00        | pending      | 2024-06-01 08:30:00  | 2  |
| 1003     | 150.00        | confirmed    | 2024-06-01 09:00:00  | 1  |
| 1003     | 150.00        | confirmed    | 2024-06-01 09:00:00  | 2  |
| 1004     | 300.00        | pending      | 2024-06-01 10:00:00  | 1  |
+----------+---------------+--------------+----------------------+----+

3.筛选rn=1的记录，完成去重

过滤rn=1得到清洗后的数据。结果为4条记录，每个订单一条。

执行SQL

select
    order_id,
    order_amount,
    order_status,
    update_time
from (
    select
        order_id,
        order_amount,
        order_status,
        update_time,
        row_number() over (partition by order_id order by update_time desc) as rn
    from t1_ods_order
) t
where rn = 1

执行结果

+----------+---------------+--------------+----------------------+
| order_id | order_amount  | order_status |     update_time      |
+----------+---------------+--------------+----------------------+
| 1001     | 100.00        | shipped      | 2024-06-01 10:00:00  |
| 1002     | 200.00        | cancelled    | 2024-06-01 08:35:00  |
| 1003     | 150.00        | confirmed    | 2024-06-01 09:00:00  |
| 1004     | 300.00        | pending      | 2024-06-01 10:00:00  |
+----------+---------------+--------------+----------------------+

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
ROW_NUMBER + 差值法	连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算
PARTITION BY	窗口函数按列分组，实现组内独立计算

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE IF NOT EXISTS t1_ods_order (
    order_id BIGINT COMMENT '订单ID',
    order_amount DECIMAL(10,2) COMMENT '订单金额',
    order_status STRING COMMENT '订单状态',
    update_time STRING COMMENT '更新时间'
);

--数据插入
INSERT INTO t1_ods_order(order_id, order_amount, order_status, update_time) VALUES
(1001, 100.00, 'pending',   '2024-06-01 08:00:00'),
(1001, 100.00, 'confirmed', '2024-06-01 09:00:00'),
(1001, 100.00, 'shipped',   '2024-06-01 10:00:00'),
(1002, 200.00, 'pending',   '2024-06-01 08:30:00'),
(1002, 200.00, 'cancelled', '2024-06-01 08:35:00'),
(1003, 150.00, 'confirmed', '2024-06-01 09:00:00'),
(1003, 150.00, 'confirmed', '2024-06-01 09:00:00'),
(1004, 300.00, 'pending',   '2024-06-01 10:00:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题