一、题目
现有订单表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.先查询出有效订单，然后计算出每笔有效订单的上一单有效订单；
selectord_id,ord_time,user_id,is_valid,lag(ord_id)over(partition by user_id order by ord_time asc) as last_valid_ord_idfrom(selectord_id,ord_time,user_id,is_validfrom t_order_037where is_valid = 1) t
查询结果
2.原始的明细数据与新的有效订单表按照用户进行关联，有效订单表的订单时间大于等于原始订单表；
with tmp as(-- 有效订单及其上一单有效记录selectord_id,ord_time,user_id,is_valid,lag(ord_id)over(partition by user_id order by ord_time asc) as last_valid_ord_idfrom(selectord_id,ord_time,user_id,is_validfrom t_order_037where is_valid = 1) t)selectt_order_037.*,tmp.*fromt_order_037left join tmpon t_order_037.user_id = tmp.user_idwhere t_order_037.ord_time <= tmp.ord_time
查询结果
3.使用row_number，原始订单记录表中的user_id、ord_id进行分组，按照有效订单表的时间排序，增加分组排序
with tmp as(-- 有效订单及其上一单有效记录selectord_id,ord_time,user_id,is_valid,lag(ord_id)over(partition by user_id order by ord_time asc) as last_valid_ord_idfrom(selectord_id,ord_time,user_id,is_validfrom t_order_037where is_valid = 1) t)selectt_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 rnfromt_order_037left join tmpon t_order_037.user_id = tmp.user_idwhere t_order_037.ord_time <= tmp.ord_time
我们可以看出，最终我们需要的就是rn=1 的记录
4.去除冗余字段，筛选rn=1 的记录
with tmp as(-- 有效订单及其上一单有效记录selectord_id,ord_time,user_id,is_valid,lag(ord_id)over(partition by user_id order by ord_time asc) as last_valid_ord_idfrom(selectord_id,ord_time,user_id,is_validfrom t_order_037where is_valid = 1) t)select*from(selectt_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 rnfromt_order_037left join tmpon t_order_037.user_id = tmp.user_idwhere t_order_037.ord_time <= tmp.ord_time) ttwhere 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);
社群
为了方便交流和得到大家的反馈，我创建了一个交流群，欢迎大家加入。（社群不卖课，不开星球，只交流）
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
下一篇：
再谈炸裂函数
请登录后发表评论
登录
全部评论
