【场景】：同时在线问题、连续时间

【分类】：聚合窗口函数、tag标记、合并行

分析思路

难点：

1.打车结束3种情况下的判断条件

2.考虑到可能有多个用户同时签入或者签出 使用union all 而不是 union

（1）统计在2021年10月期间，开始打车记录

开始打车记录 event_time

  • [条件]：date_format(event_time,'%Y%m') = '202110'

  • [使用]：开始打车 人数增加1 用tag = 1标记

（2）计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）

结束打车对应3种情况

1.取消打车: order_id is null end_time 2.取消订单: start_time is null finish_time 3.上车前：start_time is not null start_time

  • [条件]：date_format(event_time,'%Y%m') = '202110'

  • [使用]：结束打车 人数减少1 用tag = -1标记；case when

（3）合并开始打车和取消打车的记录，得到整条时间线上人数增减的记录

使用union all 是因为可能有多个用户同时签入或者签出

  • [使用]：union all

（4）统计单日同时等车人数

等车人数记作先增加后减少，所以要对 tag 降序

  • [条件]：date_format(event_time,'%Y%m') = '202110'

  • [使用]：sum(tag) over(partition by city order by time,tag desc)

（5）统计各个城市单日最大等车人数。结果按各城市最大等车人数升序排序，相同时按城市升序排序

按照 city 分组

  • [使用]：group by city；order by max_wait_uv,city

扩展

前往查看： MySQL 在线人数 场景分析

求解代码

方法一：

with子句

with
    main as(
        #统计在2021年10月期间，开始打车记录event_time
        select
            city,
            event_time as time,
            1 as tag
        from tb_get_car_record
        where date_format(event_time,'%Y%m') = '202110'
    )
    ,attr as(
        #统计在2021年10月期间，打车结束记录
        select
            city,
            (case
                when order_id is null then end_time
                when start_time is null then finish_time
                when start_time is not null then start_time
            end) as time,
            -1 as tag
        from tb_get_car_record
        left join tb_get_car_order using(order_id)
        where date_format(event_time,'%Y%m') = '202110'
    )
    ,temp as(
        #合并开始打车和取消打车的记录，得到整条时间线上人数增减的事件流
        #使用union all 是因为可能有多个用户同时签入或者签出
        (select *
        from main)
        union all
        (select *
        from attr)
        order by city,time
    )
    ,temp1 as(
        #统计单日同时等车人数，等车人数记作先增加后减少，所以要对 tag 降序
        select
            city,
            sum(tag) over(partition by city order by time,tag desc) as wait_uv
        from temp
    )
#统计各个城市单日最大等车人数。结果按各城市最大等车人数升序排序，相同时按城市升序排序
select
    city,
    max(wait_uv) as max_wait_uv
from temp1
group by city
order by max_wait_uv,city

方法二

from嵌套子查询

#统计各个城市单日最大等车人数。结果按各城市最大等车人数升序排序，相同时按城市升序排序
select
    city,
    max(wait_uv) as max_wait_uv
from(
    #统计单日同时等车人数，等车人数记作先增加后减少，所以要对 tag 降序
    select
        city,
        sum(tag) over(partition by city order by time,tag desc) as wait_uv
    from(
        #合并开始打车和取消打车的记录，得到整条时间线上人数增减的事件流
        #使用union all 是因为可能有多个用户同时签入或者签出
        (select *
        from(
            #统计在2021年10月期间，开始打车记录event_time
            select
                city,
                event_time as time,
                1 as tag
            from tb_get_car_record
            where date_format(event_time,'%Y%m') = '202110'
        ) main)
        union all
        (select *
        from(
            #统计在2021年10月期间，打车结束记录
            select
                city,
                (case
                    when order_id is null then end_time
                    when start_time is null then finish_time
                    when start_time is not null then start_time
                end) as time,
                -1 as tag
            from tb_get_car_record
            left join tb_get_car_order using(order_id)
            where date_format(event_time,'%Y%m') = '202110'
        ) attr)
        order by city,time
    ) temp
) temp1
group by city
order by max_wait_uv,city