Django Aggregate & Annotate Notları

• Bu yazımızda Django'da aggregate ve annotate kullanımını öğreneceğiz.

Tanımlar

• Aggregation, bir şeyleri gruplama, kümele, bir araya getirme sürecidir. Örneklerimizde veritabanı tablolarını bir araya getirmeyi django queryset'ler ile yapacağız.
• Annotation, bir yazıya ya da resme kısa bir açıklama ya da not eklemedir. Örneklerimizde django'daki model sınıf instance'a ekstra attribute ekleyerek kullanışlı bilgiler alacağız.

Veritabanı Modeli

 

models.py

​x
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)
​

python manage.py shell diyerek shell ortamında pratiklere başlıyoruz

xxxxxxxxxx
In [1]: import datetime
​
In [2]: from decimal import Decimal
​
In [3]: from django.db.models import *
​
In [4]: from storebook.models import Book, Store, Author, Publisher
​
In [5]: Book.objects.count() # Kac adet kitap var ?
Out[5]: 10
​
In [6]: Book.objects.filter(publisher__name="İz Yayıncılık") # iz yayincilik'a ait kitaplari getir
Out[6]: <QuerySet [<Book: Book object (10)>]>
​
In [7]: Book.objects.filter(publisher__name__istartswith="Polen") # polen yayincilik'a ait kitaplari getir
Out[7]: <QuerySet [<Book: Book object (9)>, <Book: Book object (16)>]>
​
In [8]: Book.objects.filter(publisher__name__istartswith="Polen").count() # polen yayincilik'a ait kitap sayisi
Out[8]: 2
​
In [9]: # Butun kitaplarin ortalama fiyati
​
In [10]: Book.objects.all().aggregate(Avg('price'))
Out[10]: {'price__avg': Decimal('21.5380000000000')}
​
In [11]: Book.objects.all().aggregate(ortalama_fiyat=Avg('price')) # ortalama_fiyat key'i olarak goster
Out[11]: {'ortalama_fiyat': Decimal('21.5380000000000')}
​
In [12]: Book.objects.aggregate(en_pahali=Max('price')) # en pahali kitap
Out[12]: {'en_pahali': Decimal('32.2900000000000')}
​
In [14]: Book.objects.aggregate(en_ucuz=Min('price')) # en ucuz kitap fiyati
Out[14]: {'en_ucuz': Decimal('13.75')}
​
In [15]: # En pahali kitap ortalama bir kitaptan ne kadar fazla ?
​
In [16]: Book.objects.aggregate(fiyat_farki=Max('price')-Avg('price'))
Out[16]: {'fiyat_farki': Decimal('10.7520000000000')}
​
In [17]: 32.29 - 21.538
Out[17]: 10.751999999999999
​
In [18]: # Her bir yayin evine ait kitap sayisini bulmak icin annotate kullanalim
​
In [19]: Publisher.objects.annotate(kitap_sayisi=Count('book'))
Out[19]: <QuerySet [<Publisher: Publisher object (1)>, <Publisher: Publisher object (2)>, <Publisher: Publisher object (3)>, <Publisher: Publisher object (4)>, <Publisher: Publisher object (5)>, <Publisher: Publisher object (6)>, <Publisher: Publisher object (7)>]>
​
In [20]: publishers = Publisher.objects.annotate(kitap_sayisi=Count('book'))
​
In [22]: [print("Kitap Adedi: ",p.kitap_sayisi," - Yayinevi", p.name) for p in publishers]
Kitap Adedi:  1  - Yayinevi Seha Yayıncılık
Kitap Adedi:  1  - Yayinevi İletişim Yayınları
Kitap Adedi:  2  - Yayinevi Büyük Doğu Yayınları
Kitap Adedi:  2  - Yayinevi Polen Yayınları
Kitap Adedi:  2  - Yayinevi Diriliş Yayınları
Kitap Adedi:  1  - Yayinevi İz Yayıncılık
Kitap Adedi:  1  - Yayinevi Beyan Yayınları
Out[22]: [None, None, None, None, None, None, None]
​
In [23]: # kitap sayisi en fazla olan 3 tane yayin evini kitap sayisina gore sirali sekilde alalim
​
In [24]: publishers = Publisher.objects.annotate(kitap_sayisi=Count('book')).order_by('-kitap_sayisi')[:3]
​
In [25]: print(publishers[0].name, publishers[0].kitap_sayisi)
Büyük Doğu Yayınları 2
​
In [26]: print(publishers[1].name, publishers[1].kitap_sayisi)
Polen Yayınları 2
​
In [27]: print(publishers[2].name, publishers[2].kitap_sayisi)
Diriliş Yayınları 2
​
In [28]: print(publishers[3].name, publishers[3].kitap_sayisi)
#---------------------------------------------------------------------------
# IndexError: list index out of range -> hatasi alinir cunku [:3] diyerek sadece 3 tane yayin evi aldik
​
​

Aggregate

• Queryset sonucuna göre aggregation yapılabilir. Book.objects.all().aggregate(Avg('price')) gibi.
• Book.objects.aggregate(Avg('price')) şeklinde de aggregation yapılabilir.
• aggregate() key-value çiftine sahip bir dictionary return eder.
• Aggregate değeri için manuel isim vermek isterseniz Book.objects.aggregate(ortalama_fiyat=Avg('price'))şeklinde kullanabilirsiniz. Return edilen veri {'ortalama_fiyat': 34.35} gibi olacaktır.
• Birden fazla aggregate oluşturmak için Book.objects.aggregate(Avg('price'), Max('price'), Min('price')) yapısını kullanabilirsiniz. Return edilen veri: {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')} gibi olacaktır.

Annotate

• Bir queryset'te her bir obje için bağımsız özet bir veri üretmek için kullanılır. Örneğin; bir kitap listesinde her bir kitaba kaç tane yazarın katkıda bulunduğunu bilmek için annotate yapısı kullanılabilir.

• xxxxxxxxxx
  >>> from django.db.models import Count
  >>> qs = Book.objects.annotate(yazar_sayisi=Count('authors'))
  # queryset'teki ilk obje icin yazar sayisini sorgulama
  >>> qs[0]
  <Book: Kitap Deneme 2>
  >>> qs[0].yazar_sayisi
  2
  # queryset'teki ikinci obje icin yazar sayisini sorgulama
  >>> qs[1]
  <Book: Kitap Deneme 3>
  >>> qs[1].yazar_sayisi
  1
  

• annotate() bir QuerySet return eder. Return edilen bu queryset başka bir queryset ile filter, order_by() gibi yapılar kullanılarak sorgu düzenlenebilir.

annotate ile birden fazla aggregation birleştirme

• Subquery'ler yerine join'ler kullanıldığı için birden fazla aggregation birleştirme hatalı sonuçlar çıkarmaktadır.

xxxxxxxxxx
In [36]: book1 = Book.objects.first()
​
In [37]: book1.authors.count()
Out[37]: 1
​
In [38]: book1.store_set.count()
Out[38]: 2
​
In [39]: q = Book.objects.annotate(Count('authors'),Count('store'))
​
In [40]: q[0].authors__count
Out[40]: 2
​
In [41]: q[0].store__count
Out[41]: 2
​

• Bir çok aggregate için bu problemi gidermenin yolu yoktur, ancak Count aggregate'e ait distinct parametresi yardımcı olabilir.

• xxxxxxxxxx
  In [45]: q = Book.objects.annotate(Count('authors', distinct=True),Count('store',distinct=True))
  ​
  In [46]: q[0].authors__count
  Out[46]: 1
  ​
  In [47]: q[0].store__count
  Out[47]: 2
  ​
  

Join ve Aggregate

• Şimdiye kadar yapılan örneklerde Model'e ait field'lar üzerinden aggregation yaptık. Ancak bazen ilişkili model'e ait field üzerinde de aggretaion yapma ihtiyacı duyarız. Django'daki double underscore __ notasyonu ile ilişkili modeldeki alanları aggregation'da kullanabiliriz.

• xxxxxxxxxx
  # Her bir yayinevine ait butun kitaplarin icindeki max ve min fiyatini gorelim 
  qs = Store.objects.annotate(minfiyat=Min('books__price'),maxfiyat=Max('books__price'))
  >>> [print(q.minfiyat,q.maxfiyat,q.name) for q in qs]
  13.75 32.2900000000000 Kitap Yurdu
  15.8700000000000 30.2300000000000 Idefix
  13.75 32.2900000000000 D&R
  

• Join zincirleri double underscore ile istenildigi kadar kullanılabilir.

• Örneğin; her bir yayın evindeki en genc yazar bilgisi

• xxxxxxxxxx
  In [65]: qs = Store.objects.annotate(en_genc_yazar=Min('books__authors__age'))
  ​
  In [66]: [print(q.en_genc_yazar, q.name) for q in qs]
  71 Kitap Yurdu
  77 Idefix
  58 D&R
  # aggregate ile en genc yazar
  In [92]: qs = Store.objects.aggregate(en_genc_yazar=Min('books__authors__age'))
  ​
  In [93]: qs
  Out[93]: {'en_genc_yazar': 58}
  ​
  

 

Backward relationship aggregation

• İlişkili modellerin lowercase sınıf adını ve double underscore notasyonunu kullanarak aggregation yapılabilir. Publisher -> Book arasındaki ilişkiyi yukarıda belirtmiştik.

• Örneğin her bir yayınevine ait kitap sayısını öğrenmek için;

• xxxxxxxxxx
  In [102]: qs = Publisher.objects.annotate(Count('book'))
  ​
  In [103]: qs[0].book__count
  Out[103]: 1
  ​
  In [104]: qs[1].book__count
  Out[104]: 1
  ​
  In [105]: qs[2].book__count
  Out[105]: 2
  

• Ya da her bir yayınevindeki toplam kitap sayfa sayısını bulmak için

• xxxxxxxxxx
  In [116]: qs_sum = Publisher.objects.annotate(Sum('book__pages'))
  In [118]: [print(q.book__pages__sum, q.name) for q in qs_sum]
  270 Seha Yayıncılık
  339 İletişim Yayınları
  1120 Büyük Doğu Yayınları
  584 Polen Yayınları
  276 Diriliş Yayınları
  136 İz Yayıncılık
  208 Beyan Yayınları
  

• Bütün yayınevlerindeki en eski ve en yeni yayımlanan kitaba ait tarih bilgisini aşağıdaki gibi alabiliriz

• xxxxxxxxxx
  In [121]: Publisher.objects.aggregate(en_eski_yayin=(Min('book__pubdate')))
  Out[121]: {'en_eski_yayin': datetime.date(1992, 1, 1)}
  ​
  In [122]: Publisher.objects.aggregate(en_yeni_yayin=(Max('book__pubdate')))
  Out[122]: {'en_yeni_yayin': datetime.date(2021, 2, 22)}
  ​
  

• Yazarların kitaplarının ortalama oranlarını aşağıdaki gibi alabiliriz

• xxxxxxxxxx
  # her bir yazarin spesifik ortalamasi
  In [126]: qa = Author.objects.annotate(ortalama_oran=Avg('book__rating'))
  In [127]: [print(q.ortalama_oran, q.name) for q in qa]
  4.75 Necip Fazıl Kısakürek
  4.9 Rasim Özdenören
  4.95 Sezai Karakoç
  4.7 Cemil Meriç
  5.0 İbn Kayyım El Cevziyye
  4.9 İhsan Süreyya Sırma
  Out[127]: [None, None, None, None, None, None]
  # butun yazarlarin ortalamasi
  In [128]: Author.objects.aggregate(ortalama_oran=Avg('book__rating'))
  Out[128]: {'ortalama_oran': 4.88}
  ​
  

• Yazarların kitap sayilarini almak icin

• xxxxxxxxxx
  In [131]: qa = Author.objects.annotate(kitapsayisi=Count('book'))
  ​
  In [132]: [print(q.kitapsayisi, q.name) for q in qa]
  2 Necip Fazıl Kısakürek
  1 Rasim Özdenören
  2 Sezai Karakoç
  1 Cemil Meriç
  2 İbn Kayyım El Cevziyye
  2 İhsan Süreyya Sırma
  Out[132]: [None, None, None, None, None, None]
  ​
  In [133]: qaggr = Author.objects.aggregate(kitapsayisi=Count('book'))
  ​
  In [134]: qaggr
  Out[134]: {'kitapsayisi': 10}
  ​
  

 

Aggregation ve diğer QuerySet ibareleri

• Aggregate ile filter() veya exlude() ibaresini kullanabilirsiniz.

• Örneğin Ç ile başlayan kitapların ortalama fiyatını aşağıdaki şekilde alabiliriz

• xxxxxxxxxx
  In [138]: Book.objects.filter(name__startswith='Ç').aggregate(Avg('price'))
  Out[138]: {'price__avg': Decimal('28.7900000000000')}
  

• Y ile başlayan her bir kitabın bulunduğu pazar yeri sayisi

• xxxxxxxxxx
  In [155]: qa = Book.objects.filter(name__startswith='Y').annotate(yayinci_sayisi=Count('store'))
  ​
  In [156]: [print(q.yayinci_sayisi, q.name) for q in qa]
  2 Yitik Cennet
  

• Ç ile başlayan her kitabın bulunduğu pazar yeri sayısı

• xxxxxxxxxx
  In [159]: qa = Book.objects.filter(name__startswith='Ç').annotate(yayinci_sayisi=Count('store'))
  ​
  In [160]: [print(q.yayinci_sayisi, q.name) for q in qa]
  2 Çöle İnen Nur
  3 Çile
  

Annotation'ların filtrelenmesi

• filter() veya exclude() ibareleri annotate ile kullanılabilir.

• Her bir kitap satış sitesindeki kitapların adedinin bilgisi ve adedi 5'ten büyük eşit olanların bilgisi

• xxxxxxxxxx
  In [172]: qa = Store.objects.annotate(num_book=Count('books'))
  ​
  In [173]: [print(q.num_book, q.name) for q in qa]
  8 Kitap Yurdu
  4 Idefix
  5 D&R
  ​
  In [174]: qa = Store.objects.annotate(num_book=Count('books')).filter(num_book__gte=5)
  ​
  In [175]: [print(q.num_book, q.name) for q in qa]
  8 Kitap Yurdu
  5 D&R
  ​
  

• Eğer 2 tane annotation'ı birleştirerek filtreleme yapacaksanız filter parametresini ve aşağıdaki yapıyı kullanabilirsiniz. Örneğin her bir yazarın kitap sayisini ve kitaplarinin oranlarindan 4.8'den buyuk ve esit olanlari almak icin;

• xxxxxxxxxx
  In [179]: yuksek_puanli = Count('book',filter=Q(book__rating__gte=4.8))
  ​
  In [180]: qa = Author.objects.annotate(num_books=Count('book'), yuksek_puanli_kitaplar=yuksek_puanli)
  ​
  In [181]: [print(q.num_books, q.yuksek_puanli_kitaplar ,q.name) for q in qa]
  2 1 Necip Fazıl Kısakürek # 2 kitabindan 1 tanesi 4.8 den buyuk ve esit
  1 1 Rasim Özdenören
  2 2 Sezai Karakoç
  1 0 Cemil Meriç # 1 kitabi var hicbiri 4.8'den buyuk ve esit DEGIL!
  2 2 İbn Kayyım El Cevziyye
  2 2 İhsan Süreyya Sırma
  ​
  

• filter parametresini tekil annotation ve aggregation'da kullanmak önerilmez. Bunun yerine Queryset.filter( ) yapısı kullanılmalıdır.

• filter parametresini 2 veya daha fazla aggregation ibarelerinde farklı condition'larda kullanım önerilir.

annotate() ve filter() ibarelerinin sırası

• Kompleks querysetler geliştirirken annotate ve filter ibarelerinin sırasına dikkat etmek gereklidir.

xxxxxxxxxx
#once ortalama alinir daha sonra en az 1 tane kitabin rate'i 4.'den buyuk olan(lar) gelir
In [200]: qa = Publisher.objects.annotate(ort_rate=Avg('book__rating')).filter(book__rating__gt=4.0)
​
In [201]: [print(q.ort_rate ,q.name) for q in qa]
4.7 İletişim Yayınları
4.75 Büyük Doğu Yayınları
5.0 Polen Yayınları
3.95 Diriliş Yayınları # (3.0+4.9)/2  !!!
4.9 İz Yayıncılık
4.1 Beyan Yayınları
# burada once kitap rate'i 4.0'dan buyuk olanlar alinir daha sonra ortalamalar alinir
In [202]: qa = Publisher.objects.filter(book__rating__gt=4.0).annotate(ort_rate=Avg('book__rating'))
​
In [203]: [print(q.ort_rate ,q.name) for q in qa]
4.7 İletişim Yayınları
4.75 Büyük Doğu Yayınları
5.0 Polen Yayınları
4.9 Diriliş Yayınları # 4.9/1 !!!
4.9 İz Yayıncılık
4.1 Beyan Yayınları
​
​

order_by ile annotate kullanımı

xxxxxxxxxx
In [9]: qa = Store.objects.annotate(kitapsayisi=Count('books')).order_by('kitapsayisi')
​
In [10]: [print(q.kitapsayisi, q.name) for q in qa ] 
4 Idefix
5 D&R
8 Kitap Yurdu
​
In [11]: qa = Store.objects.annotate(kitapsayisi=Count('books')).order_by('-kitapsayisi')
​
In [12]: [print(q.kitapsayisi, q.name) for q in qa ]
8 Kitap Yurdu
5 D&R
4 Idefix
​

values() ile annotate kullanımı

xxxxxxxxxx
In [17]: qa = Author.objects.values('name').annotate(ort_rate=Avg('book__rating'))
​
In [18]: qa
Out[18]: <QuerySet [{'name': 'Cemil Meriç', 'ort_rate': 4.7}, {'name': 'Necip Fazıl Kısakürek', 'ort_rate': 4.75}, {'name': 'Rasim Özdenören', 'ort_rate': 4.9}, {'name': 'Sezai Karakoç', 'ort_rate': 3.95}, {'name': 'İbn Kayyım El Cevziyye', 'ort_rate': 5.0}, {'name': 'İhsan Süreyya Sırma', 'ort_rate': 4.0}]>
In [21]: [print(q['ort_rate'], q['name']) for q in qa ]
4.7 Cemil Meriç
4.75 Necip Fazıl Kısakürek
4.9 Rasim Özdenören
3.95 Sezai Karakoç
5.0 İbn Kayyım El Cevziyye
4.0 İhsan Süreyya Sırma

• Eğer values() ibaresi annotate()ibaresinden önce gelirse, annotation'lar otomatik olarak sonuç data setine eklenecektir. Ancak values(), annotate()'den sonra gelirse annotation field'ları values içerisine eklemeniz gerekmektedir.

• xxxxxxxxxx
  In [25]: Author.objects.values('name').annotate(ort_rate=Avg('book__rating'))
  Out[25]: <QuerySet [{'name': 'Cemil Meriç', 'ort_rate': 4.7}, {'name': 'Necip Fazıl Kısakürek', 'ort_rate': 4.75}, {'name': 'Rasim Özdenören', 'ort_rate': 4.9}, {'name': 'Sezai Karakoç', 'ort_rate': 3.95}, {'name': 'İbn Kayyım El Cevziyye', 'ort_rate': 5.0}, {'name': 'İhsan Süreyya Sırma', 'ort_rate': 4.0}]>
  ​
  In [26]: Author.objects.annotate(ort_rate=Avg('book__rating')).values('name','ort_rate') # ort_rate eklendi
  ​
  Out[26]: <QuerySet [{'name': 'Necip Fazıl Kısakürek', 'ort_rate': 4.75}, {'name': 'Rasim Özdenören', 'ort_rate': 4.9}, {'name': 'Sezai Karakoç', 'ort_rate': 3.95}, {'name': 'Cemil Meriç', 'ort_rate': 4.7}, {'name': 'İbn Kayyım El Cevziyye', 'ort_rate': 5.0}, {'name': 'İhsan Süreyya Sırma', 'ort_rate': 4.0}]>
  ​
  

 

Aggreating annotations

• Annotate üzerine aggregation yapabilirsiniz.

• xxxxxxxxxx
  In [10]: Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
  Out[10]: {'num_authors__avg': 1.0}
  # baska bir ornek
  In [14]: Store.objects.annotate(num_books=Count('books')).aggregate(Avg('num_books'))
  Out[14]: {'num_books__avg': 4.25}
  ​
  

 

Kaynak

• https://docs.djangoproject.com/en/3.2/topics/db/aggregation/