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

#需求：统计各个城市在2021年10月期间，单日中最大的同时等车人数
#输出：city、max_wait_uv（最大单日同时等车人数）
#要求：1、时间范围为2021-10；
#2、同时等车时间范围为event_time-start_time或者到取消订单的时间；
#3、输出按max_wait_uv升序，相同时按city升序
#4、同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少(瞬时)
#解决思路，参考牛客博主盐咸咸的文章，她的配有图文，更加清晰明了
#计算瞬时在线用户数类型的需求，重要
#将用户进入等车时间计为1，离开等车时间计为-1
#用户进入等车瞬时：event_time
#用户离开等车瞬时有3类情况
#1、司机接单前取消订单，则表内不会订单，order_id=null，记录打车结束时间end_time
#2、司机接单后取消订单，则表内没有上车时间，start_time=null，记录订单完成时间finish_time
#3、正常完成订单，记录结束等车开始上车时间start_time
#2和3可以用ifnull合并，ifnull(start_time,finish_time)；start_time不为null则使用start_time值，为null则返回finish_time
#即，进入等车瞬时为1的有一种状态，离开等车瞬时为-1的有两种状态，分别建立查询，union关联
with t1 as(
  select city,sum(tt2)over(partition by city order by tt1,tt2 desc) ck1
  #tt1为进入/离开等车状态的时间；tt2为对tt1时间的瞬时打标，是等车时间标为1，是离开时间标为-1
  #这里的开窗函数，通过sum(tt2)得到每个城市的瞬时情况
  from(
    select city,event_time tt1,1 tt2 from tb_get_car_record
    #用户进入等车时间
    union all
    select city,event_time tt1,-1 tt2 from tb_get_car_record where order_id is null
    #接单前取消订单结束等车
    union all
    select city,ifnull(start_time,finish_time) tt1,-1 tt2
    from tb_get_car_order left join tb_get_car_record using(order_id)
    #接单前取消订单结束等车、正常完成订单两种情况，用ifnull统一
  ) t2
  where date_format(tt1,'%Y-%m')='2021-10'
)
select city,max(ck1) max_wait_uv
#加上之后的group by，得到每个city的最大瞬时数
from t1
group by city
order by max_wait_uv,city