近七日日均订单

题目：现有用户打车记录表tb_get_car_record

id	uid	city	event_time	end_time	order_id
1	101	北京	2021-09-25 08:28:10	2021-09-25 08:30:00	9011
2	102	北京	2021-09-25 09:00:30	2021-09-25 09:01:00	9012
3	103	北京	2021-09-26 07:59:00	2021-09-26 08:01:00	9013
4	104	北京	2021-09-26 07:59:00	2021-09-26 08:01:00	9023
5	104	北京	2021-09-27 07:59:20	2021-09-27 08:01:00	9014
6	105	北京	2021-09-28 08:00:00	2021-09-28 08:02:10	9015
7	106	北京	2021-09-29 17:58:00	2021-09-29 18:01:00	9016
8	107	北京	2021-09-30 11:00:00	2021-09-30 11:01:00	9017
9	108	北京	2021-09-30 21:00:00	2021-09-30 21:01:00	9018
10	102	北京	2021-10-01 09:00:30	2021-10-01 09:01:00	9002
11	106	北京	2021-10-01 17:58:00	2021-10-01 18:01:00	9006
12	101	北京	2021-10-02 08:28:10	2021-10-02 08:30:00	9001
13	107	北京	2021-10-02 11:00:00	2021-10-02 11:01:00	9007
14	108	北京	2021-10-02 21:00:00	2021-10-02 21:01:00	9008
15	103	北京	2021-10-02 07:59:00	2021-10-02 08:01:00	9003
16	104	北京	2021-10-03 07:59:20	2021-10-03 08:01:00	9004
17	109	北京	2021-10-03 18:00:00	2021-10-03 18:01:00	9009

（uid-用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号）

打车订单表tb_get_car_order

id	order_id	uid	driver_id	order_time	start_time	finish_time	mileage	fare	grade
1	9011	101	211	2021-09-25 08:30:00	2021-09-25 08:31:00	2021-09-25 08:54:00	10	35	5
2	9012	102	211	2021-09-25 09:01:00	2021-09-25 09:01:50	2021-09-25 09:28:00	11	32	5
3	9013	103	212	2021-09-26 08:01:00	2021-09-26 08:03:00	2021-09-26 08:27:00	12	31	4
4	9023	104	213	2021-09-26 08:01:00	NULL	2021-09-26 08:27:00	NULL	NULL	NULL
5	9014	104	212	2021-09-27 08:01:00	2021-09-27 08:04:00	2021-09-27 08:21:00	11	31	5
6	9015	105	212	2021-09-28 08:02:10	2021-09-28 08:04:10	2021-09-28 08:25:10	12	31	4
7	9016	106	213	2021-09-29 18:01:00	2021-09-2 918:02:10	2021-09-29 18:23:00	11	39	4
8	9017	107	213	2021-09-3011:01:00	2021-09-30 11:01:40	2021-09-30 11:31:00	11	38	5
9	9018	108	214	2021-09-30 21:01:00	2021-09-30 21:02:50	2021-09-30 21:21:00	14	38	5
10	9002	102	202	2021-10-01 09:01:00	2021-10-01 0 9:06:00	2021-10-01 09:31:00	10	41.5	5
11	9006	106	203	2021-10-0118:01:00	2021-10-01 18:09:00	2021-10-01 18:31:00	8	25.5	4
12	9001	101	202	2021-10-02 08:30:00	NULL	2021-10-02 08:31:00	NULL	NULL	NULL
13	9007	107	203	2021-10-02 11:01:00	2021-10-02 11:07:00	2021-10-02 11:31:00	9.9	30	5
14	9008	108	204	2021-10-02 21:01:00	2021-10-02 21:10:00	2021-10-02 21:31:00	13.2	38	4
15	9003	103	202	2021-10-02 08:01:00	2021-10-02 08:15:00	2021-10-02 08:31:00	11	41.5	4
16	9004	104	202	2021-10-03 08:01:00	2021-10-03 08:13:00	2021-10-03 08:31:00	7.5	22	4
17	9009	109	204	2021-10-0318:01:00	NULL	2021-10-03 18:51:00	NULL	NULL	NULL

（order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）

场景逻辑说明：

用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；
当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。
若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。
当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。
当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。

问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。

输出示例：

dt	finish_num_7d	cancel_num_7d
2021-10-01	1.43	0.14
2021-10-02	1.57	0.29
2021-10-03	1.57	0.29

解释：

2021年9月25到10月3日每天的订单完成量为：2、1、1、1、1、2、2、3、1；每天的订单取消量为：0、1、0、0、0、0、0、1、1；

因此10.1到10.3期间的近7日订单完成量分别为10、11、11，因此日均订单完成量为：1.43、1.57、1.57；

近7日订单取消量分别为1、2、2，因此日均订单取消量为0.14、0.29、0.29；

思路：

此题共包含两张表：

表1：tb_get_car_record

表2：tb_get_car_order

要解决的问题：

请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。

解题思路：

统计订单完成量和订单取消量
利用窗口函数，统计每天近7日的订单完成量和订单取消量
计算每天近7日的订单完成量和订单取消量，取时间为国庆头3天
按日期升序排序，结果取2位小数

知识点：

窗口函数各个参数的含义：

partition by ：分组子句，表示分析函数的计算范围，不同的组互不相干；
ORDER BY：排序子句，表示分组后，组内的排序方式；
ROWS/RANGE：窗口子句，是在分组后，组内的子分组（也称窗口），此时分析函数的计算范围是子分组。

区分rows和range的区别

rows:定义当前行的固定前后记录, 以行号为基准进行计算，计算结果是一个自增长的汇总
range:选项包含窗口里的所有行, 以order by为基准进行计算，用可能出现重复值

注意：当窗口函数order by 没有显示给出行的作用域范围时，默认为 RANGE between UNBOUNDED PRECEDING AND CURRENT ROW

#方法一

select dt,

ROUND(finish_num/7,2) finish_num_7d,

ROUND(cancel_num/7,2) cancel_num_7d

from(select dt,

sum(finish_num)over(order by dt rows 6 preceding) as finish_num,

sum(cancel_num)over(order by dt rows 6 preceding) as cancel_num

from(select date(order_time) dt,

sum(if (start_time is not null,1,0)) as finish_num,

sum(if (start_time is null,1,0)) as cancel_num

from tb_get_car_order

group by date(order_time)

) t

) a

where dt between '2021-10-01' and '2021-10-03'

#方法二

select *

from(select dt,

round(sum(finish_num)over(order by dt rows 6 preceding)/7,2) as finish_num_7d,

round(sum(cancel_num)over(order by dt rows 6 preceding)/7,2) as cancel_num_7d

from(select date(order_time) dt,

sum(case when start_time is not null then 1 else 0 end) as finish_num,

sum(case when start_time is null then 1 else 0 end) as cancel_num

from tb_get_car_order

group by date(order_time)

order by dt) t ) a

where dt between '2021-10-01' and '2021-10-03'

#方法三：不通过窗口函数

select dt,round(finish_num/7,2) finish_num_7d,

round(cancel_num/7,2) cancel_num_7d

from

(

select distinct date(order_time) dt,

(select sum(if(start_time is null,0,1))

from tb_get_car_order

where timestampdiff(day,date(order_time),dt) between 0 and 6

) finish_num,

(select sum(if(start_time is null,1,0))

from tb_get_car_order

where timestampdiff(day,date(order_time),dt) between 0 and 6

) cancel_num

from

tb_get_car_order t1

where date(order_time) between '2021-10-01' and '2021-10-03'

) t2

方法四：

问题拆解

统计出每天的订单完成量、订单取消量；生成子表 tb

对时间进行 GROUP BY
订单完成量：COUNT(start_time)
订单取消量：SUM(IF(start_time IS NULL,1,0))

求近7天平均订单完成量、订单取消量；生成子表tb1

近7天日均订单完成量：AVG(order_done) over(ORDER BY dt ROWS 6 preceding)
近7天日均订单取消量：AVG(order_cancel) over(ORDER BY dt ROWS 6 preceding)

国庆头三天近7天日均订单完成量、订单取消量

筛选出国庆前3天：WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
结果均保留2位小数：ROUND(X,2)
按日期升序排序:ORDER BY dt

SELECT

dt,

ROUND(finish_num_7d,2) finish_num_7d,

ROUND(cancel_num_7d,2) cancel_num_7d

FROM (

SELECT

dt,

AVG(order_done) over(ORDER BY dt ROWS 6 preceding) finish_num_7d,

AVG(order_cancel) over(ORDER BY dt ROWS 6 preceding) cancel_num_7d

FROM (

SELECT

DATE(order_time) dt,

COUNT(start_time) order_done, -- 订单完成量

SUM(IF(start_time IS NULL,1,0)) order_cancel -- 订单取消量

FROM tb_get_car_order gco

GROUP BY DATE(order_time)

) tb

) tb1

WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'

ORDER BY dt

方法5：

select

dt,

round(count(start_time)/count(distinct date(order_time)),2) as finish_num_7d,

round(sum(case when start_time is null then 1 end)/count(distinct date(order_time)),2)as cancel_num_7d

from

(select '2021-10-01' as dt

union

select '2021-10-02' as dt

union

select '2021-10-03' as dt) t1

left join

tb_get_car_order t2

DATEDIFF(dt,date(order_time)) between 0 and 6 group by dt order by dt

posted @ 2022-08-05 21:26 萧六弟阅读(181) 评论(0) 收藏举报

刷新页面返回顶部

戴小帅的学习笔记

近七日日均订单

方法四：

问题拆解

公告