Django Query to aggregate values from two rows into single result per day

Question

I am trying to create a model manager query which returns a result grouped by day for multiple balance types (DATA and AIRTIME) over a given date range. The Balance history table is updated all the time as the sim uses data, but for reporting we only want to show one balance a day

The models are simple:

class Sim(TimeStampedModel):
    number = models.CharField()

class SimBalanceHistory(TimeStampedModel):
    balance_type = models.CharField(choices=BALANCE_TYPES, max_length=10)
    amount = models.DecimalField(max_digits=10, decimal_places=2, default=0)
    sim = models.ForeignKey(Sim, related_name='balance_histories')

Some sample data from the SimBalanceHistory Table:

   ID   BALANCE_TYPE AMOUNT SIM_ID CREATED MODFIED
   1603 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1604 DATA    36.75   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1703 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1704 DATA    36.74   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1803 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1804 DATA    36.73   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1973 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   1974 DATA    36.72   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2059 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2060 DATA    36.72   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2135 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2136 DATA    36.71   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2229 AIRTIME 3.71    348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 +02:00
   2230 DATA    36.70   348 2016-11-17 11:13:42.498180 +02:00   2016-11-17 11:13:43.543159 
   440026   DATA    34.26   348 2016-11-18 23:34:36.976777 +02:00   2016-11-18 23:34:36.976836 +02:00
   440885   AIRTIME 3.71    348 2016-11-18 23:57:57.448809 +02:00   2016-11-18 23:57:57.448878 +02:00
   440889   DATA    34.25   348 2016-11-18 23:57:58.854901 +02:00   2016-11-18 23:57:58.854959 +02:00
   443590   AIRTIME 3.71    348 2016-11-19 00:35:07.598679 +02:00   2016-11-19 00:35:07.598755 +02:00

443593 DATA 34.24 348 2016-11-19 00:35:08.991217 +02:00 2016-11-19 00:35:08.991266

Currently the query looks like this:

    def daily_balances(self, start_date, end_date):
      return self.filter(
        created__range=[start_date, end_date]
      ).dates(
        'created',
        'day',
        order='DESC'
      ).order_by(
        '-created'
      ).distinct(
        'created', 'balance_type'
      ).values(
        'created',
        'amount',
        'balance_type'
      )

Which limits by day, but returns a row for every balance_type

{'balance_type': 'AIRTIME', 'created': datetime.datetime(2016, 11, 22, 0, 0, tzinfo=<UTC>), 'amount': Decimal('5.00')}
{'balance_type': 'DATA', 'created': datetime.datetime(2016, 11, 22, 0, 0, tzinfo=<UTC>), 'amount': Decimal('12.00')}

What I am trying to get to is something like this in the results of the query set (1 record for each day with a value for airtime amount and data amount:

 {'created': datetime.datetime(2016, 11, 22, 0, 0, tzinfo=<UTC>), 'data_amount': Decimal('5.00'), 'airtime_amount': Decimal('12.00')}
 {'created': datetime.datetime(2016, 11, 21, 0, 0, tzinfo=<UTC>), 'data_amount': Decimal('6.00'), 'airtime_amount': Decimal('14.00')}

score 1 · Answer 1 · edited May 23 '17 at 11:59

here is what I would try.

first, rename in each row the amount with a name specific for its type: data_amount and airtime_amount. And then, group the rows by 'created' while doing the sum of each amount.

So, I think you can do something like

from django.db.models import F, Sum

def daily_balances(self, start_date, end_date):
    airtime_records = SimBalanceHistory.objects.filter(
        created__range=[start_date, end_date],     
        balance_type='AIRTIME'
    ).annotate(airtime_amount=F('amount'))

    data_records = SimBalanceHistory.objects.filter(
        created__range=[start_date, end_date],
        balance_type='DATA'
    ).annotate(data_amount=F('amount'))

    return (airtime_records | data_records).dates(
        'created',
        'day',
        order='DESC'
    ).order_by(
        '-created'
    ).values('created').annotate(
        Sum('data_amount'),
        Sum('airtime_amount'),
    )

That code makes multiples queries but I can't think of a solution doing that in only one. Hopefully someone will post a better answer but maybe that can help you.

Refs.

Thanks for the help, when I tried to merge the two querysets it would not allow me because of the different names of amount fields from the first two queries. — WPW, Nov 24 '16 at 14:13

Charl Botha · Accepted Answer · 2016-11-24T14:03:04.150

I think your existing query is already pretty good, but if you really want one row per day with both the balances, you could use conditional aggregates:

from django.db.models import IntegerField, F, Sum, When

SimBalanceHistory.objects\
                 .filter(created__range=[start_date, end_date])\
                 .dates('created', 'day', order='DESC')\
                 .values('created')\
                 .annotate(airtime_amount=Sum(Case(When(balance_type='AIRTIME', then=F('amount')), output_field=DecimalField())),
                           data_amount=Sum(Case(When(balance_type='DATA', then=F('amount')), output_field=DecimalField())))

Django Query to aggregate values from two rows into single result per day

2 Answers2