hive sql 对一天的数据按5分钟进行分段

背景：离线的数据中有时间戳，要求按五分钟规划为一组00：00 - 23：55

 

 

 1.cast(date_format(t1.order_end_time,'HH') as string)把小时拿出来

 2.(floor(date_format(t1.order_end_time,'mm')/5 )*5) as string 把分钟数除5，24/5=4.8。再向下取整=4。再乘5=20。

 3.case when判断如果结果是0就得00，5就得05

 4.最后concat_ws拼接小时和分钟

 

concat_ws(':',cast(date_format(t1.order_end_time,'HH') as string)
,case when cast((floor(date_format(t1.order_end_time,'mm')/5 )*5) as string) = '0' then '00'
when cast((floor(date_format(t1.order_end_time,'mm')/5 )*5) as string) = '5' then '05'
else cast((floor(date_format(t1.order_end_time,'mm')/5 )*5) as string) end) as '分钟段'