番禺网站制作设计,2017网站开发兼职,wordpress 插件 后门,wordpress取消邮箱验证描述#xff1a;262. 行程和用户 - 力扣#xff08;LeetCode#xff09; 取消率 的计算方式如下#xff1a;(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。 编写解决方案找出 2013-10-01 至 2013-10-03 期间非禁止…描述262. 行程和用户 - 力扣LeetCode 取消率 的计算方式如下(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。 编写解决方案找出 2013-10-01 至 2013-10-03 期间非禁止用户乘客和司机都必须未被禁止的取消率。非禁止用户即 banned 为 No 的用户禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。 返回结果表中的数据 无顺序要求 。 输入
Trips 表
--------------------------------------------------------------------
| id | client_id | driver_id | city_id | status | request_at |
--------------------------------------------------------------------
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
--------------------------------------------------------------------
Users 表
--------------------------
| users_id | banned | role |
--------------------------
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
--------------------------
输出
-------------------------------
| Day | Cancellation Rate |
-------------------------------
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
------------------------------- 解释
2013-10-01- 共有 4 条请求其中 2 条取消。- 然而id2 的请求是由禁止用户user_id2发出的所以计算时应当忽略它。- 因此总共有 3 条非禁止请求参与计算其中 1 条取消。- 取消率为 (1 / 3) 0.33
2013-10-02- 共有 3 条请求其中 0 条取消。- 然而id6 的请求是由禁止用户发出的所以计算时应当忽略它。- 因此总共有 2 条非禁止请求参与计算其中 0 条取消。- 取消率为 (0 / 2) 0.00
2013-10-03- 共有 3 条请求其中 1 条取消。- 然而id8 的请求是由禁止用户发出的所以计算时应当忽略它。- 因此总共有 2 条非禁止请求参与计算其中 1 条取消。- 取消率为 (1 / 2) 0.50 数据准备 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)) Create table If Not Exists Users (users_id int, banned varchar(50), role ENUM(client, driver, partner)) Truncate table Trips insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (1, 1, 10, 1, completed, 2013-10-01) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (2, 2, 11, 1, cancelled_by_driver, 2013-10-01) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (3, 3, 12, 6, completed, 2013-10-01) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (4, 4, 13, 6, cancelled_by_client, 2013-10-01) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (5, 1, 10, 1, completed, 2013-10-02) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (6, 2, 11, 6, completed, 2013-10-02) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (7, 3, 12, 6, completed, 2013-10-02) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (8, 2, 12, 12, completed, 2013-10-03) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (9, 3, 10, 12, completed, 2013-10-03) insert into Trips (id, client_id, driver_id, city_id, status, request_at) values (10, 4, 13, 12, cancelled_by_driver, 2013-10-03) Truncate table Users insert into Users (users_id, banned, role) values (1, No, client) insert into Users (users_id, banned, role) values (2, Yes, client) insert into Users (users_id, banned, role) values (3, No, client) insert into Users (users_id, banned, role) values (4, No, client) insert into Users (users_id, banned, role) values (10, No, driver) insert into Users (users_id, banned, role) values (11, No, driver) insert into Users (users_id, banned, role) values (12, No, driver) insert into Users (users_id, banned, role) values (13, No, driver) 分析 ①观察trip表需要client_id,driver_id而这两个id在users表中不妨分解一下users表 分解为只有client_id 数据的表t1 和只有driver_id数据的表t2 select * from users where role client select * from users where role driver ②连接三张表并进行相应的筛选找到clent和driver的banned都为no的以及相应日期的数据 select request_at,t1.users_id uid,t1.banned t1b,t2.users_id did,t2.banned t2b,status
from (select * from users where role client) t1,(select * from users where role driver) t2,trips
where Trips.client_id t1.users_idand Trips.driver_id t2.users_idand request_at between 2013-10-01 and 2013-10-03and t1.banned noand t2.banned no ③然后通过对日期分类求出取消率 select request_at,round((count(if(status !completed,1,null))/(count(1)) ),2)as Cancellation Ratefrom t1
group by request_at; 代码
with t1 as (
select request_at,t1.users_id uid,t1.banned t1b,t2.users_id did,t2.banned t2b,status
from (select * from users where role client) t1,(select * from users where role driver) t2,trips
where Trips.client_id t1.users_idand Trips.driver_id t2.users_idand request_at between 2013-10-01 and 2013-10-03and t1.banned noand t2.banned no)
select request_at,round((count(if(status !completed,1,null))/(count(1)) ),2)as Cancellation Ratefrom t1
group by request_at;
总结 分解users表之后再进行连接会使题目迎刃而解
