postgresql - 如何在下表的PostgreSQL中将行转换为列

Question

我试图将跟踪表转换为 postgress 中的结果表。我在表中有拥抱数据。

我有名称的表：Trace

entity_id                         | ts                | key         | bool_v  |   dbl_v |   str_v   | long_v  |
---------------------------------------------------------------------------------------------------------------
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | temperature   |         |         |           | 45      |
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | operation     |         |         |   Normal  |         |
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | period        |         |         |           | 6968    |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | temperature   |         |         |           | 44      |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | operation     |         |         |   Reverse |         |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | period        |         |         |           | 3535    |

跟踪表

在PostgreSQL中将上表转换为下表

输出表：结果

entity_id                         | ts            | temperature | operation | period    |
----------------------------------------------------------------------------------------|
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155 | 45          | Normal    | 6968      |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984 | 44          | Reverse   | 3535      |

结果表

标签： postgresqljoinpivot

Answer 1

你试过这个吗？

select entity_id, ts,
       max(long_v) filter (where key = 'temperature') as temperature,
       max(str_v) filter (where key = 'operation') as operation,
       max(long_v) filter (where key = 'period') as period
  from trace
 group by entity_id, ts;