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

# 每个城市，每天内的订单按时间从小到大排序
#
# 开始打车的时间点为1
# 取消打车、取消等待、上车的时间点分别为-1

select city,
       max(wait_uv) max_wait_uv
from (
         SELECT time,
                city,
                SUM(人数变化) OVER (ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) wait_uv
         FROM ( # 开始打车的时间点
                  SELECT city,
                         event_time time,
                         1          '人数变化'
                  FROM tb_get_car_record
                  UNION ALL
                  # 未成功打车并停止打车的事件点
                  SELECT city,
                         end_time time,
                         -1       '人数变化'
                  FROM tb_get_car_record
                  WHERE order_id IS NULL
                  UNION ALL
                  #成功打到车但取消等车的时间点
                  SELECT city,
                         finish_time time,
                         -1          '人数变化'
                  FROM tb_get_car_order
                           JOIN tb_get_car_record USING (order_id)
                  WHERE start_time IS NULL
                  UNION ALL
                  # 成功打到车并上车的时间点
                  SELECT city,
                         start_time time,
                         -1         '人数变化'
                  FROM tb_get_car_order
                           JOIN tb_get_car_record USING (order_id)
                  WHERE start_time IS NOT NULL
                  ORDER BY time ASC, 人数变化 DESC
              ) table_1
     ) table_2
where substr(time,1,7)= '2021-10'
group by city
ORDER BY max_wait_uv