使用PostgreSQL进行PIVOT VIEW

Question

使用PostgreSQL进行PIVOT VIEW

4

我是新手使用PostgreSQL，版本为9.4。我有一个包含字符串测量值的表，需要使用一些始终更新的东西（例如视图）将其转换为类似于PIVOT表的格式。
此外，有些值需要转换，例如“sensor3”下面的示例中乘以1000。

源表：

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    |

很不幸，

数值没有分配到正确的列中
列不是动态的，这意味着当名称列中有额外条目时（例如'sensor4'），查询会失败
我不知道如何更改一些列（乘法）的值。

- lucas0x7B

2

为什么要使用 varchar(32672)，而不是 float 或 numeric？ - Jasen

你需要另一张表，将名称与源表匹配，并具有比例因子和所需的列顺序，然后删除“as”子句。 - Jasen

@Jasen：不是我们自己发明的！ - lucas0x7B

哇！一份来自过去的惊喜。你还在使用9.4吗？ - Jasen

2个回答

1

@Erwin：评论太长了，超过了7128个字符！不管怎样：

您的帖子给了我正确方向的提示，非常感谢您，但在我的情况下，我需要它真正地动态化。目前我有 38886行，49个不同的项目（=要透视的列）。

首先回答您和@Jasen紧急的问题：源表格布局不是由我控制的，我已经很高兴将这个数据放入RDBMS中。如果由我来做，我会始终保存UTC时间戳！但是，将数据保存为字符串也有其原因：它可能包含各种数据类型，如布尔值、整数、浮点数、字符串等。

为了避免进一步困惑，我创建了一个新的演示数据集，添加了数据类型的前缀（我知道有些人讨厌这样做！）以避免关键字问题和更好的概览更改时间戳（-->分钟）：

--  --------------------------------------------------------------------------
--  Create demo table of given schema and insert arbitrary data
--  --------------------------------------------------------------------------

    DROP TABLE IF EXISTS table_source;

    CREATE TABLE table_source
    (
        column_id BIGINT NOT NULL,
        column_name CHARACTER VARYING(255),
        column_timestamp TIMESTAMP WITHOUT TIME ZONE,
        column_value CHARACTER VARYING(32672),
        CONSTRAINT table_source_pkey PRIMARY KEY (column_id)
    );

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

    SELECT * FROM table_source;

此外，基于 @Erwin 的建议，我创建了一个视图，已将数据类型进行转换。这个方法有一个很好的特点，除了速度快之外，还只对已知项目添加所需的转换，而不影响其他（新）项目。

--  --------------------------------------------------------------------------
--  Create view to process source data
--  --------------------------------------------------------------------------

    DROP VIEW IF EXISTS view_source_processed;

    CREATE VIEW
        view_source_processed
    AS
        SELECT
            column_timestamp,
            column_name,
            CASE LOWER( column_name)
                WHEN LOWER( 'sensor3') THEN CAST( column_value AS DOUBLE PRECISION) * 1000.0
                ELSE CAST( column_value AS DOUBLE PRECISION)
            END AS column_value
        FROM
            table_source
    ;

    SELECT * FROM view_source_processed ORDER BY column_timestamp DESC LIMIT 100;

这是整个问题的期望结果：

--  --------------------------------------------------------------------------
--  Desired result:
--  --------------------------------------------------------------------------

/*
| column_timestamp          | bar_18  | foo27   | sensor1 | sensor2 | seNSor3 |
|---------------------------+---------+---------+---------+---------+---------|
| "2015-01-03 22:01:05.872" |         |         |         |    88.4 |         |
| "2015-01-03 22:02:10.887" |         |  -3.755 |  1.1704 |         |         |
| "2015-01-03 22:03:50.825" |         |    -1.4 |         |         |         |
| "2015-01-03 22:04:50.833" |  545.43 |         |         |         |         |
| "2015-01-03 22:05:50.935" |         |   -2.87 |         |         |         |
| "2015-01-03 22:06:51.044" |         |         |         |         |    6560 |
*/

这是@Erwin的解决方案，针对新的演示源数据进行了调整。只要项目（=要透视的列）不改变，它就是完美的：

--  --------------------------------------------------------------------------
--  Solution by Erwin, modified for changed demo dataset:
--  https://dev59.com/n4bca4cB1Zd3GeqPX5ge#27773730
--  --------------------------------------------------------------------------

SELECT *
    FROM
        crosstab(
            $$
                SELECT
                    column_timestamp,
                    column_name,
                    column_value
                FROM
                    view_source_processed
                ORDER BY
                    1, 2
            $$
        ,
            $$
                SELECT
                    UNNEST( '{bar_18,foo27,sensor1,sensor2,seNSor3}'::text[])
            $$
        )
    AS
        (
            column_timestamp timestamp,
            bar_18  DOUBLE PRECISION,
            foo27   DOUBLE PRECISION,
            sensor1 DOUBLE PRECISION,
            sensor2 DOUBLE PRECISION,
            seNSor3 DOUBLE PRECISION
        )
    ;

当阅读@Erwin提供的链接时，我发现了@Clodoaldo Neto的动态SQL示例，并记得我已经在Transact-SQL中这样做了；以下是我的尝试：

--  --------------------------------------------------------------------------
--  Dynamic attempt based on:
--  https://dev59.com/AGnWa4cB1Zd3GeqP4tmF#12989297
--  --------------------------------------------------------------------------

DO $DO$

DECLARE
    list_columns TEXT;

    BEGIN

        DROP TABLE IF EXISTS temp_table_pivot;

        list_columns := (
            SELECT
                string_agg( DISTINCT column_name, ' ' ORDER BY column_name)
            FROM
                view_source_processed
        );

        EXECUTE(
            FORMAT(
                $format_1$
                CREATE TEMP TABLE
                    temp_table_pivot(
                        column_timestamp TIMESTAMP,
                        %1$s
                    )
                $format_1$
            ,
                (
                    REPLACE(
                        list_columns,
                        ' ',
                        ' DOUBLE PRECISION, '
                    ) || ' DOUBLE PRECISION'
                )
            )
        );

        EXECUTE(
            FORMAT(
                $format_2$
                    INSERT INTO temp_table_pivot
                        SELECT
                            *
                        FROM crosstab(
                            $crosstab_1$
                            SELECT
                                column_timestamp,
                                column_name,
                                column_value
                            FROM
                                view_source_processed
                            ORDER BY
                                column_timestamp, column_name
                            $crosstab_1$
                        ,
                            $crosstab_2$
                            SELECT DISTINCT
                                column_name
                            FROM
                                view_source_processed
                            ORDER BY
                                column_name
                            $crosstab_2$
                        )
                        AS
                        (
                            column_timestamp TIMESTAMP,
                            %1$s
                        );
                $format_2$
            ,
                REPLACE( list_columns, ' ', ' DOUBLE PRECISION, ')
                ||
                ' DOUBLE PRECISION'
            )
        );

    END;

$DO$;

SELECT * FROM temp_table_pivot ORDER BY column_timestamp DESC LIMIT 100;

除了将此内容放入存储过程中，出于性能原因，我将尝试将其调整为一个中间表，只插入新值。我会让你保持最新状态！感谢！！！L. PS：不，我不想回答自己的问题，但“评论”字段太小了！

- lucas0x7B

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

您的查询工作方式如下：

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);

要为选定列的value乘以一个"简单" CASE语句。但是您需要先转换为数字类型。在示例中使用value::numeric。
这引出了一个问题：为什么不一开始就将值存储为数字类型呢？

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

PostgreSQL交叉表查询

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

使用CASE和GROUP BY实现动态透视表的替代方案