sql - 如何为每个组生成汇总统计数据并转置？

Question

我有两个表，如下所示

我正在尝试类似下面的东西

select person_id,
   min(value),max(value),count(value),stddev(value)
   percentile_cont(0.25) within group (order by value_as_number asc) as "25pc",
   percentile_cont(0.75) within group (order by value_as_number asc) as "75pc"
from cdm.measurement
group by person_id

如果一个person doesn't have a specific reading, the value should NA as shown in screenshot below

我想做两件事

1）创建与表中读数数量一样多的列Readings（只有唯一的ID）。例如，如果 Readings 表有 800 个阅读 id，我们将有 800 * 6 = 4800 列的 person。6 is used here because of min,max,count,stddev,25th percentile,75 percentile. 列的名称将是R_name followed by 25%,75%,min,max etc。例如：Read_1_25%、Read_1_min

2）如果人没有任何读数，他的缺失读数值为NA。例如，`person_id = 1 只有 R1 读数。所以剩下的 4794 (4800 - 6) 列将是 NA

我希望我的输出如下所示。由于图片比较宽，请点击图片放大。忽略我的屏幕截图中值的正确性。格式是我正在寻找你的帮助

标签： sqlpostgresqlaggregate-functions

Answer 1

如果我理解正确，您可以使用条件聚合：

select person_id,
       count(*) filter (where reading = 'R_1') as cnt_r_1,
       min(value) filter (where reading = 'R_1') as min_r_1,
       max(value) filter (where reading = 'R_1') as max_r_1,
       avg(value) filter (where reading = 'R_1') as avg_r_1,
       stdev(value) filter (where reading = 'R_1') as stdev_r_1,
       count(*) filter (where reading = 'R_2') as cnt_r_2,
       min(value) filter (where reading = 'R_2') as min_r_2,
       max(value) filter (where reading = 'R_2') as max_r_2,
       avg(value) filter (where reading = 'R_2') as avg_r_2,
       stdev(value) filter (where reading = 'R_2') as stdev_r_2,
       . . .
from t
group by person_id;