Django Query每天将两行中的值聚合为单个结果

Question

我正在尝试创建一个模型管理器查询，该查询针对给定日期范围内的多种余额类型(DATA 和 AIRTIME)返回按天分组的结果。当 sim 使用数据时，余额历史表一直在更新，但对于报告，我们只想每天显示一个余额

模型很简单:

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')

SimBalanceHistory 表中的一些示例数据:

   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 数据 34.24 348 2016-11-19 00:35:08.991217 +02:00 2016-11-19 00:35:08.991266

目前查询看起来像这样:

    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'
      )

按天限制，但为每个 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')}

我想要得到的是查询集结果中的类似内容(每天 1 条记录，其中包含通话时间量和数据量的值:

 {'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')}

Answer 1

我认为您现有的查询已经很不错了，但是如果您真的想要每天一行同时包含两个余额，您可以使用 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())))