Как написать SQL аналогично функции resample в pandas?

Question

[Кирилл Петров]

Приветствую! Вот допустим есть табличка:

-			v
t	
2020-08-02 08:01:21	3
2020-08-02 08:01:26	4
2020-08-02 08:01:32	1
2020-08-02 08:02:02	6
2020-08-02 08:04:09	2

Код генерации такой таблицы в Python

data = {
    't': ['2020-08-02 08:01:21', '2020-08-02 08:01:26', '2020-08-02 08:01:32', '2020-08-02 08:02:02', '2020-08-02 08:04:09'],
    'v': [3,4,1,6,2]
}
df = pd.DataFrame(data)
df.t = pd.to_datetime(df.t)
df.set_index('t', inplace=True)
df

Теперь эти данные можно модифицировать таким образом:

Опять код на Python

dfResampled = df.resample('1t')
pd.DataFrame({
    'min': dfResampled.v.min(),
    'max': dfResampled.v.max(),
    'last': dfResampled.v.last(),
    'first': dfResampled.v.first()
})

И получить результат:

-			min	max	last	first
t				
2020-08-02 08:01:00	1.0	4.0	1.0	3.0
2020-08-02 08:02:00	6.0	6.0	6.0	6.0
2020-08-02 08:03:00	NaN	NaN	NaN	NaN
2020-08-02 08:04:00	2.0	2.0	2.0	2.0

У меня очень много значений и если проводить обработку на стороне бэкенда таким образом, то из базы тянется много трафика. Что и вызывает основные задержки.

/* Затронуто строк: 0  Найденные строки: 146 364  Предупреждения: 0  Длительность  1 запрос: 0,016 сек. (+ 2,250 сек. сеть) */

Я попробовал написать след запрос:

SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i:00') AS `t`, MIN(`v`) AS `min`, MAX(`v`) AS `max` FROM table GROUP BY(DATE_FORMAT(`date`, '%Y-%m-%d %H:%i:00'))

Но тут у меня отсутствие строчки с датой 2020-08-02 08:03:00, так как в это время не было данных, и как получить первое и последнее значение. И ещё ресамплирование может быть не обязательно в 1 минуту, а допустим в 3 мин, 5 мин и т.д.
В общем прошу помощи как правильно написать SQL запрос. Использую MariaDB, но могу перейти на любую другую СУБД с открытым кодом.
Заранее спасибо!

Answer 1

[dass45]

Вам нужно начинать запрос с минут, к которым джойнить свою таблицу, тогда в результате будут и минуты с отсутствующими данными отображаться. Зачастую это в отдельной таблице хранят или процедуру с лупами делают.

Там же при выборке минут можно добавить условие на коэффициент ресамплирования (выбирать каждую минуту, каждую 3-ю, каждую 5-ю и тд, через остаток от деления, например).

Первое и последнее значение через дополнительный джойн этой же таблицы (в джойнутой таблице выбираем минимальное/максимальное время с группировкой по минуте, для этого времени значение, джойнимся с основной таблицей по времени)

ЗЫ редактировал ответ, т.к. пропустил один вопрос в тексте

Comments

[dass45]

select main.*, tmp_first.v as `first`, tmp_last.v as `last`
from (
    SELECT DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00') AS `t`, MIN(tt.v) AS `min`, MAX(tt.v) AS `max`
    FROM test_time tt
    GROUP BY DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00')
) main
    left join (
        select t as first_t, v from test_time where t in (select min(`t`) from test_time group by DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00'))
    ) as tmp_first on DATE_FORMAT(main.t, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(tmp_first.first_t, '%Y-%m-%d %H:%i:00')
    left join (
        select t as last_t, v from test_time where t in (select max(`t`) from test_time group by DATE_FORMAT(`t`, '%Y-%m-%d %H:%i:00'))
    ) as tmp_last on DATE_FORMAT(main.t, '%Y-%m-%d %H:%i:00') = DATE_FORMAT(tmp_last.last_t, '%Y-%m-%d %H:%i:00')

Я бы id добавил в таблицу, чтобы убрать некоторые преобразования дат в джойнах. Кроме того, возможно быстрее будет хранить время сразу в нужном формате (без секунд), такое тоже иногда используется. При этом секунды в отдельный столбец уносят, трэш, конечно, но встречалось, или дублируют колонку - полное время и время без секунд. В зависимости от БД и потребностей.

[dass45]

Mysql 8.0 появились оконно-аналитические функции, там есть и FIRST_VALUE и LAST_VALUE (https://dev.mysql.com/doc/refman/8.0/en/window-fun...). Если версия СУБД позволяет - вперед.

[Кирилл Петров]

Благодарю за ответы! Так и знал, что это так сложно. А у меня там ещё скользящие средние с накоплением и прочее. От чего у БД скорее всего мозг взорвётся, а работать с таким запросом будет сложно. Лучше создам отдельную таблицу, в которую буду класть уже обработанные данные. :)