ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 非标准单位的分组统计问题

非标准单位的分组统计问题

原创 Linux操作系统 作者:regonly1 时间:2009-04-08 13:32:29 0 删除 编辑

关于时间的统计
假设需求,要求以2天为一个间隔进行数据统计。
正常的统计方式都是按照一个基本数据单位作为一个分组,来进行统计的
如,
按照小时等进行统计的:
select trunc(o.created, 'hh'), count(*)
from user_objects o
group by trunc(o.created, 'hh')
同样,按照单位天--trunc(o.created, 'dd'),按照月--trunc(o.created, 'mm')等等。
(to_char()也可以实现同样要求,这里只以trunc作为例子)。
现在如果是要求以非基本数据单位来实现这个功能,比如以两天、三小时、5分钟
这种不固定分组类型。则无法用上述方式实现。
那么,该怎么实现呢?
首先定义一个基本的用法:
有这样的一个要求,现在要按照5为单位进行范围分割,数值为任意>=0的正数,
如:1   3.4  7.4  16  88.4 ...
将[0,5)的值都置为0,[5,10)的值都置为5,[10,15)的值都置为10,按照5的整数倍以此类推。。。则可以这样实现:trunc(num/5)*5。如数字trunc(3.4/5=0.68)×5=0,trunc(7.4/5=1.28)×5=5,trunc(16/5=3.2)*5=15....。

假设有学生成绩表grade(基础数据构建见本末尾),要以5分为一个分段进行统计。则可以这样实现:
SQL> select trunc(g.grade/5)*5, count(*)
  2   from grades g
  3  group by  trunc(g.grade/5)*5
  4  ;
 
TRUNC(G.GRADE/5)*5   COUNT(*)
------------------ ----------
                 0         63
                 5         66
                10         64
                15         62
                20         49
                25         59
                30         68
                35         77
                40         70
                45         80
                50         61
                55         63
                60         53
                65         62
                70         56
                75         70
                80         77
                85         56
                90         71
                95         73
 
20 rows selected
现在这样显示还不够明了,第一段0,应该是0-5的范围,可以这样:
SQL> select aa.gradepoint || '-' || lead(aa.gradepoint) over(order by aa.gradepoint) grade_range, aa.cnt
  2  from (
  3  select trunc(g.grade/5)*5 gradepoint, count(*) cnt
  4   from grades g
  5  group by  trunc(g.grade/5)*5) aa
  6  ;
 
GRADE_RANGE                                                          CNT
-------------------------------------------------------------------------------- ----------
0-5                                                                                   63
5-10                                                                                 66
10-15                                                                              64
15-20                                                                              62
20-25                                                                              49
25-30                                                                              59
30-35                                                                              68
35-40                                                                              77
40-45                                                                              70
45-50                                                                              80
50-55                                                                              61
55-60                                                                              63
60-65                                                                              53
65-70                                                                              62
70-75                                                                              56
75-80                                                                              70
80-85                                                                              77
85-90                                                                              56
90-95                                                                              71
95-                                                                                   73
 
20 rows selected
这样就很清晰的将5分为区间的成绩范围列举了出来。
注意:这里的5只是为了举例而指定的一个值,实际上可以为任意值。

说完了按成绩统计范围,再说下时间的这种统计方式。
从时间上考虑,我看了下trunc函数,发现对时间,该函数没有类似的使用方法。最多只是进行dd(天),mi(分钟)等进行截取。如果要按照5分钟,3小时这样的情况没有直接的处理方式。
比如现在要日期2009-04-08 14:38:10这个时间,对其进行5分钟单位划分。
这个时间应该划入2009-04-08 14:35:00这个范围,也就是说对分钟进行trunc操作。按照前面的思路,我只要能将分钟单独列出来,就能对这个时间进行前面一样的处理。所以有:
SQL> select sysdate, trunc(to_number(to_char(sysdate, 'mi'))/5)*5 truncminute from dual;

SYSDATE             TRUNCMINUTE
------------------- -----------
2009-04-08 14:43:34          40
实现了这个步骤,下面的就好办了:
SQL> select sysdate, trunc(to_number(to_char(sysdate, 'mi'))/5)*5 truncminute,
  2  trunc(sysdate, 'hh') + trunc(to_number(to_char(sysdate, 'mi'))/5)*5/24/60 rangeminute   from dual;

SYSDATE             TRUNCMINUTE RANGEMINUTE
------------------- ----------- -------------------
2009-04-08 14:46:03          45 2009-04-08 14:45:00

注意上面语句中的红色部分和蓝色部分。
红色部分:trunc(to_number(to_char(sysdate, 'mi'))/5)*5
将分钟单独取出来,然后对其做前面和成绩表的成绩一样的处理。
蓝色部分:trunc(sysdate, 'hh') + trunc(to_number(to_char(sysdate, 'mi'))/5)*5/24/60
将时间值截取到分钟上一级的时间点,即小时。这样将精确到小时的日期加上对分钟截取的范围,就得出了上面这样的结果。这样也就可以实现跟上面成绩表一样的统计方式了。
现假设有一电影点播数据库,每天的点播量约30万。现要求以5分钟作为一个分段点进行统计。
则有:

SQL>  desc filmhistory;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STARTTIME                                          DATE
 FILMNAME                                           VARCHAR2(256)

SQL> select trunc(fh.starttime, 'hh') + trunc(to_number(to_char(fh.starttime, 'mi'))/5)*5/24/60, count(*)
  2  from filmhistory fh
  3  group by rollup(trunc(fh.starttime, 'hh') + trunc(to_number(to_char(fh.starttime, 'mi'))/5)*5/24/60)
  4  ;

TRUNC(FH.STARTTIME,   COUNT(*)
------------------- ----------
2009-01-01 00:00:00       1024
2009-01-01 00:05:00       1087
2009-01-01 00:10:00       1134
2009-01-01 00:15:00       1277
2009-01-01 00:20:00       1311
2009-01-01 00:25:00       1161
2009-01-01 00:30:00       1097
2009-01-01 00:35:00       1056
2009-01-01 00:40:00       1136
2009-01-01 00:45:00       1088
2009-01-01 00:50:00       1022
........................
可以发现这里已经按照这个时间范围进行统计了。
如果要像前面统计成绩一样进行格式化:
SQL> select aa.statpoint || '  -> ' || lead(aa.statpoint) over(order by aa.statpoint), aa.cnt
  2  from (
  3  select trunc(fh.starttime, 'hh') + trunc(to_number(to_char(fh.starttime, 'mi'))/5)*5/24/60 statpoint, count(*) cnt
  4  from filmhistory fh
  5  group by rollup(trunc(fh.starttime, 'hh') + trunc(to_number(to_char(fh.starttime, 'mi'))/5)*5/24/60)) aa
  6  ;

AA.STATPOINT||'->'||LEAD(AA.STATPOINT)OVER(        CNT
------------------------------------------- ----------
2009-01-01 00:00:00  -> 2009-01-01 00:05:00       1024
2009-01-01 00:05:00  -> 2009-01-01 00:10:00       1087
2009-01-01 00:10:00  -> 2009-01-01 00:15:00       1134
2009-01-01 00:15:00  -> 2009-01-01 00:20:00       1277
2009-01-01 00:20:00  -> 2009-01-01 00:25:00       1311
2009-01-01 00:25:00  -> 2009-01-01 00:30:00       1161
2009-01-01 00:30:00  -> 2009-01-01 00:35:00       1097
2009-01-01 00:35:00  -> 2009-01-01 00:40:00       1056
2009-01-01 00:40:00  -> 2009-01-01 00:45:00       1136
2009-01-01 00:45:00  -> 2009-01-01 00:50:00       1088
2009-01-01 00:50:00  -> 2009-01-01 00:55:00       1022
.........................................
这样统计。

另外,如果某个统计时间点不存在,则无法在数据中体现出来。因此就需要构造数据,将没有的部分时间也补上。对应值置为0。
下面是一个特定的演示SQL。这里不做具体展开:
select c.cval, ba.* from (
select aa.ival, count(*) from (
select  o.object_name, o.created, o.last_ddl_time,
 trunc(o.created, 'mm') + trunc(to_number(to_char(o.created, 'dd'))/2) * 2ival
from user_objects o where o.created >= date '2009-01-01') aa
group by aa.ival ) ba, (select date '2009-01-01' + 2*(rownum - 1) cval from TMP_ANALYZE_IPTVDETAIL where rownum <= 100) c
where ba.ival(+) = c.cval

附:构建表数据:
create table classes(class_id number(18), class_name varchar2(255));
create table students(student_id number(18), student_name varchar2(255));
create table grades(student_id number(18), class_id number(18), grade number(10,2));

insert into classes
select rownum, dbms_random.string('A',5) from dual connect by rownum <= 13
insert into students
select rownum, dbms_random.string('A',5) from dual connect by rownum <= 100
insert into grade
select s.student_id, c.class_id, trunc(dbms_random.value(1,100),2)
from student s, classes c

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-586726/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 端口监控脚本
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1045352