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

with table1 as(
select
a.city,
date(a.event_time) as date_time,
a.event_time as waited_start_time,

case
    when  order_id is null then end_time
    when start_time is null then finish_time
    else start_time
end waited_end_time

from tb_get_car_record a left join tb_get_car_order b using(order_id)
where date_format(a.event_time, "%Y-%m") = '2021-10'
), status_table as(
    select city,
    date_time,
    waited_start_time as flow_time,1 status
    from table1
    union all
    select city,
    date_time,
    waited_end_time as flow_time,-1 status
    from table1
)


select
city,
max(flow_num)
from(
select
city,
sum(status) over(partition by city,date_time order by flow_time,status desc) as flow_num
from status_table) t1
group by city
order by max(flow_num),city

最大同时人数，老题目了不解释了

但是卡住的点是：应该要在over函数中对status进行降序排列，可以理解为窗口函数是重新对数据进行排序的，即使你传进来的表是已经order过的，over函数有自己的一套排序流程，因此应该在窗口函数中重新执行一次 status desc