大数据面试SQL037 查询最近一笔有效订单
一、题目
现有订单表t_order_037，包含订单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
1.先查询出有效订单，然后计算出每笔有效订单的上一单有效订单；
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_037
where is_valid = 1
) t查询结果
2.原始的明细数据与新的有效订单表按照用户进行关联，有效订单表的订单时间大于等于原始订单表；
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_037
where is_valid = 1
) t
)
select
t_order_037.*,
tmp.*
from
t_order_037
left join tmp
on t_order_037.user_id = tmp.user_id
where t_order_037.ord_time <= tmp.ord_time查询结果
3.使用row_number，原始订单记录表中的user_id、ord_id进行分组，按照有效订单表的时间排序，增加分组排序
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_037
where is_valid = 1
) t
)
select
t_order_037.*,
tmp.*,
row_number()over(partition by t_order_037.ord_id,t_order_037.user_id order by tmp.ord_time asc) as rn
from
t_order_037
left join tmp
on t_order_037.user_id = tmp.user_id
where t_order_037.ord_time <= tmp.ord_time我们可以看出，最终我们需要的就是rn=1 的记录
4.去除冗余字段，筛选rn=1 的记录
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_037
where is_valid = 1
) t
)
select
*
from
(
select
t_order_037.*,
tmp.last_valid_ord_id,
row_number()over(partition by t_order_037.ord_id,t_order_037.user_id order by tmp.ord_time asc) as rn
from
t_order_037
left join tmp
on t_order_037.user_id = tmp.user_id
where t_order_037.ord_time <= tmp.ord_time
) tt
where rn = 1查询结果
四、建表语句和数据插入
--建表语句
create table t_order_037
(
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_037(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);