题解 | #各城市最大同时等车人数#
各城市最大同时等车人数
http://www.nowcoder.com/practice/f301eccab83c42ab8dab80f28a1eef98
思路: 反向推导, 正向执行
反向推导
- 要输出各大城市单日的最大同时等车人数, 需要计算各大城市单日的同时等车人数
- 计算单日的同时等车人数, 可以拆解为, 单日的同一时间, 下单的用户数, 减去订单取消和上车的用户数(即结束等车的用户)
- 单日同一时间的下单用户数即record下单表中的同一时间(event_time开始计时)的用户数, 订单取消分两种情况, 一是司机接单前的取消, 二是司机接单后上车前的取消, 上车的用户数就是司机接单并且用户上车的乘客数量
正向执行
- 首先就下单用户, 取下订单用户和上车用户的数据形成一个总表, 为同时等车人数的计算做准备
- 其次是以城市和天为单位, 统计同一时间的等车人数
- 最后筛选出各个城市中单日最大同时等车人数
两个关键点
- 同时等车人数的计算逻辑: 下单时间为开始等车时间, 等车人数加1, 订单取消时间或者上车时间为结束等车时间, 等车人数减1, 在这个基础上进行计算
- 结束等车的几种情况, 以及结束等车时间的字段, 要根据题目所给出的数据注释和场景解释
select city,
max(user_cnt) as max_wait_uv
from (
select city,
sum(diff) over (partition by city, date(dt) order by dt, diff desc) as user_cnt
from (
select event_time as dt,
city,
1 as diff
from tb_get_car_record
where date_format(event_time, "%Y%m") = "202110"
union all
select end_time as dt,
city,
-1 as diff
from tb_get_car_record
where date_format(event_time, "%Y%m") = "202110"
and order_id is null
union all
select finish_time as dt,
city,
-1 as diff
from tb_get_car_order
join tb_get_car_record using(order_id)
where date_format(event_time, "%Y%m") = "202110"
and start_time is null
union all
select start_time as dt,
city,
-1 as diff
from tb_get_car_order
join tb_get_car_record using(order_id)
where date_format(event_time, "%Y%m") = "202110"
and start_time is not null
) as a
) as b
group by city
order by max_wait_uv,
city