使用 Tablefunc 在多列上透视

小编典典

使用 Tablefunc 在多列上透视

sql

有没有人曾经使用tablefunc多个变量而不是仅使用行名称？文档说明：

对于具有相同 row_name 值的所有行，“额外”列应该是相同的。

我不知道如何在不组合我想要旋转的列的情况下执行此操作（我非常怀疑这会给我所需的速度）。一种可能的方法是将实体设为数字并将其以毫秒为单位添加到本地，但这似乎是一种不稳定的方法。

我已经编辑了在回答这个问题时使用的数据：PostgreSQL Crosstab Query。

 CREATE TEMP TABLE t4 (
  timeof   timestamp
 ,entity    character
 ,status    integer
 ,ct        integer);

 INSERT INTO t4 VALUES 
  ('2012-01-01', 'a', 1, 1)
 ,('2012-01-01', 'a', 0, 2)
 ,('2012-01-02', 'b', 1, 3)
 ,('2012-01-02', 'c', 0, 4);

 SELECT * FROM crosstab(
     'SELECT timeof, entity, status, ct
      FROM   t4
      ORDER  BY 1,2,3'
     ,$$VALUES (1::text), (0::text)$$)
 AS ct ("Section" timestamp, "Attribute" character, "1" int, "0" int);

 Section                   | Attribute | 1 | 0
---------------------------+-----------+---+---
 2012-01-01 00:00:00       |     a     | 1 | 2
 2012-01-02 00:00:00       |     b     | 3 | 4

因此，正如文档所述，假设每行名称（又名“部分”）的额外列（又名“属性”）是相同的。因此，它报告第二行的b，即使 ‘entity’ 也具有该 ‘timeof’ 值的’c ‘ 值。

期望输出：

Section                   | Attribute | 1 | 0
--------------------------+-----------+---+---
2012-01-01 00:00:00       |     a     | 1 | 2
2012-01-02 00:00:00       |     b     | 3 |  
2012-01-02 00:00:00       |     c     |   | 4

任何想法或参考？

更多背景知识：我可能需要对数十亿行执行此操作，并且我正在测试以长格式和宽格式存储这些数据，并查看是否可以使用tablefunc比常规聚合函数更有效地从长格式转换为宽格式。
我将每分钟对大约 300 个实体进行大约 100 次测量。通常，我们需要比较给定实体在给定秒内进行的不同测量，因此我们需要经常使用宽格式。此外，对特定实体进行的测量是高度可变的。

阅读 260

2021-06-08

共1个答案

小编典典

您的查询的问题在于b和c共享相同的时间戳2012-01-02 00:00:00，并且您在查询中首先拥有该timestamp列timeof，因此 - 即使您添加了粗体强调 -b并且c只是属于同一组的额外列2012-01-02 00:00:00。b自（引用手册）以来，仅返回第一个 ( )：

该row_name列必须是第一个。在category与value列必须是最后两列的顺序。row_name和之间的任何列category都被视为“额外”。对于具有相同值的所有行，“额外”列应该是相同的row_name。

大胆强调我的。
只需恢复前两列的顺序即可制作entity行名称，它可以按需要工作：

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

entity 当然，必须是独一无二的。

Reiterate

row_name第一的
（可选）extra列下一个
category（由第二个参数定义）和value last。

从每个分区的第一行填充额外的列row_name。其他行的值将被忽略，每列仅row_name填充一列。通常，对于 one 的每一行，这些都是相同的row_name，但这取决于您。

对于答案中的不同设置：

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  -- ??!!

难怪您的测试中的查询执行得非常糟糕。您的测试设置有 1400 万行，您可以在将其中大部分丢弃之前处理所有这些行LIMIT 1000。对于简化的结果集，向源查询添加 WHERE 条件或 LIMIT！

另外，您使用的阵列在它之上是不必要的昂贵。我用dense_rank() 生成了一个代理行名称。

2021-06-08