一、题目
现有订单表t_order，包含订单ID，订单时间,下单用户，当前订单是否有效，请查询出每笔订单的上一笔有效订单
代码语言：javascript复制 --------- ---------------------- ---------- -----------
| ord_id | ord_time | user_id | is_valid |
--------- ---------------------- ---------- -----------
| 1 | 2023-12-11 12:01:03 | a | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 |
| 3 | 2023-12-11 12:03:15 | a | 0 |
| 4 | 2023-12-11 12:04:20 | a | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 |
| 8 | 2023-12-11 12:04:01 | b | 1 |
| 9 | 2023-12-11 12:07:03 | b | 1 |
--------- ---------------------- ---------- ----------- 期望查询结果如下：
代码语言：javascript复制 --------- ---------------------- ---------- ----------- --------------------
| ord_id | ord_time | user_id | is_valid | last_valid_ord_id |
--------- ---------------------- ---------- ----------- --------------------
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 2 | 2023-12-11 12:02:06 | a | 0 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 7 | 2023-12-11 12:03:03 | b | 0 | 6 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
--------- ---------------------- ---------- ----------- -------------------- 二、分析
本题是查询上一条记录的升级版本，所以考察的lag()函数，但是我们也不知道上一单是有效还是无效，所以这个题目难度就增加了很多。
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.先查询出有效订单，然后计算出每笔有效订单的上一单有效订单；
执行SQL
代码语言：javascript复制select ord_id,
ord_time,
user_id,
is_valid,
lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_id
from (select ord_id,
ord_time,
user_id,
is_valid
from t_order
where is_valid = 1) t查询结果
代码语言：javascript复制 --------- ---------------------- ---------- ----------- --------------------
| ord_id | ord_time | user_id | is_valid | last_valid_ord_id |
--------- ---------------------- ---------- ----------- --------------------
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
--------- ---------------------- ---------- ----------- -------------------- 2.原始的明细数据与新的有效订单表按照用户进行关联，有效订单表的订单时间大于等于原始订单表；
执行SQL
代码语言：javascript复制with tmp as (
-- 有效订单及其上一单有效记录
select ord_id,
ord_time,
user_id,
is_valid,
lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_id
from (select ord_id,
ord_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.ord_time <= t2.ord_time查询结果
代码语言：javascript复制 ------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- -----------------------
| t1.ord_id | t1.ord_time | t1.user_id | t1.is_valid | t2.ord_id | t2.ord_time | t2.user_id | t2.is_valid | t2.last_valid_ord_id |
------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- -----------------------
| 1 | 2023-12-11 12:01:03 | a | 1 | 1 | 2023-12-11 12:01:03 | a | 1 | NULL |
| 1 | 2023-12-11 12:01:03 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 6 | 2023-12-11 12:01:02 | b | 1 | NULL |
| 6 | 2023-12-11 12:01:02 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 |
------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- ----------------------- 3.使用row_number，原始订单记录表中的user_id、ord_id进行分组，按照有效订单表的时间排序，增加分组排序
执行SQL
代码语言：javascript复制with tmp as (
-- 有效订单及其上一单有效记录
select ord_id,
ord_time,
user_id,
is_valid,
lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_id
from (select ord_id,
ord_time,
user_id,
is_valid
from t_order
where is_valid = 1) t)
select t1.*,
t2.*,
row_number() over (partition by t1.ord_id,t1.user_id order by t2.ord_time asc) as rn
from t_order t1
left join tmp t2
on t1.user_id = t2.user_id
where t1.ord_time <= t2.ord_time我们可以看出，最终我们需要的就是rn=1 的记录
查询结果
代码语言：javascript复制 ------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- ----------------------- -----
| t1.ord_id | t1.ord_time | t1.user_id | t1.is_valid | t2.ord_id | t2.ord_time | t2.user_id | t2.is_valid | t2.last_valid_ord_id | rn |
------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- ----------------------- -----
| 1 | 2023-12-11 12:01:03 | a | 1 | 1 | 2023-12-11 12:01:03 | a | 1 | NULL | 1 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 2 |
| 1 | 2023-12-11 12:01:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 3 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 2 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 6 | 2023-12-11 12:01:02 | b | 1 | NULL | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 2 |
| 6 | 2023-12-11 12:01:02 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 3 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 2 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 2 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 1 |
------------ ---------------------- ------------- -------------- ------------ ---------------------- ------------- -------------- ----------------------- ----- 4.去除冗余字段，筛选rn=1 的记录
执行SQL
代码语言：javascript复制with tmp as (
-- 有效订单及其上一单有效记录
select ord_id,
ord_time,
user_id,
is_valid,
lag(ord_id) over (partition by user_id order by ord_time asc) as last_valid_ord_id
from (select ord_id,
ord_time,
user_id,
is_valid
from t_order
where is_valid = 1) t)
select *
from (select t1.*,
t2.last_valid_ord_id,
row_number() over (partition by t1.ord_id,t1.user_id order by t2.ord_time asc) as rn
from t_order t1
left join tmp t2
on t1.user_id = t2.user_id
where t1.ord_time <= t2.ord_time) tt
where rn = 1查询结果
代码语言：javascript复制 ------------ ---------------------- ------------- -------------- ----------------------- --------
| tt.ord_id | tt.ord_time | tt.user_id | tt.is_valid | tt.last_valid_ord_id | tt.rn |
------------ ---------------------- ------------- -------------- ----------------------- --------
| 1 | 2023-12-11 12:01:03 | a | 1 | NULL | 1 |
| 2 | 2023-12-11 12:02:06 | a | 0 | 1 | 1 |
| 3 | 2023-12-11 12:03:15 | a | 0 | 1 | 1 |
| 4 | 2023-12-11 12:04:20 | a | 1 | 1 | 1 |
| 5 | 2023-12-11 12:05:03 | a | 1 | 4 | 1 |
| 6 | 2023-12-11 12:01:02 | b | 1 | NULL | 1 |
| 7 | 2023-12-11 12:03:03 | b | 0 | 6 | 1 |
| 8 | 2023-12-11 12:04:01 | b | 1 | 6 | 1 |
| 9 | 2023-12-11 12:07:03 | b | 1 | 8 | 1 |
------------ ---------------------- ------------- -------------- ----------------------- -------- 四、建表语句和数据插入
代码语言：javascript复制--建表语句
create table t_order
(
ord_id bigint COMMENT '订单ID',
ord_time string COMMENT '订单时间',
user_id string COMMENT '用户',
is_valid bigint COMMENT '订单是否有效'
) COMMENT '订单记录表'
stored as orc
;
-- 数据插入
insert into t_order(ord_id,ord_time,user_id,is_valid)
values
(1,'2023-12-11 12:01:03','a',1),
(2,'2023-12-11 12:02:06','a',0),
(3,'2023-12-11 12:03:15','a',0),
(4,'2023-12-11 12:04:20','a',1),
(5,'2023-12-11 12:05:03','a',1),
(6,'2023-12-11 12:01:02','b',1),
(7,'2023-12-11 12:03:03','b',0),
(8,'2023-12-11 12:04:01','b',1),
(9,'2023-12-11 12:07:03','b',1);
