使用Django ORM计算产品可用数量结果不准确问题排查

问题背景 #

使用Django ORM为Products查询集注释生产数量、销售数量及可用数量字段时，计算结果严重偏离实际值；但采用原生SQL执行相同逻辑时结果准确。

原ORM代码（结果不准确） #

def get_queryset(self):
    queryset = super().get_queryset()
    queryset = queryset.annotate(Quantity_Produced=Sum(F('production__qunatity_produced')))
    queryset = queryset.annotate(Quantity_Sold=Sum(F('sales__qunatity_delivered')))
    queryset = queryset.annotate(Quantity_available=Sum(F('production__qunatity_produced'))
                                                        - Sum(F('sales__qunatity_delivered')))
    return queryset

ORM输出结果 #

{
    "product_id": 1,
    "product_name": "Product 1",
    "weight": 10.0,
    "Quantity_Produced": 6300.0,
    "Quantity_Sold": 2600.0,
    "Quantity_available": 3700.0
}

原生SQL代码（结果准确） #

def get_queryset(self):
    queryset= models.Products.objects.raw('''
        SELECT
            *,
            (SELECT SUM(q.qunatity_produced) FROM production q WHERE q.product_id = p.product_id) AS Quantity_Produced ,
            (SELECT SUM(s.qunatity_delivered) FROM Sales s WHERE s.product_id = p.product_id) AS Quantity_Sold,
            sum((SELECT SUM(q.qunatity_produced) FROM production q WHERE q.product_id = p.product_id)
            -(SELECT SUM(s.qunatity_delivered) FROM Sales s WHERE s.product_id = p.product_id))as Quantity_available
        FROM
            products p
            group by Product_id
            order by Product_id
        ''')
    return queryset

原生SQL输出结果 #

{
    "product_id": 1,
    "product_name": "Product 1",
    "weight": 10.0,
    "Quantity_Produced": 700.0,
    "Quantity_Sold": 260.0,
    "Quantity_available": 440.0
}

模型定义 #

class Products(models.Model):
    product_id = models.AutoField(primary_key=True)  
    product_name = models.CharField(max_length=255)
    weight = models.ForeignKey(Bags, models.DO_NOTHING)


class Production(models.Model):
    production_id = models.AutoField(primary_key=True)
    product = models.ForeignKey('Products', models.DO_NOTHING)
    date_of_production = models.DateField()
    qunatity_produced = models.FloatField()
    unit_price = models.FloatField()


class Sales(models.Model):
    sales_id = models.AutoField(primary_key=True)
    date_of_sale = models.DateField()
    customer = models.ForeignKey(Customers, models.DO_NOTHING)
    product = models.ForeignKey(Products, models.DO_NOTHING)
    qunatity_delivered = models.FloatField()
    unit_price = models.FloatField()
    total_amount = models.FloatField(default=0.0)
    payment_type = models.ForeignKey(Paymenttype, models.DO_NOTHING)
    description = models.TextField(blank=True, null=True)
    address= models.CharField(max_length=255)

失效原因 #

ORM代码执行时，Django会对Products与Production、Sales进行多表左连接，当一个产品存在多条生产记录和多条销售记录时，会产生笛卡尔积（即生产记录数 × 销售记录数）。此时使用Sum聚合会重复计算每条记录：

• 生产数量会被重复计算「销售记录数」次
• 销售数量会被重复计算「生产记录数」次
  最终导致聚合结果被放大，与实际值不符。

而原生SQL采用子查询方式，分别针对每个产品单独计算生产、销售的总和，避免了多表连接带来的笛卡尔积问题。

修复方案 #

使用Django的Subquery和OuterRef实现子查询逻辑，模拟原生SQL的计算方式，避免笛卡尔积：

from django.db.models import Subquery, OuterRef, Sum, F

def get_queryset(self):
    # 定义生产数量子查询：按产品分组计算总生产数量
    production_subquery = Production.objects.filter(
        product=OuterRef('pk')
    ).values('product').annotate(
        total_produced=Sum('qunatity_produced')
    ).values('total_produced')

    # 定义销售数量子查询：按产品分组计算总销售数量
    sales_subquery = Sales.objects.filter(
        product=OuterRef('pk')
    ).values('product').annotate(
        total_sold=Sum('qunatity_delivered')
    ).values('total_sold')

    queryset = super().get_queryset()
    queryset = queryset.annotate(
        Quantity_Produced=Subquery(production_subquery, output_field=models.FloatField()),
        Quantity_Sold=Subquery(sales_subquery, output_field=models.FloatField())
    ).annotate(
        # 基于已注释的字段计算可用数量，避免重复聚合
        Quantity_available=F('Quantity_Produced') - F('Quantity_Sold')
    )
    return queryset

修复后效果 #

执行修复后的ORM代码，将得到与原生SQL一致的准确结果：

{
    "product_id": 1,
    "product_name": "Product 1",
    "weight": 10.0,
    "Quantity_Produced": 700.0,
    "Quantity_Sold": 260.0,
    "Quantity_available": 440.0
}

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