使用 Tablefunc 旋转多列

一尘不染

使用 Tablefunc 旋转多列

javascript

有没有人习惯于使用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);

Returns:


 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 次测量。通常，我们需要比较给定实体在给定秒内所做的不同测量，因此我们需要经常使用宽格式。此外，对特定实体进行的测量是高度可变的。

阅读 112

2022-05-23

共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当然，必须是唯一的。

重申

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

额外的列从每个分区的第一行开始填充。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() 生成一个代理行名称。

db<>fiddle here - 使用更简单的测试设置和更少的行。

2022-05-23