[DB] - 자르기 및 4장 정리(4장)

[Irisation23]

0. 자르기

앞선 #98 집약 파트에서 다뤘던 내용은 GROUP BY 구의 집약 기능에 대해 중점적으로 알아봤다.
하지만 GROUP BY구의 역할은 자르기와 집약 두개의 기능을 한다.

해당 파트는 자르기에 더 초점을 맞춰 정리한다.

1. 자르기와 파티션

아래와 같은 개인 신체정보 테이블이 있다.

• 개인 신체정보 (Persons)

첫 문자 알파벳마다 몇 명의 사람이 존재하는지 계산하는 쿼리 작성이 필요하다면 아래와 같다.

SELECT SUBSTRING(name, 1, 1) AS label,
       COUNT(*)
FROM persons
GROUP BY SUBSTRING(name, 1, 1);

1.1 파티션(partition)

이렇게 GROUP BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로는 '파티션(partition)' 이라 부른다.
파티션은 서로 중복되는 요소를 가지지 않는 부분 집합이다.

나이를 기준으로 파티션을 만들어 보자.

• 나이로 자르기

SELECT CASE WHEN age < 20 THEN '어린이'
WHEN age BETWEEN 20 AND 69 THEN '성인'
WHEN age >= 70 THEN '노인'
ELSE NULL END AS age_class,
    count(*)
FROM persons
GROUP BY CASE WHEN age < 20 THEN '어린이'
WHEN age BETWEEN 20 AND 69 THEN '성인'
WHEN age >= 70 THEN '노인'
ELSE NULL END;

• BMI 로 자르기
  • BMI = w / t^2

SELECT CASE WHEN weight / POWER(height /100, 2) < 18.5 THEN '저체중'
            WHEN 18.5 <= weight / POWER(height /100, 2)
                   AND weight / POWER(height /100, 2) < 25 THEN '정상'
            WHEN 25 <= weight / POWER(height /100, 2) THEN '과체중'
            ELSE NULL END AS bmi,
            COUNT(*)
　FROM Persons
 GROUP BY CASE WHEN weight / POWER(height /100, 2) < 18.5 THEN '저체중'
               WHEN 18.5 <= weight / POWER(height /100, 2)
                   AND weight / POWER(height /100, 2) < 25 THEN '정상'
               WHEN 25 <= weight / POWER(height /100, 2) THEN '과체중'
               ELSE NULL END;

2. PARTITION BY 구를 사용해 자르기

2장에서 GROUP BY 구에서 집약 기능을 제외하고 자르는 기능만 남긴 것이 윈도우 함수의 PARTITION BY 구라 설명했다.
집약 기능을 제외한다면 GROUP BY 구와 PARTITION BY 구의 실질적인 기능에는 차이가 없다.

한마디로 PARTITION BY 구를 사용해도 단순한 필드 이름뿐만 아니라 CASE 식, 계산 식을 사용한 복잡한 기준을 사용할 수 있다.

• PARTITION BY에 식을 지정

SELECT name,
       age,
       CASE
           WHEN age < 20 THEN '어린이'
           WHEN age BETWEEN 20 AND 69 THEN '성인'
           WHEN age >= 70 THEN '노인'
           ELSE NULL END AS age_class,
       rank() OVER (PARTITION BY CASE
                                     WHEN age < 20 THEN '어린이'
                                     WHEN age BETWEEN 20 AND 69 THEN '성인'
                                     WHEN age >= 70 THEN '노인'
                                     ELSE NULL END
           ORDER BY age
           )             AS age_rank_in_class
FROM persons
ORDER BY age_class, age_rank_in_class;

age_rank_in_class가 각 파티션 내부에서의 나이 순위를 나타내는 필드이다.
PARTITION BY 구는 GROUP BY 구와 달리 집약 기능이 없으므로, 원래 Persons 테이블의 레코드가 모두 원래 형태로 나오는 것을 주목해야한다.

다르게 말하면, GROUP BY 구는 입력 집합을 집약하므로 전혀 다른 레벨의 출력을 이끌어 낸다.
PARTITION BY 구는 입력에 정보를 추가할 뿐이므로 원본 테이블 정보를 그대로 유지한다.

4장 마치며

• GROUP BY 구 또는 윈도우 함수의 PARTITION BY 구는 집합을 자를 때 사용
• GROUP BY 구 또는 윈도우 함수는 내부적으로 해시 또는 정렬 처리를 실행
• 해시 또는 정렬은 메모리를 많이 사용해 만약 메모리가 부족하면 일시 영역으로 저장소를 사용해 성능 문제를 일으킴
• GROUP BY 구 또는 윈도우 함수와 CASE 식을 함께 사용하면 다양한 것을 표현할 수 있음