Postgresql sum with transpose

Question

我的输入表有每年属于特定类别的事件值计数。

<表类> 事件年类别tbody><<tr>162022132022B32022C1132022D112022D222022D35202382023B

这是一个旋转任务的情况。

在您的特定情况下，CASE表达式帮助您收集要汇总的特定事件计数，然后您可以根据year_值进行汇总。您可以使用LIKE操作符检测D值。

SELECT year_, 
SUM(events_)                                              AS events_total,
SUM(CASE WHEN category    = 'A'  THEN events_ ELSE 0 END) AS categoryA,
SUM(CASE WHEN category    = 'B'  THEN events_ ELSE 0 END) AS categoryB,
SUM(CASE WHEN category    = 'C'  THEN events_ ELSE 0 END) AS categoryC,
SUM(CASE WHEN category LIKE 'D%' THEN events_ ELSE 0 END) AS categoryD
FROM tab
GROUP BY year_

点击这里查看演示。

---

使用FILTER运算符，结合COALESCE函数，将得到如下结果:

SELECT year_, 
SUM(events_)                                               AS events_total,
COALESCE(SUM(events_) FILTER(WHERE category    = 'A' ), 0) AS catA,
COALESCE(SUM(events_) FILTER(WHERE category    = 'B' ), 0) AS catB,
COALESCE(SUM(events_) FILTER(WHERE category    = 'C' ), 0) AS catC,
COALESCE(SUM(events_) FILTER(WHERE category LIKE 'D%'), 0) AS catD
FROM tab
GROUP BY year_

点击这里查看演示。