学习记录571@MySQL查询取消率

表结构

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;

不要忘了，很多股东希望在下跌时把股票卖掉，但不愿意在低于顶部三四个点的价位卖出。这种投机者总是发誓说，下次再有反弹，他们一定卖出。于是在股价上涨时他们挂出卖单，当看到股价走势改变，心态就又发生了变化。当然，总是有一些力求稳健的短线“快枪手”会在获利时卖出，对他们而言，只有到手的利润才是真正的利润。