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
댓글
댓글 쓰기