题解 | #各城市最大同时等车人数#

# 这里可以先找出每一个订单的开始时间和结束时间，
# 然后将每个订单转变为两行数据，时间戳分别是开始时间和结束时间，并且还带上开始就是+1，结束就是-1，
# 然后按照时间顺序进行开窗sum计算，其中就会出现加减，那么必有一个最大值。
# 还有一个很重要的逻辑就是 结束时间的判断，这里应该是 先看乘车时间是否为空，如果不为就是结束等待时间，
# 如果乘车时间为空，那么再看finish_time是否为空，如果不是那就是结束时间，在最后才是end_time

with cte001 as (
    select t001.id,
       t001.city,
       event_time start_dt,
       if(start_time is not null ,start_time,if(finish_time is not null ,finish_time,end_time)) end_dt
from tb_get_car_record t001
left join  tb_get_car_order t002
    on t001.order_id = t002.order_id
    where date_format(event_time,'%Y-%m') = '2021-10'
    ),
cte002 as (
select id,
       city,
       start_dt as dt,
       1 as tag
from cte001
union all
select id,
       city,
       end_dt as dt,
       -1 as tag
from cte001
)
  ,
cte003 as (
    select city,
           dt,
           tag,
           sum(tag) over (partition by city,date_format(dt,'%Y-%m') order by dt rows unbounded preceding) cn
# sum(score) over (PARTITION by id order by score) as a2`：按照score排序，从起始行到当前行进行加总，与a3中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW不同的是，当score相同时，算相同排名，会一起加总。类似rank的概念。
# sum(score) over (PARTITION by id order by score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`：从起始行到当前行进行加总，与a2不同的是，当score相同时，排名不同，不会加总到当前行。类似row_number的概念。
    from cte002
)
select city,max(cn) as max_wait_uv
from cte003
group by city
order by max_wait_uv ;