在sql中转换/透视数据

neskvpey 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(370)

很抱歉，我又问了一个与这个主题有关的问题——我通读了其他的文章，但我仍然不能弄清楚我的具体情况。
所以我有一个输出：

输出上述内容的查询是：

SELECT  (CASE
            WHEN ca.iteration = 'Pre' THEN '1. Pre'
            WHEN ca.iteration = 'Post-2' THEN '3. Post-2'
            WHEN ca.iteration = 'Post' THEN '2. Post'
            END) AS capes_iteration,
           ROUND(AVG(ca.emotional_problems_score)) AS avg_child_emotions_score,
           ROUND(AVG(ca.behavioral_problems_score)) AS avg_child_behaviors_score,
           ROUND(AVG(ca.total_score)) AS avg_total_intensity_score,
           ROUND(AVG(ca.total_parent)) AS avg_parent_confidence_score
     FROM  reporting_f5scppp.capes ca
LEFT JOIN  public.contacts c ON c.id = ca.contact_id
 GROUP BY  capes_iteration;

我希望它看起来像这样：

我花了大约4个小时的时间试图让这个没有用-我只是一个初学者的sql看到一个解决方案，这将是一个伟大的学习机会，为我。
如果有人能提供一个简单的解决方案，午餐我请客！

sql postgresql pivot transpose

来源：https://stackoverflow.com/questions/61929045/transposing-pivoting-data-in-sql

2条答案

按热度按时间

3zwjbxry1#

您可以使用横向连接取消分数。然后重新汇总：

SELECT v.which,
       AVG(v.score) FILTER (WHERE ca.iteration = 'Pre') as avg_1_pre,
       AVG(v.score) FILTER (WHERE ca.iteration = 'Post-2') as avg_3_post_2,
       AVG(v.score) FILTER (WHERE ca.iteration = 'Post') as avg_2_post
FROM reporting_f5scppp.capes ca LEFT JOIN
     public.contacts c
     ON c.id = ca.contact_id LEFT JOIN LATERAL
     (VALUES (emotional_problems_score, 'emotional'),
             (behavioral_problems_score, 'behaviors'),
             (total_score, 'intensity'),
             (total_parent, 'parent')
     ) v(score, which)
GROUP BY  v.which;

赞(0）回复(0）举报 2021-08-09

8hhllhi22#

一种选择是使用 each() 函数以获取键值元组，然后在主查询中通过条件聚合进行数据透视：

SELECT (kv).key AS capes_iteration, 
       MAX(CASE WHEN iteration = 'Pre' THEN (kv).value END) AS "1. Pre",
       MAX(CASE WHEN iteration = 'Post-2' THEN (kv).value END) AS "3. Post-2",
       MAX(CASE WHEN iteration = 'Post' THEN (kv).value END) AS "2. Post"
  FROM
  (
   SELECT iteration, each(hstore(q) - 'iteration'::text) AS kv
     FROM (
           SELECT iteration,
                  ROUND(AVG(COALESCE(ca.emotional_problems_score,0))) AS avg_child_emotions_score,
                  ROUND(AVG(COALESCE(ca.behavioral_problems_score,0))) AS avg_child_behaviors_score,
                  ROUND(AVG(COALESCE(ca.emotional_problems_score,0)+
                            COALESCE(behavioral_problems_score,0))) AS avg_total_intensity_score,
                  ROUND(AVG(ca.total_parent)) AS avg_tot_parent_confidence_score
             FROM capes ca
             LEFT JOIN  contacts c ON c.id = ca.contact_id
            GROUP BY  iteration ) q ) q2
 GROUP BY capes_iteration
 ORDER BY capes_iteration

哪里
使用 COALESCE() 函数的第二个参数为零，将空值赋给零
注意 avg_total_intensity_score 是两列相加得到的派生列
演示

赞(0）回复(0）举报 2021-08-09

我来回答

在sql中转换/透视数据

2条答案

相关问题

热门标签

最新问答