Блог

У меня есть программное обеспечение, которое извлекает данные с нескольких устройств с помощью SNMP. Затем программное обеспечение создает запись в таблице с начальным временем опроса и временем окончания опроса.

Для каждой извлекаемой нами таблицы SNMP мы помещаем данные в разные таблицы.

В приведенной ниже модели имеется одна таблица snmp (FrequencyValue). Таблицы выглядят следующим образом:

 | Polls |                  |                  |
|-------|------------------|------------------|
| id    | start_time       | end_time         |
|-------|------------------|------------------|
| 1     | 2019-04-01T10:00 | 2019-04-01T10:10 |
| 2     | 2019-04-01T11:00 | 2019-04-01T11:10 |
| 3     | 2019-04-01T12:00 | 2019-04-01T12:10 |

| Devices    |
|------------|
| ip         |
|------------|
| 172.16.1.1 |

| FrequencyValue |         |                  |           |       |
|----------------|---------|------------------|-----------|-------|
|      device_ip | poll_id | timestamp        | frequency | value |
|----------------|---------|------------------|-----------|-------|
|     172.16.1.1 |       1 | 2019-04-01T10:02 |      1000 |    10 |
|     172.16.1.1 |       1 | 2019-04-01T10:02 |      2000 |    20 |
|                |         |                  |           |       |
|     172.16.1.1 |       3 | 2019-04-01T12:02 |      1000 |    10 |
|     172.16.1.1 |       3 | 2019-04-01T12:02 |      2000 |    20 |

  

Проблема возникает, когда устройство не отвечает, потому что, как показано в таблице FrequencyValue , программное обеспечение не создало запись для этой таблицы.

Вопрос: Для того, чтобы отобразить столбец value на frequency , мы хотели бы создать n строки для каждой frequency , в которых был бы столбец value to null .

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

 select p.ip, coalesce(t.timestamp, p.start_time) as "time", t.frequency, t.value
from FrequencyValue as t
right join (
  select p.start_time, p.id, d.ip
  from Polls as p, Devices as d
) as p on (t.poll_id = p.id and t.device_ip = p.ip)
  

С выводом:

 |         ip | timestamp        | frequency | value |
|------------|------------------|-----------|-------|
| 172.16.1.1 | 2019-04-01T10:02 |      1000 |    10 |
| 172.16.1.1 | 2019-04-01T10:02 |      2000 |    20 |
|            |                  |           |       |
| 172.16.1.1 | 2019-04-01T11:00 |      null |  null |
|            |                  |           |       |
| 172.16.1.1 | 2019-04-01T12:02 |      1000 |    10 |
| 172.16.1.1 | 2019-04-01T12:02 |      2000 |    20 |
  

Что мы на самом деле хотим, так это:

 |         ip | timestamp        | frequency | value |
|------------|------------------|-----------|-------|
| 172.16.1.1 | 2019-04-01T10:02 |      1000 |    10 |
| 172.16.1.1 | 2019-04-01T10:02 |      2000 |    20 |
|            |                  |           |       |
| 172.16.1.1 | 2019-04-01T11:00 |      1000 |  null |
| 172.16.1.1 | 2019-04-01T11:00 |      2000 |  null |
|            |                  |           |       |
| 172.16.1.1 | 2019-04-01T12:02 |      1000 |    10 |
| 172.16.1.1 | 2019-04-01T12:02 |      2000 |    20 |
  

Мы пытались ввести другое соединение справа в запрос, но мы просто не можем получить желаемый результат.
Частоты нигде не определены, поэтому для извлечения доступных частот мы могли бы select distinct использовать частоты в таблице.
База данных — это PostgreSQL.

Используйте a cross join для генерации строк, а затем a left join для ввода значений:

 select d.ip, t.timestamp, f.frequency,  fv.value
from devices d cross join
     (select distinct timestamp from frequencyvalue) t cross join
     (select distinct frequency from frequencyvalue) f left join
     frequencyvalue fv
     on fv.device_ip = d.ip and
        fv.timestamp = t.timestamp and
        fv.frequency = f.frequency;