建表:
create table TEST
(
id NUMBER(4),
sid NUMBER(4),
time date,
value VARCHAR2(200)
)
这里不知道你的sid,id是什么意思.id应该是主键 ,sid是指时间点吗?如果是时间点的那这问题还会更简单。我这里没有用这个值。
插值:
insert into TEST (ID, SID, TIME, VALUE)
values ('1', '1', to_date('12-11-2013 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');
insert into TEST (ID, SID, TIME, VALUE)
values ('2', '2', to_date('12-11-2013 10:58:00', 'dd-mm-yyyy hh24:mi:ss'), '2');
insert into TEST (ID, SID, TIME, VALUE)
values ('3', '3', to_date('12-11-2013 04:02:00', 'dd-mm-yyyy hh24:mi:ss'), '3');
insert into TEST (ID, SID, TIME, VALUE)
values ('4', '4', to_date('12-11-2013 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), '4');
insert into TEST (ID, SID, TIME, VALUE)
values ('5', '5', to_date('12-11-2013 04:06:00', 'dd-mm-yyyy hh24:mi:ss'), '5');
insert into TEST (ID, SID, TIME, VALUE)
values ('6', '6', to_date('12-11-2013 04:57:00', 'dd-mm-yyyy hh24:mi:ss'), '6');
查询:
select t3.team,t2.value
from (select distinct first_value(t.value) over(partition by to_number(to_char(t.time, 'hh24')) * 2 +
ceil(to_number(to_char(t.time, 'mi')) / 30) order by abs(t.time-trunc(t.time,'hh24')) rows between 1 preceding and 1 following) value,
to_number(to_char(t.time, 'hh24')) * 2 +
ceil(to_number(to_char(t.time, 'mi')) / 30) team
from TEST t) t2
right join (select rownum team from dual connect by level < 49) t3
on t3.team = t2.team
order by t3.team
结果:
rownum team value
1 1
2 2
3 3
4 4
5 5
6 6 4
7 7
8 8
9 9 3
10 10 1
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22 2
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30
31 31
32 32
33 33
34 34
35 35
36 36
37 37
38 38
39 39
40 40
41 41
42 42
43 43
44 44
45 45
46 46
47 47
48 48