我正在解決以下硬 Leetcode SQL 問題。
問題鏈接：https : //leetcode.com/problems/trips-and-users/
問題：
------------- ----------
| Column Name | Type |
------------- ----------
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | date |
------------- ----------
id is the primary key for this table.
The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are foreign keys to the users_id at the Users table.
Status is an ENUM type of ('completed', 'cancelled_by_driver', 'cancelled_by_client').
圖片1
------------- ----------
| Column Name | Type |
------------- ----------
| users_id | int |
| banned | enum |
| role | enum |
------------- ----------
users_id is the primary key for this table.
The table holds all users. Each user has a unique users_id, and role is an ENUM type of ('client', 'driver', 'partner').
banned is an ENUM type of ('Yes', 'No').
圖2
取消率的計算方法是將取消（由客戶端或驅動程式）與未禁止用戶的請求數除以當天未禁止用戶的請求總數。
寫一個 SQL 查詢，查找“2013-10-01”和“2013-10-03”之間每天有未禁止用戶（客戶端和驅動程式都不能被禁止）的請求取消率。將取消率取整至小數點后兩位。
以任意順序回傳結果表。
查詢結果格式如下例。
Trips table:
---- ----------- ----------- --------- --------------------- ------------
| 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 |
---- ----------- ----------- --------- --------------------- ------------
圖3
Users table:
---------- -------- --------
| 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 |
---------- -------- --------
圖4
Output:
------------ -------------------
| Day | Cancellation Rate |
------------ -------------------
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
------------ -------------------
圖5
這是我的代碼：
WITH Requests_Cancelled AS (
SELECT Trips.client_id as ID, Trips.request_at as Day, COUNT(*) as cancelled_count
FROM Trips
INNER JOIN Users ON
Trips.client_id = Users.users_id
WHERE Users.banned = "No" AND Users.role = "client" AND Trips.status = "cancelled_by_client"
GROUP BY Trips.request_at
UNION
SELECT Trips.driver_id as ID, Trips.request_at as Day, COUNT(*) as cancelled_count
FROM Trips
INNER JOIN Users ON
Trips.driver_id = Users.users_id
WHERE Users.banned = "No" AND Users.role = "driver" AND Trips.status = "cancelled_by_driver"
GROUP BY Trips.request_at
),
Requests_Total AS (
SELECT Trips.client_id as ID, Trips.request_at as Day, COUNT(*) as total_count
FROM Trips
INNER JOIN Users ON
Trips.client_id = Users.users_id
WHERE Users.banned = "No" AND Users.role = "client"
GROUP BY Trips.request_at
UNION
SELECT Trips.driver_id as ID, Trips.request_at as Day, COUNT(*) as total_count
FROM Trips
INNER JOIN Users ON
Trips.driver_Id = Users.users_id
WHERE Users.banned = "No" AND Users.role = "driver"
GROUP BY Trips.request_at
)
SELECT Requests_Total.Day, IFNULL(MAX(ROUND(Requests_Cancelled.cancelled_count/Requests_Total.total_count, 2)), 0) as 'Cancellation Rate'
FROM Requests_Cancelled
RIGHT JOIN Requests_Total ON
Requests_Cancelled.Day = Requests_Total.Day
GROUP BY Requests_Total.Day
ORDER BY Requests_Total.Day ASC;
代碼通過了下面的第一個測驗用例：
輸入： {"headers": {"Trips": ["id", "client_id", "driver_id", "city_id", "status", "request_at"], "Users": ["users_id", "banned", "role"]}, "rows": {"Trips": [["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": [["1", "No", "client"], ["2", "Yes", "client"], ["3", "No", "client"], ["4", "No", "client"], ["10", "No", "driver"], ["11", "No", "driver"], ["12", "No", "driver"], ["13", "No", "driver"]]}}
Output: {"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.00], ["2013-10-03", 0.50]]}
Expected: {"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.00], ["2013-10-03", 0.50]]}
But does not pass the second testcase:
Input: {"headers": {"Trips": ["id", "client_id", "driver_id", "city_id", "status", "request_at"], "Users": ["users_id", "banned", "role"]}, "rows": {"Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]], "Users": [["1", "No", "client"], ["10", "No", "driver"]]}}
Output: {"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-04", 1.00]]}
Expected: {"headers":["Day","Cancellation Rate"],"values":[]}
我不明白為什么在第二個測驗用例中需要 NULL 值。
uj5u.com熱心網友回復：
啊，我明白你在做什么了。使用相同的思路，但切換到使用 request_date 而不是客戶端 ID。
取消預訂
select request_at, count(*) as cancels, 0 as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.status in ('cancelled_by_driver', 'cancelled_by_client')
and t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
獲取您的乘車請求
select request_at, 0 as cancels, count(*) as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
將他們聯合起來
select request_at, count(*) as cancels, 0 as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.status in ('cancelled_by_driver', 'cancelled_by_client')
and t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
union all
select request_at, 0 as cancels, count(*) as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
現在，對于每一天，您都有一行取消計數和零請求計數。對于每一天，您有一行取消計數和有效請求計數為零。
最后結果
select request_at as "Day",
round(coalesce(sum(cancels), 0)/coalesce(sum(requests), 0)/1.0, 2) as "Cancellation Rate"
from
(
select request_at, count(*) as cancels, 0 as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.status in ('cancelled_by_driver', 'cancelled_by_client')
and t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
union all
select request_at, 0 as cancels, count(*) as requests
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
) main
group by request_at
那會給你你想要的。
這可能會更快，因為您在一個查詢中每天都會收到取消，而在另一個查詢中每天都會收到請求。對于您的最終結果，您不必再進行任何連接。
例子
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2b5dc7262d5c7839ddb39ab02e365821
Compact version based on this
select request_at as "Day",
round(
coalesce(count( if(t.status != 'completed', 1.0, null) ), 0.0)
/
coalesce(count(*), 0.0)
, 2) as "Cancellation Rate"
from trips t
join users uc on t.client_id = uc.users_id and 'No' = uc.banned and 'client' = uc.role
join users ud on t.driver_id = ud.users_id and 'No' = ud.banned and 'driver' = ud.role
where t.request_at between '2013-10-01' and '2013-10-03'
group by request_at
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a223073878fab69b3859f7853609844
Why does your code fail a test?
- "Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]]
- "Users": [["1", "No", "client"], ["10", "No", "driver"]]
This test fails because your code does not have and request_at between '2013-10-01' and '2013-10-03' in the where clause. Here's an example you can review: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=58f638d2fccd9a2c4429c345c9dc43cb
Even with the correction to your code as above, another case will fail. Why?
- {"Trips": [["1111", "1", "10", "1", "completed", "2013-10-01"]],
- “用戶”：[[“1”，“是”，“客戶端”]，[“10”，“否”，“驅動程式”]]
此案例接下來將失敗，因為您Requests_Total從未禁止的客戶那里旅行（忽略該行程中的司機可能被禁止UNION的事實）并與未禁止的司機一起旅行（忽略該行程中的客戶可能被禁止的事實。您不應該聯合他們。
SELECT trips.request_at as Day, COUNT(*) as total_count
FROM trips
INNER JOIN users ON
trips.client_id = users.users_id
WHERE users.banned = "No" AND users.role = "client"
and request_at between '2013-10-01' and '2013-10-03'
GROUP BY trips.request_at
UNION
SELECT trips.request_at as Day, COUNT(*) as total_count
FROM trips
INNER JOIN users ON
trips.driver_Id = users.users_id
WHERE users.banned = "No" AND users.role = "driver"
and request_at between '2013-10-01' and '2013-10-03'
GROUP BY trips.request_at
解決方法是確保您在每次旅行時檢查司機和客戶是否都被取消了禁令。這就是我在代碼中所做的。
以下是您可以在閑暇時調整的代碼結果。https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=57e6d83db05a5cb4f99e715ccb133032
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/380026.html