postgresql - 如何在 PostgreSQL 中制作交叉表？

Question

创建临时表

CREATE TEMP TABLE pivot(
gid SERIAL,
zoom smallint NOT NULL,
day timestamp with time zone NOT NULL,
point integer NOT NULL
);

插入数据

INSERT INTO pivot(zoom, day, point) VALUES(6,'2015-10-01',21);
INSERT INTO pivot(zoom, day, point) VALUES(7,'2015-10-01',43);
INSERT INTO pivot(zoom, day, point) VALUES(8,'2015-10-01',18);
INSERT INTO pivot(zoom, day, point) VALUES(9,'2015-10-01',14);
INSERT INTO pivot(zoom, day, point) VALUES(10,'2015-10-01',23);
INSERT INTO pivot(zoom, day, point) VALUES(11,'2015-10-01',54);
INSERT INTO pivot(zoom, day, point) VALUES(6,'2015-10-02',657);
INSERT INTO pivot(zoom, day, point) VALUES(7,'2015-10-02',432);
INSERT INTO pivot(zoom, day, point) VALUES(8,'2015-10-02',421);
INSERT INTO pivot(zoom, day, point) VALUES(9,'2015-10-02',432);
INSERT INTO pivot(zoom, day, point) VALUES(10,'2015-10-02',454);
INSERT INTO pivot(zoom, day, point) VALUES(11,'2015-10-02',654);

让我们看看到目前为止是否一切正常:

SELECT zoom, day, point
FROM   pivot
ORDER  BY 1,2;

结果:

 zoom |          day           | point
------+------------------------+-------
    6 | 2015-10-01 00:00:00+02 |    21
    6 | 2015-10-02 00:00:00+02 |   657
    7 | 2015-10-01 00:00:00+02 |    43
    7 | 2015-10-02 00:00:00+02 |   432
    8 | 2015-10-01 00:00:00+02 |    18
    8 | 2015-10-02 00:00:00+02 |   421
    9 | 2015-10-01 00:00:00+02 |    14
    9 | 2015-10-02 00:00:00+02 |   432
   10 | 2015-10-01 00:00:00+02 |    23
   10 | 2015-10-02 00:00:00+02 |   454
   11 | 2015-10-01 00:00:00+02 |    54
   11 | 2015-10-02 00:00:00+02 |   654
(12 rows)

创建扩展:

CREATE EXTENSION tablefunc;

交叉查询

SELECT * FROM crosstab(
       'SELECT zoom, day, point
        FROM   pivot
        ORDER  BY 1,2'
      ,$$VALUES ('2015-10-01'::timestamp), ('2015-10-02')$$)
AS ct ("zoom" smallint, "2015-10-01" integer, "2015-10-02" integer);

结果:

 zoom | 2015-10-01 | 2015-10-02
------+------------+------------
    6 |            |
    7 |            |
    8 |            |
    9 |            |
   10 |            |
   11 |            |
(6 rows)

我无法返回点的值，查询本身给了我一个空点。我做错了什么？

编辑

我试着用另一种方式去做，但我仍在寻找上述问题的答案。

SELECT  * from crosstab (
    'select zoom, day, point
     from pivot
     order by 1,2',
    'select distinct day from pivot order by 1')
AS ct(zoom smallint, "2015-10-01" integer, "2015-10-02" integer);

结果:

 zoom | 2015-10-01 | 2015-10-02
------+------------+------------
    6 |         21 |        657
    7 |         43 |        432
    8 |         18 |        421
    9 |         14 |        432
   10 |         23 |        454
   11 |         54 |        654
(6 rows)

Answer 1

感谢@Abelisto 关于timestamptz 的建议，这可行:

SELECT * FROM crosstab(
       'SELECT zoom, day, point
        FROM   pivot
        ORDER  BY 1,2'
      ,$$VALUES ('2015-10-01'::timestamptz), ('2015-10-02')$$)
AS ct ("zoom" smallint, "2015-10-01" integer, "2015-10-02" integer);