连续时间段统计问题

[jiaday111]

我想用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' 这样的方式进行分段
求助应该怎么实现这个过程呢？

[hebo2005]

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(+)

[vc555]

学习。

[mantisXF]

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 22

12 rows selected

SQL> 
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                5

SQL> 

try it ..

[jiaday111]

能不能详细解释一下啊？我对ORACLE超级菜鸟啊~~~

最后一句where语句是什么作用啊？求救啊

[mantisXF]

left join(左连接) ...


FYI:

http://topic.csdn.net/t/20041027/13/3495554.html

[jiaday111]

是需要在某个选定时间范围内进行上述分天的统计，而且需要统计的那个字段可能有重复字段，需要去重，又该怎么修改呢？

[jiaday111]

还有在一定时间范围内分小时段，周段，月段又该怎么修改呢？

急需这个查询语句，可是太菜了，没办法啊~~~

各位大人帮帮忙吧

[oraclelogan]

[Quote=引用 2 楼 hebo2005 的回复:]
SQL codeselectaa.days,nvl(bb.num,0) "count"from(selectstart_date+rownum-1Daysfrom(selecttrunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_datefromTABLEa
)
connectbyrownum<=end_date-start_Date+1)aa,(selecttrunc(DATATIME ) days,count(*) numfromtablegroupbytrunc(DATATIME )
)bbwhereaa.days=bb.days(+)
[/Quote]

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

[mantisXF]

1: For "date range" issues, just add below filter condition in SQL query:

datatime between &start_time and &end_time ...

Or

datatime >= &start_time and <= &end_time ...


2: for the way to lose duplicate records:

Change

select trunc(datatime) c_time, count(1) counts
  from test_time
 group by trunc(datatime)

To

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))
         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! ..

[jiaday111]

我将trunc(dattime)变为trunc(datetime,'mm'),以为可以得到按月统计的结果，但是只有有数据的组取到了每个月的一号作为起始时间，没有数据地方日期显示还是乱的。

请问是还要作什么修改吗？

拜托继续帮帮忙~~~

[mantisXF]

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(+);

[jiaday111]

那个，周的我想显示的是每个时间段的第一天的日期，而不是周的序号，请问应该怎么修改呢？

菜鸟再次求助啊

[chensi05]

写的不错

[hebo2005]

周的话,可以通过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

[jiaday111]

如果我的周不需要知道是当年的第几周，会有一个参数设定时间范围，只是从起始时间段算起，以一周的时间间隔来进行分段统计，该怎么实现呢？

而小时会在选定的一天内以24小时进行分段统计，语句是不是跟前面的实现方法不一样呢？

急用啊，实在没时间自己好好研究了，拜托各位大人能不能给出实例呢？

[hebo2005]

[Quote=引用 15 楼 jiaday111 的回复:]
如果我的周不需要知道是当年的第几周，会有一个参数设定时间范围，只是从起始时间段算起，以一周的时间间隔来进行分段统计，该怎么实现呢？

而小时会在选定的一天内以24小时进行分段统计，语句是不是跟前面的实现方法不一样呢？

急用啊，实在没时间自己好好研究了，拜托各位大人能不能给出实例呢？
[/Quote]
那也行
你可以按照上面先成时间段内的每天日期,同时用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

[hebo2005]

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

select rownum-1 hours
from dual
connect by rownum<=24

[hebo2005]

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

[mantisXF]

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(+);

[jiaday111]

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