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

select city,max(uv) as max_wait_uv
from(select city,t,
    sum(rn) over(partition by city,date(t) order by t,rn desc) as uv
    from(select city,event_time as t,1 as rn from tb_get_car_record
        union all 
        select a.city,
        ifnull(ifnull(b.start_time,b.finish_time),a.end_time) as t,
        -1 as rn
        from tb_get_car_record a 
        left join tb_get_car_order b on a.order_id=b.order_id
        )c 
    )d 
where t like '2021-10%'
group by 1
order by 2,1

【mysql】

知识点：

1、等车人数，先增加后减少

使用union all将增加和减少的记录合并，并增加rn区分（1为增加，-1为减少）

2、各城市的单日最大同时等车人数，先增加后减少

窗口函数sum(rn) over(partition by city,date(t) order by t,rn desc)

即按照city，时间t的日期部分，进行分组，然后按照时间t升序，再按照rn降序

即按照城市，单日进行分组，按照时间先后，同时间则先增加后减少，计算sum人数

最后按照city取最大值