题目描述
有如下用户在线时间记录表，使用任意引擎SQL，得出每一分钟cur_minute的在线用户数online_num
结果第一行为表中最早的一分钟，最后一行为表中最晚的一分钟，可能会有一些分钟没有活跃用户，结果仍需展示且online_num为0
【SQL】每分钟在线用户数
有如下用户在线时间记录表，使用任意引擎SQL，得出每一分钟cur_minute的在线用户数online_num
结果第一行为表中最早的一分钟，最后一行为表中最晚的一分钟，可能会有一些分钟没有活跃用户，结果仍需展示且online_num为0
Comments 1 条评论
selectcur_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