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)

###########################################################################
<QuerySet [<Employee: 노예1 30 1000 NAVER 000-0000-0000 100>, <Employee: 무지1 10 2000 카카오 111-1111-11111 200>]>
###########################################################################

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)

###########################################################################
<QuerySet [<Employee: 노예1 30 1000 NAVER 000-0000-0000 100>, <Employee: 무지1 10 2000 카카오 111-1111-11111 200>]>
###########################################################################


################################# 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=<UTC>), 'name': 'bible', 'price': 5000}
{'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000}
{'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888}
{'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), '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=<UTC>), 'daily_total': 18888}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'daily_total': 18888}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'daily_total': 20000}
{'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=<UTC>), 'daily_total': 10000}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'daily_total': 17776}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), '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=<UTC>), 'name': 'bible', 'count': 2}
{'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'count': 1}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'count': 1}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'count': 1}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'count': 2}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'count': 1}
{'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'count': 1}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'count': 2}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), '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=<UTC>), 'name': 'bible', 'count': 2}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), '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=<UTC>), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'bible', 'price': 5000, 'sales_price': 5000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 3, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 4, 0, 0, tzinfo=<UTC>), 'name': 'what is the muscle', 'price': 10000, 'sales_price': 10000, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 5, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 6, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 1, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 2, 0, 0, tzinfo=<UTC>), 'name': 'headhunter', 'price': 8888, 'sales_price': 8888, 'cancel_price': 0}
{'created': datetime.datetime(2020, 2, 7, 0, 0, tzinfo=<UTC>), '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/