leetcode解题小记262. 行程和用户

题目

262. 行程和用户
     SQL架构
     Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

±—±----------±----------±--------±-------------------±---------+
| 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|
±—±----------±----------±--------±-------------------±---------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘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 |
±---------±-------±-------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

±-----------±------------------+
| Day | Cancellation Rate |
±-----------±------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
±-----------±------------------+

思路

看最后的输出是根据日期聚合，考虑到整体结构应该是结果字段带sum或者count等求比率的。因此以带date字段的trips表为基表。
然后看取消率的计算公式，分两个部分，一个是筛选出“非禁止用户”，一个是筛选出“取消的订单”。第一个筛选条件是，根据users表里面，banned为No的用户，然后分别根据role字段用users_id去关联trips表中的client_id或者driver_id。
最后把筛选过后的表中非取消的订单按日期分组计数，除以对应日期的总订单数，得到最后的答案。

编程

日期筛选

这里的示例代码日期列不是日期格式而是字符串格式。所以要把对应的列先to_date(‘xxxx’, ‘yyyy-MM-dd’)再与2013-10-01等对比。
而且要注意，oracle的日期，如果是带上时间的，那时间默认是0时0分0秒，所以between … and …的是包含左边界不包含右边界的。（不过，这道题里不涉及这个问题）
PS：说点题外话，在java的@DateTimeFormat中，要注意yyyy-MM-dd里面的MM必须大写，如果写了小写的mm，编译的时候不会报错，但运行的时候会因为接收不到实际值，而出问题。曾经有同事因为写错了这个，测试又没测到这个场景（前端页面的日期作为查询条件，有二十多个查询条件），导致了线上出了生产问题背锅背绩效。

算比率

两种思路去算计算

方法1

先算非取消订单数，再除以总的订单数（以已经筛选掉Banned的用户的表为基础），总订单数没什么好说的，而计算非取消订单数，用了case COLUMN when COND then XX else YY的方式，注意点oracle在count的时候，值为null的行是不计数的，所以非取消订单的计数如下，即完成的置为null，其他的各种状况置一个有效的值：

count(case  t.status when 'completed' then null else 1 end)

方法2

直接计算，用的avg函数，把status列中，已完成的置0，非完成的置1，这样直接求平均就能算出最终值，代码如下：

avg(case  t.status when 'completed' then 0 else 1 end)

保留两位小数

用round函数保留小数，round(字段，位数)，round是会做四舍五入的，这点和trunc直接截取的行为不一样。

代码

按上面的计算方法1的代码：

/* Write your PL/SQL query statement below */
select  Request_at as "day" ,
    round(count(case  t.status when 'completed' then null else 1 end)/ count(t.request_at), 2) as "cancellation rate"
from Trips t,
Users uc,
Users ud
where  t.Client_id = uc.Users_id 
and t.Driver_id = ud.Users_id 
and uc.Banned = 'No' 
and uc.role = 'client'
and ud.Banned = 'No' 
and ud.role = 'driver'
and to_date(Request_at, 'yyyy-MM-dd') between  date'2013-10-01' and date'2013-10-03'
group by Request_at
order by Request_at

改用avg做统计的代码

/* Write your PL/SQL query statement below */
select Request_at as "day",
    round(avg(case  t.status when 'completed' then 0 else 1 end),2) as "cancellation rate"
from Trips t,
Users uc,
Users ud
where  t.Client_id = uc.Users_id 
and t.Driver_id = ud.Users_id 
and uc.Banned = 'No' 
and uc.role = 'client'
and ud.Banned = 'No' 
and ud.role = 'driver'
and to_date(Request_at, 'yyyy-MM-dd') between  date'2013-10-01' and date'2013-10-03'
group by Request_at
order by Request_at

注意，其实方法1的效率更高，方法2的反而低一些。截图中631ms的是方法1的，859ms的是方法2的。

扩展

这题本身就已经有点麻烦了，没有太多扩展的想法。