SQL面试题练习 —— 查询最近一笔有效订单

目录

• 1 题目
• 2 建表语句
• 3 题解

  题目来源：字节跳动。

  
  （图片来源网络，侵删）

  1 题目

  现有订单表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                  |
  +---------+----------------------+----------+-----------+--------------------+
  

  2 建表语句

  --建表语句
  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);
  

  3 题解

  （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
        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                  |
  +---------+----------------------+----------+-----------+--------------------+
  

  （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
            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