英文:
Postgres Functions Across Columns
问题
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.
英文:
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.
答案1
得分: 2
以下是翻译好的内容:
基本上,引用的已接受解决方案回答了您的问题。为了更好地理解它,可以使用公共表达式来表示它。这有一个优点,即查询是逐步创建的,您可以在每个阶段检查结果(请参见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;
或者,您可以使用greatest()和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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论