SQL(面试实战04)

于 2022-06-09 18:08:24 发布
阅读量400 收藏
点赞数
文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_44464850/article/details/125208545
版权

1.店铺901国庆期间的7日动销率和滞销率

商品信息表tb_product_info

idproduct_idshop_idtagint_quantityrelease_time
18001901日用6010002020-01-01 10:00:00
28002901零食1405002020-01-01 10:00:00
38003901零食1605002020-01-01 10:00:00

（product_id-商品ID, shop_id-店铺ID, tag-商品类别标签, in_price-进货价格, quantity-进货数量, release_time-上架时间）

订单总表tb_order_overall

idorder_iduidevent_timetotal_amounttotal_cntstatus
13010041022021-09-30 10:00:0017011
23010051042021-10-01 10:00:0016011
33010031012021-10-02 10:00:0030021
43010021022021-10-03 11:00:0023521

（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

订单明细表tb_order_detail

idorder_idproduct_idpricecnt
130100480021801
230100580021701
33010028001851
430100280031801
530100380021501
630100380031801

（order_id-订单号, product_id-商品ID, price-商品单价, cnt-下单数量）

问题：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

注：

  • 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
  • 滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
  • 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

输出示例：

示例数据的输出结果如下：

dtsale_rateunsale_rate
2021-10-010.3330.667
2021-10-020.6670.333
2021-10-031.0000.000

解释：

10月1日的近7日（9月25日—10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；

10月2日的近7日（9月26日—10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；

10月3日的近7日（9月27日—10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，

滞销率为0.000；

示例1
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '日用', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301004, 102, '2021-09-30 10:00:00', 170, 1, 1),
  (301005, 104, '2021-10-01 10:00:00', 160, 1, 1),
  (301003, 101, '2021-10-02 10:00:00', 300, 2, 1),
  (301002, 102, '2021-10-03 11:00:00', 235, 2, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301004, 8002, 180, 1),
  (301005, 8002, 170, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 150, 1),
  (301003, 8003, 180, 1);
输出

2021-10-01|0.333|0.667
2021-10-02|0.667|0.333
2021-10-03|1.000|0.000

思路
请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序
1.2021年国庆头3天 t_over_day
select 
    distinct date(event_time) dt,
    order_id
from tb_order_overall
where date(event_time) between "2021-10-01" and "2021-10-03"

2.获取所有商品 t_order 
select 
    distinct date(event_time) order_day,
    product_id
from tb_order_overall
join tb_order_detail
using(order_id)

3.2021年国庆头3天的7日商品	t_over_day 左连接 t_order
t_over_day left join t_order on datediff(dt,order_day) between 0 and 6 

4.左连接商品信息
LEFT JOIN tb_product_info USING(product_id)

5.按日期分组
GROUP BY dt

6.统计每天店铺901近7天有销量的商品数：
dt,count(distinct if(shop_id !=901,null ,product_id)) sale_pid_cnt

7.获取901店家上架商品
select date(release_time) release_time,product_id as all_product
from tb_product_info
where shop_id=901

8.左连接两个表，得到每天已上架在售的商品：
t_901_order LEFT JOIN tb_product_info ON on dt>=release_time 

9.按日期分组
GROUP BY dt

10.店铺901在2021年国庆头3天的7日动销率和滞销率
dt,
round(sale_pid_cnt/count(all_product),3) sale_rate,
1-round(sale_pid_cnt/count(all_product),3) unsale_rate
题解
-- 店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序

select 
	dt,
	round(sale_pid_cnt/count(all_product),3) sale_rate,
	1-round(sale_pid_cnt/count(all_product),3) unsale_rate
from 
(
		-- 店铺901在2021年国庆头3天的7日
		select 
			dt,
    	count(distinct if(shop_id !=901,null,product_id)) sale_pid_cnt
		from 
		(
			select distinct date(event_time) dt,order_id
			from tb_order_overall
			where date(event_time) between "2021-10-01" and "2021-10-03"
		)t_over_day
		left join
		(
			select distinct date(event_time) order_day,product_id
			from tb_order_overall
			join tb_order_detail
			using(order_id)
		)t_order on datediff(dt,order_day) between 0 and 6 
		LEFT JOIN tb_product_info USING(product_id)
		group by dt
)t_901_order
left join 
(
		select date(release_time) release_time,product_id as all_product
		from tb_product_info
		where shop_id=901
)tb_product_info
on dt>=release_time 
group by dt
order by dt

2. 2021年国庆在北京接单3次及以上的司机统计信息

用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1101北京2021-10-01 07:00:002021-10-01 07:02:00NULL
2102北京2021-10-01 09:00:302021-10-01 09:01:009001
3101北京2021-10-01 08:28:102021-10-01 08:30:009002
4103北京2021-10-02 07:59:002021-10-02 08:01:009003
5104北京2021-10-03 07:59:202021-10-03 08:01:009004
6105北京2021-10-01 08:00:002021-10-01 08:02:109005
7106北京2021-10-01 17:58:002021-10-01 18:01:009006
8107北京2021-10-02 11:00:002021-10-02 11:01:009007
9108北京2021-10-02 21:00:002021-10-02 21:01:009008

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

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190021012012021-10-01 08:30:00NULL2021-10-01 08:31:00NULLNULLNULL
290011022022021-10-01 09:01:002021-10-01 09:06:002021-10-01 09:31:001041.55
390031032022021-10-02 08:01:002021-10-02 08:15:002021-10-02 08:31:001141.54
490041042022021-10-03 08:01:002021-10-03 08:13:002021-10-03 08:31:007.5224
590051052032021-10-01 08:02:102021-10-01 08:18:002021-10-01 08:31:0015445
690061062032021-10-01 18:01:002021-10-01 18:09:002021-10-01 18:31:008255
790071072032021-10-02 11:01:002021-10-02 11:07:002021-10-02 11:31:009.9305
890081082032021-10-02 21:01:002021-10-02 21:10:002021-10-02 21:31:0013.2384

（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星评价后填充。

问题：请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。

输出示例：

示例数据的输出结果如下

cityavg_order_numavg_income
北京3.500121.000

解释：

在2021年国庆期间北京市的订单中，202共接了3单，兼职收入105；203接了4单，兼职收入137；201共接了1单，但取消了； 接单至少3次的司机有202和203，他两人全部总共接单数为7，总收入为242。因此平均接单数为3.500，平均收入为121.000；

示例1
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    city VARCHAR(10) NOT NULL COMMENT '城市',
    event_time datetime COMMENT '打车时间',
    end_time datetime COMMENT '打车结束时间',
    order_id INT COMMENT '订单号'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_get_car_order (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    driver_id INT NOT NULL COMMENT '司机ID',
    order_time datetime COMMENT '接单时间',
    start_time datetime COMMENT '开始计费的上车时间',
    finish_time datetime COMMENT '订单结束时间',
    mileage DOUBLE COMMENT '行驶里程数',
    fare DOUBLE COMMENT '费用',
    grade TINYINT COMMENT '评分'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES
 (101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null),
 (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9001),
 (101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002),
 (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003),
 (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004),
 (105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005),
 (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006),
 (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007),
 (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008) ;

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
 (9002, 101, 201, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null),
 (9001, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5),
 (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4),
 (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),
 (9005, 105, 203, '2021-10-01 08:02:10', '2021-10-01 08:18:00', '2021-10-01 08:31:00', 15.0, 44, 5),
 (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25, 5),
 (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),
 (9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4);
输出

北京|3.500|121.000

思路
2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入，结果保留3位小数
1.关联接单表和打车记录表
tb_get_car_order left join tb_get_car_record using(order_id)
2.筛选北京国庆期间的记录：
where date(order_time) between "2021-10-01" and  "2021-10-07" and city="北京"
3.按司机ID分组：
GROUP BY driver_id
4.统计每个司机的接单量和接单收入
city,count(driver_id)order_num, sum(fare) income
5.接单至少3次的司机
having count(driver_id)>2
6.平均接单量和平均收入
select city,
round(avg(order_num),3) avg_order_num,
round(avg(income),3)avg_income
题解
select city,round(avg(order_num),3) avg_order_num,round(avg(income),3)avg_income
from 
(
	select city,count(driver_id)order_num, sum(fare) income
	from tb_get_car_order
	left join tb_get_car_record
	using(order_id)
	where date(order_time) between "2021-10-01" and  "2021-10-07"  
	and city="北京"
	group by driver_id
	having count(driver_id)>2
)t
group by city

3. 有取消订单记录的司机平均评分

现有用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1101北京2021-10-01 07:00:002021-10-01 07:02:00NULL
2102北京2021-10-01 09:00:302021-10-01 09:01:009001
3101北京2021-10-01 08:28:102021-10-01 08:30:009002
4103北京2021-10-02 07:59:002021-10-02 08:01:009003
5104北京2021-10-03 07:59:202021-10-03 08:01:009004
6105北京2021-10-01 08:00:002021-10-01 08:02:109005
7106北京2021-10-01 17:58:002021-10-01 18:01:009006
8107北京2021-10-02 11:00:002021-10-02 11:01:009007
9108北京2021-10-02 21:00:002021-10-02 21:01:009008
10109北京2021-10-08 18:00:002021-10-08 18:01:009009

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

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190021012022021-10-01 08:30:00null2021-10-01 08:31:00nullnullnull
290011022022021-10-01 09:01:002021-10-01 09:06:002021-10-01 09:31:0010.041.55
390031032022021-10-02 08:01:002021-10-02 08:15:002021-10-02 08:31:0011.041.54
490041042022021-10-03 08:01:002021-10-03 08:13:002021-10-03 08:31:007.5224
590051052032021-10-01 08:02:10null2021-10-01 08:31:00nullnullnull
690061062032021-10-01 18:01:002021-10-01 18:09:002021-10-01 18:31:008.025.55
790071072032021-10-02 11:01:002021-10-02 11:07:002021-10-02 11:31:009.9305
890081082032021-10-02 21:01:002021-10-02 21:10:002021-10-02 21:31:0013.2384
990091092032021-10-08 18:01:002021-10-08 18:11:502021-10-08 18:51:0013405

（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星评价后填充。

问题：请找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。先按driver_id升序输出，再输出总体情况。

输出示例:

driver_idavg_grade
2024.3
2034.8
总体4.6

解释：

2021年国庆有未完成订单的司机有202和203；202的所有订单评分有：5、4、4，平均分为4.3；203的所有订单评分有：5、5、4、5，平均评分为4.8；总体平均评分为(5+4+4+5+5+4+5)/7=4.6

思路
1.找出2021年10月有取消订单的司机：
WHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare)
2.筛选他们的已完成订单的评分：
WHERE driver_id in (...) AND NOT ISNULL(grade)
3.按司机分组：
GROUP BY driver_id
4.追加汇总信息：
WITH ROLLUP
5.输出每个司机的平均评分：
司机ID或总体：IFNULL(driver_id, "总体") as driver_id
平均评分：AVG(grade) as avg_grade
保留1位小数：ROUND(x, 1)
题解
方法一：	
	-- 算他们每人全部已完成的有评分订单的平均评分及总体平均评分。
	-- 保留1位小数,driver_id升序,输出总体情况。
	select ifnull(driver_id,"总体") driver_id ,round(avg(grade),1) avg_grade
	from tb_get_car_order
	where driver_id in
	(
	-- 请找到2021年10月有过取消订单记录的司机
		select driver_id
		from tb_get_car_order
		where  date_format(order_time,"%Y-%m")="2021-10" and isnull(grade)
	)
	and not isnull(grade)
	group by driver_id
	with rollup
	order by driver_id
	

写法二：
    SELECT IFNULL(driver_id, "总体") as driver_id,
        ROUND(AVG(grade), 1) as avg_grade
    FROM tb_get_car_order
    WHERE driver_id in (
        SELECT driver_id
        FROM tb_get_car_order
        WHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare)
    ) AND NOT ISNULL(grade)
    GROUP BY driver_id
    WITH ROLLUP;
    order by driver_id

4. 每个城市中评分最高的司机信息

用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1101北京2021-10-01 07:00:002021-10-01 07:02:00NULL
2102北京2021-10-01 09:00:302021-10-01 09:01:009001
3101北京2021-10-01 08:28:102021-10-01 08:30:009002
4103北京2021-10-02 07:59:002021-10-02 08:01:009003
5104北京2021-10-03 07:59:202021-10-03 08:01:009004
6105北京2021-10-01 08:00:002021-10-01 08:02:109005
7106北京2021-10-01 17:58:002021-10-01 18:01:009006
8107北京2021-10-02 11:00:002021-10-02 11:01:009007
9108北京2021-10-02 21:00:002021-10-02 21:01:009008
10109北京2021-10-08 18:00:002021-10-08 18:01:009009

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

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190021012022021-10-01 08:30:00NULL2021-10-01 08:31:00NULLNULLNULL
290011022022021-10-01 09:01:002021-10-01 09:06:002021-10-01 09:31:001041.55
390031032022021-10-02 08:01:002021-10-02 08:15:002021-10-02 08:31:001141.54
490041042022021-10-03 08:01:002021-10-03 08:13:002021-10-03 08:31:007.5224
590051052032021-10-01 08:02:10NULL2021-10-01 08:31:00NULLNULLNULL
690061062032021-10-01 18:01:002021-10-01 18:09:002021-10-01 18:31:00825.55
790071072032021-10-02 11:01:002021-10-02 11:07:002021-10-02 11:31:009.9305
890081082032021-10-02 21:01:002021-10-02 21:10:002021-10-02 21:31:0013.2384
990091092032021-10-08 18:01:002021-10-08 18:11:502021-10-08 18:51:0013405

（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星评价后填充。

问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。

注：有多个司机评分并列最高时，都输出。

平均评分和日均接单量保留1位小数，

日均行驶里程数保留3位小数，按日均接单数升序排序。

2285068

示例数据的输出结果如下

citydriver_idavg_gradeavg_order_numavg_mileage
北京2034.81.714.700

解释：

示例数据中，在北京市，共有2个司机接单，202的平均评分为4.3，203的平均评分为4.8，因此北京的最高评分的司机为203；203的共在3天里接单过，一共接单5次（包含1次接单后未完成），因此日均接单数为1.7；总行驶里程数为44.1，因此日均行驶里程数为14.700

示例1
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    city VARCHAR(10) NOT NULL COMMENT '城市',
    event_time datetime COMMENT '打车时间',
    end_time datetime COMMENT '打车结束时间',
    order_id INT COMMENT '订单号'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_get_car_order (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    driver_id INT NOT NULL COMMENT '司机ID',
    order_time datetime COMMENT '接单时间',
    start_time datetime COMMENT '开始计费的上车时间',
    finish_time datetime COMMENT '订单结束时间',
    mileage FLOAT COMMENT '行驶里程数',
    fare FLOAT COMMENT '费用',
    grade TINYINT COMMENT '评分'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES
 (101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null),
 (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9001),
 (101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002),
 (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003),
 (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004),
 (105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005),
 (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006),
 (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007),
 (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008),
 (109, '北京', '2021-10-08 18:00:00', '2021-10-08 18:01:00', 9009);

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
 (9002, 101, 202, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null),
 (9001, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5),
 (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4),
 (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),
 (9005, 105, 203, '2021-10-01 08:02:10', null, '2021-10-01 08:31:00', null, null, null),
 (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 5),
 (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),
 (9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4),
 (9009, 109, 203, '2021-10-08 18:01:00', '2021-10-08 18:11:50', '2021-10-08 18:51:00', 13, 40, 5);
思路
1.计算每个司机的评分、日均接单量、日均里程和城市内评分排名（生成子表t_driver_rk）：
关联打车记录表和订单表：tb_get_car_record JOIN tb_get_car_order USING(order_id)
2.按司机和城市分组：
GROUP BY driver_id, city
3.计算各指标：
    平均评分：AVG(grade) as avg_grade
    工作天数：COUNT(DISTINCT DATE(order_time)) as work_days
    接单量：COUNT(order_time) as order_num
    总行驶里程数：SUM(mileage) as toal_mileage
4.计算日均指标和排名：
日均订单量：order_num / work_days as avg_order_num
日均里程数：toal_mileage / work_days as avg_mileage
计算城市内的评分排名，允许并列第一：RANK() over(PARTITION BY cityORDER BY round(avg(grade),1) DESC) as rk
保留小数位数：ROUND(x, 1)
5.筛选每个城市的第一名：WHERE rk = 1
题解
select 
	city,
	driver_id,
	avg_grade,
	avg_order_num,
	avg_mileage
from 
(
    select 
        city,
        driver_id,
        round(avg(grade),1)avg_grade,
        round(count(driver_id)/count(distinct date(order_time)),1) avg_order_num,
        round(sum(mileage)/count(distinct date(order_time)),3) avg_mileage,
        rank()over(PARTITION BY city ORDER BY round(avg(grade),1) DESC)as rk
    from tb_get_car_record
    join tb_get_car_order
    using(order_id)
    group by city,driver_id
)t_driver_rk
where rk=1
order by avg_order_num

5. 国庆期间近7日日均取消订单量

现有用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1101北京2021-09-25 08:28:102021-09-25 08:30:009011
2102北京2021-09-25 09:00:302021-09-25 09:01:009012
3103北京2021-09-26 07:59:002021-09-26 08:01:009013
4104北京2021-09-26 07:59:002021-09-26 08:01:009023
5104北京2021-09-27 07:59:202021-09-27 08:01:009014
6105北京2021-09-28 08:00:002021-09-28 08:02:109015
7106北京2021-09-29 17:58:002021-09-29 18:01:009016
8107北京2021-09-30 11:00:002021-09-30 11:01:009017
9108北京2021-09-30 21:00:002021-09-30 21:01:009018
10102北京2021-10-01 09:00:302021-10-01 09:01:009002
11106北京2021-10-01 17:58:002021-10-01 18:01:009006
12101北京2021-10-02 08:28:102021-10-02 08:30:009001
13107北京2021-10-02 11:00:002021-10-02 11:01:009007
14108北京2021-10-02 21:00:002021-10-02 21:01:009008
15103北京2021-10-02 07:59:002021-10-02 08:01:009003
16104北京2021-10-03 07:59:202021-10-03 08:01:009004
17109北京2021-10-03 18:00:002021-10-03 18:01:009009

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

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190111012112021-09-25 08:30:002021-09-25 08:31:002021-09-25 08:54:0010355
290121022112021-09-25 09:01:002021-09-25 09:01:502021-09-25 09:28:0011325
390131032122021-09-26 08:01:002021-09-26 08:03:002021-09-26 08:27:0012314
490231042132021-09-26 08:01:00NULL2021-09-26 08:27:00NULLNULLNULL
590141042122021-09-27 08:01:002021-09-27 08:04:002021-09-27 08:21:0011315
690151052122021-09-28 08:02:102021-09-28 08:04:102021-09-28 08:25:1012314
790161062132021-09-29 18:01:002021-09-2918:02:102021-09-29 18:23:0011394
890171072132021-09-3011:01:002021-09-30 11:01:402021-09-30 11:31:0011385
990181082142021-09-30 21:01:002021-09-30 21:02:502021-09-30 21:21:0014385
1090021022022021-10-01 09:01:002021-10-01 0 9:06:002021-10-01 09:31:001041.55
1190061062032021-10-0118:01:002021-10-01 18:09:002021-10-01 18:31:00825.54
1290011012022021-10-02 08:30:00NULL2021-10-02 08:31:00NULLNULLNULL
1390071072032021-10-02 11:01:002021-10-0211:07:002021-10-02 11:31:009.9305
1490081082042021-10-02 21:01:002021-10-02 21:10:002021-10-02 21:31:0013.2384
1590031032022021-10-02 08:01:002021-10-02 08:15:002021-10-02 08:31:001141.54
1690041042022021-10-03 08:01:002021-10-03 08:13:002021-10-03 08:31:007.5224
1790091092042021-10-0318:01:00NULL2021-10-03 18:51:00NULLNULLNULL

（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位小数。

输出示例：

示例输出如下

dtfinish_num_7dcancel_num_7d
2021-10-011.430.14
2021-10-021.570.29
2021-10-031.570.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
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    city VARCHAR(10) NOT NULL COMMENT '城市',
    event_time datetime COMMENT '打车时间',
    end_time datetime COMMENT '打车结束时间',
    order_id INT COMMENT '订单号'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_get_car_order (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    driver_id INT NOT NULL COMMENT '司机ID',
    order_time datetime COMMENT '接单时间',
    start_time datetime COMMENT '开始计费的上车时间',
    finish_time datetime COMMENT '订单结束时间',
    mileage FLOAT COMMENT '行驶里程数',
    fare FLOAT COMMENT '费用',
    grade TINYINT COMMENT '评分'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES
 (101, '北京', '2021-09-25 08:28:10', '2021-09-25 08:30:00', 9011),
 (102, '北京', '2021-09-25 09:00:30', '2021-09-25 09:01:00', 9012),
 (103, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9013),
 (104, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9023),
 (104, '北京', '2021-09-27 07:59:20', '2021-09-27 08:01:00', 9014),
 (105, '北京', '2021-09-28 08:00:00', '2021-09-28 08:02:10', 9015),
 (106, '北京', '2021-09-29 17:58:00', '2021-09-29 18:01:00', 9016),
 (107, '北京', '2021-09-30 11:00:00', '2021-09-30 11:01:00', 9017),
 (108, '北京', '2021-09-30 21:00:00', '2021-09-30 21:01:00', 9018),
 (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9002),
 (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006),
 (101, '北京', '2021-10-02 08:28:10', '2021-10-02 08:30:00', 9001),
 (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007),
 (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008),
 (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003),
 (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004),
 (109, '北京', '2021-10-03 18:00:00', '2021-10-03 18:01:00', 9009);

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
 (9011, 101, 211, '2021-09-25 08:30:00', '2021-09-25 08:31:00', '2021-09-25 08:54:00', 10, 35, 5),
 (9012, 102, 211, '2021-09-25 09:01:00', '2021-09-25 09:01:50', '2021-09-25 09:28:00', 11, 32, 5),
 (9013, 103, 212, '2021-09-26 08:01:00', '2021-09-26 08:03:00', '2021-09-26 08:27:00', 12, 31, 4),
 (9023, 104, 213, '2021-09-26 08:01:00', null, '2021-09-26 08:27:00', null, null, null),
 (9014, 104, 212, '2021-09-27 08:01:00', '2021-09-27 08:04:00', '2021-09-27 08:21:00', 11, 31, 5),
 (9015, 105, 212, '2021-09-28 08:02:10', '2021-09-28 08:04:10', '2021-09-28 08:25:10', 12, 31, 4),
 (9016, 106, 213, '2021-09-29 18:01:00', '2021-09-29 18:02:10', '2021-09-29 18:23:00', 11, 39, 4),
 (9017, 107, 213, '2021-09-30 11:01:00', '2021-09-30 11:01:40', '2021-09-30 11:31:00', 11, 38, 5),
 (9018, 108, 214, '2021-09-30 21:01:00', '2021-09-30 21:02:50', '2021-09-30 21:21:00', 14, 38, 5),
 (9002, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5),
 (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 4),
 (9001, 101, 202, '2021-10-02 08:30:00', null, '2021-10-02 08:31:00', null, null, null),
 (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),
 (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),
 (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4),
 (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),
 (9009, 109, 204, '2021-10-03 18:01:00', null, '2021-10-03 18:51:00', null, null, null);
输出

2021-10-01|1.43|0.14
2021-10-02|1.57|0.29
2021-10-03|1.57|0.29

思路
计算每天的近7日订单的日均完成量和日均取消量：
1.计算每天的订单完成量和取消量（生成子表t_finish_cancel_daily）：
2.统计每个订单是否完成（生成子表t_order_status）：
3.筛选必要的时间窗：
WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
4.生成日期：
DATE(order_time) as dt
生成完成状态：
IF(start_time IS NULL, 0, 1) as is_finish
5.按日期分组：
GROUP BY dt
6.计算每天的订单完成量：SUM(is_finish) as finish_num
计算每天的订单取消量：COUNT(1) - SUM(is_finish) as cancel_num
计算近7日日均（滑动窗口平均）：
7日日均完成量：
AVG(finish_num) over(ORDER BY dt ROWS 6 preceding) as finish_num_7d
7日日均取消量：
AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding) as cancel_num_7d
保留2位小数：ROUND(x, 2)
筛选国庆头3天数据：WHERE dt >= '2021-10-01'
题解
方式一：
SELECT dt, finish_num_7d, cancel_num_7d
FROM (
    SELECT dt,
        ROUND(AVG(finish_num) over(ORDER BY dt ROWS 6 preceding), 2) as finish_num_7d,
        ROUND(AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding), 2) as cancel_num_7d
    FROM (
        SELECT dt, SUM(is_finish) as finish_num, COUNT(1) - SUM(is_finish) as cancel_num
        FROM (
            SELECT DATE(order_time) as dt, IF(start_time IS NULL, 0, 1) as is_finish
            FROM tb_get_car_order
            WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03'
        ) as t_order_status
        GROUP BY dt
    ) as t_finish_cancel_daily
)as t_finish_cancel_7d
WHERE dt >= '2021-10-01';

方式二：
select dt,finish_num_7d,cancel_num_7d
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 isnull(start_time)  then 0 else 1 end)finish_num,
		sum(case when isnull(start_time)  then 1 else 0 end)cancel_num
		from tb_get_car_order
		group by date(order_time)
	)t
)t1
where dt between '2021-10-01' and '2021-10-03'
林园857
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值