如何通过Django ORM从聚合字段获取累计求和值

示例模型 #

class ScheduledOperation:
    day = models.DateField()
    amount = models.DecimalField(...)

示例原始数据 #

{'day': datetime.date(2023, 2, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 2, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 3, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 3, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 4, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 4, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 5, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 5, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 5, 8), 'amount': Decimal('-4000.00')}

当前进展 #

通过以下代码可得到每日总额：

ScheduledOperation.objects.order_by('day').values('day').annotate(day_tot=Sum('amount'))

结果：

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000')}

期望结果 #

希望在每日总额基础上增加累计求和字段：

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('1000')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('2000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('3000')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('4000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000'), 'cumul_amount':Decimal('0')}

尝试过的方法及问题 #

使用Window函数直接操作原始amount字段，结果不符合预期：

self.coming_scheduled_ops.order_by('day').values('day').annotate(
    day_tot=Sum('amount')
).annotate(
    cumul_amount=Window(
        Sum('amount'),order_by='day'
    )
) 

错误结果：

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('1500')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('3000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('4500')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('6000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000'), 'cumul_amount': Decimal('2000')}

尝试直接对聚合后的day_tot使用Window函数，抛出错误：

django.core.exceptions.FieldError: Cannot compute Sum('day_tot'): 'day_tot' is an aggregate

问题原因及解决方案 #

原因分析 #

直接使用Window(Sum('amount'))时，操作的是原始表的amount字段而非聚合后的day_tot，导致累计求和计算的是所有原始记录的金额总和，而非每日总额的累加。而Django不允许在同一查询中直接对聚合字段（如day_tot）再次进行聚合操作。

解决方案：子查询+Window函数 #

先通过子查询得到每日总额，再基于子查询结果计算累计求和：

from django.db.models import Sum, Window, F
from django.db.models.expressions import WindowFrame

# 第一步：获取每日总额的子查询
daily_totals = ScheduledOperation.objects.order_by('day').values('day').annotate(day_tot=Sum('amount'))

# 第二步：在子查询基础上计算累计求和
result = daily_totals.annotate(
    cumul_amount=Window(
        Sum('day_tot'),
        order_by=F('day').asc(),
        frame=WindowFrame(start=WindowFrame.UNBOUNDED_PRECEDING, end=WindowFrame.CURRENT_ROW)
    )
)

解释 #

• WindowFrame(start=WindowFrame.UNBOUNDED_PRECEDING, end=WindowFrame.CURRENT_ROW)指定累计范围为从查询结果的第一行到当前行，实现累加效果。
• 子查询将day_tot转换为普通字段，避免了直接对聚合字段操作的错误。

替代方案：使用CTE（Common Table Expression） #

如果使用Django 1.11及以上版本，可通过CTE实现更清晰的逻辑：

from django.db.models import Sum, Window, F
from django.db.models.expressions import WindowFrame, CTEQuerySet

# 定义CTE，存储每日总额
daily_cte = ScheduledOperation.objects.values('day').annotate(day_tot=Sum('amount')).order_by('day').cte()

# 基于CTE计算累计求和
result = CTEQuerySet(daily_cte).annotate(
    cumul_amount=Window(
        Sum('day_tot'),
        order_by=F('day').asc(),
        frame=WindowFrame(start=WindowFrame.UNBOUNDED_PRECEDING, end=WindowFrame.CURRENT_ROW)
    )
)

内容的提问来源于stack exchange，提问作者PyThane