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.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
CREATE TABLE T (
DT TIMESTAMP,
SK INTEGER,
L DOUBLE PRECISION,
R DOUBLE PRECISION
);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:05:00', 1, 1.5, 1.396);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:06:00', 2, 1.5, 1.46);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:08:00', 3, 1.5, 1.644);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:09:00', 4, 1.5, 1.81);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:10:00', 1, 1.6, 1.536);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:11:00', 2, 1.6, 1.563);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:13:00', 3, 1.6, 1.645);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:14:00', 4, 1.6, 1.696);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:16:00', 5, 0.9, 1.139);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:16:00', 6, 1, 1.139);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:18:00', 1, 1, 1.139);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:19:00', 2, 1.2, 1.259);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:25:00', 1, 0.9, 1.527);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:26:00', 2, 1, 1.528);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:27:00', 3, 1.1, 1.596);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:29:00', 4, 1.2, 1.917);
INSERT INTO T (DT, SK, L, R)
VALUES ('2018-05-31 00:32:00', 5, 0.7, 1.528);
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.
with cte1 as (
select
T.*
,row_number()over(order by DT) as rn
from T
where SK=2),
cte2 as (
select
t1.rn
,T.*
,row_number()over(partition by t1.rn order by T.SK desc) as flag
from cte1 t1 left join cte1 t2 on t1.rn=t2.rn-1
inner join T on T.DT >= t1.DT and (T.DT < t2.DT or t2.DT is null)
)
select
cte1.DT as DT1
,cte1.SK as SK1
,cte1.L as L1
,cte1.R as R1
,cte2.DT as DT2
,cte2.SK as SK2
,cte2.L as L2
,cte2.R as R2
from cte1 inner join cte2 on cte1.rn=cte2.rn
where cte2.flag=1
DT1SK1L1R1DT2SK2L2R231.05.2018 0:0621,501,4631.05.2018 0:0941,501,8131.05.2018 0:1121,601,5631.05.2018 0:1661,001,1431.05.2018 0:1921,201,2631.05.2018 0:1921,201,2631.05.2018 0:2621,001,5331.05.2018 0:3250,701,53 |
