一、题目
在外卖订单中，有时用户会指定订单的配送时间。现定义：如果用户下单日期与期望配送日期相同则认为是即时单，如果用户下单日期与期望配送时间不同则是预约单。每个用户下单时间最早的一单为用户首单，请计算用户首单中即时单的占比。
t_user_order
代码语言：javascript复制 ----------- ---------- ---------------------- --------------
| order_id | user_id | order_time | desire_date |
----------- ---------- ---------------------- --------------
| 1001 | 001 | 2024-07-01 12:01:23 | 2024-07-01 |
| 1002 | 001 | 2024-07-01 12:03:23 | 2024-07-02 |
| 1003 | 002 | 2024-07-01 13:03:23 | 2024-07-02 |
| 1004 | 002 | 2024-07-01 13:07:23 | 2024-07-01 |
| 1005 | 003 | 2024-07-01 15:03:23 | 2024-07-01 |
----------- ---------- ---------------------- --------------
二、分析
- 题目中给出了即时单和首单的定义，需要先找到每个用户的首单，然后进行判断是否是即时单；
- 计算完成之后，需要统计出共有多少首单，其中即时单的比例。
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
1.找到用户首单，并判断是否是即时单
使用row_number函数，找到每个用户的首单，然后根据订单时间和预期送达时间判断是否是即时单
执行SQL
代码语言：javascript复制select order_id,
user_id,
order_time,
desire_date,
is_instant
from (select order_id,
user_id,
order_time,
desire_date,
if(to_date(order_time) = to_date(desire_date), 1, 0) as is_instant,
row_number() over (partition by user_id order by order_time asc ) as rn
from t_user_order) t
where rn = 1
查询结果
代码语言：javascript复制 ----------- ---------- ---------------------- -------------- -------------
| order_id | user_id | order_time | desire_date | is_instant |
----------- ---------- ---------------------- -------------- -------------
| 1001 | 001 | 2024-07-01 12:01:23 | 2024-07-01 | 1 |
| 1003 | 002 | 2024-07-01 13:03:23 | 2024-07-02 | 0 |
| 1005 | 003 | 2024-07-01 15:03:23 | 2024-07-01 | 1 |
----------- ---------- ---------------------- -------------- -------------
2.统计用户首单总单数和即时单数
执行SQL
代码语言：javascript复制select
count(case when is_instant = 1 then order_id end) as instant_cnt,
count(order_id) as total_cnt
from (select order_id,
user_id,
order_time,
desire_date,
if(to_date(order_time) = to_date(desire_date), 1, 0) as is_instant,
row_number() over (partition by user_id order by order_time asc ) as rn
from t_user_order) t
where rn = 1
查询结果
代码语言：javascript复制 -------------- ------------
| instant_cnt | total_cnt |
-------------- ------------
| 2 | 3 |
-------------- ------------
3.计算即时单比例
统计即时单单量除以总单量,得到用户首单即时单比例
代码语言：javascript复制select
round(count(case when is_instant = 1 then order_id end)/count(order_id),2) as instant_per
from (select order_id,
user_id,
order_time,
desire_date,
if(to_date(order_time) = to_date(desire_date), 1, 0) as is_instant,
row_number() over (partition by user_id order by order_time asc ) as rn
from t_user_order) t
where rn = 1
查询结果
代码语言：javascript复制 --------------
| instant_per |
--------------
| 0.67 |
--------------
四、建表语句和数据插入
代码语言：javascript复制--建表语句
CREATE TABLE t_user_order
(
order_id string COMMENT '订单ID',
user_id string COMMENT '用户ID',
order_time string comment '下单时间',
desire_date string comment '期望送达日期'
) COMMENT '用户订单记录表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
;
--插入数据
insert into t_user_order
values
('1001','001','2024-07-01 12:01:23','2024-07-01'),
('1002','001','2024-07-01 12:03:23','2024-07-02'),
('1003','002','2024-07-01 13:03:23','2024-07-02'),
('1004','002','2024-07-01 13:07:23','2024-07-01'),
('1005','003','2024-07-01 15:03:23','2024-07-01')
