Django Aggregation database Aggregation query, djangoaggregation

Django Aggregation database Aggregation query, djangoaggregation

In today's applications, which are constantly adjusted based on requirements, not only do they need to be able to sort projects in alphabetical order or by creation date, you also need to sort projects by some other dynamic data. Djngo aggregation can meet these requirements.

The following Model is used as an example.

from django.db import modelsclass Author(models.Model):    name = models.CharField(max_length=100)    age = models.IntegerField()class Publisher(models.Model):    name = models.CharField(max_length=300)    num_awards = models.IntegerField()class Book(models.Model):    name = models.CharField(max_length=300)    pages = models.IntegerField()    price = models.DecimalField(max_digits=10, decimal_places=2)    rating = models.FloatField()    authors = models.ManyToManyField(Author)    publisher = models.ForeignKey(Publisher)    pubdate = models.DateField()class Store(models.Model):    name = models.CharField(max_length=300)    books = models.ManyToManyField(Book)    registered_users = models.PositiveIntegerField()

Quick Introduction

# Total number of books. >>> book. objects. count () 2452 # Total number of books with publisher = BaloneyPress >>> Book. objects. filter (publisher _ name = 'baloneypres '). count () 73 # average price of books. >>> from django. db. models import Avg >>> Book. objects. all (). aggregate (Avg ('price') {'price _ avg': 34.35} # maximum price of books. >>> from django. db. models import Max >>> Book. objects. all (). aggregate (Max ('price') {'price _ max ': Decimal ('81. 20')} # All the following queries involve traversing the Book <-> Publisher # publish-to-publish relationship backward # Add a num_books attribute for each publisher, that is, the number of books published by each pulisher. >>> from django. db. models import Count >>> pubs = Publisher. objects. annotate (num_books = Count ('book') >>> pubs [<Publisher BaloneyPress>, <Publisher SalamiPress>,...] >>> pubs [0]. num_books73 # sort by num_book attribute. >>> pubs = Publisher. objects. annotate (num_books = Count ('book ')). order_by ('-num_books') [: 5] >>> pubs [0]. num_books1323

Generating aggregates over a QuerySet by aggregation

Django has two methods to generate aggregation. The first method is to generate an aggregate value for the entire QuerySet. For example, to generate an average price for all books:

>>> from django.db.models import Avg>>> Book.objects.all().aggregate(Avg('price')){'price__avg': 34.35}

It can be simply as follows:

>>> Book.objects.aggregate(Avg('price')){'price__avg': 34.35}

The parameters of the function aggregate () are a series of aggregate functions:

Avg

Returns the average value.

Count
Class Count (field, distinct = False)

Return count. When distinct = True, the number of unique objects is returned.

Max

Returns the maximum value.

Min

Returns the minimum value.

StdDev
Class StdDev (field, sample = False)
Return Standard Deviation

There is a parameter sample

By default, sample = False indicates the total standard deviation. If sample = True, the sample standard deviation is returned.

Sum

Returns the total value.

Variance
 Class Variance (field, sample = False)
Return variance

The sample parameter returns the population variance by default. If the sample is set to True, the sample variance is returned.

When the aggregate () method is called, a key-Value Pair dictionary is returned. You can specify the key name:

>>> Book.objects.aggregate(average_price=Avg('price')){'average_price': 34.35}

If you want to generate multiple aggregates, you only need to add another parameter. Therefore, if we want to know the highest and lowest prices for all books:

>>> from django.db.models import Avg, Max, Min>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price')){'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

Generating aggregates for each item in a QuerySet for each object in the query set

This is the second method for generating aggregate values. For example, you need to retrieve the number of authors in each book. The relationship between book and author is manytomany. We can summarize this relationship for each book.

You can use annotate () to generate a summary of each object:

# Create an annotate QuerySet >>> from django. db. models import Count >>> q = Book. objects. annotate (Count ('author') # first object >>> q [0] <Book: The Definitive Guide to Django >>>> q [0]. authors _ count2 # second object >>> q [1] <Book: Practical Django Projects >>> q [1]. authors _ count1

You can also specify the name of the generated attribute:

>>> q = Book.objects.annotate(num_authors=Count('authors'))>>> q[0].num_authors2>>> q[1].num_authors1

Unlike aggregate (), the output of annotate () is a QuerySet.

Union aggregation Joins and aggregates

So far, all fields of our Aggregate Query belong to the Model we want to query. We can also use fields of other models for Aggregate Query. For example:

>>> from django.db.models import Max, Min>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

In this way, you can query the price range of all books in each Store.

The depth of the joint chain can be as desired:

>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))

Following relationships backwards

Query publisher in reverse mode using book:

>>> from django.db.models import Count, Min, Sum, Avg>>> Publisher.objects.annotate(Count('book'))

Each publisher of the returned QuerySet will contain a property book_count.

Query the publication date of the longest-published book:

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

Query the total number of pages of a book written by an author:

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

Query the average rating of all the books written by the author:

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

Aggregate and other query set operations Aggregations and other QuerySet clauses

Filter () and exclude ()

Aggregation can be used with filter and exclude:

>>> from django.db.models import Count, Avg>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))

You can filter by aggregation value:

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

Different sequences have different meanings:

>>> Publisher.objects.annotate(num_books=Count('book')).filter(book__rating__gt=3.0)>>> Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))

Returns a QuerySet. The former calculates the number of all books, and the latter only calculates the number of good books.

Order_by ()

You can sort data by aggregation values:

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

Values ()

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

In this way, QuerySet combines names and returns the aggregate values of each unique name. If two authors have the same name, these two authors will be considered as a calculation, and their books will join.

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

After location swaps, an average_rating is generated for each author, and only the name and average_rating of each author are output.

By default, aggregation is used for sorting:

from django.db import modelsclass Item(models.Model):    name = models.CharField(max_length=10)    data = models.IntegerField()    class Meta:        ordering = ["name"]

If you want to calculate the number of different data, you may write as follows:

# Warning: incorrect syntax Item. objects. values ("data"). annotate (Count ("id "))

Because the query will be combined based on (data, name), you need to write this to remove the influence of default sorting:

Item.objects.values("data").annotate(Count("id")).order_by()

Aggregating annotations

You can also generate aggregate values based on annotation results. For example, there are several average authors in each book:

>>> from django.db.models import Count, Avg>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors')){'num_authors__avg': 1.66}

　　

Database Query problems. Can Aggregate functions be used together?

// Hope that the answer from the kupbpb will be helpful to you!

// First, it indicates that nested Aggregate functions are not supported!

// The following SQL query can return the running result correctly in SQL Server 2005;
// Customer represents the account table, and Bank represents the savings table;
// Cbno indicates the account number in the customer table, and Bno indicates the account number;
// City indicates the City to which the account belongs. bk is the temporary table of the returned result.

Correct code:
Select *
From customer as C, Bank as B
Where C. Cbno = B. Bno and B. City in
(Select City
From Bank
Group by City
Having count (Bno) = (select min (Nm)
From (select City, count (Bno)
From Bank
Group by City
) As bk (Cy, Nm)
)
);

How does django directly use model code (ORM) to test and view the database query results on the spot?

Use python manage. py shell in the django Project

From django. db import connection
# Import-related model class
# Query execution
Print connection. queries