Company logo
  • Empleos
  • Bootcamp
  • Acerca de nosotros
  • Para profesionales
    • Inicio
    • Empleos
    • Cursos y retos
    • Preguntas
    • Profesores
    • Bootcamp
  • Para empresas
    • Inicio
    • Nuestro proceso
    • Planes
    • Pruebas
    • Nómina
    • Blog
    • Comercial
    • Calculadora

0

144
Vistas
How can I rewrite an average value per date time interval MySQL query as a Django QuerySet?

I have the following MySQL query that displays the average value per 10 minute interval:

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(date_time) / 600) * 600) AS interval_date_time,
AVG(some_value) AS avg_value
FROM djangoapp_somemodel
GROUP BY interval_date_time
ORDER BY interval_date_time;

For example, if I have the following 3 records:

item_id date_time some_value
1 2021-11-29 00:11:01 10
2 2021-11-29 00:16:15 20
3 2021-11-29 00:24:32 25

The query will output the following:

interval_date_time avg_value
2021-11-29 00:10:00 15
2021-11-29 00:20:00 25

I suspect the query isn't that efficient but I want to get the same output using a Django QuerySet.

For reference, my Django model looks like this:

class SomeModel(models.Model):
    item_id = models.AutoField(primary_key=True)
    some_value = models.FloatField()
    date_time = models.DateTimeField(auto_now=True)

Here's my current QuerySet:

(SomeModel.objects
    .annotate(interval_date_time=F("date_time"))
    .values("interval_date_time")
    .annotate(avg_value=Avg("some_value"))
    .order_by("interval_date_time")
)

I believe I need to make changes to the first annotate method call. Any help would be appreciated as I am quite new to Django.

10 months ago · Santiago Trujillo
1 Respuestas
Responde la pregunta

0

Might be easiest to just use RawSQL here if you know you'll always be working with MySQL.

(
    SomeModel.objects.annotate(interval_date_time=RawSQL("FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(date_time) / 600) * 600)", ()))
    .values("interval_date_time")
    .annotate(avg_value=Avg("some_value"))
    .order_by("interval_date_time")
)
10 months ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos