집약과 자르기

집약

집약 함수

COUNT
SUM
AVG
MAX
MIN
여러 개의 레코드를 한 개의 레코드로 집약

한 사람과 관련된 정보가 여러 개의 레코드에 분산되어 있는 비집약 테이블을 집약 테이블로 만들 경우 다음과 같은 쿼리를 생각해볼 수 있음

SELECT id,
     CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END AS data_1
     CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END AS data_2
     CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END AS data_3
     CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END AS data_4
     CASE WHEN data_type = 'B' THEN data_5 ELSE NULL END AS data_5
     CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END AS data_6
FROM NonAggTbl
GROUP BY id

그러나 위의 쿼리는 오류가 발생함
GROUP BY 구로 집약했을 때 SELECT 구에는 아래의 세 가지만 입력할 수 있음
- 상수
- GROUP BY 구에서 사용한 집약 키
- 집약 함수

아래와 같이 집약 함수를 사용하여 작성해야 함

SELECT id,
     MAX(CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END) AS data_1
     MAX(CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END AS data_2)
     MAX(CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END AS data_3)
     MAX(CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END AS data_4)
     MAX(CASE WHEN data_type = 'B' THEN data_5 ELSE NULL END AS data_5)
     MAX(CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END AS data_6)
FROM NonAggTbl
GROUP BY id

집약, 해시, 정렬
- GROUP BY에서는 정렬 또는 해시를 사용
- 정렬과 해시 모두 메모리를 많이 사용하므로 충분한 워킹 메모리가 확보되지 않으면 스왑이 발생하여 성능 저하 (TEMP 탈락)
  합쳐서 하나
문제 1. 연령대 별로 가격이 다른 제품 중 0 ~ 100세가 이용 가능한 제품 찾기
- 상수를 합해 101인 제품 선택
```
SELECT product_id
FROM PriceByAge
GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101
```

문제 2. 호텔 방마다 도착일과 출발일을 기록한 테이블을 보고 숙박한 날이 10일 이상인 방 찾기

SELECT room_nbr,
     SUM(end_data - start_date) AS working_days
FROM HotelRooms
GROUP BY room_nbr
HAVING SUM(end_date - start_date) >= 10

자르기

자르기와 파티션

GROUP BY 구는 집약 이외에도 자르기 기능을 가짐
- 모집합인 테이블을 더 작은 부분집합으로 분리
문제 1. 이름 첫 글자를 사용해 특정한 알파벳으로 시작하는 이름을 가진 사람이 몇 명인지 집계
```
SELECT SUBSTRING(name, 1, 1) AS label,
     COUNT(*)
FROM persons
GROUP BY SUBSTRING(name, 1, 1)
```
GROUP BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로 파티션(partition)이라고 함

문제 2. 나이 기준으로 자르기

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;

GROUP BY 구에 CASE 식 또는 함수를 사용해도 실행 계획에 영향은 없음
그러나 단순한 필드가 아닌 연산을 추가한 식을 GROUP BY 구의 키로 할 경우 CPU 연산에 오버 헤드
PARTITION BY 구를 사용한 자르기

문제 1. 연령 등급 내에서 어린 순서로 순위 매기기

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)

집약과 자르기

집약

집약 함수

여러 개의 레코드를 한 개의 레코드로 집약

합쳐서 하나

자르기

자르기와 파티션

PARTITION BY 구를 사용한 자르기