数据库表结构如下:
id datetime value
1 2009-08-06 08:30 20
1 2009-08-06 08:32 23
1 2009-08-06 08:34 22
1 2009-08-06 08:36 25
1 2009-08-06 08:39 29
1 2009-08-06 08:40 23
1 2009-08-06 08:44 30
1 2009-08-06 08:47 31
1 2009-08-06 08:51 34
1 2009-08-06 08:53 36
2 2009-08-06 08:32 40
2 2009-08-06 08:34 42
2 2009-08-06 08:37 43
2 2009-08-06 08:39 45
2 2009-08-06 08:45 47
1 2009-08-07 08:30 30
1 2009-08-07 08:34 32
1 2009-08-07 08:37 33
1 2009-08-07 08:41 34
1 2009-08-07 08:44 36
1 2009-08-07 08:48 37
1 2009-08-07 08:50 39要求需要同时满足如下3个条件:
1. 按id来搜索前一天的所有结果,如搜索8.6日id=1的所有数据;
2. 以条件1为前提,以5分钟为间隔取得所有数据,如08:30,08:35,08:40,表中不足整5分钟的取最临近的时间数据;
3. 将搜索结果存在临时表或view中。
id datetime value
1 2009-08-06 08:30 20
1 2009-08-06 08:32 23
1 2009-08-06 08:34 22
1 2009-08-06 08:36 25
1 2009-08-06 08:39 29
1 2009-08-06 08:40 23
1 2009-08-06 08:44 30
1 2009-08-06 08:47 31
1 2009-08-06 08:51 34
1 2009-08-06 08:53 36
2 2009-08-06 08:32 40
2 2009-08-06 08:34 42
2 2009-08-06 08:37 43
2 2009-08-06 08:39 45
2 2009-08-06 08:45 47
1 2009-08-07 08:30 30
1 2009-08-07 08:34 32
1 2009-08-07 08:37 33
1 2009-08-07 08:41 34
1 2009-08-07 08:44 36
1 2009-08-07 08:48 37
1 2009-08-07 08:50 39要求需要同时满足如下3个条件:
1. 按id来搜索前一天的所有结果,如搜索8.6日id=1的所有数据;
2. 以条件1为前提,以5分钟为间隔取得所有数据,如08:30,08:35,08:40,表中不足整5分钟的取最临近的时间数据;
3. 将搜索结果存在临时表或view中。
3. create view view_text as select * from your_table where id = 'xxx' and (datetime > trunc(sysdate) -1 or datetime < trunc(sysdate)
2. 楼主还是先给出预期的结果方便沟通
id datetime value
1 2009-08-06 08:30 20
1 2009-08-06 08:40 23
1 2009-08-06 08:44 30
1 2009-08-06 08:51 34
这样每5分钟取一个数据点,表中不足整5分钟的取最临近的时间数据。
select * from table_a where id = '1' and trunc(datetime+1,'dd')=trunc(sysdate,'dd');
2. 以条件1为前提,以5分钟为间隔取得所有数据,如08:30,08:35,08:40,表中不足整5分钟的取最临近的时间数据;
select id, to_char(datetime, 'yyyy-mm-dd hh24:mi'), value from (select a.*, row_number() over(partition by trunc((to_number(to_char(datetime, 'mi'))+4)/5) order by mod((to_number(to_char(datetime, 'mi'))+4), 5) desc) rn from table_a a where id = 1 and trunc(datetime+1,'dd')=trunc(sysdate,'dd')) where rn=1;3. 将搜索结果存在临时表或view中。
cretate view v_1 as (select id, to_char(datetime, 'yyyy-mm-dd hh24:mi'), value from (select a.*, row_number() over(partition by trunc((to_number(to_char(datetime, 'mi'))+4)/5) order by mod((to_number(to_char(datetime, 'mi'))+4), 5) desc) rn from table_a a where id = 1 and trunc(datetime+1,'dd')=trunc(sysdate,'dd')) where rn=1);
这里要修改一下select id, to_char(datetime, 'yyyy-mm-dd hh24:mi'), value from (select a.*, row_number() over(partition by trunc(datetime, 'hh24'), trunc((to_number(to_char(datetime, 'mi'))+4)/5) order by mod((to_number(to_char(datetime, 'mi'))+4), 5) desc) rn from table_a a where id = 1 and trunc(datetime+1,'dd')=trunc(sysdate,'dd')) where rn=1; 要不,对小时采不了样了
表名:flash_table
结构:ID,DATETIME,DATAVALUEselect id,date(datetime),datavalue from (select a.*,row_number() over(partition by hour(datetime),(minute(datetime)+4)/5 order by mod((minute(datetime)+4),5) desc) rn from flash_table a where id = 1 and day(datetime)=6) where rn =111:12:39.156 DBMS NAME1 -- [IBM][CLI Driver][DB2/NT] SQL0104N 在 ")" 之后发现意外的标记 ""。期望的标记可能包括:"AS"。 SQLSTATE=42601db2和oracle语法差别较大啊。
row_number() over(partition by trunc(datetime, 'hh24'), trunc((to_number(to_char(datetime, 'mi'))+4)/5))
mod((to_number(to_char(datetime, 'mi'))+4), 5) 关键地方应该是这2处了。因为周末在家,只有DB2可以实验。实际应用中我们用的是SYBASE,因为数据库语法之间的差别,恐怕要更崩溃了,所有还是了解下原理的好。
就是说对每组runc(datetime, 'hh24'), trunc((to_number(to_char(datetime, 'mi'))+4)/5)的记录加序列!
这个为1,2,3,4,5.。。自然数!