postgres aggregate filter 에 대해 알아봅시다.

 특정 조건을 만족하는 레코드에 대해서 집계되게 하게 하는 것은 어렵지 않습니다. 그런데, 이런 경우가 있을 수 있습니다. 1월에 카데고리별로 몇 개나 쓰여졌고, 2월에는 몇 개나 쓰여졌고, 이러한 것들을 집계해야 할 경우가 있을 텐데요. 사실 case when 등으로 처리를 할 수 있겠습니다만 postgres에서는..

 

 aggregate filter를 처음 알게 되어서 여기에 쓰고자 합니다.

 

---

 

 먼저, 포스트의 데이터들입니다. 포스트에는 점수와 post 내용, id와 category가 있습니다.

 

 

 이들 중 post에 bye가 포함되는 것들을 모두 얻어와 봅시다.

 

 

 1번 카데고리에 1개, 3번 카데고리에 2개가 있습니다. 그리고 1번 카데고리의 총 점수는 18점, 3번 카데고리는 87점입니다.

 

 

 다음에 gahui가 들어가는 포스트를 모두 조회해 볼게요.

 

 

 1번 카데고리에 2개, 2번 카데고리에 1개가 있습니다. 1번 카데고리에 속한 포스트의 총점은 53점이고, 2번 카데고리는 32점이 되겠습니다. 자. 그러면, 이걸 가지고 무엇을 할 것인가? 저는 post에 bye가 들어가는 것들의 카데고리별 평균, 그리고 gahui가 들어가는 것들의 카데고리별 평균을 구하려고 합니다.

 

---

 생각만 해도 복잡해 보이는데요. aggregate 뒤에 이어지는 filter 절을 이용하면 그리 복잡하지 않습니다. 단, 일부 DBMS에서만 (예를 들자면 postgres) 지원되는 것은 아쉽긴 합니다. 공식 문서를 보시다 보면 if filter is specified 라는 문구가 있습니다. 이를 그대로 해석하면, filter 절 뒤에 나오는 절에 대해서 true로 평가되는 무언가에 대해서만 집계 함수에 제공한다고 되어 있어요. 이걸 그림으로 도식화를 시키면 아래와 같은데요.

 

 

 aggregate 함수, 예를 들면 count라던지 avg와 같은 함수를 말합니다. 입력 행들 중에, filter를 만족하지 않는 것들은 모두 걸러지게 됩니다. 예를 들자면, 카데고리 1을 봅시다.

 

 카데고리 1에는 점수가 20, 18, 33인 포스트가 있어요. 만약에 gahui가 들어가는 것만 filter가 되게 하면 어떨까요? gahui가 포함된 것은 2개밖에 없습니다. id가 1인 것과 6인 것입니다. 이 둘만 들어가고, id가 2인 것은 버려지게 됩니다.

 

 

 그림으로 그리면 이런 상황인 셈입니다.

 

 

 이제 위의 쿼리를 이해할 수 있을 겁니다. 카데고리, 평균 점수, 평균 점수 이렇게 3개를 뽑는데요. 하나는 gahui가 들어간 포스트의 카데고리별 평균 점수를 뽑고, 다른 하나는 bye가 들어간 포스트의 카데고리별 평균 점수를 뽑습니다.

 

 

 1번 카데고리의 gahui 열이 26.5임에 주목하세요. 33과 20의 평균은 26.5인데, 이는 gahui가 포함되었는가? 라는 필터에 통과한 데이터들의 평균과 일치합니다.

 

 이제, where, having을 같이 섞어 봅시다. 위 쿼리의 결과는 어떻게 나올까요? 뭔가 복잡해 보이는데요. from, where 먼저 해석해 봅시다.

 

 그러면 id가 3 이하인 것만 가져오는 것인가요? 이제, category별로 그룹핑을 해야 하는데요. 그룹핑을 하면 아래와 같이 그림이 그려질 겁니다.

 

 

 이제 having에 의해서, count가 1인 Category 2가 제거됩니다. 즉, 카데고리 2 그룹이 탈락합니다. 그러면, 카데고리 1에 대해서만 집계가 작동하는데요.

 

 

 avg(score) filter (where post like '%gahui%')를 봅시다. filter 절에 있는 것이 post가 gahui 라는 키워드를 포함하느냐이므로, aggregate function에는 gahui가 포함된 것만 들어가게 됩니다. 그러면 1번만 들어갈 겁니다.

 

 

 따라서 1번 카데고리에 gahui가 들어가는 포스트의 평균은 20이 됩니다. 마찬가지 생각하면, bye가 들어가는 포스트의 평균은 18이 됨을 알 수 있습니다.