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

又是花费大量时间的一道题！！！ 参考思路如下：

https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48

1、确定等车时间段是

event_time → start_time 这个阶段是等车  -- 这种情况是顺利完成订单的情况

event_time → finish_time -- 这种情况是中途取消订单的情况

2、编码+联立

用户下单时间点 编码为+1 即增加一人

select a.order_id,a.uid,a.city,a.event_time dt,1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10'

用户上车时间点 编码为-1 即减少一人

      select a.order_id,a.uid,a.city,b.start_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NOT NULL

用户或是司机取消订单时间点 编码为-1 即减少一人

      select a.order_id,a.uid,a.city,b.finish_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NULL

将以上三种情况编码之后用union all 联立 得到如下 ：

select a.order_id,a.uid,a.city,a.event_time dt,1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10'
	  union all
      select a.order_id,a.uid,a.city,b.start_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NOT NULL
      union all
      select a.order_id,a.uid,a.city,b.finish_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NULL
	  order by 1,4

3、用sum()窗口函数，统计按时间戳升序的观看人数变化情况： 正确代码：

sum(diff)over(partition by city,date(dt) order by dt,diff desc)

我的代码（错误方法）：

sum(diff)over(partition by city order by dt)

其实至今我都不理解。

最后，完整代码如下：

-- 请统计各个城市在2021年10月期间，单日中最大的同时等车人数。
-- event_time 下单时间
-- end_time 司机接单时间
-- order_time 司机接单时间
-- start_time 顺利接到乘客，乘客上车时间
-- finish_time null 乘客、司机取消订单时间 或者是 乘客下车时间
-- event_time-start_time 这个阶段是等车


select city,max(people) max_ppl
from(select city,dt,diff,
            sum(diff)over(partition by city,date(dt) order by dt,diff desc) people
     from
     (select a.order_id,a.uid,a.city,a.event_time dt,1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10'
	  union all
      select a.order_id,a.uid,a.city,b.start_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NOT NULL
      union all
      select a.order_id,a.uid,a.city,b.finish_time dt,-1 diff
      from tb_get_car_record a left join tb_get_car_order b on a.order_id=b.order_id
      where date_format(a.event_time,"%Y-%m")='2021-10' and start_time IS NULL
	  order by 1,4)a)b
group by city
order by max_ppl