【SQL】每分钟在线用户数-春马与夏

相关推荐
评论共1条
欢迎您留下宝贵的见解！
提交
只看作者
蹲在墙角哭
0

select
   cur_minute,
   count(user_id) online_num
from
  (
    select
      min_time + pos as cur_minute
    from
      (
        select
          min(minute_time) min_time,
          max(minute_time) max_time
        from
          (
            select
              user_id,
              cast(date_format(online_time, 'mm') as int) minute_time
            from
              (
                select
                  1 user_id,
                  '2024-01-01 01:01:01' online_time
                union all
                select
                  1 user_id,
                  '2024-01-02 01:01:02' online_time
                union all
                select
                  1 user_id,
                  '2024-01-03 01:02:01' online_time
                union all
                select
                  1 user_id,
                  '2024-01-05 01:03:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-02 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-05 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-06 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-07 01:02:01' online_time
                union all
                select
                  3 user_id,
                  '2024-01-01 01:05:01' online_time
              ) tmp
            group by
              user_id,
              minute_time
          ) t1
      ) t2
      lateral view posexplode(split(space(max_time - min_time), '')) t3 as pos,
      val
  ) t4
  left join (
    select
      user_id,
      cast(date_format(online_time, 'mm') as int) minute_time
    from
      (
        select
          1 user_id,
          '2024-01-01 01:01:01' online_time
        union all
        select
          1 user_id,
          '2024-01-02 01:01:02' online_time
        union all
        select
          1 user_id,
          '2024-01-03 01:02:01' online_time
        union all
        select
          1 user_id,
          '2024-01-05 01:03:01' online_time
        union all
        select
          2 user_id,
          '2024-01-02 01:01:01' online_time
        union all
        select
          2 user_id,
          '2024-01-05 01:01:01' online_time
        union all
        select
          2 user_id,
          '2024-01-06 01:01:01' online_time
        union all
        select
          2 user_id,
          '2024-01-07 01:02:01' online_time
        union all
        select
          3 user_id,
          '2024-01-01 01:05:01' online_time
      ) tmp
    group by
      user_id,
      minute_time
  ) t5 on t4.cur_minute=t5.minute_time group by  cur_minute
1个月前北京回复
【SQL】每分钟在线用户数

题目描述
