SELECT city, MAX(current_max) as max_wait_uv
FROM (
SELECT city, SUM(wait_uv) over(wd_city_date) as current_max
FROM (
SELECT city, event_time as at_time, 1 as wait_uv FROM tb_get_car_record
UNION ALL
SELECT city, COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv
FROM tb_get_car_record
LEFT JOIN tb_get_car_order USING(order_id)
) as t_uv_at_time
WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10"
WINDOW wd_city_date as (PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC)
) as t_city_cur_max
GROUP BY city
ORDER BY max_wait_uv, city;
最内层的子查询创建了一个临时表
t_uv_at_time
,其中包含了所有事件时间(无论是请求车辆还是释放车辆)以及一个“wait_uv”值,请求时为+1,释放时为-1。这是通过在两个SELECT语句之间使用UNION ALL来实现的:
- 第一个SELECT语句从
tb_get_car_record
表中选择城市和event_time,并分配一个wait_uv值为1。- 第二个SELECT语句从
tb_get_car_record
和tb_get_car_order
表(假设它们通过order_id相关)中选择城市和start_time、finish_time、end_time中最早的非空时间,并分配一个wait_uv值为-1。WHERE子句过滤记录,只包括2021年10月的事件时间。
WINDOW子句定义了一个窗口函数
wd_city_date
,它按城市和事件时间的日期对数据进行分区,并按event_time和wait_uv降序排序记录。这允许SUM函数计算每个城市和日期的累计等待时间。外层的CTE
t_city_cur_max
然后计算每个城市的最大累计等待时间,通过按城市分组记录,并按最大累计等待时间和城市名称排序。最后,主SELECT语句从
t_city_cur_max
CTE中选择城市和最大累计等待时间(别名为max_wait_uv),按城市分组记录,并按最大累计等待时间和城市名称排序。