Django多外键关联下Annotate分组计算KPI分数问题

项目背景 #

我正在开发一套KPI系统，管理人员可为每位用户设置特定问题，每月对这些问题进行1-10分的评分。系统包含4个部门，每个部门设有4个问题，需计算各部门的得分百分比。

模型定义 #

# 每月需回答的问题
class Question(models.Model):
    department = models.CharField(max_length=100, choices=USER_TYPE)
    question = models.CharField(max_length=100)

    def __str__(self):
        return f"{self.question}"

# 每月1号通过Celery创建
class Kpi(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="kip_for")
    created_at = models.DateField()

    class Meta:
        unique_together = ("user", "created_at")

    def __str__(self):
        return f"{self.user} for Month {self.created_at.month}"

# 按部门分组答案
class DepartmentsKPI(models.Model):
    department = models.CharField(max_length=100, choices=USER_TYPE)
    kpi = models.ForeignKey(
        Kpi, on_delete=models.CASCADE, related_name="department_kpi"
    )

class Answer(models.Model):
    question = models.ForeignKey(
        Question, on_delete=models.CASCADE, related_name="answer_for"
    )
    score = models.PositiveSmallIntegerField(default=0)
    comment = models.TextField(null=True, blank=True)
    kpi_answers = models.ForeignKey(
        DepartmentsKPI,
        on_delete=models.CASCADE,
        related_name="department_answers",
    )

当前问题与现有实现 #

在Kpi模型上使用annotate汇总各部门得分时，结果未正确分组，因此需按用户名手动分组。现有实现代码如下：

def get_kpi_range(*, year, month):
    queryset = (
        KPIRepository.filter_kpis(
            created_at__year=year,
            created_at__month=month,
        )
        .prefetch_related("department_kpi__department_answers")
        .annotate(
            score=Sum("department_kpi__department_answers__score")
            / Count("department_kpi__department_answers"),
            department=F("department_kpi__department"),
        )
        .values("id", "score", "department", username=F("user__username"))
    )

    grouped = [
        {"username": username, "kpis": list(instances)}
        for username, instances in itertools.groupby(queryset, lambda x: x["username"])
    ]

    return grouped

该方案存在缺陷：无法处理同一用户的多条记录，查询全年KPI数据时结果混乱；同时无法按月份分组返回用户数据，无法满足生成图表的需求。

当前结果 #

使用grouped后的结果 #

[
    {
        "username": "xxxxx",
        "kpis": [
            {
                "department": "HR",
                "score": 5
            },
            {
                "department": "IT",
                "score": 6
            },
            {
                "department": "QUALITY",
                "score": 4
            },
            {
                "department": "WFM",
                "score": 6
            }
        ]
    },
    {
        "username": "ahmed",
        "kpis": [
            {
                "department": "IT",
                "score": 6
            }
        ]
    }
]

未使用grouped的结果 #

[
    [
        {
            "id": 7,
            "score": 5,
            "department": "HR",
            "username": "xxx"
        },
        {
            "id": 7,
            "score": 6,
            "department": "IT",
            "username": "xxx"
        },
        {
            "id": 7,
            "score": 4,
            "department": "QUALITY",
            "username": "xxx"
        },
        {
            "id": 7,
            "score": 6,
            "department": "WFM",
            "username": "xxx"
        },
        {
            "id": 8,
            "score": 6,
            "department": "IT",
            "username": "ahmed"
        }
    ]
]

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