hive max和min计算跨天最早最晚登录时间

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;

1. 最早：05:00:00~7:59:59，
2. 最晚：21:00:00~次日04:59:59（21:00:00~23:59:59&00:00:00~4:59:59）