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

Table of Contents

Допустим, мы сохраняем данные в базу раз в 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;

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;

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

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

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

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

Similar Posts

LEAVE A COMMENT Отменить ответ