如何为每个组生成摘要统计信息并进行转置？ [英] How to generate summary statistics for each group and transpose it?

问题描述

我有两个表，如下所示

我正在尝试以下操作

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

如果人员没有特定的读数，该值应为NA，如下面的屏幕快照所示。

我想做两件事

1）创建与 Readings 表中的读数数量一样多的列（只有唯一的ID）。 例如，如果读数表具有800个阅读ID，则我们将为一个人提供800 * 6 = 4800列。在这里使用 6是因为min，max，count，stddev，25％，75％。列的名称为 R_name，后跟25％，75％，min，max等。例如：Read_1_25％，Read_1_min

1) Create as many columns as the number of readings in Readings table (has only unique ids). For example if Readings table has 800 reading ids, we will have 800 * 6 = 4800 columns for a person. 6 is used here because of min,max,count,stddev,25th percentile,75 percentile. Name of the column will be R_name followed by 25%,75%,min,max etc. Ex: Read_1_25%, Read_1_min

2）如果某人没有任何读数，则他缺少读数的值为NA。例如， person_id = 1仅具有R1读数。因此，其余4794（4800-6）列将为NA

2) If person doesn't have any readings, his value for missing readings will be NA. for example, `person_id = 1 has only R1 reading. So rest of the 4794 (4800 - 6) columns will be NA

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

I expect my output to be like as shown below. As the image is wide,Please click on the image to enlarge it. ignore the correctness of values in my screenshot. format is what I am looking for your help

推荐答案

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

If I understand correctly, you can use conditional aggregation:

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;

这篇关于如何为每个组生成摘要统计信息并进行转置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！