Aggregate functions grouping functions F queries Q queries in django

First, with the statement of mysql, aggregation is used in grouping.

groupby in mysql is a packet

Every time something is divided into groups, such as each group, it is divided into groups.

group by Field having Aggregate Function

# Example: Ask for the average grade in the class, select Avg (score) from stu

In django

Aggregation is aggreate (*args, **kwargs), calculated by QuerySet. Use when doing evaluation operations

Grouping is annotate (* args, **kwargs), and there are conditions in parentheses, so you have to group whenever you encounter anything.

First import the function to be used from models

from django.db.models import Min,Avg,Max,Sum

def aggregate(request):

  # Ask for the average price of all books 
  ret11 = Book.objects.all().aggregate(Avg("price"))
  # print(ret11)#{'price__avg': 51.975}

  # Ask for the highest price for all books 
  ret12 = Book.objects.all().aggregate(MaxPrice=Max('price'))
  # print(ret12)#{'MaxPrice': Decimal('100.00')}

  #egon  The highest price of a published book 
  p = Book.objects.filter(authors__name='egon').aggregate(Max('price'))

  print(p)#{'price__max': Decimal('100.00')}



  # Grouping  annotate()
  # Every 1 The highest price of a book published by an author   , 
  # Parsing, grouping according to the author's name, requires values Which shows the name of the field, 

  b1 = Book.objects.values('authors__name').annotate(Max('price'))

  print(b1)
  #<QuerySet [{'authors__name': 'egon', 'price__max': Decimal('100.00')},
      # {'authors__name': 'alex', 'price__max': Decimal('100.00')},
      # {'authors__name': 'yuan', 'price__max': Decimal('100.00')},
      # {'authors__name': 'oldboy', 'price__max': Decimal('100.00')}]># 4 All the authors are associated with the same 1 This book, and this book is the highest price 
# The lowest price books published by each publishing house 

b2 = Book.objects.values('publish__name').annotate(Min('price'))
print(b2)
#<QuerySet [{'publish__name': ' Beijing Press ', 'price__min': Decimal('100.00')},
# {'publish__name': ' Hexia Publishing House ', 'price__min': Decimal('45.00')},
# {'publish__name': ' Tsinghua Publishing House ', 'price__min': Decimal('39.90')},
# {'publish__name': ' People's Publishing House ', 'price__min': Decimal('23.00')}]>

F Q Query

F, mainly for query

Q query can be used to query combined criteria & Yes and, (pipe symbol) or, ~ not

def FAndQ(request):
 
  # Raise the price of each book 10 Yuan, 
  # Use sql Statement writing  update book set price=price+10
 
  #F, Mainly to do inquiries 
  # Book.objects.all().update(price=F('price')+10)
 
  # Query books are rich at the beginning, and the price is greater than 30 Books of   , comma   Can do   And   Relational query of 
  b4 = Book.objects.filter(title__startswith=" Rich ",price__gt=30)
  # print(b4)#<QuerySet [<Book:  Rich Dad >]>
 
 
  #Q Query   You can do queries for combined conditions  &  Yes   And   , | (Pipeline)   Or, ~  Non 
 
  ## The query book is rich at the beginning, or the price is greater than 30 Books of 
  b5 = Book.objects.filter(Q(title__startswith=" Rich ") | Q(price__gt=60))
  # print(b5)
  #<QuerySet [<Book: Linux>, <Book:  Xiangtalan >, <Book:  The Kite Runner >, <Book:  Rich Dad >]>
 
  # Combination query, starting with rich, the price is greater than 60  , or id Greater than 5 Books of 
  b6 = Book.objects.filter(Q(title__startswith=" Rich ")&Q(price__gt=60)|Q(id__gt=5))
  #print(b6)#<QuerySet [<Book:  Rich son >]>
  #
  # If the query condition is not added Q , it will be put at the back, 
  b7 = Book.objects.filter(Q(price__gt=60) | Q(id__gt=5),title__startswith=" Rich ")
  # print(b7)
 
  # ~  Take the reverse meaning 
  b8 = Book.objects.filter(Q(title__startswith=" Rich ") & ~Q(price__gt=60) | Q(id__gt=5))
  print(b8)