Aggregering

I ämnesguiden om Djangos databasabstraktions-API beskrivs hur du kan använda Django-frågor som skapar, hämtar, uppdaterar och tar bort enskilda objekt. Ibland behöver du dock hämta värden som härleds genom att sammanfatta eller aggregera en samling objekt. I den här ämnesguiden beskrivs hur aggregerade värden kan genereras och returneras med hjälp av Django-frågor.

I den här guiden hänvisar vi till följande modeller. Dessa modeller används för att spåra lagret för en serie onlinebokhandlar:

from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()


class Publisher(models.Model):
    name = models.CharField(max_length=300)


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, on_delete=models.CASCADE)
    pubdate = models.DateField()


class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)

Fuskark

Har du bråttom? Så här gör du vanliga aggregerade frågor, förutsatt att modellerna ovan används:

# Total number of books.
>>> Book.objects.count()
2452

# Total number of books with publisher=BaloneyPress
>>> Book.objects.filter(publisher__name="BaloneyPress").count()
73

# Average price across all books, provide default to be returned instead
# of None if no books exist.
>>> from django.db.models import Avg
>>> Book.objects.aggregate(Avg("price", default=0))
{'price__avg': 34.35}

# Max price across all books, provide default to be returned instead of
# None if no books exist.
>>> from django.db.models import Max
>>> Book.objects.aggregate(Max("price", default=0))
{'price__max': Decimal('81.20')}

# Difference between the highest priced book and the average price of all books.
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
...     price_diff=Max("price", output_field=FloatField()) - Avg("price")
... )
{'price_diff': 46.85}

# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count("book"))
>>> pubs
<QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
>>> pubs[0].num_books
73

# Each publisher, with a separate count of books with a rating above and below 5
>>> from django.db.models import Q
>>> above_5 = Count("book", filter=Q(book__rating__gt=5))
>>> below_5 = Count("book", filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12

# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count("book")).order_by("-num_books")[:5]
>>> pubs[0].num_books
1323

Generera aggregat över en QuerySet

Django erbjuder två sätt att generera aggregat. Det första sättet är att generera sammanfattningsvärden över en hel QuerySet. Säg till exempel att du vill beräkna genomsnittspriset för alla böcker som finns till försäljning. Djangos frågesyntax ger ett sätt att beskriva uppsättningen av alla böcker:

>>> Book.objects.all()

Vad vi behöver är ett sätt att beräkna sammanfattande värden över de objekt som tillhör detta QuerySet. Detta görs genom att lägga till en aggregate() klausul på QuerySet:

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

all() är överflödigt i detta exempel, så detta kan förenklas till:

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

Argumentet till klausulen aggregate() beskriver det aggregerade värde som vi vill beräkna - i det här fallet genomsnittet av fältet price i modellen Book. En lista över de aggregatfunktioner som finns tillgängliga finns i :ref:QuerySet reference <aggregation-functions>.

aggregate() är en terminalklausul för en QuerySet som, när den anropas, returnerar en ordbok med namn-värde-par. Namnet är en identifierare för aggregatvärdet; värdet är det beräknade aggregatet. Namnet genereras automatiskt från fältets namn och aggregatfunktionen. Om du manuellt vill ange ett namn för det aggregerade värdet kan du göra det genom att ange namnet när du anger aggregatklausulen:

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

Om du vill generera mer än ett aggregat lägger du till ytterligare ett argument i aggregate()-satsen. Så om vi också ville veta det högsta och lägsta priset för alla böcker skulle vi ställa frågan:

>>> 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')}

Generera aggregat för varje objekt i en QuerySet

Det andra sättet att generera sammanfattningsvärden är att generera en oberoende sammanfattning för varje objekt i en QuerySet. Om du t.ex. hämtar en lista med böcker kanske du vill veta hur många författare som har bidragit till varje bok. Varje bok har en många-till-många-relation med författaren; vi vill sammanfatta denna relation för varje bok i QuerySet.

Sammanfattningar per objekt kan genereras med hjälp av klausulen annotate(). När en annotate()-sats anges kommer varje objekt i QuerySet att annoteras med de angivna värdena.

Syntaxen för dessa annoteringar är identisk med den som används för aggregate()-klausulen. Varje argument till annotate() beskriver ett aggregat som ska beräknas. Till exempel, för att annotera böcker med antalet författare:

# Build an annotated queryset
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count("authors"))
# Interrogate the first object in the queryset
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# Interrogate the second object in the queryset
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1

Precis som för aggregate() härleds namnet på annotationen automatiskt från namnet på aggregatfunktionen och namnet på det fält som ska aggregeras. Du kan åsidosätta detta standardnamn genom att ange ett alias när du specificerar annotationen:

>>> q = Book.objects.annotate(num_authors=Count("authors"))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

Till skillnad från aggregate() är annotate() inte en terminalsats. Resultatet av annotate()-satsen är en QuerySet; denna QuerySet kan modifieras med vilken annan QuerySet-operation som helst, inklusive filter(), order_by() eller till och med ytterligare anrop till annotate().

Kombinera flera aggregat

Att kombinera flera aggregeringar med annotate() kommer att ge fel resultat eftersom joins används istället för subqueries:

>>> book = Book.objects.first()
>>> book.authors.count()
2
>>> book.store_set.count()
3
>>> q = Book.objects.annotate(Count("authors"), Count("store"))
>>> q[0].authors__count
6
>>> q[0].store__count
6

För de flesta aggregat finns det inget sätt att undvika detta problem, men Count-aggregatet har en distinct-parameter som kan hjälpa till:

>>> q = Book.objects.annotate(
...     Count("authors", distinct=True), Count("store", distinct=True)
... )
>>> q[0].authors__count
2
>>> q[0].store__count
3

Om du är osäker, inspektera SQL-frågan!

För att förstå vad som händer i din query kan du inspektera egenskapen query i din QuerySet.

Sammanfogningar och aggregat

Hittills har vi behandlat aggregeringar över fält som tillhör den modell som efterfrågas. Ibland kommer dock det värde som du vill aggregera att tillhöra en modell som är relaterad till den modell som du frågar efter.

När du anger fältet som ska aggregeras i en aggregatfunktion tillåter Django dig att använda samma :ref:double underscore notation <field-lookups-intro> som används när du hänvisar till relaterade fält i filter. Django kommer då att hantera eventuella tabelljoins som krävs för att hämta och aggregera det relaterade värdet.

För att till exempel hitta prisintervallet för böcker som erbjuds i varje butik kan du använda annotationen:

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

Detta säger till Django att hämta Store-modellen, ansluta (genom många-till-många-relationen) till Book-modellen och aggregera på prisfältet i bokmodellen för att producera ett lägsta och högsta värde.

Samma regler gäller för klausulen aggregate(). Om du vill veta det lägsta och högsta priset för en bok som finns till försäljning i någon av butikerna kan du använda aggregatet:

>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))

Join-kedjorna kan vara så djupa som du vill. Om du t.ex. vill få fram åldern på den yngsta författaren till en bok som finns till försäljning kan du ställa frågan:

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

Följa relationer baklänges

På ett sätt som liknar Uppslagningar som sträcker sig över relationer kan aggregeringar och annoteringar av fält i modeller eller modeller som är relaterade till den modell du söker efter inkludera genomgång av ”omvända” relationer. Namnet med små bokstäver på relaterade modeller och dubbla understreck används även här.

Vi kan till exempel be om alla förlag, annoterade med deras respektive totala boklagerräknare (notera hur vi använder 'book för att ange Publisher -> Book omvänd främmande nyckelhopp):

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

(Varje Publisher i den resulterande QuerySet kommer att ha ett extra attribut som heter book__count)

Vi kan också be om den äldsta boken av alla dessa som hanteras av varje förlag:

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

(Den resulterande ordboken kommer att ha en nyckel som heter 'oldest_pubdate'. Om inget sådant alias anges kommer det att vara det ganska långa 'book__pubdate__min')

Detta gäller inte bara för främmande nycklar. Det fungerar också med många-till-många-relationer. Vi kan till exempel be om varje författare, annoterad med det totala antalet sidor med tanke på alla böcker som författaren har (med)skrivit (notera hur vi använder 'book' för att specificera Author -> Book omvänd många-till-många hopp):

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

(Varje Author i den resulterande QuerySet kommer att ha ett extra attribut som heter total_pages. Om inget sådant alias anges kommer det att vara det ganska långa book__pages__sum)

Eller be om det genomsnittliga betyget för alla böcker skrivna av författare som vi har i arkivet:

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

(Den resulterande ordboken kommer att ha en nyckel som heter 'average_rating'. Om inget sådant alias anges kommer det att vara det ganska långa 'book__rating__avg')

Aggregeringar och andra QuerySet-klausuler

filter() och exkludera()

Aggregat kan också delta i filter. Alla filter() (eller exclude()) som tillämpas på normala modellfält kommer att begränsa de objekt som beaktas för aggregering.

När ett filter används med en annotate()-sats begränsas de objekt för vilka en annotering beräknas. Du kan t.ex. generera en annoterad lista över alla böcker som har en titel som börjar med ”Django” med hjälp av frågan:

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

När ett filter används med en aggregate()-sats begränsar det de objekt över vilka aggregatet beräknas. Du kan t.ex. generera genomsnittspriset för alla böcker med en titel som börjar med ”Django” med hjälp av frågan:

>>> Book.objects.filter(name__startswith="Django").aggregate(Avg("price"))

Filtrering av anteckningar

Annoterade värden kan också filtreras. Aliaset för annoteringen kan användas i filter() och exclude()-klausuler på samma sätt som alla andra modellfält.

Om du t.ex. vill skapa en lista över böcker som har mer än en författare kan du ställa frågan:

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

Den här frågan genererar en annoterad resultatuppsättning och genererar sedan ett filter baserat på annoteringen.

Om du behöver två anteckningar med två separata filter kan du använda argumentet filter med valfritt aggregat. Till exempel, för att generera en lista över författare med ett antal högt rankade böcker:

>>> highly_rated = Count("book", filter=Q(book__rating__gte=7))
>>> Author.objects.annotate(num_books=Count("book"), highly_rated_books=highly_rated)

Varje Author i resultatmängden kommer att ha attributen num_books och highly_rated_books. Se även Villkorlig aggregering.

Att välja mellan filter och QuerySet.filter()

Undvik att använda argumentet filter med en enda annotation eller aggregering. Det är mer effektivt att använda QuerySet.filter() för att utesluta rader. Argumentet filter för aggregering är endast användbart när du använder två eller flera aggregeringar över samma relationer med olika villkor.

Ordningsföljd för annotate() och filter() klausuler

När du utvecklar en komplex fråga som innehåller både annotate()- och filter()-klausuler ska du vara särskilt uppmärksam på i vilken ordning klausulerna tillämpas på QuerySet.

När en annotate()-klausul tillämpas på en fråga, beräknas annoteringen över frågans tillstånd fram till den punkt där annoteringen begärs. Den praktiska konsekvensen av detta är att filter() och annotate() inte är kommutativa operationer.

Givetvis:

• Förlag A har två böcker med betyg 4 och 5.

• Förlag B har två böcker med betyg 1 och 4.

• Förlag C har en bok med betyg 1.

Här är ett exempel med aggregatet Count:

>>> a, b = Publisher.objects.annotate(num_books=Count("book", distinct=True)).filter(
...     book__rating__gt=3.0
... )
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 2)

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count("book"))
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 1)

Båda frågorna returnerar en lista med förlag som har minst en bok med ett betyg som överstiger 3,0, vilket innebär att förlag C utesluts.

I den första frågan föregår anteckningen filtret, så filtret har ingen effekt på anteckningen. distinct=True krävs för att undvika en :ref:query bug <combining-multiple-aggregations>.

Den andra frågan räknar antalet böcker som har ett betyg som överstiger 3,0 för varje utgivare. Filtret föregår anteckningen, så filtret begränsar de objekt som beaktas vid beräkningen av anteckningen.

Här är ett annat exempel med aggregatet Avg:

>>> a, b = Publisher.objects.annotate(avg_rating=Avg("book__rating")).filter(
...     book__rating__gt=3.0
... )
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 2.5)  # (1+4)/2

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(
...     avg_rating=Avg("book__rating")
... )
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 4.0)  # 4/1 (book with rating 1 excluded)

Den första frågan gäller det genomsnittliga betyget för alla förlagets böcker för de förlag som har minst en bok med ett betyg som överstiger 3,0. I den andra frågan efterfrågas medelbetyget för ett förlags böcker för endast de betyg som överstiger 3,0.

Det är svårt att förstå hur ORM kommer att översätta komplexa querysets till SQL-frågor, så om du är osäker, inspektera SQL med str(queryset.query) och skriv många tester.

order_by()

Annoteringar kan användas som grund för beställning. När du definierar en order_by()-sats kan de aggregat du tillhandahåller referera till alla alias som definierats som en del av en annotate()-sats i frågan.

Om du t.ex. vill beställa ett QuerySet med böcker efter antalet författare som har bidragit till boken kan du använda följande fråga:

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

värden()

Vanligtvis genereras annoteringar per objekt - en annoterad QuerySet returnerar ett resultat för varje objekt i den ursprungliga QuerySet. Men när en values()-klausul används för att begränsa de kolumner som returneras i resultatuppsättningen, är metoden för utvärdering av annoteringar något annorlunda. Istället för att returnera ett annoterat resultat för varje resultat i den ursprungliga QuerySet, grupperas de ursprungliga resultaten enligt de unika kombinationerna av de fält som anges i values()-klausulen. En annotering tillhandahålls sedan för varje unik grupp; annoteringen beräknas över alla medlemmar i gruppen.

Ett exempel är en författarfråga som försöker ta reda på det genomsnittliga betyget för böcker skrivna av varje författare:

>>> Author.objects.annotate(average_rating=Avg("book__rating"))

Detta kommer att ge ett resultat för varje författare i databasen, kommenterat med deras genomsnittliga bokbetyg.

Resultatet blir dock något annorlunda om du använder en values()-sats:

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

I det här exemplet kommer författarna att grupperas efter namn, så du får bara ett annoterat resultat för varje unikt författarnamn. Det innebär att om du har två författare med samma namn kommer deras resultat att slås samman till ett enda resultat i resultatet av frågan; genomsnittet beräknas som genomsnittet för de böcker som skrivits av båda författarna.

Ordningsföljd för klausulerna annotate() och values()

Precis som med filter()-klausulen har ordningen i vilken annotate() och values()-klausulerna tillämpas på en fråga betydelse. Om values()-satsen föregår annotate(), kommer annoteringen att beräknas med hjälp av den gruppering som beskrivs i values()-satsen.

Men om klausulen annotate() föregår klausulen values() genereras annoteringarna över hela frågeuppsättningen. I detta fall begränsar klausulen values() endast de fält som genereras vid utmatningen.

Om vi till exempel vänder på ordningen i klausulerna values() och annotate() från vårt tidigare exempel:

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

Detta kommer nu att ge ett unikt resultat för varje författare; dock kommer endast författarens namn och average_rating-annoteringen att returneras i utdata.

Du bör också notera att average_rating uttryckligen har inkluderats i listan över värden som skall returneras. Detta är nödvändigt på grund av ordningsföljden i klausulerna values() och annotate().

Om klausulen values() föregår klausulen annotate() läggs eventuella anteckningar automatiskt till i resultatuppsättningen. Men om values()-satsen tillämpas efter annotate()-satsen måste du uttryckligen inkludera aggregatkolumnen.

Interaktion med order_by()

Fält som nämns i order_by()-delen av en queryset används när utdata väljs, även om de inte annars anges i values()-anropet. Dessa extra fält används för att gruppera ”liknande” resultat tillsammans och de kan göra att annars identiska resultatrader ser ut att vara separata. Detta visar sig särskilt när man räknar saker.

Som exempel kan vi anta att du har en modell som ser ut så här:

from django.db import models


class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

Om du vill räkna hur många gånger varje distinkt data-värde förekommer i en ordnad frågeuppsättning kan du prova så här:

items = Item.objects.order_by("name")
# Warning: not quite correct!
items.values("data").annotate(Count("id"))

…som kommer att gruppera Item-objekten efter deras gemensamma data-värden och sedan räkna antalet id-värden i varje grupp. Förutom att det inte riktigt kommer att fungera. Ordningen efter namn kommer också att spela en roll i grupperingen, så den här frågan kommer att gruppera efter distinkta (data, namn) par, vilket inte är vad du vill. Istället bör du konstruera denna queryset:

items.values("data").annotate(Count("id")).order_by()

…och rensar bort all ordning i frågan. Du kan också beställa efter, säg, data utan några skadliga effekter, eftersom det redan spelar en roll i frågan.

Detta beteende är detsamma som det som noteras i queryset-dokumentationen för distinct() och den allmänna regeln är densamma: normalt vill du inte att extra kolumner ska spela en roll i resultatet, så rensa bort ordningen, eller se åtminstone till att den är begränsad endast till de fält som du också väljer i ett values()-anrop.

Observera

Du kan rimligen fråga dig varför Django inte tar bort de ovidkommande kolumnerna åt dig. Den främsta anledningen är konsekvens med distinct() och andra ställen: Django tar aldrig bort ordningsbegränsningar som du har angett (och vi kan inte ändra dessa andra metoders beteende, eftersom det skulle bryta mot vår API-stabilitet policy).

Aggregering av anteckningar

Du kan också generera ett aggregat på resultatet av en annotation. När du definierar en aggregate()-sats kan de aggregat du tillhandahåller referera till alla alias som definierats som en del av en annotate()-sats i frågan.

Om du t.ex. vill beräkna det genomsnittliga antalet författare per bok måste du först anteckna författarantalet i bokuppsättningen och sedan aggregera författarantalet med hänvisning till anteckningsfältet:

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

Aggregering på tomma querysets eller grupper

När en aggregering tillämpas på en tom frågeuppsättning eller gruppering, standardiseras resultatet till dess parameter default, vanligtvis None. Detta beteende uppstår eftersom aggregeringsfunktioner returnerar NULL när den körda frågan inte returnerar några rader.

Du kan ange ett returvärde genom att ange argumentet default för de flesta aggregeringar. Men eftersom Count inte stöder argumentet default, kommer den alltid att returnera 0 för tomma frågeuppsättningar eller grupper.

Om man till exempel antar att ingen bok innehåller web i sitt namn, skulle beräkningen av det totala priset för denna bokuppsättning returnera None eftersom det inte finns några matchande rader att beräkna Sum-aggregeringen på:

>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}

Argumentet default kan dock ställas in när man anropar Sum för att returnera ett annat standardvärde om inga böcker kan hittas:

>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}

Under huven implementeras argumentet default genom att aggregeringsfunktionen förpackas med Coalesce.