Оптимизация запросов к базе данных для извлечения данных временных рядов с использованием postgresql и ruby ​​на рельсах

Question

Привет, ребята, я работаю над интересным приложением в реальном времени. Приложение выглядит следующим образом. У меня есть meter модель и meter_info модель

calss Meter
  has_many :meter_infos
  # filed: id 
end

class MeterInfo
  belongs_to :meter
  # field: meter_id, voltage 
end

Каждые две минуты новые данные сохраняются в meter_info Таблица. Итак, вы можете себе представить, что там огромный набор данных.

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

id           created_at          meter_id      voltage
2001     2017-10-19 15:40:00        2             100
2001     2017-10-19 15:45:00        1             100
2001     2017-10-19 15:39:00        3             100
2001     2017-10-19 15:48:00        4             100
2001     2017-10-19 15:38:00        5             100
2001     2017-10-19 15:42:00        6             100
...
...

Я перепробовал несколько запросов, но так как поиск записи занял слишком много времени, запрос откладывается по времени. Вот то, что я пытался для

(('2017-07-02 00:00:00').to_datetime.to_i .. 
  ('2017-07-02 23:59:59').to_datetime.to_i).step(10.minutes) do |date|
                query = "SELECT  created_at, meter_id, voltage
                FROM meter_infos
                WHERE created_at between  '#{Time.at(date).utc}' and 
                '#{Time.at(date).utc + 10.minutes}'
                AND meter_id in (1,2,3,4,5)
                ORDER BY id desc limit 1"

                voltages = ActiveRecord::Base.connection.execute(query)

end

Который рассчитан даже в среде разработки. Тогда я попытался использовать Postgresql"s generated_series как ниже

  query= "SELECT meter_id,voltage,  count(id) as ids
              , GENERATE_SERIES( timestamp without time zone '2017-10-19',
                  timestamp without time zone '2017-10-19',
                 '10 min') as time_range
              from meter_infos
              where meter_infos.created_at between '2017-10-19 00:00:01'::timestamp and  '2017-10-19 23:59:59'::timestamp
              and meter_infos.meter_id in (1,2,3,4,5)
                  GROUP BY meter_id, voltage
              ORDER BY meter_id ASC limit 1"

            sbps_plot = ActiveRecord::Base.connection.execute(query)

Что быстрее, но дает неверные данные. я использую Ruby on Rails а также Postgresql, Может ли кто-нибудь помочь мне написать более быстрый запрос, чтобы найти данные по времени, или предложить мне любую процедуру для обработки данных временных рядов. Заранее спасибо.

Answer 1

У вас есть записи каждые две минуты, но вы хотите получить образец записи с десятиминутными интервалами. Вот мое предлагаемое решение:

Вы можете взять модуль времени эпохи created_at отметка времени с 600 (десять минут в секундах). Затем сравните это с некоторым значением "допустимого отклонения" (например, 119 секунд или меньше), если временные метки ваших записей не совпадают с идеальными десятиминутными интервалами. Подумайте об этом, чтобы получить первую запись с created_at внутри 2-минутного окна, следующего за каждым 10-минутным интервалом дня.

Например,

MeterInfo
  .where(
    meter_id: [1, 2, 3, 4, 5], 
    created_at: your_date.beginning_of_day..your_date.end_of_day
  )
  .where("(cast(extract(epoch from created_at) as integer) % 600) < 119")

Попробуйте и посмотрите, сработает ли это для вас.