Postgres Functions Across Columns

huangapple go评论56阅读模式
英文:

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;

Db<>fiddle.

英文:

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.

huangapple
  • 本文由 发表于 2023年3月23日 09:26:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75818548.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定