• 大数据第40天—Mysql练习题11-行程和用户-杨大伟

    需求：写一段 SQL 语句查出 2019年10月1日 至 2019年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

    取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

    Trips表：所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

     

    IdClient_IdDriver_IdCity_IdStatusRequest_at
    1 1 10 1 completed 2019-10-01
    2 2 11 1 cancelled_by_driver 2019-10-01
    3 3 12 6 completed 2019-10-01
    4 4 13 6 cancelled_by_client 2019-10-01
    5 1 10 1 completed 2019-10-02
    6 2 11 6 completed 2019-10-02
    7 3 12 6 completed 2019-10-02
    8 2 12 12 completed 2019-10-03
    9 3 10 12 completed 2019-10-03
    10 4 13 12 cancelled_by_driver 2019-10-03

    Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

    Users_IdBannedCancellation Rate
    1 No client
    2 Yes client
    3 No client
    4 No client
    10 No driver
    11 No driver
    12 No driver
    13 No driver

    展示效果：

    DayCancellation Rate
    2019-10-01 0.33
    2019-10-02 0.00
    2019-10-03 0.50 
     1 Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50));
 2 
 3 Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner'));
 4 
 5 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (1, 1, 10, 1, 'completed', '2019-10-01');
 6 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (2, 2, 11, 1, 'cancelled_by_driver', '2019-10-01');
 7 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (3, 3, 12, 6, 'completed', '2019-10-01');
 8 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (4, 4, 13, 6, 'cancelled_by_client', '2019-10-01');
 9 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (5, 1, 10, 1, 'completed', '2019-10-02');
10 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (6, 2, 11, 6, 'completed', '2019-10-02');
11 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (7, 3, 12, 6, 'completed', '2019-10-02');
12 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (8, 2, 12, 12, 'completed', '2019-10-03');
13 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (9, 3, 10, 12, 'completed', '2019-10-03');
14 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (10, 4, 13, 12, 'cancelled_by_driver', '2019-10-03');
15 
16 insert into Users (Users_Id, Banned, Role) values (1, 'No', 'client');
17 insert into Users (Users_Id, Banned, Role) values (2, 'Yes', 'client');
18 insert into Users (Users_Id, Banned, Role) values (3, 'No', 'client');
19 insert into Users (Users_Id, Banned, Role) values (4, 'No', 'client');
20 insert into Users (Users_Id, Banned, Role) values (10, 'No', 'driver');
21 insert into Users (Users_Id, Banned, Role) values (11, 'No', 'driver');
22 insert into Users (Users_Id, Banned, Role) values (12, 'No', 'driver');
23 insert into Users (Users_Id, Banned, Role) values (13, 'No', 'driver');

    最终SQL:

     1 方法一：
 2 SELECT 
 3       T.request_at AS `Day`, 
 4       ROUND(
 5             SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS),
 6             2
 7             ) AS `Cancellation Rate`
 8 FROM 
 9       Trips AS T
10 JOIN 
11       Users AS U1 
12 ON 
13       T.client_id = U1.users_id AND U1.banned ='No'
14 JOIN 
15       Users AS U2 
16 ON 
17       T.driver_id = U2.users_id AND U2.banned ='No'
18 WHERE 
19       T.request_at BETWEEN '2019-10-01' AND '2019-10-03'
20 GROUP BY 
21       T.request_at;
22 
23 方法二：
24 SELECT 
25       T.request_at AS `Day`, 
26       ROUND(
27             SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS),
28             2
29             ) AS `Cancellation Rate`
30 FROM 
31      trips AS T 
32 LEFT JOIN (
33             SELECT
34                   users_id
35             FROM
36                   users
37             WHERE  
38                   banned = 'Yes'
39            ) AS A 
40 ON 
41      T.Client_Id = A.users_id
42 LEFT JOIN (
43             SELECT
44                   users_id
45             FROM 
46                   users
47             WHERE 
48                   banned = 'Yes'
49            ) AS A1
50 ON 
51      T.Driver_Id = A1.users_id
52 WHERE 
53      A.users_id IS NULL 
54      AND A1.users_id IS NULL 
55      AND T.request_at BETWEEN '2019-10-01' AND '2019-10-03'
56 GROUP BY 
57      T.request_at;
58 
59 方法三：
60 SELECT 
61      T.request_at AS `Day`, 
62      ROUND(
63            SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS),
64            2
65            ) AS `Cancellation Rate`
66 FROM
67      trips AS T
68 WHERE 
69      T.Client_Id NOT IN (
70                           SELECT 
71                                  users_id
72                           FROM 
73                                  users
74                           WHERE 
75                                  banned = 'Yes'
76      )
77 AND
78      T.Driver_Id NOT IN (
79                            SELECT 
80                                  users_id
81                            FROM  
82                                  users
83                            WHERE 
84                                  banned = 'Yes'
85      )
86 AND 
87      T.request_at BETWEEN '2019-10-01' AND '2019-10-03';
  • 相关阅读:
    ThinkPHP讲解（一）框架基础
    smarty简单介绍
    留言板
    文件系统处理
    文件上传（带有预览模式）
    文件上传（无预览模式版）
    注册、登陆、审核练习
    session讲解（二）——商城购物车练习
    session讲解（一）——登录网页练习
    P6216 回文匹配
  • 原文地址：https://www.cnblogs.com/shui68home/p/13519154.html
Copyright © 2020-2023  润新知