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
– Erwin Brandstetter, Commented May 20, 2013 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
– Vexator, Commented May 21, 2013 at 7: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. — user330315
– user330315, Commented May 21, 2013 at 7: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
– Vexator, Commented May 21, 2013 at 8:47
@Vexator: Please edit your question with this essential information. Comments with data are hard to read. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 21, 2013 at 16:32

Community · Accepted Answer · 2017-05-23 12:18:52Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered May 20, 2013 at 15:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vexator Over a year ago

Thank you for the reply, unfortunately it will not work for me :/

user330315 Over a year ago

@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.

Collectives™ on Stack Overflow

New array aggregate function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related