New array aggregate function

Question

I have a table like this:

id | array  
1  | {8,8,8,x,u,x,x}  
2  | {8,8,8,x,8,x,x}  
...|...
n  | {8,u,u,x,u,x,x}

It contains time of work each employee (cols in array are days of week, u and x are days free of work).
Is it possible to aggregate those rows into one containing sum of each col (if it's an int)?

My goal is to get from query answer like this: {24,16,16,0,8,0,0}

Care to explain `x` and `u`? Your Postgres version and the data type in use? Do you want to aggregate *n* rows or just combine *two*? — Erwin Brandstetter, May 20 '13 at 16:21
i need to aggregate n rows; array is made by function `string_to_array` so i assume that all data in it are strings; pgsql version is 9.0 — Vexator, May 21 '13 at 07:50
@Vexator: reading your last comment, that is *exactly* what Erwin's answer does. Additionally in your question you say you have a **table** with an array column, now suddenly your array is the result of a function call inside a query. Please tell us what you really have. — , May 21 '13 at 07:57
ok, originally i have strings with are looking like this: `'09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE|09600480Z|XE|XE'`... i need to aggregate `n` rows like this and in this aggregated cell i need to have an array. From each part of string i need only first four digits. For example: row1: `'09600480Z|XE|XE|09600480Z|...'`; row2: `'09600480Z|09600480Z|XE|XE|...'` => `{1920,960,0,960,...}` because 09600480Z = 960; XE = 0 so 09600480Z + 09600480Z = 960 + 960 = 1920; XE + 09600480Z = 0 + 960 =960; — Vexator, May 21 '13 at 08:47
@Vexator: Please edit your question with this essential information. Comments with data are hard to read. — Erwin Brandstetter, May 21 '13 at 16:32

score 2 · Answer 1 · edited May 23 '17 at 12:18

2

Assuming int[] and ignoring the unexplained x and u, one way to solve it would be with unnest() and row_number(), followed by aggregation and an array constructor.

WITH t(i,a) AS (
   VALUES
    (1, '{8,8,8,0,0,0,0}'::int[])
   ,(2, '{8,8,8,0,8,0,0}')
   )
SELECT ARRAY(
   SELECT sum(elem)
   FROM  (
      SELECT unnest(a) AS elem
            ,generate_subscripts(a, 1) AS idx
      FROM   t
      ) sub
   GROUP  BY idx
   ORDER  BY idx
   );

Result:

{16,16,16,0,8,0,0}

The upcoming Postgres 9.4 (currently beta) provides WITH ORDINALITY:
PostgreSQL unnest() with element number

Simplifies things further:

WITH t(i,a) AS (
   VALUES
    (1, '{8,8,8,0,0,0,0}'::int[])
   ,(2, '{8,8,8,0,8,0,0}')
   )
SELECT ARRAY(
   SELECT sum(elem)
   FROM   unnest(a) WITH ORDINALITY AS x (elem, idx)
   GROUP  BY idx
   ORDER  BY idx
   );

edited May 23 '17 at 12:18

Community

1
1

answered May 20 '13 at 15:22

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you for the reply, unfortunately it will not work for me :/ – Vexator May 21 '13 at 07:26
1

@Vexator: instead of obfuscating your question with `x` and `u` and "*not work*" statements you should try to say *why* Erwin's answer doesn't do what you want it to do. Given your level of requirements it's the best answer you can get. If this isn't what you are looking for you have to be **much** more specific. – May 21 '13 at 07:45

New array aggregate function

1 Answers1