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