题目描述

现有一张淘宝购物订单明细表 dwd_tb_order_di，字段如下：

• user_id：用户ID
• order_id：订单ID
• amt：订单金额
• order_time：下单时间
• dt：分区日期（yyyyMMdd）

查询每个用户在每天（按 user_id, dt 分组）满足以下条件的订单：

1. 该订单是用户当日的第一笔订单（order_time 最早）
2. 同时该订单还是用户当日的最高金额订单（amt 最大）

若用户当日“首单”不是“最高单”，则该用户当天不返回任何记录

数据模拟

DROP TABLE IF EXISTS dwd_tb_order_di;

CREATE TABLE dwd_tb_order_di (
  user_id    VARCHAR(20) NOT NULL COMMENT '用户ID',
  order_id   VARCHAR(20) NOT NULL COMMENT '订单ID',
  amt        DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  order_time DATETIME NOT NULL COMMENT '下单时间',
  dt         CHAR(8)  NOT NULL COMMENT '分区日期yyyyMMdd'
);

INSERT INTO dwd_tb_order_di (user_id, order_id, amt, order_time, dt) VALUES
('001', 'A1', 500, '2025-02-01 10:00:00', '20250201'),
('001', 'B1', 200, '2025-02-01 11:00:00', '20250201'),
('001', 'C1', 100, '2025-02-01 12:00:00', '20250201'),
('001', 'D1', 400, '2025-02-01 13:00:00', '20250201'),
('002', 'A2', 300, '2025-02-01 10:00:00', '20250201'),
('002', 'B2', 200, '2025-02-01 11:00:00', '20250201'),
('002', 'C2', 400, '2025-02-01 12:00:00', '20250201'),
('003', 'A3', 300, '2025-02-01 10:00:00', '20250201'),
('003', 'B3', 200, '2025-02-01 11:00:00', '20250201');

思路分析

这题的关键是：同一用户同一天，要同时满足“最早一单”和“最高一单”为同一条记录。

可拆解为 3 步：

1. 按 user_id, dt 分区，对订单按 order_time 升序排列，打上「首单标记」rn_first=1
2. 同分区对订单按 amt 逆序排列，打上「最高单标记」rn_max=1
3. 同时满足 rn_first = 1 AND rn_max = 1 的就是答案（如果不存在则不返回）

参考答案

SELECT
  user_id,
  dt,
  order_id,
  amt,
  order_time
FROM (
        SELECT
            user_id,
            dt,
            order_id,
            amt,
            order_time,
            ROW_NUMBER() OVER (PARTITION BY user_id, dt ORDER BY order_time ASC) AS rn_first,
            ROW_NUMBER() OVER (PARTITION BY user_id, dt ORDER BY amt DESC) AS rn_max
          FROM dwd_tb_order_di
) t
WHERE rn_first = 1
  AND rn_max = 1
ORDER BY user_id, dt;