07 | 查询最近一笔有效订单

---

目录

一、问题定义与业务场景

二、数据准备与建表语句

2.1 建表语句

2.2 样例数据

2.3 期望结果

三、问题分析

四、SQL解决方案

---

一、问题定义与业务场景

目标：为每个用户查询最近一笔有效订单（非取消状态且最近完成）。
核心需求：

1. 排除 is_valid = 0（取消状态）的订单
2. 返回每个用户最近一笔成功订单的详细信息
3. 无有效订单时返回 NULL

业务场景：电商订单追溯、风控系统交易校验、用户行为分析。

---

二、数据准备与建表语句

2.1 建表语句

CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY COMMENT '订单ID',
    order_time DATETIME NOT NULL COMMENT '下单时间',
    user_id VARCHAR(20) NOT NULL COMMENT '用户ID',
    is_valid TINYINT DEFAULT 1 COMMENT '是否有效（1有效，0取消）'
);

2.2 样例数据

INSERT INTO t_order (order_id, order_time, user_id, is_valid) VALUES
(1, '2025-06-01 09:00:00', 'U1001', 1),
(2, '2025-06-01 09:05:00', 'U1001', 0), 
(3, '2025-06-01 09:10:00', 'U1001', 0),  
(4, '2025-06-01 10:00:00', 'U1001', 1),
(5, '2025-06-01 10:30:00', 'U1001', 1),
(6, '2025-06-01 10:45:00', 'U1002', 1),
(7, '2025-06-01 11:00:00', 'U1002', 0),
(8, '2025-06-01 11:05:00', 'U1002', 1),
(9, '2025-06-01 11:10:00', 'U1002', 1);

2.3 期望结果

---

三、问题分析

本题是查询上一条记录问题的升级版本，核心难点在于：无法预知相邻订单的有效性状态，这种不确定性增加了题目的难度。

四、SQL解决方案

1.先查询出有效订单，然后计算出每笔有效订单的上一单的有效订单

SELECT
	order_id,
	order_time,
	user_id,
	is_valid,
	lag( order_id ) over ( PARTITION BY user_id ORDER BY order_time ASC ) AS last_valid_order_id 
FROM
	( SELECT order_id, order_time, user_id, is_valid FROM t_order WHERE is_valid = 1 ) tmp

2.明细数据与有效订单表按照用户进行关联，有效订单表的订单时间大于等于原始订单表；

WITH tmp AS(
  SELECT
	order_id,
	order_time,
	user_id,
	is_valid,
	lag(order_id) over (PARTITION BY user_id ORDER BY order_time ASC) AS last_valid_order_id 
FROM
(  
	SELECT
		order_id,
		order_time,
		user_id,
		is_valid 
	FROM
		t_order 
	WHERE
		is_valid = 1
) t )
SELECT
	t1.*,
	t2.* 
FROM
	t_order t1
	LEFT JOIN tmp t2 
ON t1.user_id = t2.user_id 
WHERE
	t1.order_time <= t2.order_time

3.使用row_number，原始订单记录表中的user_id、ord_id进行分组，按照有效订单表的时间排序，增加分组排序

WITH tmp AS (
	SELECT
		order_id,
		order_time,
		user_id,
		is_valid,
		lag( order_id ) over ( PARTITION BY user_id ORDER BY order_time ASC ) AS last_valid_order_id 
	FROM
		( SELECT order_id, order_time, user_id, is_valid FROM t_order WHERE is_valid = 1 ) t 
	) 
SELECT
	t1.*,
	t2.*,
	ROW_NUMBER() OVER ( PARTITION BY t1.order_id, t1.user_id ORDER BY t2.order_time ASC ) AS rn 
FROM
	t_order t1
	LEFT JOIN tmp t2 ON t1.user_id = t2.user_id 
WHERE
	t1.order_time <= t2.order_time

4.去除冗余字段，筛选 rn=1 的记录

WITH tmp AS (
	SELECT
		order_id,
		order_time,
		user_id,
		is_valid,
		lag( order_id ) over ( PARTITION BY user_id ORDER BY order_time ASC ) AS last_valid_order_id 
	FROM
		( SELECT order_id, order_time, user_id, is_valid FROM t_order WHERE is_valid = 1 ) t 
	) 
SELECT
	* 
FROM
	(
	SELECT
		t1.*,
		t2.last_valid_order_id,
		ROW_NUMBER() OVER ( PARTITION BY t1.order_id, t1.user_id ORDER BY t2.order_time ASC ) AS rn 
	FROM
		t_order t1
		LEFT JOIN tmp t2 ON t1.user_id = t2.user_id 
	WHERE
		t1.order_time <= t2.order_time 
	) t3 
WHERE
	rn = 1;