with loin_format as(
select
user_id,dt,case when cast(hour(login_time) as int) <=4 then
concat(
(cast(hour(login_time) as int) + 24),'@',login_time) else concat(cast(hour(login_time) as int) + 10 ,'@',login_time) end as login_time_format
from ods.user_login where dt >= '2021-01-01' and dt <= '2021-12-15'
), login as(
select user_id userid,min(login_time) min_time,max(login_time) max_time,count(distinct dt) login_dt from loin_format group by user_id
)
select split(t1.min_time,'@')[1],split(t1.max_time,'@')[1],userid from login limit 10;
- 最早：05:00:00~7:59:59，
- 最晚：21:00:00~次日04:59:59（21:00:00~23:59:59&00:00:00~4:59:59）
