表结构
Create table If Not Exists Trips (id int, client_id int, driver_id int, city_id int, status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), request_at varchar(50));
Create table If Not Exists Users (users_id int, banned varchar(50), role ENUM('client', 'driver', 'partner'));
Truncate table Trips;
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status, request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
Truncate table Users;
insert into Users (users_id, banned, role) values ('1', 'No', 'client');
insert into Users (users_id, banned, role) values ('2', 'Yes', 'client');
insert into Users (users_id, banned, role) values ('3', 'No', 'client');
insert into Users (users_id, banned, role) values ('4', 'No', 'client');
insert into Users (users_id, banned, role) values ('10', 'No', 'driver');
insert into Users (users_id, banned, role) values ('11', 'No', 'driver');
insert into Users (users_id, banned, role) values ('12', 'No', 'driver');
insert into Users (users_id, banned, role) values ('13', 'No', 'driver');
要求
取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
思路
核心就是取消率的计算，这是典型的分类型比率计算，也就是某种条件的记录条数和总条数的比率，这种类型的sql典型的方式就是使用sum+if，if就是判断条件，如果条件为真就记为1，如果条件为假就记为0，然后sum，就是条件为真后的记录条数，然后再比上总条数即可。
sql实现
因为要“被司机或乘客取消的非禁止用户生成的订单数量”，所以要连接两次users。
T.STATUS LIKE 'cancelled_by_%'表示被取消的条件。
SELECT
T.request_at AS `Day`,
ROUND( SUM( IF ( T.STATUS LIKE 'cancelled_by_%', 1, 0 ) ) / count( T.id ), 2 ) AS `Cancellation Rate`
FROM
Trips AS T
JOIN Users U1 ON ( T.client_id = U1.users_id AND U1.banned = 'No' )
JOIN Users U2 ON ( T.driver_id = U2.users_id AND U2.banned = 'No' )
WHERE
T.request_at BETWEEN '2013-10-01'
AND '2013-10-03'
GROUP BY
T.request_at;
不要忘了，很多股东希望在下跌时把股票卖掉，但不愿意在低于顶部三四个点的价位卖出。这种投机者总是发誓说，下次再有反弹，他们一定卖出。于是在股价上涨时他们挂出卖单，当看到股价走势改变，心态就又发生了变化。当然，总是有一些力求稳健的短线“快枪手”会在获利时卖出，对他们而言，只有到手的利润才是真正的利润。
