Mysql 解法, 执行用时: 36ms, 内存消耗: 6396KB, 提交时间: 2022-01-26

with t1 as(
SELECT city,date(event_time) as dt,r.uid,event_time as time,1 as tag
from tb_get_car_record r
left join tb_get_car_order using(order_id)
where date_format(event_time,'%Y-%m')='2021-10'
union
SELECT city,date(event_time) as dt,r.uid,if(end_time=order_time,start_time,end_time)as time,-1 as tag
from tb_get_car_record r
left join tb_get_car_order using(order_id)
where date_format(event_time,'%Y-%m')='2021-10'),

t2 as(
SELECT *,sum(tag) over(PARTITION by city,dt order by time asc,tag desc) as wait_num
from t1)

SELECT city,max(wait_num) as max_wait_num
from t2
GROUP BY city,dt
ORDER BY 2,1

Mysql 解法, 执行用时: 36ms, 内存消耗: 6508KB, 提交时间: 2021-12-08

select
    city,
    max(cnt) max_wait_uv
from
    (select
        city,
        wait_time,
        sum(num)over(partition by city order by wait_time asc,num desc) cnt
    from
        (select
            city,
            event_time wait_time,
            1 num
        from
            tb_get_car_record
        where
             date(event_time) between '2021-10-01' and '2021-10-31'
        union all
        select
            b.city,
            coalesce(a.start_time,a.finish_time) wait_time,
            -1 num
        from
            tb_get_car_order a
        left join
            tb_get_car_record b
        on
            a.order_id = b.order_id
        where
             date(coalesce(a.start_time,a.finish_time)) between '2021-10-01' and '2021-10-31') m )n
group by
    city
order by
    max_wait_uv,
    city

Mysql 解法, 执行用时: 37ms, 内存消耗: 6392KB, 提交时间: 2022-01-03

SELECT city, MAX(sum_wait_num)
FROM(
	SELECT city, time, SUM(if_wait) OVER(PARTITION BY city, left(time, 10) ORDER BY time, if_wait DESC) sum_wait_num
	FROM(
	SELECT city, event_time time, 1 if_wait
	FROM tb_get_car_record
	UNION ALL
	SELECT city, IFNULL(start_time, finish_time) time, -1 if_wait
	FROM tb_get_car_order
	JOIN tb_get_car_record
	USING(order_id)
		)a
        )b
WHERE LEFT(time, 7) = '2021-10'
GROUP BY city
ORDER BY MAX(sum_wait_num), city

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-12-08

WITH tmp AS(
SELECT city, event_time dt, 1 diff #开始等待，人数+1
FROM tb_get_car_record
WHERE LEFT(event_time, 7) = '2021-10'
UNION all
SELECT city, o.start_time dt, -1 diff #顺利上车，人数-1
FROM tb_get_car_record r
LEFT JOIN tb_get_car_order o USING(order_id)
WHERE LEFT(o.start_time, 7) = '2021-10'
      AND
      start_time IS NOT NULL
UNION all
SELECT city, o.finish_time dt,  -1 diff #上车前取消，人数-1
FROM tb_get_car_record r
LEFT JOIN tb_get_car_order o USING(order_id)
WHERE LEFT(o.finish_time, 7) = '2021-10'
      AND
      start_time IS NULL
)
select city,
    max(wait_uv) as max_wait_uv
    from
(SELECT city,
    sum(diff) over(partition by city,date(dt) order by dt,diff desc) as wait_uv
    from tmp)a
    GROUP BY city
    order by max_wait_uv,city

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2022-01-22

SELECT city, MAX(wait_uv) as max_wait_uv
FROM (
    SELECT city, t
          , SUM(num_change) OVER (PARTITION BY city ORDER BY t, num_change DESC) as wait_uv
    FROM (
            SELECT city, event_time as t, 1 AS num_change
            FROM tb_get_car_record
            WHERE DATE_FORMAT(event_time, '%Y%m') = '202110'

            UNION ALL

            SELECT city, IFNULL(start_time, IFNULL(finish_time, order_time)) as t, -1 AS num_change
            FROM tb_get_car_record
            JOIN tb_get_car_order USING (order_id)
            WHERE DATE_FORMAT(event_time, '%Y%m') = '202110'
         ) time_num_changes
    ) num_waiting
GROUP BY 1
ORDER BY 2, 1