这题和SQL8其实一个思路就是求瞬时的最大UV,但是要注意两个点
- 这里的in_time是event_time, out_time是start_time
- 要注意同一时间段同时uv的上升和下降
1 将tb_get_car_record及tb_get_car_order合并两次分别筛选event_time和start_time进行并集获取连续的时间段的用户行为。同时将
select city,uid,event_time as dt , 1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
UNION
select city,uid,start_time as dt, -1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
2 将各个时间段的瞬时人数进行计算并保证时间在2021年10月
select city,
sum(inc) over(partition by city order by dt Asc,inc desc) as uv
from
(
select city,uid,event_time as dt , 1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
UNION
select city,uid,start_time as dt, -1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
) temp where date_format(dt,'%Y-%m') = '2021-10'
3 按照城市分组,再按照最大等车人数升序排序,相同时按城市升序排序。
select city,
max(uv) max_wait_uv
from (
select city,
sum(inc) over(partition by city order by dt Asc,inc desc) as uv
from
(
select city,uid,event_time as dt , 1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
UNION
select city,uid,start_time as dt, -1 inc
from tb_get_car_record
inner join tb_get_car_order
using(order_id,uid)
) temp where date_format(dt,'%Y-%m') = '2021-10'
) temp2
group by city
order by max_wait_uv,city