Агрегация django для более низкого разрешения с использованием группировки по диапазону дат

ужасное название, но позвольте мне объяснить: у меня есть эта модель django, содержащая временную метку (дату) и атрибут для журнала - f.e. количество пользователей, потребляющих какой-либо ресурс - (значение).

class Viewers(models.Model):
    date = models.DateTimeField()
    value = models.IntegerField()

на каждые 10 секунд таблица содержит количество пользователей. что-то вроде этого:

| date | value |
|------|-------|
|  t1  |   15  |
|  t2  |   18  |
|  t3  |   27  |
|  t4  |   25  |
|  ..  |   ..  |
|  t30 |   38  |
|  t31 |   36  |
|  ..  |   ..  |

теперь я хочу генерировать разные статистические данные из этих данных, каждый с другим разрешением. например для графика последнего дня мне не нужно 10-секундное разрешение, поэтому я хочу 5-минутные шаги (которые строятся путем усреднения значений (и, возможно, также даты) строк от t1 до t29, от t30 до t59, . ..), так что я получу:

| date | value |
|------|-------|
|  t15 |   21  |
|  t45 |   32  |
|  ..  |   ..  |

атрибуты, которые нужно сохранить переменными, - это метка времени начала и окончания и разрешение (например, 5 минут). есть ли способ использовать django orm/queryset api, и если нет, то как это сделать с помощью пользовательского sql?


django sql average aggregation date-range
person kraiz    schedule 07.06.2011    source источник

Ответы (4)


arrow_upward
4
arrow_downward

Я пытался решить эту проблему максимально возможным способом «джанго». Я остановился на следующем. Он усредняет значения для 15-минутных временных интервалов между start_date и end_date, где имя столбца — «дата»:

readings = Reading.objects.filter(date__range=(start_date, end_date)) \
   .extra(select={'date_slice': "FLOOR (EXTRACT (EPOCH FROM date) / '900' )"}) \
   .values('date_slice') \
   .annotate(value_avg=Avg('value'))

Он возвращает словарь:

 {'value_avg': 1116.4925373134329, 'date_slice': 1546512.0}
 {'value_avg': 1001.2028985507246, 'date_slice': 1546513.0}
 {'value_avg': 1180.6285714285714, 'date_slice': 1546514.0}

Суть идеи исходит из этого ответа на тот же вопрос для PHP/SQL. Код, переданный дополнительно, предназначен для БД Postgres.

person Richard Corden    schedule 12.02.2014
comment
Чтобы использовать это с MySQL, замените вызов extra() на: .extra(select={"date_slice": "from_unixtime(unix_timestamp(time) - unix_timestamp(time) mod 900)"}) - person cdr; 02.03.2021

arrow_upward
2
arrow_downward 

from django.db.models import Avg

Viewers.objects.filter(date__range=(start_time, end_time)).aggregate(average=Avg('value'))

Это даст вам среднее значение всех values между start_time и end_time, возвращенных в виде словаря в форме { 'average': <the average> }.

start_time и end_time должны быть объектами даты и времени Python. Поэтому, если у вас есть метка времени или что-то в этом роде, вам нужно сначала преобразовать ее. Вы также можете использовать datetime.timedelta для расчета end_time на основе start_time. Для пятиминутного разрешения что-то вроде этого:

from datetime import timedelta

end_time = start_time + timedelta(minutes=5)
person Chris Pratt    schedule 07.06.2011
comment
да, это простой фильтр диапазона. я думаю, что недостаточно ясно объяснил, что между start_time и end_time может быть - или, лучше сказать, "есть" - много периодов/временных дельт f.e. 5 минут. поэтому результатом является не одно агрегированное значение, а таблица, подобная второй в моем вопросе: с отметкой времени (средняя отметка времени 5-минутного периода времени) и усредненным значением этого периода. возьми? :) на самом деле это кажется простой работой по уплотнению, но я пытаюсь добиться этого уже 3 часа :( - person kraiz; 07.06.2011
comment
По сути, вам нужен цикл, который действительно невозможен с одним запросом к БД, прямым SQL или каким-либо другим образом. Вы всегда можете использовать приведенный выше код в цикле, увеличивая время начала и окончания на 5 минут в каждой итерации, но это, вероятно, крайне неэффективно. - person Chris Pratt; 07.06.2011
comment
как сказал Крисдпратт, я не думаю, что вы могли бы сделать это с SQL. Вы должны получить набор запросов со всеми значениями (или подмножеством) и вычислить средние значения с помощью Python. - person Facundo Casco; 07.06.2011
comment
хрррр, посмотрите мой ответ - person kraiz; 08.06.2011

arrow_upward
1
arrow_downward

вы смотрели фильтр диапазона?

https://docs.djangoproject.com/en/dev/ref/models/querysets/#range

Пример, приведенный в документе, похож на вашу ситуацию.

person Timmy O'Mahony    schedule 07.06.2011
comment
эй, спасибо 4 ответ. как вы сказали, это просто фильтр, с помощью которого я могу выбрать период (5 минут). но я хочу сделать это для потенциально многих периодов. - person kraiz; 07.06.2011

arrow_upward
-1
arrow_downward

После долгих попыток я сделал это как SQL-оператор:

SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(date))), SUM(value)
FROM `my_table`
WHERE date BETWEEN SUBTIME(NOW( ), '0:30:00') AND NOW()
GROUP BY UNIX_TIMESTAMP(date) DIV 300
ORDER BY date DESC

с участием

start_time = SUBTIME(NOW( ), '0:30:00')
end_time = NOW()
period = 300 # in seconds

в конце концов - не очень сложно - и действительно не зависит от временного разрешения выборок в исходной таблице.

person kraiz    schedule 08.06.2011