|
|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|
+----+-----------+-----------+---------+--------------------+----------+
TheUsers table holds all users. Each user has an unique Users_Id, and Role is anENUM type of (‘client’, ‘driver’, ‘partner’).
?
+----------+--------+--------+
|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 |
+----------+--------+--------+
Writea SQL query to find the cancellation rate of requests made by unbanned clientsbetween Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL queryshould return the following rows with the cancellation rate being rounded totwo decimal places.
?
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
|2013-10-01 | 0.33 |
|2013-10-02 | 0.00 |
|2013-10-03 | 0.50 |
+------------+-------------------+
建表及插入测试数据 (for oracle)
DROP TABLE Trips PUGRE; CREATE TABLE Trips( Id INT, Client_Id INT, Driver_Id INT, City_Id INT, Status CHAR(20), Request_at CHAR(20)); INSERT INTO Trips(Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ( 1,1,10,1,'completed','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' ); ? DROP TABLE Users PUGRE; CREATE TABLE Users( Users_Id INT, Banned CHAR(10), Role char(10)); INSERT INTO Users(Users_Id, Banned, Role) VALUES( 1,'No','client'),( 2,'Yes','client'),( 3,'No','client'),( 4,'No','client'),( 10,'No','driver'),( 11,'No','driver'),( 12,'No','driver'),( 13,'No','driver'); |
Oracle 初级版
select ta.Day Day, round(sum(ta.flag)/count(*),2) "Cancellation Rate" from ( select t1.status sta, t1.Request_at Day , case t1.status when 'completed' then 0 else 1 end as flag from Trips t1, Users u1 where t1.Client_Id = u1.Users_Id and u1.Banned = 'No' and t1.Request_at between '2013-10-01' and '2013-10-03') ta group by ta.Day order by ta.Day; |
Oracle 版本的查询 (works for mysql)
select t1.Request_at Day, round( sum( case t1.status when 'completed' then 0 else 1 end)/count(*),2) "Cancellation Rate" from Trips t1, Users u1 where t1.Client_Id = u1.Users_Id and u1.Banned = 'No' and t1.Request_at between '2013-10-01' and '2013-10-03' group by t1.Request_at order by t1.Request_at; |
比较
| Oracle |
mySQL |
| 支持PURGE |
不支持 PURGE |
| No ENUM type |
CREATE TABLE Y( Id INT, Client_Id INT, Driver_Id INT, City_Id INT, Status ENUM( 'completed', 'cancelled_by_driver', 'cancelled_by_client' ), Request_at DATE); |
知识点: ENUM（mysql）
| CREATE |
CREATE TABLE Trips( Id INT, Client_Id INT, Driver_Id INT, City_Id INT, Status ENUM( 'completed', 'cancelled_by_driver', 'cancelled_by_client' ), Request_at DATE); |
| INSERT用ENUM 的索引及值都可以。插入完成后，下面第一条记录的status值为“completed”。 |
INSERT INTO Trips(Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES |
|