题目
- 行程和用户
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的。
扩展
这题本身就已经有点麻烦了,没有太多扩展的想法。