大数据面试SQL（五）：查询最近一笔有效订单

文章目录

查询最近一笔有效订单

一、题目

二、分析

三、SQL实战

四、样例数据参考

查询最近一笔有效订单

一、题目

现有订单表t5_order，包含订单ID，订单时间，下单用户，当前订单是否有效。

请查询出每笔订单的上一笔有效订单，注意不是每笔订单都是有效的。

样例数据：

目标结果：

二、分析

本题是查询上一条记录的升级版本，所以考察的lag()函数，但是我们也不知道上一单是有效还是无效，所以这个题目难度就增加了很多。

维度	评分
题目难度	⭐️⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL实战

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

查询语句：

select ord_id,ord_time,user_name,is_valid,lag(ord_id) over (partition by user_name order by ord_time asc) as last_valid_ord_id
from (select ord_id,ord_time,user_name,is_validfrom t5_orderwhere is_valid = 1) t;

查询结果：

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

查询语句：

with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_name,is_valid,lag(ord_id) over (partition by user_name order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_name,is_validfrom t5_orderwhere is_valid = 1) t)
select t1.*,t2.*
from t5_order t1
left join tmp t2 on t1.user_name = t2.user_name
where t1.ord_time <= t2.ord_time;

查询结果：

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

查询语句：

with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_name,is_valid,lag(ord_id) over (partition by user_name order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_name,is_validfrom t5_orderwhere is_valid = 1) t)
select t1.*,t2.*,row_number() over (partition by t1.ord_id,t1.user_name order by t2.ord_time asc) as rn
from t5_order t1
left join tmp t2 on t1.user_name = t2.user_name
where t1.ord_time <= t2.ord_time;

我们可以看出，最终我们需要的就是rn=1 的记录。

查询结果：

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

查询语句：

with tmp as (-- 有效订单及其上一单有效记录select ord_id,ord_time,user_name,is_valid,lag(ord_id) over (partition by user_name order by ord_time asc) as last_valid_ord_idfrom (select ord_id,ord_time,user_name,is_validfrom t5_orderwhere is_valid = 1) t
)
select * from
(select t1.*,t2.*,row_number() over (partition by t1.ord_id,t1.user_name order by t2.ord_time asc) as rn
from t5_order t1
left join tmp t2 on t1.user_name = t2.user_name
where t1.ord_time <= t2.ord_time) tt
where tt.rn = 1;

查询结果：

四、样例数据参考

--建表语句
create table t5_order
(ord_id bigint COMMENT '订单ID',ord_time string COMMENT '订单时间',user_name string COMMENT '用户名',is_valid int COMMENT '订单是否有效'
);
-- 数据插入
insert into t5_order(ord_id,ord_time,user_name,is_valid)
values(1,'2024-08-11 12:01:03','姬小满',1),(2,'2024-08-11 12:02:06','姬小满',0),(3,'2024-08-11 12:03:15','姬小满',0),(4,'2024-08-11 12:04:20','姬小满',1),(5,'2024-08-11 12:05:03','姬小满',1),(6,'2024-08-11 12:01:02','甄姬',1),(7,'2024-08-11 12:03:03','甄姬',0),(8,'2024-08-11 12:04:01','甄姬',1),(9,'2024-08-11 12:07:03','甄姬',1);