Function to create N arrays from one data group

Question

I am new in PL/pgsql and I would like to create a new Function.

In my case I have this table:

Column 1: VARIABLE = 2 2 2 2 2 2 2 2 2 2
Column 2: VALUE = 20 20 20 20 180 180 180 180 180 180
Column3: STRETCH = 1 1 1 1 1 1 2 2 2 2

And I would like to do a function to create N arrays from field VALUE where the STRETCH is different, for example:

Array 1: 20,20,20,20,180,180,
Array 2: 180,180,180,180,

How can I do it?

the select to do that should be "select array_agg(VALUE) from t group by STRETCH". I think you can work out the function yourself? — Leo
– Leo, Commented Oct 25, 2013 at 13:17
Please provide a proper table definition (like \d tbl in psql would give you), your version of Postgres. And "different" from what? — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 26, 2013 at 0:34
The problem is that stackoverflow don´t let me attach a image capture from my table. Anyway my columns are VARIABLE(integer), VALUE(real) and STRETCH(integer). My version Postgresql is 9.1. I would like as result few arrays from the column VALUE where the condition is the STRETCH value. In the above example while the value of STRETCH is '1' I get all values from VALUE and I create a array1, then while the value is '2' I get all values from column VALUE and I create another array, array2. I want to do this because then I would like get all my arrays and calculate the mean value each. — Acicate
– Acicate, Commented Oct 26, 2013 at 9:28
if you need the mean for each STREACH what's wrong with a simple "select STRETCH, avg(VALUE) from t group by STRETCH" ??? — Leo
– Leo, Commented Oct 26, 2013 at 17:44
@Acicate: Please use "edit" left under your question to update it with basic information. Don't squeeze that into comments. Table definitions should never be provided as images. We can't copy / paste images to work with them. Also, you can use SQLfiddle to provide a test case. Random example: sqlfiddle.com/#!12/80d87/2 — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 26, 2013 at 22:35

Community · Accepted Answer · 2017-05-23 12:14:17Z

1

This would be a useful way to provide a test case:

CREATE TABLE tbl (variable int, value real, stretch int);
INSERT INTO tbl VALUES
  (2, 20, 1)
 ,(2, 20, 1)
 ,(2, 20, 1)
 ,(2, 20, 1)
 ,(2, 180, 1)
 ,(2, 180, 1)
 ,(2, 180, 2)
 ,(2, 180, 2)
 ,(2, 180, 2)
 ,(2, 180, 2);

What you asked

SELECT variable, stretch, array_agg(value) AS val_arr
FROM   tbl
GROUP  BY 1,2
ORDER  BY 1,2;

What you seem to need

I want to do this because then I would like get all my arrays and calculate the mean value each.

SELECT variable, stretch, avg(value) AS val_avg
FROM   tbl
GROUP  BY 1,2
ORDER  BY 1,2;

-> SQLfiddle demo.

Aggregate functions in the manual.

Try a search to find plenty of code examples how to create a function.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Oct 26, 2013 at 22:54

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Function to create N arrays from one data group

1 Answer 1

What you asked

What you seem to need

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

What you asked

What you seem to need

Comments

Your Answer

Sign up or log in

Post as a guest

Related