Grouping by a given number of row values

Question

I have a list of values in a column 1, now I want to get the sum of next 5 row values or 6 row values like below and populate the value in appropriate column.

for example if you see 1st row value of the column 'next-5row-value' would be the sum of values from the current row to the next 5 rows which would be 9 and the next column would be sum of next 5 row values from that reference point.

I am trying to write functions to loop through to arrive at the sum. Is there an efficient way . can some one help me out. I am using postgres, greenplum . Thanks!

Answer 1

for example if you have this simple table:

sebpa=# \d numbers
                         Table "public.numbers"
 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('numbers_id_seq'::regclass)
 i      | integer |

sebpa=# select * from numbers limit 15;
  id  | i
------+---
 3001 | 3
 3002 | 0
 3003 | 5
 3004 | 1
 3005 | 1
 3006 | 4
 3007 | 1
 3008 | 1
 3009 | 4
 3010 | 0
 3011 | 4
 3012 | 0
 3013 | 3
 3014 | 2
 3015 | 1
(15 rows)

you can use this sql:

sebpa=# select id, i, sum(i) over( order by id rows between 0 preceding and 4 following) from numbers;
  id  | i | sum
------+---+-----
 3001 | 3 |  10
 3002 | 0 |  11
 3003 | 5 |  12
 3004 | 1 |   8
 3005 | 1 |  11
 3006 | 4 |  10
 3007 | 1 |  10
 3008 | 1 |   9
 3009 | 4 |  11
 3010 | 0 |   9
 3011 | 4 |  10
 3012 | 0 |  10
 3013 | 3 |  13
 3014 | 2 |  15
 3015 | 1 |  17
 3016 | 4 |  20
 3017 | 3 |  17
--cutted output