Get column values from multiple rows as array

Question

I am trying to fetch column values as an array in order to use them in the function array_agg_transfn() to calculate the median value as defined in the Postgres Wiki.

The column values of a particular column I fetch based on the current row. For example, 13 rows below the current row. I tried using the following query:

select a."Week_value", 
       array_agg(a."Week_value") 
            over(order by prod_name,week_date desc 
                 rows between 0 preceding and 12 following) 
from vin_temp_table

But got this error message:

array_agg_transfn called in non-aggregate context

Is it possible to build an array from column-values in the next n rows?

It would be most helpful to provide the table definition and some sample values for your case, ideally in an SQLfiddle (example) or something like that. And fix your statement while being at it, references to a are invalid. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 24, 2014 at 17:49

Erwin Brandstetter · Accepted Answer · 2014-02-24 18:07:23Z

1

This works just fine with Postgres 9.3:

SELECT week_value
     , array_agg(week_value) OVER(ORDER BY prod_name, week_date DESC
                                  ROWS BETWEEN 0 PRECEDING AND 12 FOLLOWING)
FROM   tbl

-> SQLfiddle for 9.3

But not in version 8.4:

-> SQLfiddle for 8.4

The syntax ROWS BETWEEN frame_start AND frame_end was introduced with Postgres 9.0 and is not available in 8.4. Compare the current manual with its 8.4 counterpart.

Postgres 8.4 is rather old by now and reaching EOL this summer. Consider upgrading to the current version.

edited Feb 24, 2014 at 18:07

answered Feb 24, 2014 at 18:01

Erwin Brandstetter

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

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

Comments

krokodilko · Accepted Answer · 2014-02-24 18:35:48Z

0

If array_agg doesn't work in your version, then try this approach

WITH Q AS(
  SELECT week,
         row_number() over (order by week) rn
  FROM test
)
SELECT week,
       ( SELECT array_agg(q2.week) 
         FROM q q2
         WHERE q2.rn BETWEEN q1.rn
                     AND q1.rn + 12
       )
FROM q q1;

working demo (on 8.4.17) --> http://sqlfiddle.com/#!11/7eda9/1
but it can be slow.

answered Feb 24, 2014 at 18:35

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Collectives™ on Stack Overflow

Get column values from multiple rows as array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related