Django Annotations and Aggregations

연습기록

• 2021-01-29

annotate

aggregate

문제를 풀어보자

from django.db import models


# Create your models here.
class Question(models.Model):
    question_text = models.CharField(max_length=200)

    def __str__(self):
        return self.question_text


class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

    def __str__(self):
        return f"Choice: {self.choice_text}, Question: {self.question.question_text}"

>>> from django.db.models import *
>>> 
>>> Question.objects.values()
<QuerySet [{'id': 1, 'question_text': 'question text 1'}, 
{'id': 2, 'question_text': 'question text 2'}, 
{'id': 3, 'question_text': 'question text 3'}, 
{'id': 4, 'question_text': 'question text 4'}, 
{'id': 5, 'question_text': 'question
 text 5'}]>
>>>

>>> 
>>> Choice.objects.values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1}, 
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2}, 
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3}, 
{'id'
: 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1}]>
>>>

• Number of Questions

>>> Question.objects.count()
5
>>> Question.objects.all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>>

• Number of Choices

>>> Choice.objects.count()
4
>>> Choice.objects.all()
<QuerySet [<Choice: Choice: choice text 1, Question: question text 1>, <Choice: Choice: choice text 2, Question: question text 1>, <Choice: Choice: choice text 3, Question: question text 1>, <Choice: Choice: choice text 4, Question: qu
estion text 2>]>
>>>

• Number of choices per question

# 여기서 질문! Count('choice')
# Choice 모델명은 대문자인데 왜 소문자로 표시할까?!
# Count('Choice')로하면 에러난당
# django.core.exceptions.FieldError: Cannot resolve keyword 'Choice' into field. Choices are: choice, id, question_text

>>> Question.objects.annotate(choice_num=Count('choice')).all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(choice_num=Count('choice')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'choice_num': 3}, 
{'id': 2, 'question_text': 'question text 2', 'choice_num': 1}, 
{'id': 3, 'question_text': 'question text 3', 'choice_num': 0}, 
{'id': 4, 'question_text': 'ques
tion text 4', 'choice_num': 0}, 
{'id': 5, 'question_text': 'question text 5', 'choice_num': 0}]>
>>>

• Number of choices per question after filtering on question_text

>>> Question.objects.filter(question_text="question text 1").all()
<QuerySet [<Question: question text 1>]>
>>> Question.objects.filter(question_text="question text 1").values()
<QuerySet [{'id': 1, 'question_text': 'question text 1'}]>
>>>

>>> Choice.objects.filter(question__question_text="question text 1").all()
<QuerySet [<Choice: Choice: choice text 1, Question: question text 1>, <Choice: Choice: choice text 2, Question: question text 1>, <Choice: Choice: choice text 3, Question: question text 1>]>
>>> Choice.objects.filter(question__question_text="question text 1").values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1}, 
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2}, 
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3}]>
>>>

>>> Question.objects.filter(question_text="question text 1").annotate(choice_count=Count('choice')).all()
<QuerySet [<Question: question text 1>]>
>>> Question.objects.filter(question_text="question text 1").annotate(choice_count=Count('choice')).all().values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'choice_count': 3}]>
>>>

• Number of votes per question

>>> Question.objects.annotate(vote_sum=Sum('choice__votes')).all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(vote_sum=Sum('choice__votes')).all().values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'vote_sum': 6}, 
{'id': 2, 'question_text': 'question text 2', 'vote_sum': 1}, 
{'id': 3, 'question_text': 'question text 3', 'vote_sum': None}, 
{'id': 4, 'question_text': 'questio
n text 4', 'vote_sum': None}, 
{'id': 5, 'question_text': 'question text 5', 'vote_sum': None}]>
>>>

>>> Choice.objects.all().values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1}, 
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2}, 
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3', 'votes': 3}, 
{'id'
: 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1}]>
>>>

• Questions with a maximum number of votes

>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count')
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>

>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').all()
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'vote_count': 3}, 
{'id': 2, 'question_text': 'question text 2', 'vote_count': 1}, 
{'id': 3, 'question_text': 'question text 3', 'vote_count': 0}, 
{'id': 4, 'question_text': 'ques
tion text 4', 'vote_count': 0}, 
{'id': 5, 'question_text': 'question text 5', 'vote_count': 0}]>
>>>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('-vote_count').values()[0]
{'id': 1, 'question_text': 'question text 1', 'vote_count': 3}
>>>

• Questions with a minimum number of votes

>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('vote_count').values()
<QuerySet [{'id': 3, 'question_text': 'question text 3', 'vote_count': 0}, 
{'id': 4, 'question_text': 'question text 4', 'vote_count': 0}, 
{'id': 5, 'question_text': 'question text 5', 'vote_count': 0}, 
{'id': 2, 'question_text': 'ques
tion text 2', 'vote_count': 1}, 
{'id': 1, 'question_text': 'question text 1', 'vote_count': 3}]>
>>> Question.objects.annotate(vote_count=Count('choice')).filter(vote_count__isnull=False).order_by('vote_count').values()[0]
{'id': 3, 'question_text': 'question text 3', 'vote_count': 0}
>>>

• Questions with no choices

>>> Question.objects.annotate(vote_count=Sum('choice__votes')).filter(vote_count__isnull=True).values()
<QuerySet [{'id': 3, 'question_text': 'question text 3', 'vote_count': None}, 
{'id': 4, 'question_text': 'question text 4', 'vote_count': None}, 
{'id': 5, 'question_text': 'question text 5', 'vote_count': None}]>
>>>

• Total number of votes cast

>>> Choice.objects.aggregate(num_votes=Sum('votes'))
{'num_votes': 7}
>>> 

• Average votes per choice

>>> Choice.objects.aggregate(avg=Avg('votes'))
{'avg': 1.75}
>>> 

• Average votes per question

>>> Question.objects.annotate(avg_vote=Avg('choice__votes'))
<QuerySet [<Question: question text 1>, <Question: question text 2>, <Question: question text 3>, <Question: question text 4>, <Question: question text 5>]>
>>> Question.objects.annotate(avg_vote=Avg('choice__votes')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'avg_vote': 2.0}, 
{'id': 2, 'question_text': 'question text 2', 'avg_vote': 1.0}, 
{'id': 3, 'question_text': 'question text 3', 'avg_vote': None}, 
{'id': 4, 'question_text': 'que
stion text 4', 'avg_vote': None}, 
{'id': 5, 'question_text': 'question text 5', 'avg_vote': None}]>
>>>

• Number of questions per question_text

>>> Question.objects.values('question_text').annotate(cnt=Count('question_text'))
<QuerySet [{'question_text': 'question text 1', 'cnt': 1}, {'question_text': 'question text 2', 'cnt': 1}, {'question_text': 'question text 3', 'cnt': 1}, {'question_text': 'question text 4', 'cnt': 1}, {'question_text': 'question text
 5', 'cnt': 1}]>
>>> 
>>> Question.objects.values('question_text').annotate(cnt=Count('question_text')).values()
<QuerySet [{'id': 1, 'question_text': 'question text 1', 'cnt': 1}, 
{'id': 2, 'question_text': 'question text 2', 'cnt': 1}, 
{'id': 3, 'question_text': 'question text 3', 'cnt': 1}, 
{'id': 4, 'question_text': 'question text 4', 'cnt':
1}, 
{'id': 5, 'question_text': 'question text 5', 'cnt': 1}]>
>>>

• Number of choices per question_text

>>> Choice.objects.values('question__question_text').annotate(cnt=Count('question__question_text')).values()
<QuerySet [{'id': 1, 'question_id': 1, 'choice_text': 'choice text 1', 'votes': 1, 'cnt': 1}, 
{'id': 2, 'question_id': 1, 'choice_text': 'choice text 2', 'votes': 2, 'cnt': 1}, 
{'id': 3, 'question_id': 1, 'choice_text': 'choice text 3'
, 'votes': 3, 'cnt': 1}, 
{'id': 4, 'question_id': 2, 'choice_text': 'choice text 4', 'votes': 1, 'cnt': 1}]>
>>>

Reference

• Django Annotations and Aggregations (https://medium.com/better-programming/django-annotations-and-aggregations-48685994d149)