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 )