连续时间段统计问题

Question

我想用SQL实现这样一个功能： 数据库中有一个DATA字段， 
希望统计每小时，或每天，或每周，或每个月所含有的不重复的某个字段的记录条数， 
起始时间为第一个记录所在时间取整，如第一个记录时间为2008-1-1 02：22，则起始时间取为2008-1-1 
然后根据所设时间间隔进行统计， 
直至统计完数据库中的所有记录。 
（数据库中的记录是在动态变化的） 此外，由于有的时间段数据库中不存在数据，我希望也能得到这个时间段的分组，记录条数显示为0，而不是出现一个空值，应该怎么实现呢？求救牛人们解答~~~ 例如：表 TABLE 
DATATIME 
2008-1-1 01:22 
2008-1-1 07:22 
2008-1-1 09:22 
2008-1-2 11:22 
2008-1-2 12:22 
2008-1-2 17:22 
2008-1-2 21:22 
2008-1-4 01:22 
2008-1-4 05:22 
2008-1-4 14:22 
2008-1-4 16:22 
2008-1-4 22:22 最后希望的结果是 起始时间                           count  
2008 1-1                        3
2008 1-2                        4
2008 1-3                        0
2008 1-4                        5数据库中的记录是在动态变化的,不能使用between '2008 1-1 00:00' and  '2008 1-1 12：00' 这样的方式进行分段 
求助应该怎么实现这个过程呢？

Answer 1

这个不难
先取出此表的最开始日,截止日(min,max)
然后用这两个值生成两个日期间的所有天数,然后再与原表做外联接
再分组求count
生成时间段的日期的语法select start_date+rownum-1 every_Day
    from (
          select trunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_date
              from TABLE a
         )
connect by rownum <=end_date-start_Date+1
再把这段SQL与原表做外联接

Answer 2

select aa.days,nvl(bb.num,0) "count"
   from (
           select start_date+rownum-1 Days
    from (
          select trunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_date
              from TABLE a
         )
connect by rownum <=end_date-start_Date+1
           )aa,(
                select trunc(DATATIME ) days,count(*) num
                     from table
                     group by trunc(DATATIME )
           )bb
where aa.days=bb.days(+)

Answer 3

Except for 1st way which provided by hebo2005, below is 2nd way:
SQL> select * from test_time;       TID DATATIME
---------- -----------
         1 2008-1-1 1:
         2 2008-1-1 7:
         3 2008-1-1 9:
         4 2008-1-2 11
         5 2008-1-2 12
         6 2008-1-2 17
         7 2008-1-2 21
         8 2008-1-4 1:
         9 2008-1-4 5:
        10 2008-1-4 14
        11 2008-1-4 16
        12 2008-1-4 2212 rows selectedSQL> 
SQL> select a_time, nvl(counts, 0)
  2    from (select trunc(datatime) c_time, count(1) counts
  3            from test_time
  4           group by trunc(datatime)) count_time,
  5         (select min_time + rownum - 1 a_time
  6            from all_objects,
  7                 (select min(trunc(datatime)) min_time,
  8                         max(trunc(datatime)) max_time
  9                    from test_time) loop_time
 10           where rownum < = max_time - min_time + 1) all_time
 11   where a_time = c_time(+);A_TIME      NVL(COUNTS,0)
----------- -------------
2008-1-1                3
2008-1-2                4
2008-1-3                0
2008-1-4                5SQL> try it ..

Answer 4

能不能详细解释一下啊？我对ORACLE超级菜鸟啊~~~最后一句where语句是什么作用啊？求救啊

Answer 5

left join(左连接) ...  
FYI:http://topic.csdn.net/t/20041027/13/3495554.html

Answer 6

学习啦，hebo2005 是我们oracle板块的 活雷锋！

Answer 7

1: For "date range" issues, just add below filter condition in SQL query:datatime between &start_time and &end_time ...Ordatatime >= &start_time and <= &end_time ...
2: for the way to lose duplicate records:Changeselect trunc(datatime) c_time, count(1) counts
  from test_time
 group by trunc(datatime)
Toselect trunc(datatime) c_time, count(1) counts
          from (select datatime
                  from test_time t1
                 where t1.rowid =
                       (select max(rowid)
                          from test_time t2
                         where t1.datatime = t2.datatime))
         group by trunc(datatime)So the changed SQL query for above concerns as below:
select a_time, nvl(counts, 0)
  from (select trunc(datatime) c_time, count(1) counts
          from (select datatime
                  from test_time t1
                 where t1.rowid =
                       (select max(rowid)
                          from test_time t2
                         where t1.datatime = t2.datatime)
                   and datatime >= &start_time 
                   and datatime <= &end_time)
         group by trunc(datatime)) count_time,
       (select min_time + rownum - 1 a_time
          from all_objects,
               (select min(trunc(datatime)) min_time,
                       max(trunc(datatime)) max_time
                  from test_time) loop_time
         where rownum < = max_time - min_time + 1) all_time
 where a_time = c_time(+); 3: For hour range, week range and month range issues:
By my understanding for the requirement, just change group by clause(group by trunc(datatime)):
For hour range : group by trunc(datatime,'HH24')
For week range : group by trunc(datatime,'ww')
For month range: group by trunc(datatime,'mm')Good luck to you! guys! .. 

Answer 8

我将trunc(dattime)变为trunc(datetime,'mm'),以为可以得到按月统计的结果，但是只有有数据的组取到了每个月的一号作为起始时间，没有数据地方日期显示还是乱的。请问是还要作什么修改吗？拜托继续帮帮忙~~~

Answer 9

Month range:
select a_time, nvl(counts, 0)
  from (select trunc(datatime,'mm') c_time, count(1) counts
          from (select datatime
                  from test_time t1
                 where t1.rowid =
                       (select max(rowid)
                          from test_time t2
                         where t1.datatime = t2.datatime)
                  )
         group by trunc(datatime,'mm')) count_time,
       (select add_months(min_time, rownum - 1) a_time
          from all_objects,
               (select min(trunc(datatime,'mm')) min_time,
                       max(trunc(datatime,'mm')) max_time
                  from test_time) loop_time
         where rownum < = MONTHS_BETWEEN(max_time,min_time) + 1) all_time
 where a_time = c_time(+);

Answer 10

周的话,可以通过to_char(date,'iw')取出该日期的所在周
月的话用trunc(date,'mm')实际上是取的当月的1号,另一种方法是to_char(date,'yyyymm')取周要注意的问题就是跨年的问题,
向去年的12月31号,到今年1月1号,都是今年的第一周而12.29就是去年的52周
如果单纯取从去年1.1到今年,就会有错误,把去年的第一周和今年的合并了
所以做跨年时,还要另外取年份做为区分不过又遇到另外个问题,像12.31取周的话,是第一周,取年的话是2007,合起来就变成了2007年的第一周,就不对了
所以这里要加个decode判断,如果月份=12,并且周等于第一周,则年份+1

Answer 11

那也行
你可以按照上面先成时间段内的每天日期,同时用rownum/7来标志是第几周,最后按这个周标志来分组
select weeks,sum("count") "count"
from
(
select aa.days,aa.weeks,nvl(bb.num,0) "count"
   from (
           select start_date+rownum-1 Days,trunc(rownum/7) weeks
    from (
          select trunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_date
              from TABLE a
         )
              connect by rownum <=end_date-start_Date+1
           )aa,(
                select trunc(DATATIME ) days,count(*) num
                     from table
                     group by trunc(DATATIME )
           )bb
where aa.days=bb.days(+))
group by weeks

Answer 12

24小时分段统计
用to_number(to_char(DATATIME,'hh24'))来进行分组
如果没有数据也需要统计出来,也就是必须有24条纪录
也是先用connect by配合rownum生成24小时select rownum-1 hours
from dual
connect by rownum<=24

Answer 13

to_number(to_char(DATATIME,'hh24'))
里面用to_number主要是为了和下面的SQL进行关联
因为to_char生成的1点,是01,0 点是00
而那段sql生成的是0,1,2

Answer 14

1: for "week range of 24hrs" issues, you can consult the SQL query which provided by hebo2005.2: "hours range of 24hrs" issues
1> If you need to get data which hours range not in 1 day by doing statistics, FYI below querys:select a_time, nvl(counts, 0) "count_time"
  from (select trunc(datatime, 'HH24') c_time, count(1) counts
          from (select datatime
                  from test_time t1
                 where t1.rowid = (select max(rowid)
                          from test_time t2
                         where t1.datatime = t2.datatime)
                )
         group by trunc(datatime, 'HH24')) count_time,
       (select (min_time + (rownum - 1)/24) a_time,
               rownum rn
          from all_objects,
               (select min(trunc(datatime, 'HH24')) min_time,
                       max(trunc(datatime, 'HH24')) max_time
                  from test_time) loop_time
         where rownum < = (max_time - min_time)*24 + 1) all_time
 where a_time = c_time(+);2> If you need to get data which just 24 hours range in 1 day by doing statistics, consult following:select a_time, nvl(counts, 0) "count_time"
  from (select trunc(datatime, 'HH24') c_time, count(1) counts
          from (select datatime
                  from test_time t1
                 where t1.rowid = (select max(rowid)
                          from test_time t2
                         where t1.datatime = t2.datatime)
                   and to_char(datatime,'yyyy-mm-dd') = &day_time_char
                )
         group by trunc(datatime, 'HH24')) count_time,
       (select (trunc(min_time) + (rownum - 1)/24) a_time,
               rownum rn
          from all_objects,
               (select min(trunc(datatime, 'HH24')) min_time
                  from test_time
                 where to_char(datatime,'yyyy-mm-dd') = &day_time_char) loop_time
         where rownum < = 24) all_time
 where a_time = c_time(+);

Answer 15

谢谢各位大人帮忙了，继续学习ING~~~