我正在解決以下硬 Leetcode SQL 問題。
問題鏈接：https ://leetcode.com/problems/trips-and-users/ （可以直接看解法，理解問題）
題：
行程表：
---- ----------- ----------- --------- --------------------- ------------
| 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_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 |
------------ -------------------
這是我的代碼：
SELECT request_at,
count(*) c,
sum(case when status='cancelled_by_driver' or status='cancelled_by_client' then 1 else 0 end) s,
round(sum(case when status='cancelled_by_client' or status='cancelled_by_client' then 1 else 0 end)/count(*),2) as Cancellation_rate
FROM trips
WHERE
client_id not in (select users_id from users where banned = 'Yes')
AND
driver_id not in (select users_id from users where banned = 'Yes')
GROUP BY request_at;
輸出是：
request_at | c | s | cancellation_rate
------------ --- --- -------------------
2013-10-01 | 3 | 1 | 0.00
2013-10-03 | 2 | 1 | 0.00
2013-10-02 | 2 | 0 | 0.00
當通過查看前面的列（s/c）很清楚它應該是 0.33、0.50、0.00 時，cancellation_rate 是 0.00 的情況如何。
uj5u.com熱心網友回復：
好訊息是你只是因為一個錯字而離開了。
在您的示例中，您正在使用cancelled_by_client or cancelled_by_client
round(sum(case when status='cancelled_by_client' or status='cancelled_by_client' then 1 else 0 end)/count(*),2) as Cancellation_rate
而不是：
.. when status='cancelled_by_driver' or status='cancelled_by_client' then ..
這將回傳：
| request_at | C | s | 取消率 |
|---|---|---|---|
| 2013-10-01 | 3 | 1 | 0.33 |
| 2013-10-02 | 2 | 0 | 0.00 |
| 2013-10-03 | 2 | 1 | 0.50 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/441836.html
標籤：sql PostgreSQL 和
上一篇：SQL：從多列的值中拆分行
下一篇：需要幫助從DB2獲取資料