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