Django (18) aggregate function

Posted by rem on Wed, 09 Feb 2022 16:02:39 +0100

preface

Purchase discount https://m.fenfaw.cn/

The aggregate functions in the orm model are consistent with those in MySQL, including Sum, Avg, Count, Max and Min. let's introduce them one by one

Aggregate function

All aggregate functions are placed in Django db. Under models. And the aggregate function cannot be executed alone. The aggregate function is implemented through the aggregate method. When explaining the usage of aggregate functions, they are implemented based on the following model objects.

class Author(models.Model):
     """Author model"""
     name = models.CharField(max_length=100)
     age = models.IntegerField()
     email = models.EmailField()

     class Meta:
         db_table = 'author'


 class Publisher(models.Model):
     """Publishing house model"""
     name = models.CharField(max_length=300)

     class Meta:
         db_table = 'publisher'


 class Book(models.Model):
     """Book model"""
     name = models.CharField(max_length=300)
     pages = models.IntegerField()
     price = models.FloatField()
     rating = models.FloatField()
     author = models.ForeignKey(Author,on_delete=models.CASCADE)
     publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

     class Meta:
         db_table = 'book'


 class BookOrder(models.Model):
     """Book order model"""
     book = models.ForeignKey("Book",on_delete=models.CASCADE)
     price = models.FloatField()

     class Meta:
         db_table = 'book_order'

Avg

Avg: average. For example, you want to get the average price of all books. Then you can use the following code.

 from django.db.models import Avg
 result = Book.objects.aggregate(Avg('price'))
 print(result)

The above print result is:

 {"price__avg":23.0}

Where price__ The structure of Avg is based on field__avg rules constitute. If you want to change the default name, you can assign Avg to a keyword parameter. The example code is as follows:

 from django.db.models import Avg
 result = Book.objects.aggregate(my_avg=Avg('price'))
 print(result)

The above print result is

 {"my_avg":23}

Count

Count: gets the number of specified objects. The example code is as follows:

 from django.db.models import Count
 result = Book.objects.aggregate(book_num=Count('id'))

The above result will return the total number of books in the Book table.
In the Count class, there is another parameter called distinct, which is equal to False by default. If it is equal to True, those duplicate values will be removed. For example, to get the total number of non duplicate mailboxes in the author table, you can use the following code:

from djang.db.models import Count
result = Author.objects.aggregate(count=Count('email',distinct=True))

Max and Min

Max and Min: get the maximum and minimum values of the specified object. For example, you want to get the maximum age and minimum age in the Author table. This can be achieved through the following code:

from django.db.models import Max,Min
result = Author.objects.aggregate(Max('age'),Min('age'))

If the maximum age is 90, the minimum age is 10. Then the above result will be:

{"age__max":90,"age__min":10}

Sum

Sum: sum the specified objects. For example, the total sales of books are required. Then you can use the following code:

from djang.db.models import Sum
result = Book.objects.annotate(total=Sum("bookorder__price"))

The above code annotate means to add a field called total to the Book table when querying. The data source of this field is the sum of the price of the BookOrder model.

The difference between aggregate and annotate

The same thing: both methods can perform aggregate functions.
difference:
- Aggregate returns a dictionary in which the execution result of the aggregate function is stored. annotate returns a QuerySet object, and an aggregate function attribute will be added to the searched model.
- aggregate will not group, but annotate will use the group by clause to group. Only when the group by clause is called can the value of the aggregation function be calculated for each piece of data.

F expression:

F expression: dynamically obtain the value of a field. And this f expression will not really query the data in the database. It is equivalent to just playing the role of identification. For example, if you want to increase the price of each book by 10 yuan, you can use the following code:

from django.db.models import F
Book.objects.update(price=F("price")+10)

Q expression

Q expression: use Q expression to wrap query criteria. You can perform multiple operations between conditions. And / or not, so as to realize some complex query operations. Examples are as follows:

Find books with a price greater than 100 and a score of more than 4.85:

  # Without Q expression
  books = Book.objects.filter(price__gte=100,rating__gte=4.85)
  # Using Q expression
  books = Book.objects.filter(Q(price__gte=100)&Q(rating__gte=4.85))

Find books with a price of less than 100 yuan or a score of less than 4:

books = Book.objects.filter(Q(price__gte=100)&Q(rating__gte=4.85))

Obtain books with a price greater than 100 and the name of the book does not contain the word "Chuan":

books = Book.objects.filter(Q(price__gte=100)&~Q(name__icontains='pass'))

Programmer Think