Блог

У меня есть 2 таблицы measurement_timestamps и sensor_double_precision следующего вида:

 id    start_time    stop_time
 1    2020-02-22    2020-02-24
 2    2020-02-25    2020-02-27 
  

и

 id    sensor_name    value_cal    timestamp
 1    start_freq            15    2020-02-23
 2    stop_freq             18    2020-02-23
 3    start_freq            15    2020-02-26
 4    stop_freq             18    2020-02-26
  

Я хочу ПРЕДСТАВЛЕНИЕ, которое просматривает временные метки измерений и для каждой пары start_time — stop_time выполняет поворот (транспонирует?) столбец sensor_name такой, что start_freq и stop_freq становятся там собственными столбцами с соответствующим value_cal в качестве строк.

Итак, я в основном хочу, чтобы представление выглядело следующим образом:

 id    start_freq    stop_freq    timestamp
 1            15           18    2020-02-23
 2            15           18    2020-02-26
  

Обратите внимание, что временная метка, связанная с идентификатором 1 в ПРЕДСТАВЛЕНИИ, находится между start_time и stop_time для идентификатора 1 в таблице measurement_timestamps.

Каков разумный способ сделать это? Я не хочу создавать ПРЕДСТАВЛЕНИЕ для каждого отдельного sensor_name, потому что у меня намного больше датчиков, чем это, и это просто не кажется очень надежным. Ниже приведен подход, который мне рекомендовали, но, похоже, он не работает, потому что я, вероятно, делаю что-то неправильно.

 SELECT * 
FROM crosstab('with current_data as (
        select distinct on (mt.id)
                mt.id, sdp.sensor_name, sdp.value_cal
            from measurement_timestamps mt, sensor_double_precision sdp
            order by mt.id desc
    ),
    ids as (
        select distinct id from current_data
    ),
    sensor_names as (
        select distinct sensor_name from current_data
    )
    select ids.id, sensor_names.sensor_name, current_data.value_cal
    from ids cross join sensor_names
    left join current_data on (ids.id=current_data.id and sensor_names.sensor_name=current_data.sensor_name)
    order by ids.id,sensor_names.sensor_name') final_data (id integer, start_freq double precision,
                                                        stop_freq double precision, timestamp timestamp)
  

Sidenote — start_freq и stop_freq не похожи на имена датчиков, но я работаю с таблицей, которая соответствует стандартизированной форме, из-за чего мы называем ее sensor_name. Есть также другие датчики, о которых я не беспокоюсь для этой задачи.

РЕДАКТИРОВАТЬ — результат запроса, который был предложен ниже:

 id.  start_freq       stop_freq.             timestamp
18      15             null         "2020-07-09 20:03:38.937195 00"
19     null             18.         "2020-07-09 20:03:39.051836 00"
20     null            null          "2020-07-09 20:03:39.171837 00"
21     null            null         "2020-07-09 20:03:39.287994 00"
22     null            null         "2020-07-09 20:03:39.287994 00"
23     15              null         "2020-07-09 20:03:39.287994 00"
24     null            18           "2020-07-09 20:03:39.287994 00"
  

EDIT2 — Данные, которые я прикрепил к задаче, являются образцами данных, чтобы упростить обсуждение проблемы. Структура и все остальное аналогичны реальному набору данных.

Для этого есть 2 способа. (условно только один набор событий запуска и остановки с временным диапазоном measurement_timestamps )

1. используя обычный aggregation и filter

 select 
m.id, 
min(s.value_cal) filter (where sensor_name='start_freq'),
min(s.value_cal) filter (where sensor_name='stop_freq'),
min(s.timestamp) filter (where sensor_name='stop_freq')
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time
group by m.id
  

Примечание: вы не указали, какую дату вы хотите, поэтому я взял stop_freq timestamp . Вы можете изменить то, что хотите.

2. Использование Crosstab (по-вашему)

 select * from crosstab('select 
m.id, 
s.timestamp,
s.sensor_name,
s.value_cal
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time',
'select ''start_freq'' union select ''stop_freq'' ') as (id int, timestamp date, start_freq varchar, stop_freq varchar)

  

Примечание: Приведенное выше будет работать корректно только тогда, когда start_freq timestamp и stop_freq timestamp одинаковы

ДЕМОНСТРАЦИЯ

Я не понимаю, почему это не должно работать. Ваш комментарий о «нулевых значениях между точками данных» для меня не имеет смысла.

 select
    m.id,
    min(case when m.sensor_name = 'start_freq' then m.value_cal end) as start_freq,
    min(case when m.sensor_name = 'stop_freq'  then m.value_cal end) as stop_freq, 
    s.timestamp
from measurement_timestamps m inner join sensor_double_precision s
    on s.timestamp between m.start_time and m.stop_time
group by m.id, s.timestamp