1create table t1 (
2pk int primary key,
3a int,
4b int,
5c real
6);
7insert into t1 values
8(101 , 0, 10, 1.1),
9(102 , 0, 10, 2.1),
10(103 , 1, 10, 3.1),
11(104 , 1, 10, 4.1),
12(108 , 2, 10, 5.1),
13(105 , 2, 20, 6.1),
14(106 , 2, 20, 7.1),
15(107 , 2, 20, 8.15),
16(109 , 4, 20, 9.15),
17(110 , 4, 20, 10.15),
18(111 , 5, NULL, 11.15),
19(112 , 5, 1, 12.25),
20(113 , 5, NULL, 13.35),
21(114 , 5, NULL, 14.50),
22(115 , 5, NULL, 15.65),
23(116 , 6, 1, NULL),
24(117 , 6, 1, 10),
25(118 , 6, 1, 1.1),
26(119 , 6, 1, NULL),
27(120 , 6, 1, NULL),
28(121 , 6, 1, NULL),
29(122 , 6, 1, 2.2),
30(123 , 6, 1, 20.1),
31(124 , 6, 1, -10.4),
32(125 , 6, 1, NULL),
33(126 , 6, 1, NULL),
34(127 , 6, 1, NULL);
35select pk, a, b, sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
36from t1;
37pk	a	b	sum(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
38101	0	10	20
39102	0	10	20
40103	1	10	20
41104	1	10	20
42105	2	20	40
43106	2	20	60
44107	2	20	50
45108	2	10	30
46109	4	20	40
47110	4	20	40
48111	5	NULL	1
49112	5	1	1
50113	5	NULL	1
51114	5	NULL	NULL
52115	5	NULL	NULL
53116	6	1	2
54117	6	1	3
55118	6	1	3
56119	6	1	3
57120	6	1	3
58121	6	1	3
59122	6	1	3
60123	6	1	3
61124	6	1	3
62125	6	1	3
63126	6	1	3
64127	6	1	2
65select pk, a, c, sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
66from t1;
67pk	a	c	sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
68101	0	1.1	3.2
69102	0	2.1	3.2
70103	1	3.1	7.199999999999999
71104	1	4.1	7.199999999999999
72105	2	6.1	13.2
73106	2	7.1	21.35
74107	2	8.15	20.35
75108	2	5.1	13.250000000000002
76109	4	9.15	19.3
77110	4	10.15	19.3
78111	5	11.15	23.4
79112	5	12.25	36.75
80113	5	13.35	40.1
81114	5	14.5	43.5
82115	5	15.65	30.15
83116	6	NULL	10
84117	6	10	11.1
85118	6	1.1	11.1
86119	6	NULL	1.0999999999999996
87120	6	NULL	NULL
88121	6	NULL	2.1999999999999997
89122	6	2.2	22.3
90123	6	20.1	11.9
91124	6	-10.4	9.7
92125	6	NULL	-10.400000000000002
93126	6	NULL	NULL
94127	6	NULL	NULL
95drop table t1;
96