CODEWITHSUNDEEP
djangodjango-orm
Альберт Александров
Альберт Александров

Reputation: 901

Django - Group by, split by date range

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?

Upvotes: 1

Views: 813

Answers (2)

mrash
mrash

Reputation: 903

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.

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476614

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.

Upvotes: 1

Related Questions