Django orm query | filter, annotate, aggregate

Jihun Kim·2021년 12월 24일
ORMdjangoquery
1

Django

목록 보기
4/8
post-thumbnail

filter

조건 사용시 “{컬럼명}__{조건}”의 형태(언더바 두개!)를 사용한다.

exact

  • 정확히 일치하는 조건 입력시 사용
  • “iexact”는 대소문자를 가리지 않고 일치하는 조건을 찾는 데 사용
Posts.objects.filter(title__exact="jihun")
Posts.objects.filter(title__iexact="jihun")

contains

  • 부분적으로 일치하는 조건을 입력할 때 사용
  • ‘like’와 같은 기능
Posts.objects.filter(title__contains="jihun")
Posts.objects.filter(title__icontains="jihun")

in

  • 여러 조건 포함 여부 확인
Posts.objects.filter(title__in=["tomato", "apple"])

gt/ lt/ gte/ lte: 대소 비교

  • gt: >
  • lt: <
  • gte: ≥
  • lte: ≤
Posts.objects.filter(created_at__gt=datetime.date(2021.12.27)
Posts.objects.filter(created_at__lt=datetime.date(2021.12.27)

startswith/ endswith

  • startswith: 조건으로 시작하는 문자열 검색
  • endswith: 조건으로 끝나는 문자열 검색
Posts.objects.filter(title__startswith="what")

union

  • 서로 다른 모델에서 구한 쿼리셋들을 합침(합집합)
data1 = Posts.objects.filter(title__exact="jihun")
data2 = Posts.objects.filter(title__exact="Kim")
data = union(data1, data2)

intersection

  • 서로 다른 모델에서 구한 쿼리셋들의 교집합
data1 = Schools.objects.filter(school__in=["your", "school"])
data2 = Schools.objects.filter(year__in=["1학년", "2학년"])
data3 = intersection(data1, data2)


annotate

annotate는 장고의 쿼리 표현식 중 하나이며,
1. SELECT 대상의 필드 명을 변경하고
2. values 메소드와 함께 사용하여 group by, having 절을 구현 한다.

필드 이름 변경

가령, 아래의 두 모델이 fk로 연결되어 있다고 하자.

class School(models.Model):
	name = models.CharField('이름', max_length=50)
	city = models.CharField('도시', max_length=50)

class Student(models.Model):
	school = models.ForeignKey('School')
	name = models.CharField('이름', max_length=10)
	year = models.IntegerField('학년')
	student_id = models.IntegerField('학생번호')

쿼리셋을 불러올 때 아래와 같은 방법으로 fk에 있는 컬럼도 함께 불러올 수 있다.

  • {클래스명 소문자}__{컬럼명}
students = Student.objects.values('name', 'school__name', 'school__city')
-> Student에 대해 fk로 연결된 School의 컬럼을 불러올 수 있다.

이 때, 쿼리셋 결과는 아래와 같이 언더바가 그대로 등장한 형태이다.

{'school__name': '땡땡고등학교', 'school__city': '서울시', name: '김땡땡'}, ...

이런 형태는 보기 좋지도 않고, 사용하기도 어려우므로 annotate를 써서 고칠 수 있다.

from django.db import F

students = Student.objects.annotate(
                      school_name=F('school__name'),
                      city=F('school__city')
		).values(
                          'school__name', 
                          'school__city', 
                          'name'	
                         )

결과는 아래와 같다.

{'school_name': '땡땡고등학교', 'city': '서울시', 'name': '김땡땡'}, ...

기준으로 값 묶기

예시 1)

아래의 SQL 쿼리가 있다고 하자.

  • HAVING 절은 집계함수를 가지고 조건 비교를 할 때 사용하며, GROUP BY 절과 함께 사용된다.
SELECT year, class, avg(score) as average 
FROM student_grade 
WHERE year=2021 
GROUP BY HAVING COUNT(student_id) >= 30;

이를 orm으로 나타내면 다음과 같다.

StudentGrade.objects
	.filter(year=2021)
	.values("class")
	.filter(student_count__gte=30)
	.values("score_average")

  • values는 group by의 대상이 되는 컬럼이다.

  • annotate는 집계함수로 계산할 열을 지정한다.

    → “지정할 열의 이름 = 집계 함수(집계될 열의 이름)”


예시 2)

가령, 아래와 같은 두 개의 객체가 있을 때, 

class Product(models.Model):
    name  = models.CharField('이름', max_length=150)
    price = models.IntegerField('가격')

class Order(models.Model):
    product = models.ForeignKey('Product')
    created = models.DateTimeField() # 판매일

1. 총 판매액은 어떻게 구할까?

일단 order_qs을 구한다.

  order_qs = OrderLog.objects.annotate(
      name=F('product__name'), 
      price=F('product__price')
  ).values(
      'created', 'name', 'price'
  )

order_qs을 하나씩 출력해 보면 다음과 같다.
이는 엑셀에서 행 별로 하나씩 출력한 것과 같은 결과이다.

{'price': 9900, 'created': datetime.datetime(2016, 4, 1, 0, 0), 'name': 'ABC Activity'}
{'price': 8200, 'created': datetime.datetime(2016, 4, 1, 0, 0), 'name': '동물동요'}

# (중략)

{'price': 9900, 'created': datetime.datetime(2016, 4, 3, 0, 0), 'name': 'ABC Activity'}

2. 날짜별 판매량을 알고 싶다면 아래와 같이 하면 된다.

Django에서 뭔가를 기준으로 값들을 묶고 싶다면 values와 annotate를 사용하면 된다. 이 때 values는 기준이 되며 annotate는 연산을 행해서 보여질 대상이 된다.

일단 order_qs을 만들고(위에서 만든 것과 동일) 이를 ‘created’로 묶어서 price를 더하면 된다.

order_qs은 다음과 같다.

  order_qs = OrderLog.objects.annotate(
      name=F('product__name'), 
      price=F('product__price')
  ).values(
      'created', 'name', 'price'
  )

그럼 이제 날짜별 판매량을 구해보자.
여기서 ".values("created")는 날짜별로 묶으라는 의미이다.


daily_list = order_qs.values(
	'created'
).annotate(daily_total=Sum('product__price'))

이 때, 'order_qs'에는 product__price가 이제 사용되지 않지만 order_qs에서 annotate로 변경된 컬럼명(price)은 그대로 참조할 수가 없다.

따라서 'product__price'를 사용해야 한다.

결과는 다음과 같이 날짜와 해당 날짜의 총 판매 금액으로만 구성되어 있다.

{'created': datetime.datetime(2021, 4, 1, 0, 0), 'daily_total': 1000000}, 
{'created': datetime.datetime(2021, 4, 2, 0, 0), 'daily_total': 1500000}, 
...


컬럼 개수 세기

Student의 ‘name’ 컬럼의 데이터 개수를 확인하고자 한다면 다음과 같이 한다.

n = Student.objects.annotate(Count('name'))

그럼 결과는 아래와 같이 나온다.

<QuerySet [<Student: Student object (1)>, <Student: Student object (2)>, ...>

이 때 0번째 학생의 이름을 알고 싶다면 아래와 같이 한다.

n[0].name


aggregate

aggregate은 쿼리셋의 특정 필드 전체에 연산을 적용할 때 사용한다.
-> 즉, 필드 전체의 합이나 평균, 개수 등을 계산할 때 주로 사용한다.

아래는 공식 문서의 예시 코드이다.

>>> 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')}
  • aggregate()을 이용하면 구하고자 하는 값에 대해 ‘name’-’value’의 dictionary 형태로 그 결과를 반환한다.
  • 위의 예시는 이름을 직접 지정할 수도 있다.
Book.objects.aggregate(average_price=Avg('price'), max_price=, Max('price'), min_price=Min('price'))

이렇게 하면 ‘__’가 사라져서 조금 더 보기 좋은 형태로 만들 수 있다.



심화된 형태의 aggregate, annotate 예시

가령, 고객이 결제 후 '취소한 금액'이 있다고 해 보자.
그러면 'OrderLog'에 is_canceled 필드가 추가될 것이다.
따라서 만약 결제 취소한 경우 'is_canceled'는 True가 된다.
그렇다면 이 때, "총 판매액"은 어떻게 계산해야 할까?

총 판매액 = 총 결제 금액 - 취소한 금액

으로 계산해야 한다.

엑셀에서라면
1. 판매 금액 컬럼과 취소 금액 컬럼을 추가하고, 결제 취소 필드의 내용에 따라 각 컬럼에 가격을 넣은 다음,
2. 판매 금액의 합에서 취소 금액의 합을 빼주면 된다.

이를 'annotate'에서 'CASE-WHEN' 구문으로 해결할 수 있다.

# order_qs는 위에서 사용한 것과 동일하다.
 order_qs = OrderLog.objects.annotate(
      name=F('product__name'), 
      price=F('product__price')
  ).values(
      'created', 'name', 'price'
  )

order_list_2 = order_qs.annotate(
    sales_price=Case(
        When(
            is_cancel=False,  # 결제 취소가 아닌 경우
            then=F('price')
        ),
        default=0
    ),
    cancel_price=Case(
        When(
            is_cancel=True,   # 결제 취소인 경우
            then=F('price')
        ),
        default=0
    )
)

이렇게 하면 결제 취소일 때의 sales_price, cancel_price 열이 추가된 형태가 된다. 아래와 같이!
마치 엑셀에서 'cancel_price' 열을 추가했을 때와 같은 결과를 볼 수 있다.

>>> for data in order_list_2:
...     print(data)
...
{'cancel_price': 0, 'name': 'ABC Activity', 'sales_price': 9900, 'price': 9900, 'created': datetime.datetime(2016, 4, 1, 0, 0)}
{'cancel_price': 0, 'name': '동물동요', 'sales_price': 8200, 'price': 8200, 'created': datetime.datetime(2016, 4, 1, 0, 0)}

# (중략)

{'cancel_price': 9900, 'name': 'ABC Activity', 'sales_price': 0, 'price': 9900, 'created': datetime.datetime(2016, 4, 3, 0, 0)}

그러면 이제 'price' 의 합 - 'cancel_price'의 합을 구하면 총 판매액을 알 수 있다.

쿼리셋의 특정 필드 전체에 연산을 적용하라 때는 "aggregate"를 이용하면 된다.

total_sales = order_list_2.aggregate(
    total_price=Sum('sales_price')-Sum('cancel_price')
)

그러면 결과는 다음과 같다.

>>> total_sales
{'total_price': 252300}


다시 한 번 정리해 보면,

  1. 애너테이션(annotate)은 엑셀에서 계산용 컬럼을 하나 추가하는 것과 같다.
  2. 애그리게이션(aggregate)은 엑셀에서 특정 컬럼 전체를 대상으로 계산하는 것과 같다. (합, 평균, 개수 등)


참고

https://velog.io/@may_soouu/장고-Annotate-Aggregate
https://blog.live2skull.kr/django/django-orm-03-group/

profile
Jihun Kim
쿄쿄
이전 포스트

Django | class형 view

다음 포스트

Django models | proxy

0개의 댓글