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

# 求单日最大同时等车人数，同一时间有增有减，先增后减
# 前面有一道题也是这样

# 令开始打车为1，取消打车或上车时为-1。同时筛选符合日期的记录
# 要注意的是开始打车是指event_time，而取消打车是finish_time存在而start_time不存在，上车是start_time存在。采用条件选择语句新增一个字段 
# 再使用union all组合
# 分城市求，在窗口函数分页排序时要先把1排到前面，也就是根据这个新增的字段正序(想说的意思是先1后-1，应该是倒序，正序的话就是先减后增了）
#

#临时表0，查询城市、开始时间、结束时间
# 结束时间存在两种情况，一种是取消，一种是上车，应该分情况
WITH temp_0 AS(
    SELECT city, event_time, 
            CASE WHEN fare IS NULL THEN finish_time ELSE start_time END end_time
    FROM tb_get_car_record a
    JOIN tb_get_car_order b USING(order_id)
    WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-31'
    ),
	# 临时表1，将两个日期字段堆叠在一起，并对开始时间的记录在右边新增一个字段，值都为1；同理，对结束时间赋值-1
    temp_1 AS(
    SELECT city, event_time _t, 1 status
    FROM temp_0
    UNION ALL
    SELECT city, end_time _t, -1 status
    FROM temp_0
    )

# 主查询，查询城市，最大等待人数
SELECT city, MAX(wait_u) max_wait_uv
FROM (
	# 子查询，还需要先求每个城市下每个时间段的等待人数，这里采用聚合开窗函数，针对城市开窗，以时间正序和状态倒序排列，这样是的从最开始的日期到最后，先增后减的瞬时等待人数
    SELECT city, SUM(status) OVER(PARTITION BY city ORDER BY _t,status DESC) wait_u
    FROM temp_1
    ) a
GROUP BY city
ORDER BY max_wait_uv, city

虽然是单日，但是我们求的是瞬时的，比其粒度更细，因此求得的最大也是单日的最大。