如何使用 PostgreSQL 的 tablefunc 有效地透视多个列上的数据？

使用Tablefunc进行多列数据透视

处理包含多个属性和度量的数据时，可能需要将其从长格式转换为宽格式以进行高效分析。PostgreSQL 的 tablefunc 功能为此类转换提供了一种便捷的解决方案。但是，在处理多个透视列时，务必了解其局限性。

在对先前查询的回复中，一位用户寻求有关使用 tablefunc 进行数据透视的指导，但在处理多个透视列时遇到了挑战。由于 tablefunc 期望每行名称都具有一致的额外列，因此原始查询导致数据不完整。

问题解决

要解决此问题，务必遵守 tablefunc 指定的顺序：

1. 行名称：此列必须始终位于第一位。
2. 额外列（可选）：如果需要，任何其他列都应位于行名称列之后。
3. 类别和值（最后两列）：透视类别和值列必须按此顺序作为最后两列。

实现

在给定的示例中，所需的输出需要对两列（实体和状态）进行透视。为此，查询进行了如下修改：

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT entity, timeof, status, ct
    FROM t4
    ORDER BY 1'
 , 'VALUES (1), (0)'
   ) AS ct (
      "Attribute" character
    , "Section" timestamp
    , "status_1" int
    , "status_0" int
      );</code>

通过将 entity 作为行名称并将 timeof 和 entity 的顺序互换，查询成功地对多列进行了透视。

不同设置的变体

对于响应中提到的设置，其中数据按 localt 和 entity 排序，修改后的查询如下：

<code class="language-sql">SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM test
-- WHERE  ???   -- instead of LIMIT at the end
   ORDER BY localt, entity, msrmnt
-- LIMIT ???'   -- instead of LIMIT at the end
, 'SELECT generate_series(1,5)'  -- more?
   ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 1000  -- ?!</code>

此查询使用 dense_rank() 生成代理行名称，并包含可选的 WHERE 子句以过滤处理之前的数据。此外，从子查询中删除了 LIMIT 条件，以通过仅处理必要的行来提高性能。

结论

通过了解局限性并遵循 tablefunc 指定的顺序，即使对于大型数据集，也可以有效地对多列进行数据透视。请记住，通过使用适当的 WHERE 子句或 LIMIT 条件来优化查询，以避免不必要的处理。

以上是如何使用 PostgreSQL 的 tablefunc 有效地透视多个列上的数据？的详细内容。更多信息请关注PHP中文网其他相关文章！