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

题目

  1. 行程和用户
    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的。
在这里插入图片描述

扩展

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值