django query 기초 3

1. select_related and prefetch_related

모델 정의

class Company(models.Model): name = models.CharField(max_length=30) phone_num = models.CharField(max_length=30) floor = models.PositiveIntegerField(default=1) def __str__(self): return "{} {} {}".format(self.name, self.phone_num, self.floor) class Employee(models.Model): name = models.CharField(max_length=30) age = models.PositiveIntegerField(default=1) salary = models.PositiveIntegerField(default=1) company = models.OneToOneField(Company, on_delete=models.CASCADE, default=None) def __str__(self): return "{} {} {} {}".format(self.name, self.age, self.salary, self.company)

데이터 입력

c = Company.objects.create(name="NAVER", phone_num="000-0000-0000", floor="100") Employee.objects.create(name="노예1", age=30, salary=1000, company=c) c = Company.objects.create(name="카카오", phone_num="111-1111-11111", floor="200") Employee.objects.create(name="무지1", age=10, salary=2000, company=c)

Select_releated

쿼리셋을 반환할 때 foreign-key, OneToOneFeild 관계인 모델들을 함께 가져오기 위한 함수 (inner join)

일반적인 all() 검색일시 ( employee의 str에 선언된 company 정보에 의해서 자동으로 Company를 가져옴 )

모든 조인에 대해 검색하여 n+1의 쿼리가 발생한다.

from polls.models import Company, Employee e = Employee.objects.all() print(e.query) ########################################################################### SELECT "polls_employee"."id", "polls_employee"."name", "polls_employee"."age", "polls_employee"."salary", "polls_employee"."company_id" FROM "polls_employee" ########################################################################### print(e) ########################################################################### , ]> ########################################################################### from django.db import connection connection.queries #################################. N + 1 ##########################################33 [{'sql': 'SELECT "polls_employee"."id", "polls_employee"."name", "polls_employee"."age", "polls_employee"."salary", "polls_employee"."company_id" FROM "polls_employee" LIMIT 21', 'time': '0.000'}, {'sql': 'SELECT "polls_company"."id", "polls_company"."name", "polls_company"."phone_num", "polls_company"."floor" FROM "polls_company" WHERE "polls_company"."id" = 1', 'time': '0.000'}, {'sql': 'SELECT "polls_company"."id", "polls_company"."name", "polls_company"."phone_num", "polls_company"."floor" FROM "polls_company" WHERE "polls_company"."id" = 2', 'time': '0.000'}]

select_relate() 설정시

쿼리가 inner join으로 변경되며, 쿼리가 하나가 되는 것을 볼 수 있다.

e = Employee.objects.all().select_related('company') print(e.query) # inner join 이 들어감 ########################################################################### SELECT "polls_employee"."id", "polls_employee"."name", "polls_employee"."age", "polls_employee"."salary", "polls_employee"."company_id", "polls_company"."id", "polls_company"."name", "polls_company"."phone_num", "polls_company"."floor" FROM "polls_employee" INNER JOIN "polls_company" ON ("polls_employee"."company_id" = "polls_company"."id") ########################################################################### print(e) ########################################################################### , ]> ########################################################################### ################################# 1 ##########################################33 [{'sql': 'SELECT "polls_employee"."id", "polls_employee"."name", "polls_employee"."age", "polls_employee"."salary", "polls_employee"."company_id", "polls_company"."id", "polls_company"."name", "polls_company"."phone_num", "polls_company"."floor" FROM "polls_employee" INNER JOIN "polls_company" ON ("polls_employee"."company_id" = "polls_company"."id") LIMIT 21', 'time': '0.000'}] ###########################################################################

Prefetch_related

Foreign-key, OneToOne + ManyToMany , ManyToOne 관계 모델 포함

선언된 모델별로 쿼리 실행 후 결과값을 반환

e = Employee.objects.all().prefetch_related('company') from django.db import connection connection.queries ########################################################################### [{'sql': 'SELECT "polls_employee"."id", "polls_employee"."name", "polls_employee"."age", "polls_employee"."salary", "polls_employee"."company_id" FROM "polls_employee" LIMIT 21', 'time': '0.000'}, {'sql': 'SELECT "polls_company"."id", "polls_company"."name", "polls_company"."phone_num", "polls_company"."floor" FROM "polls_company" WHERE "polls_company"."id" IN (1, 2)', 'time': '0.000'}] ###########################################################################

2. 모델과 쿼리 분할

모델에 함수로 정의가 가능하나, models.Manager 클래스 선언 후 objects에 할당하면 해당 클래스 함수를 실행 가능 (오버 라이딩~)

annotate_with_copies_sold() 쿼리 함수를 모델이 아닌 다른 매니저 클래스로 선언.

모델과 쿼리의 분할로 재사용 및 패턴화가 쉬워진다

class AuthorQuerySet(models.Manager): def annotate_with_copies_sold(self): queryset = Author.objects.annotate(copies_sold=Sum('books__copies_sold')) return queryset ###################################################################### class Author(models.Model): # Make sure this manager is available. first_name = models.CharField(max_length=30) last_name = models.CharField(max_length=30) objects = AuthorQuerySet() >>> Author.objects.annotate_with_copies_sold() Django Annotation/Aggregation class Product(models.Model): name = models.CharField(max_length=30) price = models.IntegerField(default=0) stock = models.IntegerField(default=0) class OrderLog(models.Model): product = models.ForeignKey('Product', on_delete=models.CASCADE) created = models.DateTimeField(u'판매일') from polls.models import Product, OrderLog p = Product.objects.create(name="bible", price=5000) OrderLog.objects.create(product=p, created="2020-02-01") OrderLog.objects.create(product=p, created="2020-02-01") OrderLog.objects.create(product=p, created="2020-02-03") OrderLog.objects.create(product=p, created="2020-02-03") p = Product.objects.create(name="what is the muscle", price=10000) OrderLog.objects.create(product=p, created="2020-02-02") OrderLog.objects.create(product=p, created="2020-02-03") OrderLog.objects.create(product=p, created="2020-02-04") p = Product.objects.create(name="headhunter", price=8888) OrderLog.objects.create(product=p, created="2020-02-01") OrderLog.objects.create(product=p, created="2020-02-02") OrderLog.objects.create(product=p, created="2020-02-05") OrderLog.objects.create(product=p, created="2020-02-05") OrderLog.objects.create(product=p, created="2020-02-06") OrderLog.objects.create(product=p, created="2020-02-06")

3. Annotation , Aggregation

Annotation == group by

Aggregation == 피봇(열)

아래의 엑셀표와 같이 판매내역 데이터 세팅되어 있을 경우 날짜별, 상품의 판매 액수 구하기

from django.db.models import F, Sum, Count, Case, When order_qs = OrderLog.objects.annotate( name=F('product__name'), price=F('product__price') ).values( 'created', 'name', 'price' ) ########################################################################### SELECT "polls_orderlog"."created", "polls_product"."name" AS "name", "polls_product"."price" AS "price" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id") ########################################################################### {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000} {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000} {'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888} ###########################################################################

총합계 (aggregate, sum)

order_qs.aggregate(total_price=Sum('price')) ########################################################################### [{'sql': 'SELECT SUM("price") FROM (SELECT "polls_orderlog"."created" AS Col1, "polls_product"."name" AS "name", "polls_product"."price" AS "price" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id")) subquery', 'time': '0.000'}] ########################################################################### {'total_price': 103328} ###########################################################################

일별 판매액 구하기 (values, annotate, sum)

daily_list = order_qs.values( 'created' ).annotate( daily_total=Sum('product__price') ) for data in daily_list: print(data) ########################################################################### SELECT "polls_orderlog"."created", SUM("polls_product"."price") AS "daily_total" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id") GROUP BY "polls_orderlog"."created" ########################################################################### ########################################################################### {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'daily_total': 18888} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'daily_total': 18888} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'daily_total': 20000} {'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=), 'daily_total': 10000} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'daily_total': 17776} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'daily_total': 17776} ###########################################################################

날짜별+제품별 판매 개수 구하기(values, annotate, count)

daily_count = order_qs.values( 'created', 'name' ).annotate( count=Count('name') ) for data in daily_count: print(data) ########################################################################### [{'sql': 'SELECT "polls_orderlog"."created", "polls_product"."name" AS "name", COUNT("polls_product"."name") AS "count" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id") GROUP BY "polls_orderlog"."created", "polls_product"."name"', 'time': '0.000'}] ########################################################################### {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'count': 2} {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'headhunter', 'count': 1} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'headhunter', 'count': 1} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'what is the muscle', 'count': 1} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'count': 2} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'what is the muscle', 'count': 1} {'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=), 'name': 'what is the muscle', 'count': 1} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'name': 'headhunter', 'count': 2} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'name': 'headhunter', 'count': 2} ###########################################################################

날짜별+제품별 판매 금액 구하기(values, annotate, sum)

특정 제품의 날짜별 판매 개수 구하기(filter)

bibla_daily_count = order_qs.filter( name='bible' ).values( 'created', 'name' ).annotate( count=Count('product') ) for data in bibla_daily_count: print(data) ########################################################################### {'sql': 'SELECT "polls_orderlog"."created", "polls_product"."name" AS "name", COUNT("polls_orderlog"."product_id") AS "count" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id") WHERE "polls_product"."name" = \'bible\' GROUP BY "polls_orderlog"."created", "polls_product"."name"', 'time': '0.000'} ########################################################################### {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'count': 2} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'count': 2} ###########################################################################

심화

모델 수정

class OrderLog(models.Model): product = models.ForeignKey('Product', on_delete=models.CASCADE) created = models.DateTimeField(u'판매일') is_cancel = models.BooleanField(u'결제 취소인지', default=False)

취소 데이터 추가

o = OrderLog.objects.last() OrderLog.objects.create(product=o.product, created="2020-02-07", is_cancel=True)

총 판매액

order_qs = OrderLog.objects.annotate( name=F('product__name'), price=F('product__price') ).values( 'created', 'name', 'price', 'is_cancel' ) order_qs.aggregate(total_price=Sum('price')) ########################################################################### {'sql': 'SELECT SUM("price") FROM (SELECT "polls_orderlog"."created" AS Col1, "polls_orderlog"."is_cancel" AS Col2, "polls_product"."name" AS "name", "polls_product"."price" AS "price" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id")) subquery', 'time': '0.000'} ########################################################################### {'total_price': 112216} ###########################################################################

취소 한건이 있기 때문에 기존과 같이 "103328" 이 나와야 한다!!

취소한 건은 빼야 한다.

order_qs.exclude( is_cancel=True ).aggregate( total_price=Sum('price') ) {'total_price': 103328}

판매 금액과 취소 금액을 같이 보여 줘야 할 때

########################################################################### {'sql': 'SELECT "polls_orderlog"."created", "polls_product"."name" AS "name", "polls_product"."price" AS "price", CASE WHEN "polls_orderlog"."is_cancel" = 0 THEN "polls_product"."price" ELSE 0 END AS "sales_price", CASE WHEN "polls_orderlog"."is_cancel" = 1 THEN "polls_product"."price" ELSE 0 END AS "cancel_price" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id")', 'time': '0.000'} ########################################################################### {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0} {'created': datetime.datetime(2020, 2, 7, 0, 0, tzinfo=), 'name': 'headhunter', 'price': 8888, 'sales_price': 0, 'cancel_price': 8888} ###########################################################################

판매금액 합계, 취소 금액 합계

order_list_2.aggregate(total_sell_price=Sum('sales_price'), total_cancel_price=Sum('cancel_price')) ########################################################################### {'sql': 'SELECT SUM("sales_price"), SUM("cancel_price") FROM (SELECT "polls_orderlog"."created" AS Col1, "polls_product"."name" AS "name", "polls_product"."price" AS "price", CASE WHEN "polls_orderlog"."is_cancel" = 0 THEN "polls_product"."price" ELSE 0 END AS "sales_price", CASE WHEN "polls_orderlog"."is_cancel" = 1 THEN "polls_product"."price" ELSE 0 END AS "cancel_price" FROM "polls_orderlog" INNER JOIN "polls_product" ON ("polls_orderlog"."product_id" = "polls_product"."id")) subquery', 'time': '0.000'} ########################################################################### {'total_sell_price': 103328, 'total_cancel_price': 8888} ###########################################################################

http://raccoonyy.github.io/django-annotate-and-aggregate-like-as-excel/

from http://uiandwe.tistory.com/1263 by ccl(A) rewrite - 2020-03-25 15:54:17