CODEWITHSUNDEEP
arraysaggregatewindow-functionspostgresql-9.2
vyegorov
vyegorov

Reputation: 22915

Aggregate values by row count

I have a series of rows and I need to aggregate values from these rows into the groups of N elements, accumulating values from current and N-1 succeeding rows.

With N=3 and data being:

VALUES (1),(2),(3),(4),(5);

I want to receive the following set of rows (arrays):

{1,2,3}
{2,3,4}
{3,4,5}
{4,5}
{5}

It is important, that N is a variable, so I cannot use joins.

Upvotes: 1

Views: 89

Answers (1)

vyegorov
vyegorov

Reputation: 22915

Well, this can be solved using frames together with window functions.

The question in subject can be solved like this:

WITH v(v) AS (VALUES (1),(2),(3),(4),(5))
SELECT v,
       array_agg(v) OVER (ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS arr
  FROM v;

And the following example illustrates how to get a list of complete arrays, i.e. eliminate those that don't contain all N entries:

WITH cnt(c) AS (SELECT 3),
     val(v) AS (VALUES (1),(2),(3),(4),(5)),
     arr AS
(SELECT v,
        array_agg(v) OVER (ROWS BETWEEN CURRENT ROW
                           AND (SELECT c-1 FROM cnt) FOLLOWING) AS arr
   FROM val)
SELECT v,arr
  FROM arr
 WHERE array_upper(arr,1) = (SELECT c FROM cnt);

I really love window functions!

Upvotes: 1

Related Questions