如何在Django Orm中计算总和以及累计总和 [英] How to calculate sum and also cumulative sum in django orm

问题描述

我有表项目，项目有子项目，子项目有开发者。另一个表sprint，工作分布在sprint 1 2 ..n中，依此类推，每个sprint具有不同的开发人员数据。现在，如何使用Django Orm计算总和，当前sprint值和完成百分比。

I have table project, project has sub project and subproject has developers. Another table sprint, work distribute in sprint 1 2 ..n so on, Each sprint has different developers data. Now How can i calculate sum, current sprint value and percentage complete Using Django Orm.

项目具有子项目外键

 class project(models.Model):
        title = models.CharField(max_length=150)
        project = models.ForeignKey("self", on_delete=models.CASCADE,
                             related_name="subproject", blank=True, null=True)

  Developers table

  class Developers(models.Model):
        quantity = models.FloatField(default=0.0)
        charge_rate = models.FloatField(default=0.0)
        project = models.ForeignKey("Project", on_delete=models.SET_NULL,
                                 related_name="developers",  null=True)

Table Sprint

class Sprint(models.Model):
    sprint_name = models.CharField(max_length=150)
    percentage = models.FloatField(default=0.0)

sprint data

class SprintsData(models.Model):
    sprint = models.ForeignKey(
        "project", on_delete=models.CASCADE, related_name="d",
        blank=True)
    percentage = models.FloatField(default=0.0)

这是示例数据

项目

id name         project
1  development  null

子项目

id   name       project
 1  Homepage     1
 2  header       1
 3  footer       1

开发人员

id   name        quantity rate  project (sub project foreign key)
1    developers   5       200    1
2    designer      5       150    2

Sprint

id     name    start_date end_date
1     sprint1    -           -

SprintsData

SprintsData

id    sprint  project(subproject foreign key)  percentage
 1      1          1                               5
 2      1          2                               80

输出看起来像

sprint    project      sub_project   total_sum             percentage_complete current_sprint_amount
sprint1  development     Homepage   (quantity*charge_rate)         5              (total_sum)5% 
sprint1  development     Header        5*150                       80             (5*150)*80%     

推荐答案

以下查询将返回所有 SprintsData 对象，并用 total_sum 和 current_sprint_amount 注释。由此，您应该能够从django.db.models import Sum，F
SprintsData生成表

The following query will return all SprintsData objects annotated with total_sum and current_sprint_amount. From this you should be able to generate your table

from django.db.models import Sum, F
SprintsData.objects.annotate(
    total_sum=Sum(F('sprint__developers__quantity') * F('sprint__developers__charge_rate'), output_field=models.FloatField())
).annotate(
    current_sprint_amount=F('total_sum') * F('percentage')
)

SprintsData.sprint 实际上是项目模型的外键，这有点令人困惑

SprintsData.sprint is actually a foreign key to the project model which is a little confusing

Sprint 模型与任何其他模型都没有关系，所以我不确定您将如何获得sprint名称

The Sprint model has no relationships to any other model so I'm not sure how you would get the sprint name

这篇关于如何在Django Orm中计算总和以及累计总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！