如何在Django的annotate中使用average_ndvi与field_count变量

需求说明 #

需要将Django annotate操作里standart_deviation计算中的硬编码值0.14替换为已注解的average_ndvi变量，5替换为已注解的field_count变量。

原代码 #

commune = (
    Commune.objects.annotate(
        year=SearchVector("field__fieldattribute__date__year"),
        month=SearchVector(Cast("field__fieldattribute__date__month", CharField())),
        size=Sum(F("field__fieldattribute__planted_area")),
        average_ndvi=Avg(F("field__fieldattribute__ndvi")),
        field_count=Count("field"),
        standart_deviation=Sum(
            ((F("field__fieldattribute__ndvi") - 0.14) ** 2) / 5,
            output_field=FloatField(),
        ),
    )
    .filter(year=year, month=str(month))
    .only("id", "name")
)

模型定义（models.py） #

from django.db import models
from django.core.validators import MinValueValidator, MaxValueValidator

# 假设NDVI_MIN、NDVI_MAX、NDMI_MIN、NDMI_MAX已定义
NDVI_MIN = -1.0
NDVI_MAX = 1.0
NDMI_MIN = -1.0
NDMI_MAX = 1.0

class Region(models.Model):
    geometry = models.MultiPolygonField(geography=True)
    code = models.CharField(max_length=20)
    name = models.CharField(max_length=255)


class Commune(models.Model):
    geometry = models.MultiPolygonField(geography=True)
    code = models.CharField(max_length=20)
    name = models.CharField(max_length=255)
    region = models.ForeignKey(Region, on_delete=models.CASCADE, db_index=True)


class CommuneAttribute(models.Model):
    commune = models.ForeignKey(Commune, on_delete=models.CASCADE, db_index=True)
    date = models.DateField(db_index=True)
    ndvi = models.FloatField(
        null=True,
        blank=True,
        validators=[MinValueValidator(NDVI_MIN), MaxValueValidator(NDVI_MAX)],
    )
    ndmi = models.FloatField(
        null=True,
        blank=True,
        validators=[MinValueValidator(NDMI_MIN), MaxValueValidator(NDMI_MAX)],
    )
    average_yield = models.FloatField(
        null=True,
        blank=True,
    )
    planted_area = models.FloatField(
        null=True,
        blank=True,
    )
    crop_risk = models.FloatField(
        null=True,
        blank=True,
    )
    available_agriculture_land = models.FloatField(
        null=True,
        blank=True,
    )


class Field(models.Model):
    name = models.CharField(max_length=255)
    farm = models.CharField(max_length=255)
    farm_leader = models.CharField(max_length=255)
    geometry = models.MultiPolygonField(geography=True)
    commune = models.ForeignKey(Commune, on_delete=models.CASCADE)
    

class FieldAttribute(models.Model):
    date = models.DateField(db_index=True)
    ndvi = models.FloatField(
        null=True,
        blank=True,
        validators=[MinValueValidator(NDVI_MIN), MaxValueValidator(NDVI_MAX)],
    )
    ndmi = models.FloatField(
        null=True,
        blank=True,
        validators=[MinValueValidator(NDMI_MIN), MaxValueValidator(NDMI_MAX)],
    )
    harvest_forecast = models.FloatField(
        null=True,
        blank=True,
    )
    planted_area = models.FloatField(
        null=True,
        blank=True,
    )
    crop_risk = models.FloatField(
        null=True,
        blank=True,
    )
    field = models.ForeignKey(Field, on_delete=models.CASCADE)
    crop = models.CharField(
        max_length=32,
        null=True,
        blank=True,
    )
    average_yield = models.FloatField(
        null=True,
        blank=True,
    )

修改后的代码 #

from django.db.models import F, Sum, Avg, Count, SearchVector, Cast, CharField, FloatField, Case, When, Value

commune = (
    Commune.objects.annotate(
        year=SearchVector("field__fieldattribute__date__year"),
        month=SearchVector(Cast("field__fieldattribute__date__month", CharField())),
        size=Sum(F("field__fieldattribute__planted_area")),
        average_ndvi=Avg(F("field__fieldattribute__ndvi")),
        field_count=Count("field"),
        # 替换硬编码值为注解变量，同时处理field_count为0的情况避免除以0
        standart_deviation=Sum(
            ((F("field__fieldattribute__ndvi") - F("average_ndvi")) ** 2) / 
            Case(
                When(field_count=0, then=Value(1.0)),  # 避免除以0，可根据实际调整默认值
                default=F("field_count"),
                output_field=FloatField()
            ),
            output_field=FloatField(),
        ),
    )
    .filter(year=year, month=str(month))
    .only("id", "name")
)

关键说明 #

• 用F('average_ndvi')引用同一次annotate中定义的平均NDVI值，替代硬编码的0.14；
• 用F('field_count')替代硬编码的5，同时通过Case/When处理field_count为0的场景，防止出现数据库除以0的错误；
• Django的F表达式支持在annotate内部引用已定义的注解字段，会直接转换为对应SQL表达式，确保计算在数据库层面完成，效率更高。

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