LeetCode:Database 05.ストロークとユーザー

要求:SQL文を書いて「2013-10-01」から「2013-10-03」までの間、非禁止ユーザー(乗客も運転手も禁止されていない必要がある)のキャンセル率を調べる
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 |
+----+-----------+-----------+---------+---------------------+------------+


Id        。
               。        Id ，   Client_Id   Driver_Id   Users    Users_Id    。
Status               ，     (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

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 |
+----------+--------+--------+

Users_Id        。
         ，            Users_Id ，Role               ，      (‘client’, ‘driver’, ‘partner’) 。
Banned                  ，      (‘Yes’, ‘No’)。

クエリの結果:

+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33              |
| 2013-10-02 | 0.00              |
| 2013-10-03 | 0.50              |
+------------+-------------------+

分析:1.照会日は「2013-10-01」から「2013-10-03」であるため、フィルタ条件1は日付が1日-3日の間である.非禁止ユーザ情報はusersテーブルにあり、ユーザは乗客と運転手に分かれているためjoinが2回必要であり、フィルタ条件はbanned=‘no’3である.キャンセル率を求める必要があるため、私たちはすべてのユーザーとキャンセルユーザーの数を得るため、sumを使ってキャンセルユーザーの数を求めることができて、countはユーザーの総数を求めて、ifはキャンセルユーザーが0あるいは1であるかどうかを判断して、if用法:if(条件、trueの結果、falseの結果)4.結果は2桁保持されるのでround()関数を使用します

SELECT e.r1 AS 'Day',ROUND(e.c1/e.c2,2) AS 'Cancellation Rate'
FROM 
(SELECT d.request_at AS r1,SUM(IF(STATUS='completed',0,1)) AS c1,COUNT(*) AS c2
FROM
(
SELECT a.request_at,a.status,row_number() over(PARTITION BY a.request_at) AS r
FROM trips a
JOIN users b ON
b.users_id=a.client_id AND b.banned='no' AND a.request_at>='2013-10-01' AND a.request_at<='2013-10-03'
JOIN users c ON c.users_id=a.client_id AND c.banned='no' )d
GROUP BY request_at)e;