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

最新推荐文章于 2024-01-03 19:21:24 发布

baidu_41056274

最新推荐文章于 2024-01-03 19:21:24 发布

阅读量3k

点赞数

文章标签： SQL 时间段分组统计

本文链接：https://blog.csdn.net/baidu_41056274/article/details/83931127

版权

该博客介绍了如何使用SQL对一天中8点至18点的时间段进行每半小时分组统计的方法。通过提取日期时间戳的小时和分钟，补全0并进行分组，最后将小于8点的小时设为8点，大于18点的设为18点，分钟小于30的设为00，大于30的设为30，实现统计目的。

摘要由CSDN通过智能技术生成

1、原始数据如下：

2、利用分组求和计算出一天内小时与分合计

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

   || case when length( extract(minute from cast (oper_date as timestamp)) )<2 then '0'|| extract(minute from cast (oper_date as timestamp)) else to_char( extract(minute from cast (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