Geek Answers Handbook

Django aggregation for lower resolution using date grouping

terrible title, but let me explain: I have this django model containing the timestamp (date) and the log attribute - fe the number of users consuming some resource - (value).

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

for every 10 seconds, the table contains the number of users. something like that:

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

Now I want to generate different statistics from this data, each with a different resolution. FE for the last day’s graph, I don’t need a resolution of 10 seconds, so I want 5-minute steps (which are built by averaging the values ​​(or maybe the dates) of the rows from t1 to t29, t30 to t59 ...), so I will get:

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

(, 5 ). django orm/queryset api, , sql?

+5
4
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)
+2

"django". . 15 start_date end_date, is'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.

+2

?

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

The example provided in the document is similar to your situation.

+1
source

After long attempts, I did this as an SQL statement:

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

with

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

in the end - not very difficult - and indeed regardless of the temporal resolution of the samples in the origin table.

-1
source

More articles:


All Articles