SQL: выборка разреженных данных (не больше раза в час)

• 07, 30, 2016
•  bullgare
•  Databases
• No Comments.

Допустим, мы сохраняем данные в базу раз в 10 минут.
Нужно достать данные из базы, начинающиеся с определённого времени и не чаще одного раза за час.
В качестве базы используется sqlite3, но идея реализуется так же для других SQL.

Способ 1. Более простой.

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

SELECT res.*
FROM
  (SELECT
     CAST(STRFTIME("%M", DATETIME(c.dt_request, 'unixepoch')) AS DECIMAL) AS minute,
     c.id
   FROM table AS c
   WHERE c.dt_request >= 1469474975
  ) AS c
  INNER JOIN table AS res
    ON res.id = c.id
WHERE c.minute BETWEEN 0 AND 10
ORDER BY res.dt_request DESC;

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

Способ 2. Чуть сложнее.

Для каждого часа выбираем запись с наименьшим временем.

SELECT res.*
FROM
  (SELECT
     MIN(c.dt_request) AS min_time,
     CAST(STRFTIME("%Y%m%d%H", DATETIME(c.dt_request, 'unixepoch')) AS DECIMAL) AS dh
   FROM table AS c
   WHERE c.dt_request >= 1
   GROUP BY dh
  ) AS m
  INNER JOIN table AS res
    ON res.dt_request = m.min_time
ORDER BY res.dt_request DESC;

Если не забывать про индекс по столбцу dt_request, план выполнения должен получиться вполне приличным.

В качестве дополнения — форматирование дат в sqlite3