Trips and Users --Hard

Trips表 status是枚举类型

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

User表 Role是枚举类型

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

求出在2013年10月1日到2013年10月3日，未被禁止用户的订单取消率

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

MySQL：

SQL语句

SELECT Request_at as Day,
round(sum(if (status!='completed',1,0))/count(*),2) CancellationRate
FROM Trips as t, Users as u
WHERE t.Request_at BETWEEN'2013-10-1' AND '2013-10-3'
AND t.Client_Id=u.User_Id
AN Du.Banned='No'
AND u.Role='Client'
GROUP BY t.request_at;

或者(网上的答案)

SELECT Trips.Request_at Day,
       round(sum(if(status != 'completed', 1, 0)) / sum(1), 2) 'Cancellation Rate'
FROM Trips
JOIN Users
  ON Trips.Client_Id = Users.Users_Id
WHERE Users.Banned = 'No' 
  AND Trips.Request_at between '2013-10-01' AND '2013-10-03'   
GROUP BY Trips.Request_at 

round()：

SELECT ROUND(column_name,decimals) FROM table_name

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

sum()返回数值列的总额

SELECT SUM(column_name) FROM table_name

count()返回指定匹配函数的值(NULL 不计入)

SELECT COUNT(column_name) FROM table_name

count(*)返回表中所有行数

group by 结合合计函数(例如sum())使用，对一个或多个结果集分组

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate