Postgres Functions Across Columns

Question

I've got unstacked data in a Postgres database, trying to perform calculations across columns.

Here's my data:

group	obs1	obs2	obs3	obs4	obs5
1	74.030	74.002	74.019	73.992	74.008
2	73.995	73.992	74.001	74.011	74.004
3	73.988	74.024	74.021	74.005	74.002
4	74.002	73.996	73.993	74.015	74.009

Trying to get the average and range (Max()-Min()) across columns. Below is the desired outcome:

| group | obs1  |  obs2 |  obs3 | obs4  | obs5|grp_avg| grp_range|
|:-----:|:-----:|:-----:|:-----:|:-----:|:-----:|:-----:|:------:|
|1      |74.030 | 74.002| 74.019| 73.992| 74.008| 74.010| 0.038 |
|2      |73.995 | 73.992| 74.001| 74.011| 74.004| 74.001| 0.019 |
|3      |73.988 | 74.024| 74.021| 74.005| 74.002| 74.008| 0.036 |
|4      |74.002 | 73.996| 73.993| 74.015| 74.009| 74.003| 0.022 |

I looked at this question and don't understand, but don't believe it solves this. Based on that question it pointed to Postgres documentation but it seems way more complicated than I am looking to do.

In a Pandas DataFrame (how I'm doing this now) there is the option of changing the axis parameter, causing functions to work on columns rather than rows. I'm hopeful Postgres has some kind of similar functionality.

No, you don't need a custom aggregate function. SELECT *, (SELECT avg(val) FROM unnest(ARRAY[obs1, obs2, obs3, obs4, obs5]) val) AS grp_avg FROM my_table will do. — Bergi
– Bergi, Commented Mar 23, 2023 at 2:23
But yes, storing data in a non-normalised form will require queries that are slightly more complicated than GROUP BY. — Bergi
– Bergi, Commented Mar 23, 2023 at 2:24

klin · Accepted Answer · 2023-03-23 03:31:14Z

2

Basically, the quoted accepted solution answers your question. To understand it better, it can be represented with a common table expression. This has the advantage that the query is created step by step and you can check the results at each stage (see db<>fiddle).

with one_column as (
    select 
        grp, 
        unnest(array[obs1, obs2, obs3, obs4, obs5]) as elem
    from my_table
),
aggs as (
    select 
        grp, 
        avg(elem)::numeric(20, 3), 
        max(elem)- min(elem) as rng
    from one_column
    group by grp
)
select 
    grp, 
    obs1, obs2, obs3, obs4, obs5,
    avg, 
    rng
from aggs
join my_table using(grp)
order by grp;

Alternatively, you can just calculate the aggregates using the functions greatest() and least().

select 
    grp, obs1, obs2, obs3, obs4, obs5,
    ((obs1+ obs2+ obs3+ obs4+ obs5)/ 5)::numeric(20, 3) as avg,
    greatest(obs1, obs2, obs3, obs4, obs5)- least(obs1, obs2, obs3, obs4, obs5) as rng
from my_table;

Db<>fiddle.

edited Mar 23, 2023 at 3:31

answered Mar 23, 2023 at 3:12

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Python_Learner Over a year ago

Your 'about' section quote is the funniest thing I've read in a while!

Python_Learner Over a year ago

Step by step CTE in db<>fiddle was helpful to understand - thank you!

Collectives™ on Stack Overflow

Postgres Functions Across Columns

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