• 数据库系统原理与应用教程（079）—— MySQL 练习题：操作题 181-185（二十三）：综合练习

    数据库系统原理与应用教程（079）—— MySQL 练习题：操作题 181-185（二十三）：综合练习

    181、分组统计、联合查询

    该题目使用的表和数据如下：

    /*
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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    用户打车记录表：tb_get_car_record（uid-用户ID，city-城市，event_time-打车时间，end_time-打车结束时间， order_id-订单号），表中数据如下：

    mysql> select * from 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 |
+----+-----+--------+---------------------+---------------------+----------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    打车订单表：tb_get_car_order（order_id-订单号，uid-用户ID，driver_id-司机ID，order_time-接单时间，start_time-开始计费的上车时间，finish_time-订单完成时间，mileage-行驶里程数，fare-费用，grade-评分），表中数据如下：

    mysql> select * from 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-09-22 08:15:00|2021-09-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 |
+----+----------+-----+--+---------------------+---------------------+---------+------+-------+
11 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    【问题】统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以 event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留 1 位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。查询结果如下：

    说明：

    （1）不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 , 20:00:00）、休息时间 [20:00:00 , 07:00:00）；

    （2）时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）；

    （3）从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间。

    periodget_car_numavg_wait_timeavg_dispatch_time
    工作时间10.51.7
    休息时间10.72.3
    晚高峰32.17.3
    早高峰42.28.0

    解答：

    /*
select period, get_car_num, 
       round(avg_wait_time, 1) avg_wait_time, 
       round(avg_dispatch_time, 1) avg_dispatch_time
from 
(select '工作时间' period, 
       count(gco.order_time) get_car_num,
       avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
           substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
           right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
       avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
           substr(timediff(gco.start_time, gco.order_time), 4, 2) +
           right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
from tb_get_car_record gcr join tb_get_car_order gco
on gcr.order_id = gco.order_id
where DAYOFWEEK(event_time) between 2 and 6 and 
      time(event_time) >= '09:00:00' and time(event_time) < '17:00:00'
union
select '休息时间' period, 
       count(gco.order_time) get_car_num,
       avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
           substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
           right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
       avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
           substr(timediff(gco.start_time, gco.order_time), 4, 2) +
           right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
from tb_get_car_record gcr join tb_get_car_order gco
on gcr.order_id = gco.order_id
where DAYOFWEEK(event_time) between 2 and 6 and 
      time(event_time) >= '20:00:00' or time(event_time) < '07:00:00'
union      
select '晚高峰' period, 
       count(gco.order_time) get_car_num,
       avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
           substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
           right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
       avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
           substr(timediff(gco.start_time, gco.order_time), 4, 2) +
           right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
from tb_get_car_record gcr join tb_get_car_order gco
on gcr.order_id = gco.order_id
where DAYOFWEEK(event_time) between 2 and 6 and 
      time(event_time) >= '17:00:00' and time(event_time) < '20:00:00'
union
select '早高峰' period, 
       count(gco.order_time) get_car_num,
       avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
           substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
           right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
       avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
           substr(timediff(gco.start_time, gco.order_time), 4, 2) +
           right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
from tb_get_car_record gcr join tb_get_car_order gco
on gcr.order_id = gco.order_id
where DAYOFWEEK(event_time) between 2 and 6 and 
      time(event_time) >= '07:00:00' and time(event_time) < '09:00:00') a;
*/
mysql> select period, get_car_num, 
    ->        round(avg_wait_time, 1) avg_wait_time, 
    ->        round(avg_dispatch_time, 1) avg_dispatch_time
    -> from 
    -> (select '工作时间' period, 
    ->        count(gco.order_time) get_car_num,
    ->        avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
    ->            substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
    ->            right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
    ->        avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
    ->            substr(timediff(gco.start_time, gco.order_time), 4, 2) +
    ->            right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
    -> from tb_get_car_record gcr join tb_get_car_order gco
    -> on gcr.order_id = gco.order_id
    -> where DAYOFWEEK(event_time) between 2 and 6 and 
    ->       time(event_time) >= '09:00:00' and time(event_time) < '17:00:00'
    -> union
    -> select '休息时间' period, 
    ->        count(gco.order_time) get_car_num,
    ->        avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
    ->            substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
    ->            right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
    ->        avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
    ->            substr(timediff(gco.start_time, gco.order_time), 4, 2) +
    ->            right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
    -> from tb_get_car_record gcr join tb_get_car_order gco
    -> on gcr.order_id = gco.order_id
    -> where DAYOFWEEK(event_time) between 2 and 6 and 
    ->       time(event_time) >= '20:00:00' or time(event_time) < '07:00:00'
    -> union      
    -> select '晚高峰' period, 
    ->        count(gco.order_time) get_car_num,
    ->        avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
    ->            substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
    ->            right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
    ->        avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
    ->            substr(timediff(gco.start_time, gco.order_time), 4, 2) +
    ->            right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
    -> from tb_get_car_record gcr join tb_get_car_order gco
    -> on gcr.order_id = gco.order_id
    -> where DAYOFWEEK(event_time) between 2 and 6 and 
    ->       time(event_time) >= '17:00:00' and time(event_time) < '20:00:00'
    -> union
    -> select '早高峰' period, 
    ->        count(gco.order_time) get_car_num,
    ->        avg(left(timediff(gco.order_time, gcr.event_time), 2) * 60 +
    ->            substr(timediff(gco.order_time, gcr.event_time), 4, 2) +
    ->            right(timediff(gco.order_time, gcr.event_time), 2)/60) avg_wait_time,
    ->        avg(left(timediff(gco.start_time, gco.order_time), 2) * 60 +
    ->            substr(timediff(gco.start_time, gco.order_time), 4, 2) +
    ->            right(timediff(gco.start_time, gco.order_time), 2)/60) avg_dispatch_time
    -> from tb_get_car_record gcr join tb_get_car_order gco
    -> on gcr.order_id = gco.order_id
    -> where DAYOFWEEK(event_time) between 2 and 6 and 
    ->       time(event_time) >= '07:00:00' and time(event_time) < '09:00:00') a;
+--------------+-------------+---------------+-------------------+
| 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 |
+--------------+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121

    182、分组统计查询

    该题目使用的表和数据如下：

    /*
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);
 */

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    产品情况表：product_tb（item_id：某款号的具体货号，style_id：款号，tag_price：标签价格，inventory：库存量），表中数据如下：

    mysql> select * from product_tb;
+---------+----------+-----------+-----------+
| 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 |
+---------+----------+-----------+-----------+
9 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    【问题】统计每款的 SPU（货号）数量，并按 SPU 数量降序排序，查询结果如下：

    style_idSPU_num
    B4
    A3
    C2

    解答：

    /*
select style_id, count(*) SPU_num
from product_tb
group by style_id 
order by SPU_num desc;
*/
mysql> select style_id, count(*) SPU_num
    -> from product_tb
    -> group by style_id 
    -> order by SPU_num desc;
+----------+---------+
| style_id | SPU_num |
+----------+---------+
| B        |       4 |
| A        |       3 |
| C        |       2 |
+----------+---------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    183、聚合函数的使用（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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    11 月份销售数据表：sales_tb（sales_date：销售日期，user_id：用户编号，item_id：货号，sales_num：销售数量，sales_price：结算金额），表中数据如下：

    mysql> select * from sales_tb;
+------------+---------+---------+-----------+-------------+
| 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 |
+------------+---------+---------+-----------+-------------+
15 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    【问题】请统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数），查询结果如下：

    sales_totalper_trans
    2725247.73

    解答：

    /*
select sum(sales_price) sales_total,
       round(sum(sales_price)/count(distinct user_id), 2) per_trans
from sales_tb;
*/
mysql> select sum(sales_price) sales_total,
    ->        round(sum(sales_price)/count(distinct user_id), 2) per_trans
    -> from sales_tb;
+-------------+-----------+
| sales_total | per_trans |
+-------------+-----------+
|        2725 |    247.73 |
+-------------+-----------+
1 row in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    184、聚合函数的使用（2）

    该题目使用的表和数据如下：

    /*
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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    产品情况表：product_tb（item_id：某款号的具体货号，style_id：款号，tag_price：标签价格，inventory：库存量），表中数据如下：

    mysql> select * from product_tb;
+---------+----------+-----------+-----------+
| 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 |
+---------+----------+-----------+-----------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    11 月份销售数据表：sales_tb（sales_date：销售日期，user_id：用户编号，item_id：货号，sales_num：销售数量，sales_price：结算金额），表中数据如下：

    mysql> select * from sales_tb;
+------------+---------+---------+-----------+-------------+
| 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 |
+------------+---------+---------+-----------+-------------+
15 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    【问题】请统计折扣率（GMV/吊牌金额，GMV 指的是成交金额），折扣率保留两位小数，查询结果如下：

    discount_rate(%)
    93.97

    解答：

    /*
select round(sum(s.sales_price)/sum(p.tag_price * s.sales_num) * 100, 2) `discount_rate(%)`
from product_tb p join sales_tb s 
on p.item_id = s.item_id;
*/
mysql> select round(sum(s.sales_price)/sum(p.tag_price * s.sales_num) * 100, 2) `discount_rate(%)`
    -> from product_tb p join sales_tb s 
    -> on p.item_id = s.item_id;
+------------------+
| discount_rate(%) |
+------------------+
|            93.97 |
+------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    185、聚合函数的使用（2）

    该题目使用的表和数据如下：

    /*
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);
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    产品情况表：product_tb（item_id：某款号的具体货号，style_id：款号，tag_price：标签价格，inventory：库存量），表中数据如下：

    mysql> select * from product_tb;
+---------+----------+-----------+-----------+
| 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 |
+---------+----------+-----------+-----------+
9 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    11 月份销售数据表：sales_tb（sales_date：销售日期，user_id：用户编号，item_id：货号，sales_num：销售数量，sales_price：结算金额），表中数据如下：

    mysql> select * from sales_tb;
+------------+---------+---------+-----------+-------------+
| 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 |
+------------+---------+---------+-----------+-------------+
15 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    【问题】请统计每款的动销率（pin_rate，有销售的 SKU 数量/在售 SKU 数量）与售罄率（sell-through_rate，GMV/备货值，备货值 = 吊牌价 * 库存数），按 style_id 升序排序，查询结果如下：

    style_idpin_rate(%)sell-through_rate(%)
    A8.337.79
    B14.8111.94
    C10.268.75

    解答：

    /*
select a.style_id, 
       round(a.sales_num/(b.inventory - a.sales_num) * 100, 2) `pin_rate(%)`,
       round(a.sales_price/b.tag_price * 100, 2) `sell-through_rate(%)`
from
(select p.style_id,
       sum(s.sales_num) sales_num,
       sum(s.sales_price) sales_price
from sales_tb s join product_tb p
on s.item_id = p.item_id
group by p.style_id) a
join
(select style_id,
       sum(inventory) inventory,
       sum(tag_price * inventory) tag_price
from product_tb
group by style_id) b
on a.style_id = b.style_id
order by a.style_id;
*/
mysql> select a.style_id, 
    ->        round(a.sales_num/(b.inventory - a.sales_num) * 100, 2) `pin_rate(%)`,
    ->        round(a.sales_price/b.tag_price * 100, 2) `sell-through_rate(%)`
    -> from
    -> (select p.style_id,
    ->        sum(s.sales_num) sales_num,
    ->        sum(s.sales_price) sales_price
    -> from sales_tb s join product_tb p
    -> on s.item_id = p.item_id
    -> group by p.style_id) a
    -> join
    -> (select style_id,
    ->        sum(inventory) inventory,
    ->        sum(tag_price * inventory) tag_price
    -> from product_tb
    -> group by style_id) b
    -> on a.style_id = b.style_id
    -> order by a.style_id;
+----------+-------------+----------------------+
| style_id | pin_rate(%) | sell-through_rate(%) |
+----------+-------------+----------------------+
| A        |        8.33 |                 7.79 |
| B        |       14.81 |                11.94 |
| C        |       10.26 |                 8.75 |
+----------+-------------+----------------------+
3 rows in set (0.02 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
  • 相关阅读:
    基于javaweb简单的在线考试系统
    代码扫描搭建Sonar+docker+jenkins
    C++中struct与class区别，C与C++中struct区别
    （附源码）springboot自习室座位预约系统 毕业设计674156
    react基本使用、jsx语法介绍
    【Hadoop】在spark读取clickhouse中数据
    Django models新增属性后 迁移报错 KeyError，求解
    通过51单片机控制SG90舵机按角度正反转转动
    web网页设计期末课程大作业 HTML+CSS+JavaScript 美食餐饮文化主题网站设计 学生DW静态网页设计
    apollo lidar 模块3.0&6.0
  • 原文地址：https://blog.csdn.net/weixin_44377973/article/details/126180286