Как сделать кросс-таблицу в PostgreSQL?

СОЗДАТЬ ВРЕМЕННЫЙ СТОЛ

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;

CROSSTAB QUERY

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)
Источник

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);
Источник
Другие вопросы по тегам