1、分析题目:统计各个城市在2021年10月期间,单日中最大的同时等车人数。 ①单日、同时等车人数、最大值:计算瞬时人数变化,然后找出最多人数的时刻。 ②2021年10月 2、实操: ③最后,挑选出人数最大的数据即可 select t4.city, max(sum_num) as max_wait_uv from ( ②在此基础上,按照city进行分组统计,利用窗口函数反应每个时刻的汇总人数情况(进行“2020-10”的条件筛选) select t3.city, sum(start_num) over(partition by t3.city order by uv_time,start_num desc) as sum_num from ( ① 等车指从“开始打车”起,直到“取消打车 / 取消等待 / 上车前”的这段时间里用户的状态: (0)开始打车+1:event_time(原因:1个用户有且只有1个event_time) (1)取消打车-1(用户下单、但司机未接单👉用户提交打车请求后,在用户打车记录表生成一条打车记录,订单号order_id设为null + 若一直无司机接单、超时或中途用户主动取消打车,则记录打车结束时间👉order_id is null的情况下,存在end_time); (2)取消等待-1(司机接单、用户在上车前取消订单👉若乘客上车前,乘客或司机点击取消订单,会将打车订单表对应订单的订单完成时间-finish_time填充为取消时间,其余字段设为null👉当mileage/fare/grade is null时,存在finish_time); (3)上车前-1(用户上车👉当司机接上乘客时,填充打车订单表中该订单的上车时间start_time👉存在start_time) 拔高:(2)+(3)= ifnull(start_time,finish_time) = 如果start_time为空,则返回finish_time;如果start_time非空,则返回start_time本身👉 select city, event_time as uv_time, 1 as start_num from tb_get_car_record union all select city, end_time as uv_time, -1 as start_num from tb_get_car_record where order_id is null union all select city, ifnull(start_time,finish_time) as uv_time, -1 as start_num from tb_get_car_record as t1 left join tb_get_car_order as t2 on t1.order_id = t2.order_id ) as t3 where date_format(t3.uv_time,'%Y%m') = '202110' ) as t4 group by 1 order by 2,1