Django - Group by, split by date range-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.