数据库系统原理与应用教程（078）—— MySQL 练习题：操作题 173-180

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

/\*
select concat(left(event\_time, 4), '-', substr(event\_time, 6, 2)) month,
 sum(total\_amount) GMV
from tb\_order\_overall
where event\_time between '2021-01-01 00:00:00' and '2021-12-31 23:59:59'
 and status in (0, 1)
group by month
having GMV > 100000
order by GMV;
\*/
mysql> select concat(left(event_time, 4), '-', substr(event_time, 6, 2)) month,
    ->        sum(total_amount) GMV
    -> from tb_order_overall
    -> where event_time between '2021-01-01 00:00:00' and '2021-12-31 23:59:59'
    ->       and status in (0, 1)
    -> group by month
    -> having GMV > 100000
    -> order by GMV;
+---------+--------+
| month   | GMV    |
+---------+--------+
| 2021-10 | 109800 |
| 2021-11 | 111900 |
+---------+--------+
2 rows in set (0.03 sec)

175、分组统计查询（3）

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

/\*
DROP TABLE IF EXISTS tb\_user\_event;
CREATE TABLE tb\_user\_event (
 id INT PRIMARY KEY AUTO\_INCREMENT COMMENT '自增ID',
 uid INT NOT NULL COMMENT '用户ID',
 product\_id INT NOT NULL COMMENT '商品ID',
 event\_time datetime COMMENT '行为时间',
 if\_click TINYINT COMMENT '是否点击',
 if\_cart TINYINT COMMENT '是否加购物车',
 if\_payment TINYINT COMMENT '是否付款',
 if\_refund TINYINT COMMENT '是否退货退款'
) CHARACTER SET utf8 COLLATE utf8\_bin;

INSERT INTO tb\_user\_event(uid, product\_id, event\_time, if\_click, if\_cart, if\_payment, if\_refund) VALUES
 (101, 8001, '2021-10-01 10:00:00', 0, 0, 0, 0),
 (102, 8001, '2021-10-01 10:00:00', 1, 0, 0, 0),
 (103, 8001, '2021-10-01 10:00:00', 1, 1, 0, 0),
 (104, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
 (105, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
 (101, 8002, '2021-10-03 10:00:00', 1, 1, 1, 0),
 (109, 8001, '2021-10-04 10:00:00', 1, 1, 1, 1);
\*/

商品行为表：tb_user_event（uid-用户ID，product_id-商品ID，event_time-行为时间，if_click-是否点击，if_cart-是否加购物车，if_payment-是否付款，if_refund-是否退货退款），表中数据如下：

mysql> select \* from tb_user_event;
+----+-----+------------+---------------------+----------+---------+------------+-----------+
| id | uid | product_id | event_time          | if_click | if_cart | if_payment | if_refund |
+----+-----+------------+---------------------+----------+---------+------------+-----------+
|  1 | 101 |       8001 | 2021-10-01 10:00:00 |        0 |       0 |          0 |         0 |
|  2 | 102 |       8001 | 2021-10-01 10:00:00 |        1 |       0 |          0 |         0 |
|  3 | 103 |       8001 | 2021-10-01 10:00:00 |        1 |       1 |          0 |         0 |
|  4 | 104 |       8001 | 2021-10-02 10:00:00 |        1 |       1 |          1 |         0 |
|  5 | 105 |       8001 | 2021-10-02 10:00:00 |        1 |       1 |          1 |         0 |
|  6 | 101 |       8002 | 2021-10-03 10:00:00 |        1 |       1 |          1 |         0 |
|  7 | 109 |       8001 | 2021-10-04 10:00:00 |        1 |       1 |          1 |         1 |
+----+-----+------------+---------------------+----------+---------+------------+-----------+
7 rows in set (0.00 sec)

【问题】请统计 2021 年 10 月每个有展示记录的退货率不大于 0.5 的商品各项指标（商品点展比，加购率，成单率，退货率），当分母为 0 时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。查询结果如下：

说明：

（1）商品点展比 = 点击数 ÷ 展示数；

（2）加购率 = 加购数 ÷ 点击数；

（3）成单率 = 付款数 ÷ 加购数；

（4）退货率 = 退款数 ÷ 付款数。

product_id	ctr	cart_rate	payment_rate	refund_rate
8001	0.833	0.800	0.750	0.333
8002	1.000	1.000	1.000	0.000

解答：

/\*
select product\_id, round(sum(if\_click)/count(\*), 3) ctr,
 round(sum(if\_cart)/sum(if\_click), 3) cart\_rate,
 round(sum(if\_payment)/sum(if\_cart), 3) payment\_rate,
 round(sum(if\_refund)/sum(if\_payment), 3) refund\_rate
from tb\_user\_event
where event\_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59'
group by product\_id
order by product\_id;
\*/
mysql> select product_id, round(sum(if_click)/count(\*), 3) ctr,
    ->        round(sum(if_cart)/sum(if_click), 3) cart_rate,
    ->        round(sum(if_payment)/sum(if_cart), 3) payment_rate,
    ->        round(sum(if_refund)/sum(if_payment), 3) refund_rate
    -> from tb_user_event
    -> where event_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59'
    -> group by product_id
    -> order by product_id;
+------------+-------+-----------+--------------+-------------+
| product_id | ctr   | cart_rate | payment_rate | refund_rate |
+------------+-------+-----------+--------------+-------------+
|       8001 | 0.833 |     0.800 |        0.750 |       0.333 |
|       8002 | 1.000 |     1.000 |        1.000 |       0.000 |
+------------+-------+-----------+--------------+-------------+
2 rows in set (0.04 sec)

175、分组统计查询（3）

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

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

INSERT INTO tb\_order\_overall(order\_id, uid, event\_time, total\_amount, total\_cnt, `status`) VALUES
 (301001, 101, '2021-10-01 10:00:00', 30000, 3, 1),
 (301002, 102, '2021-10-01 11:00:00', 23900, 2, 1),
 (301003, 103, '2021-10-02 10:00:00', 31000, 2, 1);

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, '家电', 6000, 100, '2020-01-01 10:00:00'),
 (8002, 902, '家电', 12000, 50, '2020-01-01 10:00:00'),
 (8003, 901, '3C数码', 12000, 50, '2020-01-01 10:00:00');

INSERT INTO tb\_order\_detail(order\_id, product\_id, price, cnt) VALUES
 (301001, 8001, 8500, 2),
 (301001, 8002, 15000, 1),
 (301002, 8001, 8500, 1),
 (301002, 8002, 16000, 1),
 (301003, 8002, 14000, 1),
 (301003, 8003, 18000, 1);
\*/

商品信息表：tb_product_info（product_id-商品ID，shop_id-店铺ID，tag-商品类别标签，in_price-进货价格，quantity-进货数量，release_time-上架时间），表中数据如下：

mysql> select \* from tb_product_info;
+----+------------+---------+----------+----------+----------+---------------------+
| id | product_id | shop_id | tag      | in_price | quantity | release_time        |
+----+------------+---------+----------+----------+----------+---------------------+
|  1 |       8001 |     901 | 家电     |     6000 |      100 | 2020-01-01 10:00:00 |
|  2 |       8002 |     902 | 家电     |    12000 |       50 | 2020-01-01 10:00:00 |
|  3 |       8003 |     901 | 3C数码   |    12000 |       50 | 2020-01-01 10:00:00 |
+----+------------+---------+----------+----------+----------+---------------------+
3 rows in set (0.00 sec)

订单总表：tb_order_overall（order_id-订单号，uid-用户ID，event_time-下单时间，total_amount-订单总金额，total_cnt-订单商品总件数，status-订单状态），表中数据如下：

mysql> select \* from tb_order_overall;
+----+----------+-----+---------------------+--------------+-----------+--------+
| id | order_id | uid | event_time          | total_amount | total_cnt | status |
+----+----------+-----+---------------------+--------------+-----------+--------+
|  1 |   301001 | 101 | 2021-10-01 10:00:00 |        30000 |         3 |      1 |
|  2 |   301002 | 102 | 2021-10-01 11:00:00 |        23900 |         2 |      1 |
|  3 |   301003 | 103 | 2021-10-02 10:00:00 |        31000 |         2 |      1 |
+----+----------+-----+---------------------+--------------+-----------+--------+
3 rows in set (0.00 sec)

订单明细表：tb_order_detail（order_id-订单号，product_id-商品ID，price-商品单价，cnt-下单数量），表中数据如下：

mysql> select \* from tb_order_detail;
+----+----------+------------+-------+-----+
| id | order_id | product_id | price | cnt |
+----+----------+------------+-------+-----+
|  1 |   301001 |       8001 |  8500 |   2 |
|  2 |   301001 |       8002 | 15000 |   1 |
|  3 |   301002 |       8001 |  8500 |   1 |
|  4 |   301002 |       8002 | 16000 |   1 |
|  5 |   301003 |       8002 | 14000 |   1 |
|  6 |   301003 |       8003 | 18000 |   1 |
+----+----------+------------+-------+-----+
6 rows in set (0.02 sec)

【问题】请计算 2021 年 10 月以来店铺 901 中商品毛利率大于 24.9% 的商品信息及店铺整体毛利率。结果先输出店铺毛利率，再按商品 ID 升序输出各商品毛利率，均保留 1 位小数。查询结果如下：

说明：

（1）商品毛利率 = (1-进价/平均单件售价) * 100%；

（2）店铺毛利率 = (1-总进价成本/总销售收入) * 100%。

product_id	profit_rate
店铺汇总	31.0%
8001	29.4%
8003	33.3%

解答：

/\*
select '店铺汇总' product\_id,
 concat(round((1 - sum(pi.in\_price \* od.cnt)/sum(od.price \* od.cnt)) \* 100, 1),'%') 
 profit\_rate 
from tb\_product\_info pi join tb\_order\_detail od
 on pi.product\_id = od.product\_id
where pi.shop\_id = '901' and od.order\_id in
 (select order\_id from tb\_order\_overall where event\_time >= '2021-10-01 00:00:00')
union 
select pi.product\_id,
 concat(round((1 - pi.in\_price/(sum(od.price \* od.cnt)/sum(od.cnt))) \* 100, 1), '%')
 profit\_rate 
from tb\_product\_info pi join tb\_order\_detail od
 on pi.product\_id = od.product\_id
where pi.shop\_id = '901' and od.order\_id in
 (select order\_id from tb\_order\_overall where event\_time >= '2021-10-01 00:00:00')
group by pi.product\_id,pi.in\_price;
\*/
mysql> select '店铺汇总' product_id,
    ->        concat(round((1 - sum(pi.in_price \* od.cnt)/sum(od.price \* od.cnt)) \* 100, 1),'%') 
    ->        profit_rate 
    -> from tb_product_info pi join tb_order_detail od
    ->      on pi.product_id = od.product_id
    -> where pi.shop_id = '901' and od.order_id in
    ->      (select order_id from tb_order_overall where event_time >= '2021-10-01 00:00:00')
    -> union     
    -> select pi.product_id,
    ->        concat(round((1 - pi.in_price/(sum(od.price \* od.cnt)/sum(od.cnt))) \* 100, 1), '%')
    ->        profit_rate 
    -> from tb_product_info pi join tb_order_detail od
    ->      on pi.product_id = od.product_id
    -> where pi.shop_id = '901' and od.order_id in
    ->      (select order_id from tb_order_overall where event_time >= '2021-10-01 00:00:00')
    -> group by pi.product_id,pi.in_price
    -> order by product_id;
+--------------+-------------+
| product_id   | profit_rate |
+--------------+-------------+
| 8001         | 29.4%       |
| 8003         | 33.3%       |
| 店铺汇总     | 31.0%       |
+--------------+-------------+
3 rows in set (0.00 sec)

176、分组统计查询（4）

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

/\*
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
 (301001, 101, '2021-09-30 10:00:00', 140, 1, 1),
 (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
 (301011, 102, '2021-10-31 11:00:00', 250, 2, 1),
 (301003, 101, '2021-11-02 10:00:00', 300, 2, 1),
 (301013, 105, '2021-11-02 10:00:00', 300, 2, 1),
 (301005, 104, '2021-11-03 10:00:00', 170, 1, 1);

INSERT INTO tb\_order\_detail(order\_id, product\_id, price, cnt) VALUES
 (301001, 8002, 150, 1),
 (301011, 8003, 200, 1),
 (301011, 8001, 80, 1),
 (301002, 8001, 85, 1),
 (301002, 8003, 180, 1),
 (301003, 8002, 140, 1),
 (301003, 8003, 180, 1),
 (301013, 8002, 140, 2),
 (301005, 8003, 180, 1);
 \*/

商品信息表：tb_product_info（product_id-商品ID，shop_id-店铺ID，tag-商品类别标签，in_price-进货价格，quantity-进货数量，release_time-上架时间），表中数据如下：

mysql> select \* from tb_product_info;
+----+------------+---------+--------+----------+----------+---------------------+
| id | product_id | shop_id | tag    | in_price | quantity | release_time        |
+----+------------+---------+--------+----------+----------+---------------------+
|  1 |       8001 |     901 | 零食   |       60 |     1000 | 2020-01-01 10:00:00 |
|  2 |       8002 |     901 | 零食   |      140 |      500 | 2020-01-01 10:00:00 |
|  3 |       8003 |     901 | 零食   |      160 |      500 | 2020-01-01 10:00:00 |
+----+------------+---------+--------+----------+----------+---------------------+
3 rows in set (0.01 sec)

订单总表：tb_order_overall（order_id-订单号，uid-用户ID，event_time-下单时间，total_amount-订单总金额，total_cnt-订单商品总件数，status-订单状态），表中数据如下：

mysql> select \* from tb_order_overall;
+----+----------+-----+---------------------+--------------+-----------+--------+
| id | order_id | uid | event_time          | total_amount | total_cnt | status |
+----+----------+-----+---------------------+--------------+-----------+--------+
|  1 |   301001 | 101 | 2021-09-30 10:00:00 |          140 |         1 |      1 |
|  2 |   301002 | 102 | 2021-10-01 11:00:00 |          235 |         2 |      1 |
|  3 |   301011 | 102 | 2021-10-31 11:00:00 |          250 |         2 |      1 |
|  4 |   301003 | 101 | 2021-11-02 10:00:00 |          300 |         2 |      1 |
|  5 |   301013 | 105 | 2021-11-02 10:00:00 |          300 |         2 |      1 |
|  6 |   301005 | 104 | 2021-11-03 10:00:00 |          170 |         1 |      1 |
+----+----------+-----+---------------------+--------------+-----------+--------+
6 rows in set (0.00 sec)

订单明细表：tb_order_detail（order_id-订单号，product_id-商品ID，price-商品单价，cnt-下单数量），表中数据如下：

mysql> select \* from tb_order_detail;
+----+----------+------------+-------+-----+
| id | order_id | product_id | price | cnt |
+----+----------+------------+-------+-----+
|  1 |   301001 |       8002 |   150 |   1 |
|  2 |   301011 |       8003 |   200 |   1 |
|  3 |   301011 |       8001 |    80 |   1 |
|  4 |   301002 |       8001 |    85 |   1 |
|  5 |   301002 |       8003 |   180 |   1 |
|  6 |   301003 |       8002 |   140 |   1 |
|  7 |   301003 |       8003 |   180 |   1 |
|  8 |   301013 |       8002 |   140 |   2 |
|  9 |   301005 |       8003 |   180 |   1 |
+----+----------+------------+-------+-----+
9 rows in set (0.00 sec)

【问题】请统计零食类商品中复购率 top3 高的商品。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序。查询结果如下：

说明：

（1）复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率。

（2）某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

（3）近 90 天指包含最大日期（记为当天）在内的近 90 天。

product_id	repurchase_rate
8001	1.000
8002	0.500
8003	0.333

解答：

/\*
select a.product\_id, round(colB / colA, 3) repurchase\_rate
from 
(select od.product\_id,
 count(distinct uid) colA
from tb\_order\_overall oo join tb\_order\_detail od
on oo.order\_id = od.order\_id
where oo.event\_time >=
 (select adddate(max(event\_time), -89) from tb\_order\_overall) and
 od.product\_id in
 (select product\_id from tb\_product\_info where tag = '零食')
group by od.product\_id) a
join 
(select product\_id, count(\*) colB
from (select od.product\_id, oo.uid, count(\*) aa
 from tb\_order\_overall oo join tb\_order\_detail od
 on oo.order\_id = od.order\_id
 where oo.event\_time >=
 (select adddate(max(event\_time), -89) from tb\_order\_overall) and
 od.product\_id in
 (select product\_id from tb\_product\_info where tag = '零食')
 group by od.product\_id, oo.uid
 having aa > 1) bb
group by product\_id) b
on a.product\_id = b.product\_id
order by repurchase\_rate desc, a.product\_id;
\*/
mysql> select a.product_id, round(colB / colA, 3) repurchase_rate
    -> from 
    -> (select od.product_id,
    ->        count(distinct uid) colA
    -> from tb_order_overall oo join tb_order_detail od
    -> on oo.order_id = od.order_id
    -> where oo.event_time >=
    ->       (select adddate(max(event_time), -89) from tb_order_overall) and
    ->       od.product_id in
    ->       (select product_id from tb_product_info where tag = '零食')
    -> group by od.product_id) a
    -> join 
    -> (select product_id, count(\*) colB
    -> from (select od.product_id, oo.uid, count(\*) aa
    ->       from tb_order_overall oo join tb_order_detail od
    ->       on oo.order_id = od.order_id
    ->       where oo.event_time >=
    ->           (select adddate(max(event_time), -89) from tb_order_overall) and
    ->            od.product_id in
    ->           (select product_id from tb_product_info where tag = '零食')
    ->       group by od.product_id, oo.uid
    ->       having aa > 1) bb
    -> group by product_id) b
    -> on a.product_id = b.product_id
    -> order by repurchase_rate desc, a.product_id;
+------------+-----------------+
| product_id | repurchase_rate |
+------------+-----------------+
|       8001 |           1.000 |
|       8002 |           0.500 |
|       8003 |           0.333 |
+------------+-----------------+
3 rows in set (0.00 sec)

177、分组统计查询（5）

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

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

用户打车记录表：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 | 101 | 北京   | 2021-10-01 07:00:00 | 2021-10-01 07:02:00 |     NULL |
|  2 | 102 | 北京   | 2021-10-01 09:00:30 | 2021-10-01 09:01:00 |     9001 |
|  3 | 101 | 北京   | 2021-10-01 08:28:10 | 2021-10-01 08:30:00 |     9002 |
|  4 | 103 | 北京   | 2021-10-02 07:59:00 | 2021-10-02 08:01:00 |     9003 |
|  5 | 104 | 北京   | 2021-10-03 07:59:20 | 2021-10-03 08:01:00 |     9004 |
|  6 | 105 | 北京   | 2021-10-01 08:00:00 | 2021-10-01 08:02:10 |     9005 |
|  7 | 106 | 北京   | 2021-10-01 17:58:00 | 2021-10-01 18:01:00 |     9006 |
|  8 | 107 | 北京   | 2021-10-02 11:00:00 | 2021-10-02 11:01:00 |     9007 |
|  9 | 108 | 北京   | 2021-10-02 21:00:00 | 2021-10-02 21:01:00 |     9008 |
+----+-----+--------+---------------------+---------------------+----------+
9 rows in set (0.00 sec)

打车订单表：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|9002|101|201|2021-10-01 08:30:00 | NULL                | 2021-10-01 08:31:00 |NULL| NULL |NULL |
|2|9001|102|202|2021-10-01 09:01:00 | 2021-10-01 09:06:00 | 2021-10-01 09:31:00 |10  | 41.5 | 5 |
|3|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 |
|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 |
|5|9005|105|203|2021-10-01 08:02:10 | 2021-10-01 08:18:00 | 2021-10-01 08:31:00 |15  |   44 | 5 |
|6|9006|106|203|2021-10-01 18:01:00 | 2021-10-01 18:09:00 | 2021-10-01 18:31:00 |8   |   25 | 5 |
|7|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 |
|8|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 |
+----+-------------------+---------------------+---------------------+---------+------+-------+
8 rows in set (0.04 sec)

说明：

（1）用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为 null；

（2）当有司机接单时，在打车订单表生成一条订单，填充 order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为 null，并把 order_id-订单号和 order_time-接单时间**（**end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录 end_time-打车结束时间。

（3）若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的 finish_time-订单完成时间填充为取消时间，其余字段设为 null。

（4）当司机接上乘客时，填充订单表中该 start_time-开始计费的上车时间。

（5）当订单完成时填充订单完成时间、里程数、费用；评分设为 null，在用户给司机打 1~5 星评价后填充。

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

city	avg_order_num	avg_income
北京	3.500	121.000

解答：

/\*
select '北京' city, 
 round(avg(cnt), 3) avg\_order\_num, 
 round(avg(sum\_fare), 3) avg\_income
from
(select count(\*) cnt, sum(fare) sum\_fare
from tb\_get\_car\_order
where order\_time between '2021-10-01 00:00:00' and '2021-10-07 23:59:59'
 and order\_id in
 (select order\_id from tb\_get\_car\_record where city= '北京')
group by driver\_id
having count(\*) >= 3) a;
\*/
mysql> select '北京' city, avg(cnt) avg_order_num, avg(sum_fare) avg_income
    -> from
    -> (select count(\*) cnt, sum(fare) sum_fare
    -> from tb_get_car_order
    -> where order_time between '2021-10-01 00:00:00' and '2021-10-07 23:59:59'
    ->       and order_id in
    ->       (select order_id from tb_get_car_record where city= '北京')
    -> group by driver_id
    -> having count(\*) >= 3) a;
+--------+---------------+------------+
| city   | avg_order_num | avg_income |
+--------+---------------+------------+
| 北京   |        3.5000 |        121 |
+--------+---------------+------------+
1 row in set (0.00 sec)

178、分组统计查询（6）

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

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

用户打车记录表：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 | 101 | 北京   | 2021-10-01 07:00:00 | 2021-10-01 07:02:00 |     NULL |
|  2 | 102 | 北京   | 2021-10-01 09:00:30 | 2021-10-01 09:01:00 |     9001 |
|  3 | 101 | 北京   | 2021-10-01 08:28:10 | 2021-10-01 08:30:00 |     9002 |
|  4 | 103 | 北京   | 2021-10-02 07:59:00 | 2021-10-02 08:01:00 |     9003 |
|  5 | 104 | 北京   | 2021-10-03 07:59:20 | 2021-10-03 08:01:00 |     9004 |
|  6 | 105 | 北京   | 2021-10-01 08:00:00 | 2021-10-01 08:02:10 |     9005 |
|  7 | 106 | 北京   | 2021-10-01 17:58:00 | 2021-10-01 18:01:00 |     9006 |
|  8 | 107 | 北京   | 2021-10-02 11:00:00 | 2021-10-02 11:01:00 |     9007 |
|  9 | 108 | 北京   | 2021-10-02 21:00:00 | 2021-10-02 21:01:00 |     9008 |
| 10 | 109 | 北京   | 2021-10-08 18:00:00 | 2021-10-08 18:01:00 |     9009 |
+----+-----+--------+---------------------+---------------------+----------+
10 rows in set (0.00 sec)

打车订单表：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|9002|101|202|2021-10-01 08:30:00|NULL               |2021-10-01 08:31:00|NULL |NULL |  NULL |
|2|9001|102|202|2021-10-01 09:01:00|2021-10-01 09:06:00|2021-10-01 09:31:00|  10 |41.5 |     5 |
|3|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 |
|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 |
|5|9005|105|203|2021-10-01 08:02:10|NULL               |2021-10-01 08:31:00|NULL |NULL |  NULL |
|6|9006|106|203|2021-10-01 18:01:00|2021-10-01 18:09:00|2021-10-01 18:31:00|   8 |25.5 |     5 |
|7|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 |
|8|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 |
|9|9009|109|203|2021-10-08 18:01:00|2021-10-08 18:11:50|2021-10-08 18:51:00|  13 |  40 |     5 |
+----+----------+-----+-----------+----------------+---------------------+---------+------+-------+
9 rows in set (0.00 sec)

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

driver_id	avg_grade
202	4.3
203	4.8
总体	4.6

解答：

/\*
select driver\_id, round(avg(grade), 1) avg\_grade
from tb\_get\_car\_order
where driver\_id in
 (select driver\_id
 from tb\_get\_car\_order
 where finish\_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59' and 
 start\_time is null)
group by driver\_id
union
select '总体' driver\_id, round(avg(grade), 1) avg\_grade
from tb\_get\_car\_order
where driver\_id in
 (select driver\_id
 from tb\_get\_car\_order
 where finish\_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59' and 
 start\_time is null)


![img](https://img-blog.csdnimg.cn/img_convert/1d85865c67d67f6bf7c11663fa7fa66b.png)
![img](https://img-blog.csdnimg.cn/img_convert/699746fc35235b3226db82f9a55ceef4.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以添加戳这里获取](https://bbs.csdn.net/topics/618668825)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

`
/\*
select driver\_id, round(avg(grade), 1) avg\_grade
from tb\_get\_car\_order
where driver\_id in
 (select driver\_id
 from tb\_get\_car\_order
 where finish\_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59' and 
 start\_time is null)
group by driver\_id
union
select '总体' driver\_id, round(avg(grade), 1) avg\_grade
from tb\_get\_car\_order
where driver\_id in
 (select driver\_id
 from tb\_get\_car\_order
 where finish\_time between '2021-10-01 00:00:00' and '2021-10-31 23:59:59' and 
 start\_time is null)


[外链图片转存中...(img-43svhTbr-1715808991991)]
[外链图片转存中...(img-nC4VKXoa-1715808991992)]

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以添加戳这里获取](https://bbs.csdn.net/topics/618668825)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**