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

各城市最大同时等车人数

http://www.nowcoder.com/practice/f301eccab83c42ab8dab80f28a1eef98

各城市最大同时等车人数

明确题意:

统计各个城市在2021年10月期间,单天里最大的同时等车人数。结果按最大等车人数升序排序,相同时按城市升序排序。

等车指从开始打车起,直到取消打车或取消等待或上车止的这段时间里用户的状态。如果同一时刻有人停止等车,有人开始等车,等车人数记作先增加后减少。


问题分解:

  • 计算截止当前时刻最大在等车人数(生成子表t_city_cur_max):
    • 计算每个时刻人数变化量(生成子表t_uv_at_time):
      • 记录等车人数增加(有人开始打车):event_time as at_time, 1 as wait_uv
      • 记录等车人数减少(上车了、打到车取消、没打到车取消):
        • 关联打车记录表和订单表:tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)
        • 减少:COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv
      • 合并上述记录:UNION ALL
    • 筛选时间窗:WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10"
    • 定义按城市和日期分区按时刻排序的窗口(先增加后减少,所以uv倒排):WINDOW wd_city_date as (PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC)
    • 计算当前最大等车人数:SUM(wait_uv) over(wd_city_date) as current_max
  • 按城市分组:GROUP BY city
  • 计算各城市最大等待人数:MAX(current_max) as max_wait_uv

细节问题:

  • 表头重命名:as
  • 按最大等车人数、城市升序排序:ORDER BY max_wait_uv, city

完整代码:

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;
SQL大厂真题 文章被收录于专栏

大厂真题手把手教你怎么解~

全部评论
求问为啥最后只用city来group by呀,求各城市单日max,应该要用city和日期来group by ?
1 回复 分享
发布于 2022-03-22 20:21
为啥我用的union不行,上海最大人数为2, 用题主的union all 上海最大人数为3 我想不通这两个子表还能有完全重复的值吗
点赞 回复 分享
发布于 2022-08-12 19:55
max()over() 是根据城市和单日最大数分组,获取城市最大等待的人数 再用group by city 分组,获取城市最大等待数,
点赞 回复 分享
发布于 2022-06-10 15:54

相关推荐

东孝子_强东我偶像:你怎么当孝子都和我时间一样😭
点赞 评论 收藏
分享
评论
7
3
分享

创作者周榜

更多
牛客网
牛客企业服务