按时间分组统计的SQL语句

如下表table1：

　　日期(exportDate)               数量(amount)
　　 --------------                    -----------
　　14-2月 -08                       20
　　10-3月 -08                       2
　    14-4月 -08                       6
　　14-6月 -08                       75
　　24-10月-09                      23
　　14-11月 -09                      45
　　04-8月 -10                       5
　　04-9月 -10                       44
　　04-10月-10                      88

　　注意：为了显示更直观，如下查询已皆按相应分组排序

　　1.按年份分组

　　select to_char（exportDate，‘yyyy’），sum（amount） from table1 group by to_char（exportDate，‘yyyy’）;

　　年份      数量
　　-----------------------------
　　2009    68
　　2010    137
　　2008    103

　　2.按月份分组

　　select to_char（exportDate，‘yyyy-mm’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-mm’）

　　order by to_char（exportDate，‘yyyy-mm’）;

　　月份           数量
　　-----------------------------
　　2008-02    20
　    2008-03    2
　　2008-04    6
　　2008-06    75
　　2009-10    23
　    2009-11    45
　　2010-08    5
　　2010-09    44
　　2010-10    88

　　3.按季度分组

　　select to_char（exportDate，‘yyyy-Q’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-Q’）

　　order by to_char（exportDate，‘yyyy-Q’）;

　　季度          数量
　　------------------------------
　　2008-1    22
　    2008-2    81
　　2009-4    68
　　2010-3    49
　　2010-4    88

　　4.按周分组

　　select to_char（exportDate，‘yyyy-IW’），sum（amount） from table1 group by to_char（exportDate，‘yyyy-IW’）

　　order by to_char（exportDate，‘yyyy-IW’）;

　　周             数量
　　------------------------------
　　2008-07    20
　　2008-11    2
　　2008-16    6
　　2008-24    75
　　2009-43    23
　　2009-46    45
　　2010-31    5
　　2010-35    44
　    2010-40    88

　　补充：

　　按季度分组还有个比较笨的方法（参考网络资源）

　　select to_char（exportDate，‘yyyy’），

　　sum（decode（to_char（exportDate，‘mm’），‘01’，amount，‘02’，amount，‘03’，amount，0）） as 第一季，

　　sum（decode（to_char（exportDate，‘mm’），‘04’，amount，‘05’，amount，‘06’，amount，0）） as 第二季，

　　sum（decode（to_char（exportDate，‘mm’），‘07’，amount，‘08’，amount，‘09’，amount，0）） as 第三季，

　　sum（decode（to_char（exportDate，‘mm’），‘10’，amount，‘11’，amount，‘12’，amount，0）） as 第四季

　　from table1

　　group by to_char（exportDate，‘yyyy’）;

　　年份        第一季     第二季     第三季     第四季
　　 --------------------------------------------------
　　2009         0            0            0          68
　　2010         0            0            49        88
　　2008         22          81           0          0

5:按时间段统计

按时间段分组显示 。。。
比如有数据是           

                        2010-8-13 09:46:05
                        2010-8-13 10:09:05
                        2010-8-13 10:46:05
按照每30分钟分段统计，然后统计字段显示成10:00-10:30形式

SQL:

select to_char((trunc(sysdate)+trunc((motime-trunc(sysdate))*24*60/30)*30/60/24),'hh24:mi')||'-'||to_char((trunc(sysdate)+trunc((motime-trunc(sysdate))*24*60/30+1)*30/60/24),'hh24:mi') period, count(*) "total" from mo where trunc(motime)=to_date(&day,'yyyymmdd') group by to_char((trunc(sysdate)+trunc((motime-trunc(sysdate))*24*60/30)*30/60/24),'hh24:mi')||'-'||to_char((trunc(sysdate)+trunc((motime-trunc(sysdate))*24*60/30+1)*30/60/24),'hh24:mi') order by 1;

还可以使用connect by 和level来完成这个操作

这里需要用connect by和level来共同构建当天内每分钟的时间点。


select to_char(to_date(to_char(sysdate, 'yyyy-MM-dd') || ' 00:00:00','yyyy-MM-dd HH24:mi:ss') + (level / 24 / 60),'yyyy-MM-dd HH24:mi:ss') dt
  from dual
connect by level <=
           trunc((sysdate -to_date('2013-1-5 00:00:00', 'yyyy-MM-dd HH24:mi:ss')) * 24 * 60,0)
           
得到这样的结果后，接下来的统计就简单了很多，下面是统计每个时间点单据总数的sql：


select dt,
       (select count(1)
          from orderTest o
         where o.create_time >
               to_date(to_char(sysdate, 'yyyy-MM-dd') || ' 00:00:00',
                       'yyyy-MM-dd HH24:mi:ss')
           and o.create_time <= to_date(d.dt, 'yyyy-MM-dd HH24:mi:ss')) n
  from (select to_char(to_date(to_char(sysdate, 'yyyy-MM-dd') || ' 00:00:00',
                               'yyyy-MM-dd HH24:mi:ss') +
                       (level / 24 / 60),
                       'yyyy-MM-dd HH24:mi:ss') dt
          from dual
        connect by level <= trunc((sysdate -
                                  to_date('2013-1-5 00:00:00',
                                           'yyyy-MM-dd HH24:mi:ss')) * 24 * 60,
                                  0)) d
统计的结果如下：


DT                           N
------------------- ----------
2013-01-05 00:01:00          0
2013-01-05 00:02:00          0
2013-01-05 00:03:00          0
2013-01-05 00:04:00          0
2013-01-05 00:05:00          0
2013-01-05 00:06:00          0
2013-01-05 00:07:00          0
2013-01-05 00:08:00          0
2013-01-05 00:09:00          0
2013-01-05 00:10:00          0
2013-01-05 00:11:00          0
2013-01-05 00:12:00          0
2013-01-05 00:13:00          0
2013-01-05 00:14:00          0
2013-01-05 00:15:00          0
2013-01-05 00:16:00          0
2013-01-05 00:17:00          0
2013-01-05 00:18:00          0
2013-01-05 00:19:00          0
2013-01-05 00:20:00          0
由于是凌晨的关系，所以统计出来的数据都是0。


上面写的语句都是默认时间间隔是1分钟的，如果你需要修改间隔时间，只需要修改下面两处地方


select to_char(to_date(to_char(sysdate, 'yyyy-MM-dd') || ' 00:00:00',
                               'yyyy-MM-dd HH24:mi:ss') +
                       (level / 24 / 60 /*自定义时间间隔*/),
                       'yyyy-MM-dd HH24:mi:ss') dt
          from dual
        connect by level <= trunc((sysdate -
                                  to_date('2013-1-5 00:00:00',
                                           'yyyy-MM-dd HH24:mi:ss')) * 24 * 60/*自定义时间间隔*/,
                                  0)
第一处地方需要除以间隔时间，第二处地方则是乘以间隔时间。


说到原理其实很简单，先用系统当前时间减去今日的初始时间，减出来的天数*24*60就是分钟数trunc之后就可以用做level了。