Блог

У меня есть данные временных рядов в базе данных PostgreSQL. В настоящее время я импортирую это в pandas для проведения некоторого анализа, и мой первый шаг часто заключается в повторной выборке данных о частоте за 5 минут до средних данных за 1 час. Я делаю это так, что сначала я поворачиваю данные, чтобы привести их в расширенную форму, затем я переназначаю их на 1 час, а после этого я расплавляю их, чтобы снова получить их в длинной форме.

Теперь я хочу выполнить повторную выборку в базе данных, чтобы сразу же импортировать среднее значение за 1 час.

Так выглядят данные в базе данных. У меня есть два разных типа с 3 разными именами.

     datetime                    value   type   name
0    2018-01-01 13:35:00 01:00   0.22    HLN    NO2
1    2018-01-01 13:35:00 01:00   0.31    HLN    CO
2    2018-01-01 13:35:00 01:00   1.15    HLN    NO
3    2018-01-01 13:40:00 01:00   1.80    AIS    NO2
4    2018-01-01 13:40:00 01:00   2.60    AIS    CO
5    2018-01-01 13:40:00 01:00   2.30    AIS    NO
6    2018-01-01 13:45:00 01:00   2.25    HLN    NO2
7    2018-01-01 13:45:00 01:00   2.14    HLN    CO
8    2018-01-01 13:45:00 01:00   2.96    HLN    NO
9    2018-01-01 14:35:00 01:00   0.76    HLN    NO2
10   2018-01-01 14:35:00 01:00   0.80    HLN    CO
11   2018-01-01 14:35:00 01:00   1.19    HLN    NO
12   2018-01-01 14:40:00 01:00   1.10    AIS    NO2
13   2018-01-01 14:40:00 01:00   2.87    AIS    CO
14   2018-01-01 14:40:00 01:00   2.80    AIS    NO
15   2018-01-01 14:45:00 01:00   3.06    HLN    NO2
16   2018-01-01 13:45:00 01:00   2.86    HLN    CO
17   2018-01-01 13:45:00 01:00   2.22    HLN    NO

 

Теперь начинается та часть, с которой у меня возникли проблемы. После повторной выборки и построения графика в pandas и plotly я получаю ожидаемый результат, который является правильным, по одному значению на каждый час:

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

 SELECT date_trunc('hour', datetime) AS hour, type, AVG(value) AS measure, name
                    FROM data_table
                    GROUP BY datetime, type, name
                    ORDER BY datetime
 

Я получаю это после построения графика:

Это не гладко, и в час есть несколько значений, я думаю, что в течение часа есть все значения.

Мой вопрос, как я могу правильно выполнить повторную выборку временных рядов в SQL?

Редактировать: ожидаемый результат в виде таблицы:

  datetime             value  type  name
2018-01-01 13:00:00   1.235   HLN   NO2
2018-01-01 13:00:00   2.65   HLN   CO
2018-01-01 13:00:00   2.96   HLN   NO
2018-01-01 13:00:00   2.48   AIS   NO2
2018-01-01 13:00:00   2.65   AIS   CO
2018-01-01 13:00:00   2.26   AIS   NO
2018-01-01 14:00:00   2.78   HLN   NO2
2018-01-01 14:00:00   3.65   HLN   CO
2018-01-01 14:00:00   1.95   HLN   NO
2018-01-01 14:00:00   1.45   AIS   NO2
2018-01-01 14:00:00   1.64   AIS   CO
2018-01-01 14:00:00   3.23   AIS   NO

 

Альтернативой является создание временных интервалов с использованием generate_series() или просто с помощью подзапроса / CTE, усекающих часы для каждого type и name , а во внешнем запросе объединить обе записи и объединить values с avg() помощью столбцов hour type и name , например

 WITH j AS (
  SELECT DISTINCT date_trunc('hour', datetime) AS hour, type,name 
  FROM data_table
)
SELECT j.*, avg(d.value)
FROM data_table d
JOIN j ON date_trunc('hour', d.datetime) = j.hour AND 
          j.type = d.type AND 
          d.name = j.name
GROUP BY j.hour, j.name, j.type
ORDER BY j.hour ASC,j.type DESC;

        hour         | type | name |          avg           
--------------------- ------ ------ ------------------------
 2018-01-01 13:00:00 | HLN  | CO   |     1.7700000000000000
 2018-01-01 13:00:00 | HLN  | NO   |     2.1100000000000000
 2018-01-01 13:00:00 | HLN  | NO2  | 1.23500000000000000000
 2018-01-01 13:00:00 | AIS  | CO   |     2.6000000000000000
 2018-01-01 13:00:00 | AIS  | NO   |     2.3000000000000000
 2018-01-01 13:00:00 | AIS  | NO2  | 1.80000000000000000000
 2018-01-01 14:00:00 | HLN  | CO   | 0.80000000000000000000
 2018-01-01 14:00:00 | HLN  | NO   | 1.19000000000000000000
 2018-01-01 14:00:00 | HLN  | NO2  |     1.9100000000000000
 2018-01-01 14:00:00 | AIS  | CO   |     2.8700000000000000
 2018-01-01 14:00:00 | AIS  | NO   |     2.8000000000000000
 2018-01-01 14:00:00 | AIS  | NO2  | 1.10000000000000000000
 

ДЕМОНСТРАЦИЯ: db<>fiddle