Как получить сводную таблицу значений?

Question

[kolya_krut]

Доброе время суток.

Есть приборы:

id | title 
---|------
1  | dev1
2  | dev2

У которых есть параметры:

id | device_id | title 
---|-----------|------
1  | 1         | par1
2  | 1         | par2
3  | 2         | par3

Есть значения этих параметров по времени:

id | param_id | value | date
---|----------|-------|-----
1  | 1        | 1     | 2020-01-01 00:00:00
2  | 1        | 2     | 2020-01-01 01:00:00
3  | 1        | 3     | 2020-01-01 02:00:00
4  | 2        | 4     | 2020-01-01 00:00:00
5  | 3        | 5     | 2020-01-01 00:00:00
6  | 3        | 6     | 2020-01-01 01:00:00

Как получить свобдную таблицу по приборам и по времени? Вот такую:

date                | device_id | par1 | par2 | par3
--------------------|-----------|------|------|-----
2020-01-01 00:00:00 | 1         | 1    | 4    | null
2020-01-01 01:00:00 | 1         | 2    | null | null
2020-01-01 02:00:00 | 1         | 3    | null | null
2020-01-01 00:00:00 | 2         | null | null | 5
2020-01-01 01:00:00 | 2         | null | null | 6

Answer 1

[kolya_krut]

получилось как-то так:

SELECT *
FROM crosstab(
    'SELECT
        CONCAT(v.date, $$-$$, p.device_id) AS device,
        v.date,
        p.device_id,
        v.param_id,
        v.value
    FROM
        values AS v
        JOIN params AS p ON p.id = v.param_id
    ORDER BY
        device',
    'SELECT
        id
    FROM
        params
    ORDER BY
        id') AS (
        device VARCHAR,
        date TIMESTAMP,
        device_id INT,
        par1 DOUBLE PRECISION,
        par2 DOUBLE PRECISION,
        par3 DOUBLE PRECISION
    )