Django - Group by, split by date range

user_9112151 0
Viewed 211

I have a model:

class MyModel(models.Model):
    store_id = models.TextField()
    day_dt = models.DateField()
    param1 = models.IntegerField()
    param2 = models.IntegerField()

Some data example:

store_id | day_dt     | param1 | param2
----------------------------------------
STORE1   | 2021-09-30 | 10     | 30
STORE2   | 2021-09-31 | 20     | 40
....
STORE1   | 2021-10-01 | 4      | 5
STORE1   | 2021-10-02 | 6      | 10
STORE1   | 2021-10-03 | 2      | 5
STORE2   | 2021-10-02 | 3      | 7
STORE2   | 2021-10-03 | 1      | 19
....

I need to split data into groups by store_id and interval (day_dt shoould be between 2021-10-01 and 2021-10-04):

STORE1   | 2021-10-01
STORE1   | 2021-10-02
STORE1   | 2021-10-03

and

STORE2   | 2021-10-02
STORE2   | 2021-10-03

and then apply to each (of two) groups aggregation: Avg('param1') and Avg('param2').

The expected output for data example:

store_id | param1_avg | param2_avg
----------------------------------
STORE1   | 6          | 10
STORE2   | 2          | 13

How could I do this aggregation with ORM?

django-orm django
2 Answers

You can implement this with:

from django.db.models import Avg

MyModel.objects.filter(
    date_dt__range=('2021-10-01', '2021-10-04')
).values('store_id').annotate(
    param1_avg=Avg('param1'),
    param2_avg=Avg('param2')
).order_by('store_id')

This will return a QuerySet of dictionaries that will look like:

<QuerySet [
    {'store_id': 'STORE1', param1_avg: 6, param2_avg: 10},
    {'store_id': 'STORE2', param1_avg: 2, param2_avg: 13}
]>

stores that have no MyModel records for the given date range will not have a dictionary object in the resulting queryset.

user_675790

You can use this solution that is the simplest way.

But the Django solution is using Expression and writing your own Query Expressions. In this solution, you make an expression like Hours and use it in the annotate function like: .annotate(Hours("day_dt")). You should override the as_sql function to write your custom SQL method or override the as_sqlite and as_postgresql functions for different support DBMS.

user_19402000
Related