postgresql - Postgres - 按年份绑定(bind)相同类型的结果 - 从长到宽数据

Question

请原谅我不太恰当的提问方式，因为我是 postgres 的新手...

有如下两个表:

CREATE TABLE pub (
   id int
 , time timestamp
);

     id                time
1     1 2010-02-10 01:00:00
2     2 2011-02-10 01:00:00
3     3 2012-02-10 01:00:00

和

CREATE TABLE val (
   id int
 , type text
 , val int
);

     id  type   val
1     1     A     1
2     1     B     2
3     1     C     3
4     2     A     4
5     2     B     5
6     3     D     6

我想获得以下输出(对于 id <= 2 )

   type   2010   2011
1     A      1      4
2     B      2      5
3     C      3   NULL

所以 type是表 val 中存在的所有类型的超集。
NULL 表示标签 C 没有值。
理想情况下，列标题是时间的年份。或者 id 本身......

Answer 1

至少有两种方法可以做到这一点。

如果你的表格没有太多类别你可以使用 CTE

WITH x AS (
    SELECT type,
           sum(val) FILTER (WHERE date_part('year', time) = 2010) AS "2010",
           sum(val) FILTER (WHERE date_part('year', time) = 2011) AS "2011"
    FROM pub AS p JOIN val AS v ON (v.id = p.id)
    GROUP BY type
) 
SELECT * FROM x
WHERE "2010" is NOT NULL OR "2011" IS NOT NULL
ORDER BY type
;

但是如果你有很多或动态的类别，你必须使用交叉表:

CREATE EXTENSION tablefunc;

SELECT * FROM crosstab(
     $$
         SELECT type,
                date_part('year', time)::text as time,
                sum(val) AS val
           FROM pub AS p JOIN val AS v ON (v.id = p.id)
       GROUP BY type, 2 
       ORDER BY 1, 2
     $$,
     $$VALUES ('2010'::text), ('2011'), ('2012') $$
     ) AS ct (type text, "2010" int, "2011" int, "2012" int);
;