Django聚合查询包含零计数 [英] Django Aggregate Query Include Zero Count

问题描述

在我的Django应用程序中，我试图获取所有学生提交的纸张的计数，包括未提交论文的学生(表示为count = 0)).

models.py

  class Student(models.Model):idstudent = models.AutoField(primary_key = True)student_name = models.CharField(max_length = 250，null = False，blank = False，verbose_name ='学生姓名')Paper(models.Model)类:idpaper = models.AutoField(primary_key = True)学生=模型.ForeignKey(学生，on_delete =模型.保护，null =假，空白=假) 

查询尝试1:仅返回已提交论文的学生

  papers = Paper.objects.order_by('submission_date')结果= papers.values('student'，student_name = F('student__student_name')).annotate(count = Count('student')).distinct().order_by('-count')打印(结果)< QuerySet [{'idstudent':1，'student_name':'\ nMichael Jordan \ n'，'count':4}，{'idstudent':2，'student_name':'\ nSteve White \ n'，'count':2}，{'idstudent':3，'student_name':'\ n希拉里克林顿\ n'，'count':1}]]> 

查询尝试2:返回已提交0篇论文但每其他学生的计数为1的学生

 结果= Student.objects.values('pk'，student_name = F('student_name')).注释(count = Count('pk'，filter = Q(pk__in = Paper.objects.values('student'))))).order_by('-count')打印(结果)< QuerySet [{'idstudent':1，'student_name':'\ nMichael Jordan \ n'，'count':1}，{'idstudent':2，'student_name':'\ nSteve White \ n'，'count':1}，{'idstudent':3，'student_name':'\ n希拉里克林顿\ n'，'count':1}，，{'idstudent':4，4，'student_name':'\ nDoug Funny \ n'，'count':0}，，{'idstudent':5，'student_name':'\ nSkeeter Valentine \ n'，'count':0}]]> 

与 Attempt 2 相同，我还使用 Sum(Case(2 原始SQL实际上使用了 Case(当时，但是当中存在 Student.pk 时，似乎仅计数

"Paper.objects.values 列表"(虽然不计算它出现了多少次).

  result = Student.objects.values('pk'，student_name = F('student_name')).annotate(count = Sum(案件(当(pk__in = Paper.objects.values('student')，则= 1)，默认值= 0，output_field = IntegerField())))< QuerySet [{'idstudent':1，'student_name':'\ nMichael Jordan \ n'，'count':1}，{'idstudent':2，'student_name':'\ nSteve White \ n'，'count':1}，{'idstudent':3，'student_name':'\ n希拉里克林顿\ n'，'count':1}，，{'idstudent':4，4，'student_name':'\ nDoug Funny \ n'，'count':0}，，{'idstudent':5，'student_name':'\ nSkeeter Valentine \ n'，'count':0}]]> 

我该如何调整查询范围，以包括已提交0篇论文的学生，同时又为已提交论文的学生保持正确的计数?

解决方案

与尝试2相同，我还尝试了以下方法Sum(Case(产生了相同的结果，因为我认识到尝试2原始SQL实际上利用了Case(何时，但似乎只算当Paper.objects.values列表"中存在Student.pk时(当不考虑它出现了多少次.

我都不理解这个问题/问题，但是您的尝试2"示例仅将计数过滤为 Paper.objects.values"list" ，它的正常行为是这样?

您尝试过以下简单方法吗:

  Student.objects.annotate(num_papers = Count('paper')) 

如果您要对计数进行其他过滤，我的建议是使用子查询这是一个示例:

  Student.objects.annotate(num_papers =子查询(Paper.objects.filter(student = OuterRef('pk'))#第一个.values调用定义了我们的GROUP BY子句#对此处定义的每个字段进行过滤很重要#否则，每个组将有多于一行！#在此示例中，我们仅按学生分组#，我们已经按学生过滤了.#您想要的任何额外过滤也应在此处进行(在分组之前)..values('学生')#这里我们说:计算每个组有多少行.annotate(cnt = Count('pk'))#这里我们说:只返回计数.values('cnt'))) 

In my Django application, I'm trying to get a Count of all Student submitted Papers, including students who have submitted NO papers (represented as count=0).

models.py

class Student(models.Model):
   idstudent = models.AutoField(primary_key=True)
   student_name = models.CharField(max_length=250, null=False, blank=False, verbose_name='Student Name')

class Paper(models.Model):
   idpaper = models.AutoField(primary_key=True)
   student = models.ForeignKey(Student, on_delete=models.PROTECT, null=False, blank=False)

Query Attempt 1: Returns only Students who have submitted Papers

papers = Paper.objects.order_by('submission_date')
result = papers.values('student', student_name=F('student__student_name')).annotate(count=Count('student')).distinct().order_by('-count')
print(result)       

<QuerySet [{'idstudent': 1, 'student_name': '\nMichael Jordan\n', 'count': 4}, {'idstudent': 2, 'student_name': '\nSteve White\n', 'count': 2}, {'idstudent': 3, 'student_name': '\nHillary Clinton\n', 'count': 1}]>

Query Attempt 2: Returns Students who have submitted 0 Papers, but the Count for every other Student is 1

result = Student.objects.values('pk', student_name=F('student_name'))
    .annotate(
        count=Count(
            'pk',
            filter=Q(pk__in=Paper.objects.values('student')
            )
        )
    )
).order_by('-count')
print(result)

<QuerySet [{'idstudent': 1, 'student_name': '\nMichael Jordan\n', 'count': 1}, {'idstudent': 2, 'student_name': '\nSteve White\n', 'count': 1}, {'idstudent': 3, 'student_name': '\nHillary Clinton\n', 'count': 1}, , {'idstudent': 4, 'student_name': '\nDoug Funny\n', 'count': 0}, , {'idstudent': 5, 'student_name': '\nSkeeter Valentine\n', 'count': 0}]>

Along the same lines as Attempt 2, I also tried the following using Sum(Case( which yielded the same result, as I recognized that the Attempt 2 raw SQL actually utilizes Case(When, but seems to only count when Student.pk is present in the Paper.objects.values "list" (while not accounting for how many times it is present).

result = Student.objects.values('pk', student_name=F('student_name')).annotate(
    count=Sum(
        Case(
            When(pk__in=Paper.objects.values('student'), then=1),
            default=0, output_field=IntegerField()
        )
    )
)

<QuerySet [{'idstudent': 1, 'student_name': '\nMichael Jordan\n', 'count': 1}, {'idstudent': 2, 'student_name': '\nSteve White\n', 'count': 1}, {'idstudent': 3, 'student_name': '\nHillary Clinton\n', 'count': 1}, , {'idstudent': 4, 'student_name': '\nDoug Funny\n', 'count': 0}, , {'idstudent': 5, 'student_name': '\nSkeeter Valentine\n', 'count': 0}]>

How might I adjust my query to include students who have submitted 0 papers while also maintaining the correct counts for students who have?

解决方案

Along the same lines as Attempt 2, I also tried the following using Sum(Case( which yielded the same result, as I recognized that the Attempt 2 raw SQL actually utilizes Case(When, but seems to only count when Student.pk is present in the Paper.objects.values "list" (while not accounting for how many times it is present).

Either I'm not understanding the problem/question, but your Attempt 2 example is filtering the count to only Paper.objects.values "list", its normal to act like this ?

Have you tried with the simple:

Student.objects.annotate(num_papers=Count('paper'))

If you want to make an additional filter on the count, my suggestion is to use subqueries here is an example:

Student.objects.annotate(
    num_papers=Subquery(
        Paper.objects.filter(student=OuterRef('pk'))
            # The first .values call defines our GROUP BY clause
            # Its important to have a filtration on every field defined here
            # Otherwise you will have more than one row per group!
            # In this example we group only by student
            # and we already filtered by student.
            # any extra filtration you want should be make here too (before the grouping).
            .values('student')
            # Here we say: count how many rows we have per group 
            .annotate(cnt=Count('pk'))
            # Here we say: return only the count
            .values('cnt')
    )
)

这篇关于Django聚合查询包含零计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！