sql - 使用 PostgreSQL 的透视 View

Question

我是 PostgreSQL 新手，使用的是 9.4 版。我有一个表，其中收集的测量值作为字符串，需要使用始终保持最新状态的内容(例如 VIEW)将其转换为一种 PIVOT 表。
此外，一些值需要转换，例如。 G。乘以 1000，就像你可以在下面的示例中看到“sensor3”。

源表:

CREATE TABLE source (
    id bigint NOT NULL,
    name character varying(255),
    "timestamp" timestamp without time zone,
    value character varying(32672),
    CONSTRAINT source_pkey PRIMARY KEY (id)
);

INSERT INTO source VALUES
  (15,'sensor2','2015-01-03 22:02:05.872','88.4')
, (16,'foo27'  ,'2015-01-03 22:02:10.887','-3.755')
, (17,'sensor1','2015-01-03 22:02:10.887','1.1704')
, (18,'foo27'  ,'2015-01-03 22:02:50.825','-1.4')
, (19,'bar_18' ,'2015-01-03 22:02:50.833','545.43')
, (20,'foo27'  ,'2015-01-03 22:02:50.935','-2.87')
, (21,'sensor3','2015-01-03 22:02:51.044','6.56');

源表结果:

| id | name      | timestamp                 | value    |
|----+-----------+---------------------------+----------|
| 15 | "sensor2" | "2015-01-03 22:02:05.872" | "88.4"   |
| 16 | "foo27"   | "2015-01-03 22:02:10.887" | "-3.755" |
| 17 | "sensor1" | "2015-01-03 22:02:10.887" | "1.1704" |
| 18 | "foo27"   | "2015-01-03 22:02:50.825" | "-1.4"   |
| 19 | "bar_18"  | "2015-01-03 22:02:50.833" | "545.43" |
| 20 | "foo27"   | "2015-01-03 22:02:50.935" | "-2.87"  |
| 21 | "sensor3" | "2015-01-03 22:02:51.044" | "6.56"   |

期望的最终结果:

| timestamp                 | sensor1 | sensor2 | sensor3 | foo27   | bar_18  |
|---------------------------+---------+---------+---------+---------+---------|
| "2015-01-03 22:02:05.872" |         | 88.4    |         |         |         |
| "2015-01-03 22:02:10.887" | 1.1704  |         |         | -3.755  |         |
| "2015-01-03 22:02:50.825" |         |         |         | -1.4    |         |
| "2015-01-03 22:02:50.833" |         |         |         |         | 545.43  |
| "2015-01-03 22:02:50.935" |         |         |         | -2.87   |         |
| "2015-01-03 22:02:51.044" |         |         | 6560.00 |         |         |

使用这个:

--    CREATE EXTENSION tablefunc;
SELECT *
    FROM
        crosstab(
            'SELECT
                source."timestamp",
                source.name,
                source.value
            FROM
                public.source
            ORDER BY
                1'
            ,
            'SELECT
                DISTINCT
                source.name
            FROM
                public.source
            ORDER BY
                1'
        )
    AS
        (
            "timestamp" timestamp without time zone,
            "sensor1" character varying(32672),
            "sensor2" character varying(32672),
            "sensor3" character varying(32672),
            "foo27" character varying(32672),
            "bar_18" character varying(32672)
        )
    ;

我得到了结果:

| timestamp                 | sensor1 | sensor2 | sensor3 | foo27   | bar_18  |
|---------------------------+---------+---------+---------+---------+---------|
| "2015-01-03 22:02:05.872" |         |         |         | 88.4    |         |
| "2015-01-03 22:02:10.887" |         | -3.755  | 1.1704  |         |         |
| "2015-01-03 22:02:50.825" |         | -1.4    |         |         |         |
| "2015-01-03 22:02:50.833" | 545.43  |         |         |         |         |
| "2015-01-03 22:02:50.935" |         | -2.87   |         |         |         |
| "2015-01-03 22:02:51.044" |         |         |         |         | 6.56    |

不幸的是，

1. 值未分配给正确的列，
2. 列不是动态的；这意味着当名称列中有其他条目(如“sensor4”和
)时查询失败
3. 我不知道如何更改某些列的值(乘法)。

Answer 1

你的查询是这样的:

SELECT * FROM crosstab(
  $$SELECT "timestamp", name
         , CASE name
           WHEN 'sensor3' THEN value::numeric * 1000
       --  WHEN 'sensor9' THEN value::numeric * 9000  -- add more ...
           ELSE value::numeric END AS value
    FROM   source
    ORDER  BY 1, 2$$
 ,$$SELECT unnest('{bar_18,foo27,sensor1,sensor2,sensor3}'::text[])$$
) AS (
  "timestamp" timestamp
, bar_18  numeric
, foo27   numeric
, sensor1 numeric
, sensor2 numeric
, sensor3 numeric);

要将所选列的值相乘，请使用"simple" CASE陈述。但是您需要转换为 numeric type第一的。在示例中使用 value::numeric。
这就引出了一个问题:为什么不首先将值存储为数字类型？

您需要使用带有两个参数的版本。详细解释:

• PostgreSQL Crosstab Query

真正动态交叉表几乎是不可能的，因为 SQL 要求提前知道结果类型 - 最迟在调用时。但是你可以用多态类型做一些事情:

• Dynamic alternative to pivot with CASE and GROUP BY