锐客网

  1. 首页 > it技术 > >

SQL将一天分段分为8点到18点统计没半个小时一分组分成22组

1、原始数据如下：
SQL将一天分段分为8点到18点统计没半个小时一分组分成22组
文章图片

2、利用分组求和计算出一天内小时与分合计
select( case when length( extract(hourfromcast (oper_date as timestamp)) )<2 then '0'|| extract(hourfromcast (oper_date as timestamp)) else to_char( extract(hourfromcast (oper_date as timestamp)) )end

【SQL将一天分段分为8点到18点统计没半个小时一分组分成22组】|| case whenlength( extract(minutefromcast (oper_date as timestamp)) )<2 then'0'|| extract(minutefromcast (oper_date as timestamp)) else to_char( extract(minutefromcast (oper_date as timestamp))) end

)aa,count(*) COUNT_P
from STD_LOG
where OPER_DATE >= to_date('2018-08-01','yyyy-MM-dd')
and OPER_DATE < to_date('2018-08-02','yyyy-MM-dd')
group by case when length( extract(hourfromcast (oper_date as timestamp)))<2 then '0'|| extract(hourfromcast (oper_date as timestamp)) else to_char(extract(hourfromcast (oper_date as timestamp))) end

|| case whenlength( extract(minutefromcast (oper_date as timestamp)))<2 then'0'|| extract(minutefromcast (oper_date as timestamp)) else to_char(extract(minutefromcast (oper_date as timestamp))) end
order by aa
结果如下：主要将小时与分分别补0；主要使用函数cast与timestamp 将to_date转换为timestamp类型 然后用extract分别取出小时与分钟 然后合并
SQL将一天分段分为8点到18点统计没半个小时一分组分成22组
文章图片

3、最后 将小时小于8点的 默认为8点大于18点的默认为18点，将分钟小于30分钟的默认为00大于30分钟的默认为30，然后SUM

select （case when cast( substr(aa,0,2) as decimal)<8 then '08' when cast( substr(aa,0,2)as decimal)>18 then '18' else substr(aa,0,2) end|| ':'||

case when cast (substr(aa,3) as decimal)<30 then '00' else '30' end ） hhmm, sum (COUNT_P)

from(

select( case when length( extract(hourfromcast (oper_date as timestamp)) )<2 then '0'|| extract(hourfromcast (oper_date as timestamp)) else to_char( extract(hourfromcast (oper_date as timestamp)) )end

|| case whenlength( extract(minutefromcast (oper_date as timestamp)) )<2 then'0'|| extract(minutefromcast (oper_date as timestamp)) else to_char( extract(minutefromcast (oper_date as timestamp))) end

)aa,count(*) COUNT_P
from STD_LOG
where OPER_DATE >= to_date('2018-08-01','yyyy-MM-dd')
and OPER_DATE < to_date('2018-08-02','yyyy-MM-dd')
group by case when length( extract(hourfromcast (oper_date as timestamp)))<2 then '0'|| extract(hourfromcast (oper_date as timestamp)) else to_char(extract(hourfromcast (oper_date as timestamp))) end

|| case whenlength( extract(minutefromcast (oper_date as timestamp)))<2 then'0'|| extract(minutefromcast (oper_date as timestamp)) else to_char(extract(minutefromcast (oper_date as timestamp))) end
order by aa

) a
group by case when cast( substr(aa,0,2) as decimal)<8 then '08' when cast( substr(aa,0,2)as decimal)>18 then '18' else substr(aa,0,2) end||':'||

case when cast (substr(aa,3) as decimal)<30 then '00' else '30' end

order by hhmm
4、最终结果如下：
SQL将一天分段分为8点到18点统计没半个小时一分组分成22组
文章图片

    推荐阅读


    上一篇：Android实现双屏异显

    下一篇：12款帮助学习代码的游戏