滴滴司机专题

此专题为【滴滴司机专题】，博主我是盲写~

本套题中大多数为中等题，少量简单题。

但限时一小时，对快速理解题意要求很高。

现有两个表，表1为司机基础信息表 drv，表2为订单基础信息表 od，字段的说明和部分展示见下：

表1 drv

表2 od

注：巴西（BR）时区比北京时间慢11小时；“五一” 期间是指北京时间 2021-05-01 到 2021-05-05。

1. 求 “五一” 期间北京地区完成订单量

select count(distinct order_id) as cnt
from od
where city_name='Beijing'
and order_status=5
and left(finish_time,10) between '2021-05-01' and '2021-05-05'

2. 求截至2021年5月1日北京地区注册司机总数（不算5月1日）

select count(distinct driver_id)
from drv
where city_name='Beijing'
and left(reg_time,10)<'2021-05-01'

3. 求北京地区所有激活司机“五一”期间每日人均完成订单量

思路：每天所有订单加起来，除以每天总人数。

select left(finish_time,10) as day,
    count(distinct order_id)/
    count(distinct drv.driver_id) as day_person_orders
from drv left join od
on drv.driver_id=od.driver_id
where drv.city_name='Beijing'
and is_regular=1
and order_status=5
and left(finish_time,10) between '2021-05-01' and '2021-05-05'
group by day

4. 求“五一”期间北京地区的日均 DAU（司机在当地时区的当日完成订单数 >=1 则该司机算当日活跃司机）

思路：活跃司机总数除以五一期间天数。

注意每天对司机id去重，因为每天最多计数1次。

with tmp as
(select left(finish_time,10) as day,
    count(distinct driver_id) as cnt
from od
where city_name='Beijing'
and order_status=5
and left(finish_time,10) between '2021-05-01' and '2021-05-05'
group by day)

select sum(cnt)/count(day) as dau
from tmp

5. 求在巴西时间5月1日至5月5日巴西 Belo 城市快车和优享各自的日均完成订单量

思路：总订单量除以天数。

注：巴西时区比北京时间慢11个小时。

这里也可以用 case when 处理。

(select 'quick' as car_type,
    count(distinct order_id)/5 as day_orders
from od
where city_name='Belo'
and order_status=5
and finish_time between '2021-05-01 11:00:00' and '2021-05-06 10:59:59'
and product_id=1)
union all
(select 'good' as car_type,
    count(distinct order_id)/5 as day_orders
from od
where city_name='Belo'
and order_status=5
and finish_time between '2021-05-01 11:00:00' and '2021-05-06 10:59:59'
and product_id=2)

6. 求在巴西时间5月1日至5月5日巴西 Belo 城市连续两天活跃的司机数

思路：连续两天活跃，即连续两天有订单；若每天多单的话，只需要计一次。

注：巴西时区比北京时间慢11个小时。

# 过滤，去重：
with tmp as
(select distinct driver_id,
    left(finish_time,10) as date
from od
where city_name='Belo'
and order_status=5
and finish_time between '2021-05-01 11:00:00' and '2021-05-06 10:59:59')

#新建列：
,tmp2 as
(select driver_id,date,
    lag(date,1,0) over(partition by driver_id order by date) as front_1
from tmp)

# 连续两天有订单的司机id数
select count(distinct driver_id) as cnt
from tmp2
where datediff(date,front_1)=1

7. 求北京地区在4月26日至5月2日注册并激活且在5月3日至5月9日完成首单的司机数量（要求使用窗口函数）。

# 司机表过滤
with tmp as
(select distinct driver_id
from drv
where city_name='Beijing'
and reg_time between '2021-04-26' and '2021-05-02'
and is_regular=1)

# od表过滤，先过滤出每个司机完成的首单，再过滤出日期
,tmp3 as
select driver_id,finish_time as min_finish_time
from
    (select driver_id,finish_time,
        rank() over(partition by driver_id order by finish_time) as rnk
    from od
    where order_status=5) as tmp2
where rnk=1

,tmp4 as
(select distinct driver_id,left(min_finish_time,10) as min_finish_date
from tmp3
where left(min_finish_time,10) between '2021-05-03' and '2021-05-09')

# 满足两个条件的司机数量
select count(distinct tmp.driver_id) as cnt
from tmp,tmp4
where tmp.driver_id=tmp4.driver_id

-END