CODEWITHSUNDEEP
sqlarrayspostgresqlwindow-functions
Nick
Nick

Reputation: 565

Cumulative array from previous rows array?

Is there a query that would merge previous arrays into a cumulative array, that would result in:

id array_field   c_array
---------------------------------------------
1  {one,two}     {one,two}
2  {three}       {one,two,three}
3  {four,five}   {one,two,three,four,five}
4  {six}         {one,two,three,four,five,six}

Upvotes: 3

Views: 607

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658062

It depends on what you work with. Seems like your base table holds text arrays text[].

In addition to these functions, any built-in or user-defined aggregate function can be used as a window function

To aggregate array types, create this aggregate function:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

Details in this related answer:
Selecting data into a Postgres array

Now, the job is strikingly simple:

SELECT array_agg_mult(array_field) OVER (ORDER BY id) AS result_array
FROM   tbl

Since the aggregate is defined for polymorphic types, this works for any array type, not just text[].

SQL Fiddle including alternative solution for text representation in a list.

Upvotes: 2

Tomas Greif
Tomas Greif

Reputation: 22663

You can use recursive CTE.

SQLFiddle

Data:

-- drop table if exists sample_data;
create table sample_data (id int, arr varchar[]);

insert into sample_data
  values
    (1,ARRAY['One','Two','Three']),
    (2,ARRAY['Four','Six']),    
    (3,ARRAY['Seven']);

Query:

with recursive cte as (
  select 
    id, arr, arr as merged_arr 
  from 
    sample_data
  where
    id = 1

  union all

  select 
    sd.id, sd.arr, cte.merged_arr || sd.arr
  from
    cte
    join sample_data sd on (cte.id + 1  = sd.id)
)

select * from cte

Result:

1;{One,Two,Three};{One,Two,Three}
2;{Four,Six};     {One,Two,Three,Four,Six}
3;{Seven};        {One,Two,Three,Four,Six,Seven}

Upvotes: 1

Patrick
Patrick

Reputation: 32316

You need an UPDATE with a CTE that refers back to the previous id:

WITH prev AS (SELECT id, c_array AS prev_array FROM your_table)
UPDATE your_table
SET c_array = prev_array || array_field
FROM prev
WHERE id = prev.id + 1;

If you want to do this automatically, then you need a BEFORE INSERT trigger and in the trigger function you simply execute SELECT c_array || NEW.array_field INTO NEW.c_array FROM your_table WHERE id = NEW.id - 1 if - and only if - you set the id yourself. If the id comes from a sequence then you need an AFTER INSERT trigger with an update as described above.

Upvotes: 0

Related Questions