问题关键,弄清等车时间。这里有几个时间维度。首先是等车开始时间。这个是唯一的,event_time,
随后是取消时间,这里有好几种情况,情况一:一直无司机接单、超时、途中用户主动取消打车,记录打车结束时间,这里应该是end_time, 情况二:乘客上车前,乘客或司机点击取消订单,会将打车订单的finish_time填充为取消时间,
取消的时间段确定后,就可以采用编码的思路来解题了,也即在event_time构造新列全为1,在取消的时间段构造新列全为-1,之后合并后,使用窗口函数,对事件进行排序后累加。这样一遇到打车开始时间event_time就加一,遇到取消时间段就减一;
注意:同一时刻有人停止等车,也有人开始等车的话,等车人数先增加后减少。也即时间相同,先计算增加,然后再减少,那么增加的列必须排前边,sum(t1.num) over(partition by t1.city order by t1.time1, num desc) as wait_uv。新增一个排序字段即可
with temp as (select t1.city, sum(t1.num) over(partition by t1.city order by t1.time1, num desc) as wait_uv from ( select cr.city, cr.event_time time1, 1 as 'num' from tb_get_car_record cr union all select cr.city, if(co.fare is null, co.finish_time, co.start_time) time1, # ifnull(co.start_time, co.finish_time) time1, -1 as 'num' from tb_get_car_order co join tb_get_car_record cr on co.order_id=cr.order_id # union all # select cr.city, # cr.end_time time1, # -1 as 'num' # from tb_get_car_record cr # where cr.order_id is null ) t1 where t1.time1 between '2021-10-01' and '2021-11-01' ) select temp.city, max(temp.wait_uv) as max_wait_uv from temp group by temp.city order by max_wait_uv asc