Saya mencoba mengonversi tabel jejak menjadi tabel hasil di postgress. Saya memiliki data pelukan di tabel.

Saya memiliki tabel dengan nama: Jejak

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    |

Melacak Tabel

Ubah tabel di atas menjadi tabel berikut di PostgreSQL

Tabel Keluaran: Hasil

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

Tabel Hasil

0
Pavankumar Behere 6 Juli 2020, 10:46

1 menjawab

Jawaban Terbaik

Sudahkah Anda mencoba ini?

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;
0
Mike Organek 6 Juli 2020, 10:19