今天测试人员提出一个需求，需要统一张日志表今天上午9点到12点之间每隔五分钟的统计总值，和速率，以下SQL根据小时，分钟分组比较灵活，而且速度也还快，记录下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
temphall=> select to_char(act_time,'yyyy-mm-dd hh24 '),min (act_time) begin_time, max (act_time) end_time ,
temphall-> sum(down_bytes), sum(down_time), sum(down_bytes)/sum(down_time)
temphall-> from tmp_table
temphall-> where act_time > '2010-11-19 00:00:00'
temphall-> and date_part('hour',act_time) in ('09','10','11')
temphall-> group by to_char(act_time,'yyyy-mm-dd hh24 ') , floor(to_number( to_char(act_time,'mi') ,'99')/5)
temphall-> order by 1,2;
to_char | begin_time | end_time | sum | sum | ?column?
----------------+----------------------------+----------------------------+-----------+--------+----------
2010-11-19 09 | 2010-11-19 09:00:00.150474 | 2010-11-19 09:04:59.919062 | XXXXXXXXX | XXXXXX | XXXX
2010-11-19 09 | 2010-11-19 09:05:00.012315 | 2010-11-19 09:09:59.9162 | XXXXXXXXX | XXXXXX | XXXX
....后面省略
```
日期分组如下所示，每五分钟的数据在一个组。
```
1/5 0
2/5 0
3/5 0
4/5 0
5/5 1
6/5 1
7/5 1
8/5 1
9/5 1
10/5 2
11/5 2
....后面省略
|
扩展一下，按天统计每小时的统计量，这个相对上面的就简单些了，也顺便记录下
1
2
3
4
5
6
7
8
9
10
11
|
temphall=> select to_char(act_time,'yyyy-mm-dd '), date_part('hour',act_time),sum(down_bytes), sum(down_time),sum(down_bytes)/sum(down_time)
temphall-> from tmp_table
temphall-> where act_time > '2010-11-17 00:00:00'
temphall-> group by to_char(act_time,'yyyy-mm-dd '), date_part('hour',act_time)
temphall-> order by 1,2
temphall-> ;
to_char | date_part | sum | sum | ?column?
-------------+-----------+------------+---------+----------
2010-11-17 | 0 | XXXXXXXXXX | XXXXXXX | XXXX
2010-11-17 | 1 | XXXXXXXXXX | XXXXXX | XXXX
2010-11-17 | 2 | XXXXXXXXX | XXXXXX | XXXX
|
原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/236336.html
