数据库时间段分组查询解决方法和数据转储方法
问题：将Table1中的数据按照时间段以及其他可能的主键进行统计，然后转存到另一个表Table2中。
说明：时间段分类有两种，一是标准的按照分钟、小时、天等进行分段统计，二是用户自定义时间段，比如上午8点到11点，下午1点到5点。
原理：对时间粒度进行分类，相同的时间段内的时间取一个相同的标示，然后对其分组查询。
方法：对于第一类时间段，可以采用取分钟数，小时数，天数作为标示。
对于SQL Server，采用datediff(hour,'2007-5-17',Time)函数，可以使用minute，day等不同粒度。例如15分钟可以这样处理datediff(minute,'2007-5-17',myTime)/15，不要忘了反过来恢复时间时应该将该值再乘以15然后与标准时间'2007-5-17'相加 dateadd(minute,stime*15,'2007-5-17')。
对于Oracle，采用时间相减，得到一个小数，单位是天，乘以相关值再取整得到天数、小时数、分钟等，如
trunc((mytime-TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss'))*96)得到的是15分钟的分组标示。
对于第二类时间段，另建一个表，存储开始时间、结束时间、标示，然后利用该表联合查询。
注意：对于转存表中已经有相关数据可能造成失败的情况进行判别，防止出错。这样执行第二次，就不会再插入相同的数据了。
示例：
第一类解决方法：
1
/** SQL Server **/
2
CREATE TABLE [dbo].[Table1] (
3[myTime] [datetime] NOT NULL ,
4[ac] [char] (10) NOT NULL ,
5[bi] [int] NOT NULL ,
6[ci] [int] NOT NULL
7)
8GO
9
10CREATE TABLE [dbo].[Table2] (
11[myTime] [datetime] NOT NULL ,
12[ac] [char] (10) NOT NULL ,
13[bi] [int] NOT NULL ,
14[ci] [int] NOT NULL
15)
16GO
17
18delete from Table1;
19delete from Table2;
20
21insert into Table1 values('2007-5-17 1:00:00','Tom',25,300);
22insert into Table1 values('2007-5-17 1:15:00','Tom',25,400);
23insert into Table1 values('2007-5-17 1:30:00','Tom',25,500);
24insert into Table1 values('2007-5-17 2:00:00','Tom',25,600);
25insert into Table1 values('2007-5-17 2:30:00','Tom',25,550);
26insert into Table1 values('2007-5-17 3:00:00','Tom',25,560);
27insert into Table1 values('2007-5-17 4:15:00','Tom',25,800);
28insert into Table1 values('2007-5-17 3:30:00','Jerry',20,200);
29insert into Table1 values('2007-5-17 4:00:00','Jerry',20,300);
30insert into Table1 values('2007-5-17 4:15:00','Jerry',20,500);
31insert into Table1 values('2007-5-17 2:30:00','Jerry',20,150);
32GO
33
34insert into Table2
35select dateadd(hour,stime,'2007-5-17') newtime,ac,bi,cisum from --两个select，因为里面的一个是聚合查询，外面一个要将时间转化回来，不要企图合并
36 ( select datediff(hour,'2007-5-17',myTime) stime,ac,bi,sum(ci) cisum
37 from Table1
38 where (not exists --对插入数据进行保护，如果仅仅查询，这部分就不需要
39 ( select * from Table2
40 where myTime>='2007-5-17'
41 and myTime<'2007-5-18'
42 )
43 )
44 and (myTime>='2007-5-17' and myTime <'2007-5-18') --统计转存某一时段的数据
45 group by datediff(hour,'2007-5-17',myTime),ac,bi --分组方法
46 ) tablea
47order by newtime
48
49
/** SQL Server **/
2
CREATE TABLE [dbo].[Table1] (3
[myTime] [datetime] NOT NULL ,4
[ac] [char] (10) NOT NULL ,5
[bi] [int] NOT NULL ,6
[ci] [int] NOT NULL 7
)8
GO9
10
CREATE TABLE [dbo].[Table2] (11
[myTime] [datetime] NOT NULL ,12
[ac] [char] (10) NOT NULL ,13
[bi] [int] NOT NULL ,14
[ci] [int] NOT NULL 15
)16
GO17
18
delete from Table1;19
delete from Table2;20
21
insert into Table1 values('2007-5-17 1:00:00','Tom',25,300);22
insert into Table1 values('2007-5-17 1:15:00','Tom',25,400);23
insert into Table1 values('2007-5-17 1:30:00','Tom',25,500);24
insert into Table1 values('2007-5-17 2:00:00','Tom',25,600);25
insert into Table1 values('2007-5-17 2:30:00','Tom',25,550);26
insert into Table1 values('2007-5-17 3:00:00','Tom',25,560);27
insert into Table1 values('2007-5-17 4:15:00','Tom',25,800);28
insert into Table1 values('2007-5-17 3:30:00','Jerry',20,200);29
insert into Table1 values('2007-5-17 4:00:00','Jerry',20,300);30
insert into Table1 values('2007-5-17 4:15:00','Jerry',20,500);31
insert into Table1 values('2007-5-17 2:30:00','Jerry',20,150);32
GO33
34
insert into Table2 35
select dateadd(hour,stime,'2007-5-17') newtime,ac,bi,cisum from --两个select，因为里面的一个是聚合查询，外面一个要将时间转化回来，不要企图合并36
( select datediff(hour,'2007-5-17',myTime) stime,ac,bi,sum(ci) cisum 37
from Table1 38
where (not exists --对插入数据进行保护，如果仅仅查询，这部分就不需要39
( select * from Table2 40
where myTime>='2007-5-17' 41
and myTime<'2007-5-18'42
)43
)44
and (myTime>='2007-5-17' and myTime <'2007-5-18') --统计转存某一时段的数据45
group by datediff(hour,'2007-5-17',myTime),ac,bi --分组方法46
) tablea 47
order by newtime48
49
1/** ORACLE **/
2
3CREATE TABLE Table1 (
4myTime DATE NOT NULL ,
5ac varchar2(10) NOT NULL ,
6bi int NOT NULL ,
7ci int NOT NULL
8);
9
10CREATE TABLE Table2 (
11myTime DATE NOT NULL ,
12ac varchar2(10) NOT NULL ,
13bi int NOT NULL ,
14ci int NOT NULL
15);
16
17delete from Table1;
18delete from Table2;
19
20insert into Table1 values(TO_DATE('2007-5-17 1:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,300);
21insert into Table1 values(TO_DATE('2007-5-17 1:15:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,400);
22insert into Table1 values(TO_DATE('2007-5-17 1:30:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,500);
23insert into Table1 values(TO_DATE('2007-5-17 2:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,600);
24insert into Table1 values(TO_DATE('2007-5-17 2:30:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,550);
25insert into Table1 values(TO_DATE('2007-5-17 3:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,560);
26insert into Table1 values(TO_DATE('2007-5-17 4:15:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,800);
27insert into Table1 values(TO_DATE('2007-5-17 3:30:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,200);
28insert into Table1 values(TO_DATE('2007-5-17 4:00:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,300);
29insert into Table1 values(TO_DATE('2007-5-17 4:15:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,500);
30insert into Table1 values(TO_DATE('2007-5-17 2:30:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,150);
31commit;
32
33insert into Table2
34select TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss')+stime/24 newtime,ac,bi,cisum from
35 ( select trunc((mytime-TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss'))*24) stime,ac,bi,sum(ci) cisum
36 from Table1
37 where (not exists
38 (select * from Table2
39 where mytime>=TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss')
40 and mytime<TO_DATE('2007-5-18 0:00:00','yyyy-mm-dd hh24:mi:ss')
41 )
42 )
43 and ( mytime>=TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss')
44 and mytime<TO_DATE('2007-5-18 0:00:00','yyyy-mm-dd hh24:mi:ss'))
45 group by trunc((mytime-TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss'))*24),ac,bi) tablea
46order by newtime;
47
48
/** ORACLE **/2
3
CREATE TABLE Table1 (4
myTime DATE NOT NULL ,5
ac varchar2(10) NOT NULL ,6
bi int NOT NULL ,7
ci int NOT NULL 8
);9
10
CREATE TABLE Table2 (11
myTime DATE NOT NULL ,12
ac varchar2(10) NOT NULL ,13
bi int NOT NULL ,14
ci int NOT NULL 15
);16
17
delete from Table1;18
delete from Table2;19
20
insert into Table1 values(TO_DATE('2007-5-17 1:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,300);21
insert into Table1 values(TO_DATE('2007-5-17 1:15:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,400);22
insert into Table1 values(TO_DATE('2007-5-17 1:30:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,500);23
insert into Table1 values(TO_DATE('2007-5-17 2:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,600);24
insert into Table1 values(TO_DATE('2007-5-17 2:30:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,550);25
insert into Table1 values(TO_DATE('2007-5-17 3:00:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,560);26
insert into Table1 values(TO_DATE('2007-5-17 4:15:00','yyyy-mm-dd hh24:mi:ss'),'Tom',25,800);27
insert into Table1 values(TO_DATE('2007-5-17 3:30:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,200);28
insert into Table1 values(TO_DATE('2007-5-17 4:00:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,300);29
insert into Table1 values(TO_DATE('2007-5-17 4:15:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,500);30
insert into Table1 values(TO_DATE('2007-5-17 2:30:00','yyyy-mm-dd hh24:mi:ss'),'Jerry',20,150);31
commit;32
33
insert into Table234
select TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss')+stime/24 newtime,ac,bi,cisum from35
( select trunc((mytime-TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss'))*24) stime,ac,bi,sum(ci) cisum 36
from Table1 37
where (not exists38
(select * from Table2 39
where mytime>=TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss') 40
and mytime<TO_DATE('2007-5-18 0:00:00','yyyy-mm-dd hh24:mi:ss')41
)42
) 43
and ( mytime>=TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss') 44
and mytime<TO_DATE('2007-5-18 0:00:00','yyyy-mm-dd hh24:mi:ss'))45
group by trunc((mytime-TO_DATE('2007-5-17 0:00:00','yyyy-mm-dd hh24:mi:ss'))*24),ac,bi) tablea 46
order by newtime;47
48
第二类解决方法
不具体写了，因为要比上面的简单，这里就非常简单的表示一下查询原理，SQL Server
1CREATE TABLE [dbo].[Table3] (
2[starttime] [datetime] NULL ,
3[endtime] [datetime] NULL ,
4[id] [int] NULL --看你怎么用了，没有这个也可以
5)
6GO
7
8delete from Table3;
9
10insert into Table3 values('2007-5-17 0:0:0','2007-5-17 2:0:0',1);
11insert into Table3 values('2007-5-17 3:0:0','2007-5-17 5:0:0',2);
12GO
13
14select id,ac,bi,sum(ci) cisum
15 from Table1,Table3 where ( myTime>=starttime and myTime<endTime)
16 group by id,ac,bi order by id
17
CREATE TABLE [dbo].[Table3] (2
[starttime] [datetime] NULL ,3
[endtime] [datetime] NULL ,4
[id] [int] NULL --看你怎么用了，没有这个也可以5
)6
GO7
8
delete from Table3;9
10
insert into Table3 values('2007-5-17 0:0:0','2007-5-17 2:0:0',1);11
insert into Table3 values('2007-5-17 3:0:0','2007-5-17 5:0:0',2);12
GO13
14
select id,ac,bi,sum(ci) cisum 15
from Table1,Table3 where ( myTime>=starttime and myTime<endTime)16
group by id,ac,bi order by id17
浙公网安备 33010602011771号