SQL을 이해하고 사용하는 Django ORM, 신동현 - PyCon Korea 2022 - 학습 정리

https://www.youtube.com/watch?v=J3pg-Ivl_MU

 

 - 우연하게 좋은 자료를 발견하게 되어 몇가지를 기록할겸 정리한다.

 

 1. sql 실행계획 확인 방법

import sqlparse

queryset = User.objects.all()
query = str(queryset.query)
print(sqlparse.format(query, reindent = True)

print(queryset.explain())

 

 2. aggregate() vs count()

   - 두 함수의 실행계획을 확인하면 sql문이 다른것을 확인할 수 있다.

      - Count('id')와 count(*)의 결과가 달라 질 수 있는 이유로 SQL에서 NULL은 0이 아니기 때문에 집계에서 제외된다.

      - 0은 데이터로 취급되지만 NULL은 데이터로 취급되지 않는다.

   - aggregate()

User.objects.aggregate(Count('id'))

SELECT COUNT(id) FROM User

   - count()

User.objects.count()

SELECT COUNT(*) FROM User

 

 3. 시간의 비교표현식 비교, DateTimeField 사용의 문제

   - range() 는 인클루시브(포괄)적으로 동작한다. 즉 조건의 시작과 끝 값이 결과 집합에 포함된다.

 

   - gte, lte 사용

User.objects.filter(
	joined_at__gte=date(2022,1,1)
    joined_at__lte=date(2022,12,31)
)

SELECT * FROM User WHERE joined_at >= '2022-01-01' AND joined_at <= '2022-12-31'

 

   - range()

User.objects.filter(
	joined_at__range=(
    	date(2022, 1, 1), date(2022, 12, 31)
    )
)

SELECT * FROM User Where joined_at BETWEEN '2022-01-01' AND '2022-12-31'

 

   - DateTimeField 

      - 인자로 date()로 전달했지만, 쿼리는 datetime()으로 변경된다.

      - datetime()을 사용해도 날자만 명시하고 시간을 명시하지 않으면 동일한 결과를 얻게 된다.

      - 따라서 날자 조회에서 end 조건에는 익스클루시브하게 쿼리하는 것이 안전하다.

User.objects.filter(
	joined_at__range=(
    	date(2022, 1, 1), date(2022, 12, 31)
    )
)

User.objects.filter(
	joined_at__range=(
    	datetime(2022, 1, 1), datetime(2022, 12, 31)
    )
)

 

      - 아래 쿼리는 12/31에 대한 데이터가 누락됨

SELECT * FROM User WHERE joined_at >= '2022-01-01 00:00:00' 
AND joined_at <= '2022-12-31 00:00:00'

 

      - 올바른 쿼리 조건

SELECT * FROM User WHERE joined_at >= '2022-01-01 00:00:00' 
AND joined_at <= '2023-01-01 00:00:00'

 

 4. 기본 sql - f()

from django.db.models import F

Book.objects.annotate(
	sale_prince=F('price') - F('discount')
)

SELECT id, title, price, discount, price - discount as sale_price 
FROM Book

 

 5. model property 사용

   - 탈퇴일이 없는 유저 가져오기

@property
def is_Active(self):
	#탈퇴일 없으면 활성 유저
    return bool(not self.unregistered_at)

>> active_users = [
	user for user in User.objets.all()
    if user.is_active
    ]

 

 6. sql 쿼리 순서 annotate()

   - annotate()로 지정된 컬럼은 select의 기존 컬럼보다 늦게 선언된다. 만약 선언하는 순서가 중요하다면 다음과 같이 정의하여 사용할 수 있다.

Book.objects
	.annotate(
		sale_price=F('price') - F('discount')
        )
        .values(
        	_sale_price=F('sale_price'),
            _price=F('price'),
            _discount=F('discount'),
        )

SELECT
	price - discount AS _sale_price,
    price AS _price,
    discount AS _discount
FROM Book

 

 7. group by 사용하기

   - values()와 annotate를 함께 사용하면 group by 동작을 기대할 수 있다.

   - select문에는 group by로 선언된 컬럼과 집계함수만 정의할 수 있다.

>> User.objects.values('type').annotate(count=Count('id'))
<QuerySet [{'type': 'A', 'count': 2}, {'type': 'B', 'count': 1}]>

SELECT type, COUNT(id) AS count, FROM User GROUP BY type

 

 8. index 

   - 다중 컬럼 index를 사용하는 경우 동치 조건과 비교표현식을 사용한다면 동치 조건을 쓰는 컬럼을 index의 선행 컬럼으로 사용해야 한다.

SELECT * FROM Order 
Where product_id = 1
AND ordered_at BETWEEN '2022-10-01' AND '2022-10-02'

class Order(models.Model):
	...
    class Meta:
    	indexes = [
        		models.Index(fields=['product', 'ordered_at']),
            ]

 

   - 데이터 조회시 index는 하나만 사용가능하다 만약 다중 컬럼 index를 사용한다면, 외래키의 경우 기본적으로 index를 사용하기 때문에 db_index를 사용해 기본 index 생성을 막아준다.

class Order(models.Model)
	product = models.ForeignKey(
    	'Product',
        on_delete=models.CASCADE,
        db_index=False
        )
        
        class Meta:
        	indexes = [
            	models.Index(fields=['product', 'ordered_at']),
                ]

 

   - index를 사용하지 않는 쿼리

User.objects.filter(name__contains = 'Guido')

SELECT * FROM User WHERE name LIKE '%Guido%'

User.objects.filter(joined_at__year=2022)

SELECT * FROM User WHERE YEAR(joined_at) = '2022'

 

   - index를 사용하는 쿼리

User.objects.filter(name__startwith = 'Guido')

SELECT * FROM User WHERE name LIKE 'Guido%'

# indexes = [models.Index(fields=['product', 'ordered_at'])]
queryset = (
	Order.objects.filter(product_id=1, ordered_at__gte='2022-01-01)
    .only('product', 'ordered_at')
)

 

   - or Q 사용

      - index를 사용하지 않는 쿼리

User.objects.filter(Q(id=1) | Q(type='A'))

SELECT * FROM USER
WHERE id = 1 OR type = 'A'

 

      - index를 사용하는 쿼리

User.objects.filter(id=1).union(User.objects.filter(type='A'))

( SELECT * FROM USER
WHERE id = 1)
UNION 
( SELECT * FROM USER 
WHERE type = 'A')

 

  9. CASE를 이용한 조건부 정렬

   - 해당 쿼리는 index 정렬을 활용할 수 없고 결과 레코드에 alias를 이용하여 추가적인 정렬을 수행하여야 한다.

cities = (
	City.objects.annotate(
    	search_order=Case(
        	when(name='Seoul', then=Value(1)),
            default=Value(0),
            output_field=IntegerField(),
            )
        )
        .order_by('-search_order')
    )
    
SELECT *, 
	CASE
    	WHEN name = 'Seoul' THEN 1
        ELSE 0
    END AS search_order
FROM
	City
ORDER BY
	search_order DESC

 

 10. 동적 annotation 사용

   - 날자별로 게시글에 달린 댓글수 확인

start = date(2022, 1, 1)
end = date(2022, 1, 1)

date_range = [
	start + timedelta(days=x) for x in range((end - start).days + 1)
]

posts = Post.objects.all()
for _date in date_range:
	posts = posts.annotate(
    	**{
        	str(_date): Coalesce(
            	Count('id', filter=Q(
                	comments__created_at__lt=_date + timedelta(days=1)
                )
                	), 0
                )
            }
        )

SELECT
*,
	COALESCE( COUNT(
    	CASE
        	WHEN created_at < '2022-01-02 00:00:00' THEN id
        ELSE
        NULL
    END
    	),
    0) AS '2022-01-01',
    COALESCE( COUNT(
    	CASE
        	WHEN created_at < '2022-01-03 00:00:00' THEN id
        ELSE
        NULL
    END
    	),
    0) AS '2022-01-02',
    ...
FROM
	Post
INNER JOIN
	Comment
ON
	Post.id = Comment.post_id
GROUP BY
	Post.id
ORDER BY
	NULL