小编典典

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


阅读 53

收藏
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