锐客网

  1. 首页 > 睿知 > it技术 > >

mysql|SQL(面试实战05)

SQL数据库mysqldatabase

1. 工作日各时段叫车量、等待接单时间和调度时间 用户打车记录表tb_get_car_record

id uid city event_time end_time order_id
1 107 北京 2021-09-20 11:00:00 2021-09-20 11:00:30 9017
2 108 北京 2021-09-20 21:00:00 2021-09-20 21:00:40 9008
3 108 北京 2021-09-20 18:59:30 2021-09-20 19:01:00 9018
4 102 北京 2021-09-21 08:59:00 2021-09-21 09:01:00 9002
5 106 北京 2021-09-21 17:58:00 2021-09-21 18:01:00 9006
6 103 北京 2021-09-22 07:58:00 2021-09-22 08:01:00 9003
7 104 北京 2021-09-23 07:59:00 2021-09-23 08:01:00 9004
8 103 北京 2021-09-24 19:59:20 2021-09-24 20:01:00 9019
9 101 北京 2021-09-24 08:28:10 2021-09-24 08:30:00 9011
（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 9017 107 213 2021-09-20 11:00:30 2021-09-20 11:02:10 2021-09-20 11:31:00 11 38 5
2 9008 108 204 2021-09-20 21:00:40 2021-09-20 21:03:00 2021-09-20 21:31:00 13.2 38 4
3 9018 108 214 2021-09-20 19:01:00 2021-09-20 19:04:50 2021-09-20 19:21:00 14 38 5
4 9002 102 202 2021-09-21 09:01:00 2021-09-21 09:06:00 2021-09-21 09:31:00 10 41.5 5
5 9006 106 203 2021-09-21 18:01:00 2021-09-21 18:09:00 2021-09-21 18:31:00 8 25.5 4
6 9007 107 203 2021-09-22 11:01:00 2021-09-22 11:07:00 2021-09-22 11:31:00 9.9 30 5
7 9003 103 202 2021-09-22 08:01:00 2021-10-22 08:15:00 2021-10-22 08:31:00 11 41.5 4
8 9004 104 202 2021-09-23 08:01:00 2021-09-23 08:13:00 2021-09-23 08:31:00 7.5 22 4
9 9005 105 202 2021-09-23 10:01:00 2021-09-23 10:13:00 2021-09-23 10:31:00 9 29 5
10 9019 103 202 2021-09-24 20:01:00 2021-09-24 20:11:00 2021-09-24 20:51:00 10 39 4
11 9011 101 211 2021-09-24 08:30:00 2021-09-24 08:31:00 2021-09-24 08:54:00 10 35 5
（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星评价后填充。
问题：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。
注：
输出示例：
示例数据的输出结果如下：
period get_car_num avg_wait_time avg_dispatch_time
工作时间 1 0.5 1.7
休息时间 1 0.7 2.3
晚高峰 3 2.1 7.3
早高峰 4 2.2 8.0
解释：订单9017打车开始于11点整，属于工作时间，等待时间30秒，调度时间为1分40秒，示例数据中工作时间打车订单就一个，平均等待时间0.5分钟，平均调度时间1.7分钟。
示例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 (107, '北京', '2021-09-20 11:00:00', '2021-09-20 11:00:30', 9017), (108, '北京', '2021-09-20 21:00:00', '2021-09-20 21:00:40', 9008), (108, '北京', '2021-09-20 18:59:30', '2021-09-20 19:01:00', 9018), (102, '北京', '2021-09-21 08:59:00', '2021-09-21 09:01:00', 9002), (106, '北京', '2021-09-21 17:58:00', '2021-09-21 18:01:00', 9006), (103, '北京', '2021-09-22 07:58:00', '2021-09-22 08:01:00', 9003), (104, '北京', '2021-09-23 07:59:00', '2021-09-23 08:01:00', 9004), (103, '北京', '2021-09-24 19:59:20', '2021-09-24 20:01:00', 9019), (101, '北京', '2021-09-24 08:28:10', '2021-09-24 08:30:00', 9011); INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9017, 107, 213, '2021-09-20 11:00:30', '2021-09-20 11:02:10', '2021-09-20 11:31:00', 11, 38, 5), (9008, 108, 204, '2021-09-20 21:00:40', '2021-09-20 21:03:00', '2021-09-20 21:31:00', 13.2, 38, 4), (9018, 108, 214, '2021-09-20 19:01:00', '2021-09-20 19:04:50', '2021-09-20 19:21:00', 14, 38, 5), (9002, 102, 202, '2021-09-21 09:01:00', '2021-09-21 09:06:00', '2021-09-21 09:31:00', 10.0, 41.5, 5), (9006, 106, 203, '2021-09-21 18:01:00', '2021-09-21 18:09:00', '2021-09-21 18:31:00', 8.0, 25.5, 4), (9007, 107, 203, '2021-09-22 11:01:00', '2021-09-22 11:07:00', '2021-09-22 11:31:00', 9.9, 30, 5), (9003, 103, 202, '2021-09-22 08:01:00', '2021-09-22 08:15:00', '2021-09-22 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-09-23 08:01:00', '2021-09-23 08:13:00', '2021-09-23 08:31:00', 7.5, 22, 4), (9005, 105, 202, '2021-09-23 10:01:00', '2021-09-23 10:13:00', '2021-09-23 10:31:00', 9, 29, 5), (9019, 103, 202, '2021-09-24 20:01:00', '2021-09-24 20:11:00', '2021-09-24 20:51:00', 10, 39, 4), (9011, 101, 211, '2021-09-24 08:30:00', '2021-09-24 08:31:00', '2021-09-24 08:54:00', 10, 35, 5);

输出：
工作时间|1|0.5|1.7
休息时间|1|0.7|2.3
晚高峰|3|2.1|7.3
早高峰|4|2.2|8.0
思路
统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间 (开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间) 1.用户打车记录表tb_get_car_record 连接 打车订单表tb_get_car_order from tb_get_car_order join tb_get_car_record using(order_id) 2.获取出车时间分类，订单id，等待接单时间、调度时间， select case when date_format(event_time,"%H:%i:%s") between "07:00:00" and "08:59:59"then "早高峰" when date_format(event_time,"%H:%i:%s") between "09:00:00" and "16:59:59"then "工作时间" when date_format(event_time,"%H:%i:%s") between "17:00:00" and "19:59:59"then "晚高峰" else "休息时间" end period, order_id, timestampdiff(second,event_time,end_time) wait_time, timestampdiff(second,order_time,start_time) dispatch_time 3.按照不同时段分组 group by period 4.获取 不同时段，叫车量，平均等待接单时间和平均调度时间 select period, count(order_id) get_car_num, round(avg(wait_time)/60,1) avg_wait_time, round(avg(dispatch_time)/60,1) avg_dispatch_time

题解
select period, count(order_id) get_car_num, round(avg(wait_time)/60,1) avg_wait_time, round(avg(dispatch_time)/60,1) avg_dispatch_time from ( select case when date_format(event_time,"%H:%i:%s") between "07:00:00" and "08:59:59"then "早高峰" when date_format(event_time,"%H:%i:%s") between "09:00:00" and "16:59:59"then "工作时间" when date_format(event_time,"%H:%i:%s") between "17:00:00" and "19:59:59"then "晚高峰" else "休息时间" end period, order_id, timestampdiff(second,event_time,end_time) wait_time, timestampdiff(second,order_time,start_time) dispatch_time from tb_get_car_order join tb_get_car_record using(order_id) where dayofweek(order_time) between 2 and 6 )t group by period order by get_car_num

2. 各城市最大同时等车人数 用户打车记录表tb_get_car_record
id uid city event_time end_time order_id
1 108 北京 2021-10-20 08:00:00 2021-10-20 08:00:40 9008
2 118 北京 2021-10-20 08:00:10 2021-10-20 08:00:45 9018
3 102 北京 2021-10-20 08:00:30 2021-10-20 08:00:50 9002
4 106 北京 2021-10-20 08:05:41 2021-10-20 08:06:00 9006
5 103 北京 2021-10-20 08:05:50 2021-10-20 08:07:10 9003
6 104 北京 2021-10-20 08:01:01 2021-10-20 08:01:20 9004
7 105 北京 2021-10-20 08:01:15 2021-10-20 08:01:30 9019
8 101 北京 2021-10-20 08:28:10 2021-10-20 08:30:00 9011
（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 9008 108 204 2021-10-20 08:00:40 2021-10-20 08:03:00 2021-10-20 08:31:00 13.2 38 4
2 9018 108 214 2021-10-20 08:00:45 2021-10-20 08:04:50 2021-10-20 08:21:00 14 38 5
3 9002 102 202 2021-10-20 08:00:50 2021-10-20 08:06:00 2021-10-20 08:31:00 10 41.5 5
4 9006 106 206 2021-10-20 08:06:00 2021-10-20 08:09:00 2021-10-20 08:31:00 8 25.5 4
5 9003 103 203 2021-10-20 08:07:10 2021-10-20 08:15:00 2021-10-20 08:31:00 11 41.5 4
6 9004 104 204 2021-10-20 08:01:20 2021-10-20 08:13:00 2021-10-20 08:31:00 7.5 22 4
7 9019 105 205 2021-10-20 08:01:30 2021-10-20 08:11:00 2021-10-20 08:51:00 10 39 4
8 9011 101 211 2021-10-20 08:30:00 2021-10-20 08:31:00 2021-10-20 08:54:00 10 35 5
【mysql|SQL(面试实战05)】（order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）
场景逻辑说明：
  • 用户提交打车请求后，在用户打车记录表生成一条打车记录，订单号-order_id设为null；
  • 当有司机接单时，在打车订单表生成一条订单，填充接单时间-order_time及其左边的字段，上车时间及其右边的字段全部为null，并把订单号和接单时间（打车结束时间）写入打车记录表；若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间。
  • 若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的订单完成时间-finish_time填充为取消时间，其余字段设为null。
  • 当司机接上乘客时，填充打车订单表中该订单的上车时间start_time。
  • 当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。
问题：请统计各个城市在2021年10月期间，单日中最大的同时等车人数。
注: 等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态。
如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。
结果按各城市最大等车人数升序排序，相同时按城市升序排序。
输出示例：
示例结果如下
city max_wait_uv
北京 5
解释：由打车订单表可以得知北京2021年10月20日有8条打车记录，108号乘客从08:00:00等到08:03:00，118号乘客从08:00:10等到08:04:50…,由此得知08:02:00秒时刻，共有5人在等车。
示例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 (108, '北京', '2021-10-20 08:00:00', '2021-10-20 08:00:40', 9008), (108, '北京', '2021-10-20 08:00:10', '2021-10-20 08:00:45', 9018), (102, '北京', '2021-10-20 08:00:30', '2021-10-20 08:00:50', 9002), (106, '北京', '2021-10-20 08:05:41', '2021-10-20 08:06:00', 9006), (103, '北京', '2021-10-20 08:05:50', '2021-10-20 08:07:10', 9003), (104, '北京', '2021-10-20 08:01:01', '2021-10-20 08:01:20', 9004), (103, '北京', '2021-10-20 08:01:15', '2021-10-20 08:01:30', 9019), (101, '北京', '2021-10-20 08:28:10', '2021-10-20 08:30:00', 9011); INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9008, 108, 204, '2021-10-20 08:00:40', '2021-10-20 08:03:00', '2021-10-20 08:31:00', 13.2, 38, 4), (9018, 108, 214, '2021-10-20 08:00:45', '2021-10-20 08:04:50', '2021-10-20 08:21:00', 14, 38, 5), (9002, 102, 202, '2021-10-20 08:00:50', '2021-10-20 08:06:00', '2021-10-20 08:31:00', 10.0, 41.5, 5), (9006, 106, 203, '2021-10-20 08:06:00', '2021-10-20 08:09:00', '2021-10-20 08:31:00', 8.0, 25.5, 4), (9003, 103, 202, '2021-10-20 08:07:10', '2021-10-20 08:15:00', '2021-10-20 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-10-20 08:01:20', '2021-10-20 08:13:00', '2021-10-20 08:31:00', 7.5, 22, 4), (9019, 103, 202, '2021-10-20 08:01:30', '2021-10-20 08:11:00', '2021-10-20 08:51:00', 10, 39, 4), (9011, 101, 211, '2021-10-20 08:30:00', '2021-10-20 08:31:00', '2021-10-20 08:54:00', 10, 35, 5);

思路
请统计各个城市在2021年10月期间，单日中最大的同时等车人数。 方式一： - 状态1：司机接单前取消，则没有生成order_id,这种情况 order_id IS NULL 记录end_time - 状态2：司机接单后取消，则没有上车时间，start_time IS NULL 记录 finish_time - 状态3：正常上车，记录start_time,start_time IS NOT NULL SELECT city,event_time uv_time,1 AS uv FROMtb_get_car_record #进入等车状态 UNION ALL SELECT city,end_time uv_time,-1 AS uv FROMtb_get_car_record WHERE order_id IS NULL #接单前取消 UNION ALL SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车方式二： SELECT city, event_time as at_time, 1 as wait_uv FROM tb_get_car_record UNION ALL SELECT city, COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv FROM tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)2.筛选时间窗：WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10" 3.定义按城市和日期分区按时刻排序的窗口（先增加后减少，所以uv倒排）： SUM(wait_uv) over(PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC) as current_max 4.计算当前最大等车人数： SUM(wait_uv) over(wd_city_date) as current_max 5.按城市分组： GROUP BY city 6.计算各城市最大等待人数： MAX(current_max) as max_wait_uv

题解
方式一： select city,max(wait_uv)max_wait_uv from ( select city,date(time)days,sum(uv)over(partition by city,date(time) order by time,uv desc) wait_uv from ( #进入等车状态 SELECT city,event_time time,1 AS uv FROMtb_get_car_record UNION ALL SELECT city,end_time time,-1 AS uv FROMtb_get_car_record WHERE order_id IS NULL #接单前取消 UNION ALL SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车 )t_city_wait where date_format(time,"%Y-%m")="2021-10" )t_max_city_wait group by city order by max_wait_uv,city方式二： SELECT city, MAX(current_max) as max_wait_uv FROM ( SELECT city, SUM(wait_uv) over(PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC) as current_max FROM ( SELECT city, event_time as at_time, 1 as wait_uv FROM tb_get_car_record UNION ALL SELECT city, COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv FROM tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id) ) as t_uv_at_time WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10" --WINDOW wd_city_date as (PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC) ) as t_city_cur_max GROUP BY city ORDER BY max_wait_uv, city; 方式三： WITH t1 AS( #每个城市等车瞬时UV SELECT city, SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt FROM ( SELECT city,event_time uv_time,1 AS uv FROMtb_get_car_record #进入等车状态 UNION ALL SELECT city,end_time uv_time,-1 AS uv FROMtb_get_car_record WHERE order_id IS NULL #接单前取消 UNION ALL SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车 )AS t WHERE DATE_FORMAT(uv_time,'%Y%m')='202110' #2021年10月 ) SELECT city,MAX(uv_cnt) max_wait_uv FROM t1 GROUP BY citY ORDER BY max_wait_uv,citY; #排序先按照uv升序，uv一样按照城市升序

拓展
coalesce(expression_1, expression_2, ...,expression_n)函数在学习sql时，发现coalesce函数，和三则运算表达式有些类似，不过它又好像跟三则表达式有些不同，它跟java 的泛型也有些类似，参数个数可以动态传递，类似这样的用法在mysql函数中是很少见的。语法 coalesce(expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用coalesce在于大部分包含空值的表达式最终将返回空值select coalesce(null,null,1,2); 输出： 1

3. 某宝店铺的SPU数量 11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
已知产品情况表product_tb如下（其中，item_id指某款号的具体货号，style_id指款号，tag_price表示标签价格，inventory指库存量）：
item_id style_id tag_price inventory
A001 A 100 20
A002 A 120 30
A003 A 200 15
B001 B 130 18
B002 B 150 22
B003 B 125 10
B004 B 155 12
C001 C 260 25
C002 C 280 18
请你统计每款的SPU（货号）数量，并按SPU数量降序排序，以上例子的输出结果如下：
style_id SPU_num
B 4
A 3
C 2
示例1
drop table if exists product_tb; CREATE TABLE product_tb( item_id char(10) NOT NULL, style_id char(10) NOT NULL, tag_price int(10) NOT NULL, inventory int(10) NOT NULL ); INSERT INTO product_tb VALUES('A001', 'A', 100,20); INSERT INTO product_tb VALUES('A002', 'A', 120, 30); INSERT INTO product_tb VALUES('A003', 'A', 200,15); INSERT INTO product_tb VALUES('B001', 'B', 130, 18); INSERT INTO product_tb VALUES('B002', 'B', 150,22); INSERT INTO product_tb VALUES('B003', 'B', 125, 10); INSERT INTO product_tb VALUES('B004', 'B', 155,12); INSERT INTO product_tb VALUES('C001', 'C', 260, 25); INSERT INTO product_tb VALUES('C002', 'C', 280,18);

输出
B|4
A|3
C|2
题解
select style_id,count(*) SPU_num from product_tb group by style_id order by SPU_num desc

4.某宝店铺的实际销售额与客单价 11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
已知11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：
sales_date user_id item_id sales_num sales_price
2021-11-01 1 A001 1 90
2021-11-01 2 A002 2 220
2021-11-01 2 B001 1 120
2021-11-02 3 C001 2 500
2021-11-02 4 B001 1 120
2021-11-03 5 C001 1 240
2021-11-03 6 C002 1 270
2021-11-04 7 A003 1 180
2021-11-04 8 B002 1 140
2021-11-04 9 B001 1 125
2021-11-05 10 B003 1 120
2021-11-05 10 B004 1 150
2021-11-05 10 A003 1 180
2021-11-06 11 B003 1 120
2021-11-06 10 B004 1 150
请你统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数），以上例子的输出结果如下：
sales_total per_trans
2725 247.73
示例1
drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL ); INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001',1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002',2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001',1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001',2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001',1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001',1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002',1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003',1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002',1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001',1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003',1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004',1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003',1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003',1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004',1, 150);

输出
2725|247.73
题解
select sum(sales_price)sales_total, round(sum(sales_price)/count(distinct user_id),2)per_trans from sales_tb where month(sales_date)="11"

5. 某宝店铺折扣率 11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
已知产品情况表product_tb如下（其中，item_id指某款号的具体货号，style_id指款号，tag_price表示标签价格，inventory指库存量）：
item_id style_id tag_price inventory
A001 A 100 20
A002 A 120 30
A003 A 200 15
B001 B 130 18
B002 B 150 22
B003 B 125 10
B004 B 155 12
C001 C 260 25
C002 C 280 18
11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：
sales_date user_id item_id sales_num sales_price
2021-11-01 1 A001 1 90
2021-11-01 2 A002 2 220
2021-11-01 2 B001 1 120
2021-11-02 3 C001 2 500
2021-11-02 4 B001 1 120
2021-11-03 5 C001 1 240
2021-11-03 6 C002 1 270
2021-11-04 7 A003 1 180
2021-11-04 8 B002 1 140
2021-11-04 9 B001 1 125
2021-11-05 10 B003 1 120
2021-11-05 10 B004 1 150
2021-11-05 10 A003 1 180
2021-11-06 11 B003 1 120
2021-11-06 10 B004 1 150
请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额），以上例子的输出结果如下（折扣率保留两位小数）：
discount_rate(%)
93.97
示例1
drop table if exists product_tb; CREATE TABLE product_tb( item_id char(10) NOT NULL, style_id char(10) NOT NULL, tag_price int(10) NOT NULL, inventory int(10) NOT NULL ); INSERT INTO product_tb VALUES('A001', 'A', 100,20); INSERT INTO product_tb VALUES('A002', 'A', 120, 30); INSERT INTO product_tb VALUES('A003', 'A', 200,15); INSERT INTO product_tb VALUES('B001', 'B', 130, 18); INSERT INTO product_tb VALUES('B002', 'B', 150,22); INSERT INTO product_tb VALUES('B003', 'B', 125, 10); INSERT INTO product_tb VALUES('B004', 'B', 155,12); INSERT INTO product_tb VALUES('C001', 'C', 260, 25); INSERT INTO product_tb VALUES('C002', 'C', 280,18); drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL ); INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001',1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002',2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001',1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001',2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001',1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001',1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002',1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003',1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002',1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001',1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003',1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004',1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003',1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003',1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004',1, 150);

输出
93.97
题解
select round(sum(sales_price)/sum(sales_num*tag_price)*100,2) 'discount_rate(%)' from product_tb join sales_tb using(item_id)

    推荐阅读


    上一篇：MySQL|MySQL的JDBC编程及增删改查

    下一篇：数据库的分组数据