一、题目
现有订单表t_order,包含订单ID,订单时间,下单用户,当前订单是否有效,请查询出每笔订单的上一笔有效订单
+---------+----------------------+----------+-----------+
| 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 |
+---------+----------------------+----------+-----------+
期望查询结果如下:
+---------+----------------------+----------+-----------+--------------------+
| 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
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
查询结果
+---------+----------------------+----------+-----------+--------------------+
| 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 |
+---------+----------------------+----------+-----------+--------------------+
执行SQL
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
查询结果
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+
| 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 |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+
执行SQL
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 的记录
查询结果
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+-----+
| 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 |
+------------+----------------------+-------------+--------------+------------+----------------------+-------------+--------------+-----------------------+-----+
执行SQL
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
查询结果
+------------+----------------------+-------------+--------------+-----------------------+--------+
| 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 |
+------------+----------------------+-------------+--------------+-----------------------+--------+
--建表语句
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);