题目大意
给定Trips表和Users表,trips记录每次打车订单的情况,users表示目前存在的用户信息,求10月1号到3号之间,非禁止的用户打车预订终止率
解题思路
首先肯定是将两张表进行连接,由于一切都是以client为中心的,因此这里使用client_id的左连接,然后统计当前天非禁止用户下单终止个数counter,同时将结果按照天数进行分组并统计每组的总订单数,二者相除就能满足最终题目需求。
总结
注意要先对连接后的表按照日期进行排序,同时在查表时要跳过那些不在区间范围的时间。
代码
SELECT request_at AS Day, round((max(counter)*1.0/count(request_at)),2) AS Cancellation_Rate
FROM(
SELECT id,client_id,status,request_at,banned,
@counter := if (@curDate=request_at AND status <> 'completed', @counter+1, 0) ,
@counter := if (@curDate<>request_at AND status <> 'completed', 1, @counter) AS counter,
@curDate := request_at AS curDate
FROM (SELECT * FROM Trips LEFT JOIN Users ON Trips.client_id = Users.users_id ORDER BY request_at) as tmp0,
(SELECT @counter := 0, @curDate := '') as parameters
WHERE banned <> 'Yes' AND request_at >= '2013-10-01' AND request_at <= '2013-10-03'
) AS tmp
GROUP BY request_at