Django annotate多Sum字段导致结果重复的问题排查

问题场景 #

单独对TransactionOut字段求和时结果正确，但同时添加TransactionIn的求和注解后，两个字段的结果均出现倍数重复（如2000变为4000）。

单注解正确代码 #

centers = Center.objects.filter(branch=request.user.center.branch,name="test").annotate(
    transaction_out=Coalesce(Sum('_TransactionOut__amount', filter=Q(_TransactionOut__date_time__gte=thirty_days_ago)),Value(0.0)),
)

添加双注解后出现重复的代码 #

centers = Center.objects.filter(branch=request.user.center.branch,name="test").annotate(
    transaction_in=Coalesce(Sum('_TransactionIn__amount', filter=Q(_TransactionIn__date_time__gte=thirty_days_ago)),Value(0.0)),
    transaction_out=Coalesce(Sum('_TransactionOut__amount', filter=Q(_TransactionOut__date_time__gte=thirty_days_ago)),Value(0.0)),
)

模型定义 #

class TransactionIn(models.Model):
    amount = models.FloatField()
    center=models.ForeignKey("center.Center",on_delete=models.SET_NULL,null=True,blank=True, related_name="_TransactionIn")

class TransactionOut(models.Model):
    amount = models.FloatField()
    user=models.ForeignKey("account.User",on_delete=models.SET_NULL,null=True,blank=True)
    center=models.ForeignKey("center.Center",on_delete=models.SET_NULL,null=True,blank=True, related_name="_TransactionOut")

class Center(models.Model):
    name = models.CharField(max_length=35)

生成的SQL查询语句 #

SELECT "center_center"."id",
       "center_center"."name",
       "center_center"."branch_id",
       "center_center"."image",
       "center_center"."manager_id",
       "center_center"."lat",
       "center_center"."long",
       "center_center"."mobile_number",
       COALESCE(SUM("accounting_transactionin"."amount") FILTER (WHERE "accounting_transactionin"."date_time" >= '2023-06-10T21:08:09.278740+00:00'::timestamptz), 0.0) AS "transaction_in",
       COALESCE(SUM("accounting_transactionout"."amount") FILTER (WHERE "accounting_transactionout"."date_time" >= '2023-06-10T21:08:09.278740+00:00'::timestamptz), 0.0) AS "transaction_out"
  FROM "center_center"
  LEFT OUTER JOIN "accounting_transactionin"
    ON ("center_center"."id" = "accounting_transactionin"."center_id")
  LEFT OUTER JOIN "accounting_transactionout"
    ON ("center_center"."id" = "accounting_transactionout"."center_id")
 WHERE ("center_center"."branch_id" = 1 AND "center_center"."name" = 'test')
 GROUP BY "center_center"."id"

问题原因 #

两次LEFT JOIN操作生成了笛卡尔积：假设一个Center关联2条TransactionIn和2条TransactionOut，JOIN后会产生2×2=4条记录，求和时每条TransactionIn的amount会被计算2次，TransactionOut的amount也会被计算2次，最终结果出现倍数重复。

解决方案 #

方法1：使用子查询（Subquery）分别计算求和值 #

通过子查询单独计算每个关联表的求和结果，避免JOIN带来的笛卡尔积问题，兼容性覆盖所有数据库：

from django.db.models import Subquery, OuterRef

# 子查询计算transaction_in
transaction_in_subquery = TransactionIn.objects.filter(
    center=OuterRef('pk'),
    date_time__gte=thirty_days_ago
).values('center').annotate(
    total=Sum('amount')
).values('total')

# 子查询计算transaction_out
transaction_out_subquery = TransactionOut.objects.filter(
    center=OuterRef('pk'),
    date_time__gte=thirty_days_ago
).values('center').annotate(
    total=Sum('amount')
).values('total')

# 主查询
centers = Center.objects.filter(
    branch=request.user.center.branch,
    name="test"
).annotate(
    transaction_in=Coalesce(Subquery(transaction_in_subquery), Value(0.0)),
    transaction_out=Coalesce(Subquery(transaction_out_subquery), Value(0.0))
)

方法2：使用distinct参数配合Sum（仅PostgreSQL可用） #

如果使用PostgreSQL数据库，可以在Sum中添加distinct=True，基于关联表的主键去重，确保每条记录只被计算一次：

centers = Center.objects.filter(branch=request.user.center.branch,name="test").annotate(
    transaction_in=Coalesce(Sum('_TransactionIn__amount', filter=Q(_TransactionIn__date_time__gte=thirty_days_ago), distinct=True), Value(0.0)),
    transaction_out=Coalesce(Sum('_TransactionOut__amount', filter=Q(_TransactionOut__date_time__gte=thirty_days_ago), distinct=True), Value(0.0)),
)

方法3：通过Count修正重复结果（临时方案，不推荐） #

如果已知重复倍数，可以用Count获取关联记录数后修正结果，但该方法灵活性差，仅适用于临时场景：

from django.db.models import Case, When, FloatField

centers = Center.objects.filter(branch=request.user.center.branch,name="test").annotate(
    transaction_in_sum=Coalesce(Sum('_TransactionIn__amount', filter=Q(_TransactionIn__date_time__gte=thirty_days_ago)),Value(0.0)),
    transaction_out_sum=Coalesce(Sum('_TransactionOut__amount', filter=Q(_TransactionOut__date_time__gte=thirty_days_ago)),Value(0.0)),
    in_count=Count('_TransactionIn', filter=Q(_TransactionIn__date_time__gte=thirty_days_ago), distinct=True),
    out_count=Count('_TransactionOut', filter=Q(_TransactionOut__date_time__gte=thirty_days_ago), distinct=True)
).annotate(
    transaction_in=Case(
        When(in_count__gt=0, then=F('transaction_in_sum') / F('out_count')),
        default=Value(0.0),
        output_field=FloatField()
    ),
    transaction_out=Case(
        When(out_count__gt=0, then=F('transaction_out_sum') / F('in_count')),
        default=Value(0.0),
        output_field=FloatField()
    )
)

推荐方案 #

优先选择方法1（子查询），逻辑清晰且兼容性强；若使用PostgreSQL环境，**方法2（distinct参数）**代码更简洁，也是优质选择。

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