目录
0 题目需求
订单配送中,顾客有时候希望自定配送日期,如果期望配送日期和下单日期相同,称为即时订单,如果配送日期和下单日期不同,称为计划订单。
每个用户最早下单的时间被称为首单。
请求出每个用户的首单为即时订单的比例,保留两位小数,以小数形式显示。
表结构
配送信息表
字段名 | 字段类型 | 字段含义 |
String | 配送订单id | |
String | 用户id | |
date | 下单日期 | |
date | 顾客希望的配送日期 |
1 数据准备
(1)建表
DROP TABLE IF EXISTS delivery_info;
CREATE TABLE delivery_info (
`delivery_id` string ,
`user_id` string ,
`order_date` string ,
`custom_date` string
) COMMENT '邮寄信息表';
(2)插入数据
INSERT INTO delivery_info
(
delivery_id,
user_id,
order_date,
custom_date
)
VALUES ('1','1','2021-08-01','2021-08-02')
,('2','2','2021-08-02','2021-08-02')
,('3','1','2021-08-11','2021-08-12')
,('4','3','2021-08-24','2021-08-24')
,('5','3','2021-08-21','2021-08-22')
,('6','2','2021-08-11','2021-08-13')
,('7','4','2021-08-09','2021-08-09');
1 1 2021-08-01 2021-08-02
2 2 2021-08-02 2021-08-02
3 1 2021-08-11 2021-08-12
4 3 2021-08-24 2021-08-24
5 3 2021-08-21 2021-08-22
6 2 2021-08-11 2021-08-13
7 4 2021-08-09 2021-08-09
Time taken: 0.146 seconds, Fetched: 7 row(s)
2 数据分析
第一步:需求明确
目标:每个用户的首单中即时订单的比例,保留两位小数
(1)首单的定义:每个用户最早下单的时间。min(time)
(2) 即时订单的定义:配送日期和下单日期相同
(3)落脚点:最终求的是订单的比例,且保留两位小数
(4)维度:用户
需求疑问点:这里的比例指的是按条件所生成的订单数占全部订单的比例还是,按条件所生成的订单数占用户订单数的比例,如果是后者的话,题目限定了为首单,那么满足条件的话要么数量为1,要么为0,,1比上用户所有订单数,这样好像分析的意义不大,本题暂且按前者去求。
方法:窗口函数进行辅助计算,根据条件生成条件标签
第二步:求首单日期
select *
,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info
delivery_info.delivery_id delivery_info.user_id delivery_info.order_date delivery_info.custom_date first_order_date
1 1 2021-08-01 2021-08-02 2021-08-01
3 1 2021-08-11 2021-08-12 2021-08-01
2 2 2021-08-02 2021-08-02 2021-08-02
6 2 2021-08-11 2021-08-13 2021-08-02
4 3 2021-08-24 2021-08-24 2021-08-21
5 3 2021-08-21 2021-08-22 2021-08-21
7 4 2021-08-09 2021-08-09 2021-08-09
第三步:求首单中即时订单的标签值,是为‘1’,不是为0
select *
,case when first_order_date = order_date and order_date=custom_date then '1' else '0' end as flg
from
(select *
,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info
) t
t.delivery_id t.user_id t.order_date t.custom_date t.first_order_date flg
1 1 2021-08-01 2021-08-02 2021-08-01 0
3 1 2021-08-11 2021-08-12 2021-08-01 0
2 2 2021-08-02 2021-08-02 2021-08-02 1
6 2 2021-08-11 2021-08-13 2021-08-02 0
4 3 2021-08-24 2021-08-24 2021-08-21 0
5 3 2021-08-21 2021-08-22 2021-08-21 0
7 4 2021-08-09 2021-08-09 2021-08-09 1
第三步:根据分组条件flg求出满足条件的订单数及全部的订单数
select flg
,count(case when flg=1 then delivery_id else null end) over(partition by flg) as flg_cnt
,count(delivery_id) over() as all_cnt
from
(select *
,case when first_order_date = order_date and order_date=custom_date then '1' else '0' end as flg
from
(select *
,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info
) t
) t
flg flg_cnt all_cnt
0 0 7
0 0 7
0 0 7
0 0 7
0 0 7
1 2 7
1 2 7
Time taken: 2.245 seconds, Fetched: 7 row(s)
第四步:求出比例,并去重。最终SQL如下:
select
max(cast(flg_cnt / all_cnt as decimal(18, 2))) as ratio
from
(
select
flg,
count(
case
when flg = 1 then delivery_id
else null
end
) over(partition by flg) as flg_cnt,
count(delivery_id) over() as all_cnt
from
(
select
*,
case
when first_order_date = order_date
and order_date = custom_date then '1'
else '0'
end as flg
from
(
select
*,
min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from
delivery_info
) t
) t
) t
where
flg = 1
group by
flg
OK
ratio
0.29
Time taken: 3.263 seconds, Fetched: 1 row(s)
3 小结
本文分析一种即时订单比例问题分析方法,采用按照条件生成标签的形式求解,当然本题也可以用关联的形式求解,读者可自行尝试