Recosh
@Recosh
Программист студент

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

Приветствую! Вот допустим есть табличка:
-			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, но могу перейти на любую другую СУБД с открытым кодом.
Заранее спасибо!
  • Вопрос задан
  • 203 просмотра
Решения вопроса 1
@dass45
Вам нужно начинать запрос с минут, к которым джойнить свою таблицу, тогда в результате будут и минуты с отсутствующими данными отображаться. Зачастую это в отдельной таблице хранят или процедуру с лупами делают.

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

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

ЗЫ редактировал ответ, т.к. пропустил один вопрос в тексте
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы