Блог

Проблема заключается в следующем. У меня есть таблица, содержащая следующие столбцы:

Что я хочу знать-это расстояние , пройденное в каждый час для каждой машины, поэтому мне нужно вычесть время текущей строки с меткой времени следующем ряду, а затем умножить на скорость в первой строке (е.г: 16:55 — 16:45 = 10 мин -> 10 * 15 = 150 метров между 16:45 и 16:55).

Я смог сделать это, используя логику, аналогичную приведенной ниже (это не один и тот же запрос).:

  ' 1st get the timestamp of the next row'
 lead(query."timestamp") OVER (PARTITION BY query.id ORDER BY query."timestamp") AS lead_timestamp
 ' 2nd get the duration'
 query."lead_timestamp" - query."timestamp" AS duration
 ' 3rd calculate the distance'
 query."duration" * query."speed" AS distance
 ' 4th group by hour'
 GROUP BY date_trunc('hour', CAST(query."timestamp" AS timestamp)
 

Это работает почти на 100% нормально. Я получаю таблицу, аналогичную приведенной ниже:

Но, как вы можете видеть, когда я группирую данные в час, общее количество счетчиков за час 16:00 неверно, потому что после метки времени, равной «22/9/2020, 16:55», не было отметки времени, равной «22/9/2020, 16:55», что вынудило группировку закончить на «22/9/2020, 16: 59». Итак, в конце концов, я добавил к часу 16:00 часть продолжительности для часа 17:00 (эти 20 минут были добавлены к часу 16:00).

Я не уверен, как решить эту проблему, но я изучил ОБЪЕДИНЕНИЕ, чтобы добавлять «искусственную» строку всякий раз, когда происходит переход часа между метками времени, прежде чем даже начинать вычитание значений для вычисления продолжительности. Но это кажется довольно сложным, так как мне пришлось бы делать это для каждой машины, и я не знаю, сколько это будет строк.

Вы не могли бы мне помочь? Спасибо! Если я не был ясен, пожалуйста, запросите дополнительную информацию!

Это могло бы помочь:

 WITH cte as (
   select 'c1' as  machine, '2020-09-22 16:46' as timestamp, 15 as speed
   union all 
   select 'c1', '2020-09-22 16:56', 5
   union all 
   select 'c1', '2020-09-22 17:20', 19)
select 
   machine,
   timestamp,
   speed,
   lead(timestamp) over (partition by machine) nextTime,
   TIMEDIFF( lead(timestamp) over (partition by machine), timestamp) diffTime,
   minute(TIMEDIFF( lead(timestamp) over (partition by machine), timestamp))*speed meters
from cte;
 

выход: