4장 집약과 자르기

12. 집약

- SQL의 특징적인 사고방식 중에, 레코드 단위가 아닌 레코드의 '집합' 단위로 처리를 기술하는 것이다.

    ㆍ이런 사용 방식을 집합 지향(set-oriented)라고 불린다.

- 이러한 특징이 가장 잘 드러나는 때가 GROUP BY 구, HAVING 구와, 그것과 함께 사용하는 SUM 또는 COUNT 등의 집약 함수를 사용하는 때이다.

- SQL은 이러한 집합 조작이 굉장히 잘 구현되어 있어서, 절차 지향 언어에서 반복문 또는 분기를 여러 번 사용해야 하는 복잡한 처리를 굉장히 간단한 코드로 작성할 수 있다.

 

- SQL에는 집합 함수(aggregate function)가 있다.

    ㆍCOUNT

    ㆍSUM

    ㆍAVG

    ㆍMAX

    ㆍMIN

- 함수들의 이름 앞에 '집약'이라는 접두사가 붙은 이유는 문자 그대로 여러 개의 레코드를 한 개의 레코드로 집약하는 기능을 가지고 있기 때문이다.

 

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

- 쿼리의 결과가 필드 수가 다르다면 UNION 으로 하나의 쿼리로 집약하는 것이 불가능하다.

- UNION으로 여러 개의 쿼리르 머지하는 것은 성능적으로 안티 패턴이다.

 

CASE 식과 GROUP BY 응용

- 안타깝게도 오류가 발생하는 쿼리

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' TEHN data_1 ELSE NULL END) AS data_1,
        MAX(CASE WHEN data_type = 'A' TEHN data_2 ELSE NULL END) AS data_2,
        MAX(CASE WHEN data_type = 'B' TEHN data_3 ELSE NULL END) AS data_3,
        MAX(CASE WHEN data_type = 'B' TEHN data_4 ELSE NULL END) AS data_4,
        MAX(CASE WHEN data_type = 'B' TEHN data_5 ELSE NULL END) AS data_5,
        MAX(CASE WHEN data_type = 'C' TEHN data_6 ELSE NULL END) AS data_6,
    FROM NonAggTbl
GROUP BY id;

- 여기서 집약 함수가 적용되면 NULL을 제외하고 하나의 요소만 있는 집합이 만들어진다.

    ㆍ여기에 MAX 함수를 사용하면 내부에 있는 하나의 요소를 선택할 수 있다.

 

집약, 해시, 정렬

- 실행계획은 간단하다.

    ㆍ테이블을 모두 스캔하고 GROUP BY로 집약을 수행하는 단순한 실행 계획이다.

- 주목해야 하는 부분은 GROUP BY의 집약 조작에 모두 '해시'라는 알고리즘을 사용하고 있다.

    ㆍ경우에 따라서 정렬을 사용하기도 한다 (SORT GROUP BY)

- 최근에는 GROUP BY를 사용하는 집약에서 정렬보다 해시를 사용하는 경우가 많다.

    ㆍ이는 GROUP BY 구 에 지정되어 있는 필드를 해시 함수를 사용해 해시키로 변환하고,

        같은 해시 키를 가진 그룹을 모아 집약하는 방법이다.

- 고전적인 정렬을 사용하는 방법보다 빠르므로 많이 사용한다.

- 특히 해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.

 

- 정렬과 해시 모두 메모리를 많이 사용하므로, 충분한 해시용(또는 정렬용) 워킹 메모리가 확보되지 않으면 스왑이 발생한다.

    ㆍ저장소 위의 파일을 사용되면서 굉장히 느려진다.

- 정렬 또는 해시를 위해 메모리 영역을 사용하는데 집약 대상 데이터양에 비해 부족하면, 일시 영역(저장소)를 사용해 부족한 만큼 채운다.

    ㆍ이것이 TEMP 탈락이라고 부르는 현상이다.

- TEMP 탈락이 발생하면 메모리만으로 처리가 끝나는 경우와 비교해서 극단적으로 성능이 떨어진다.

- 최악의 경우 TEMP 영역을 모두 써버려 SQL 구문이 비정상적으로 종료되는 경우가 발생할 수도 있다.

 

2. 합쳐서 하나

CREATE TABLE PriceByAge
( product_id VARCHAR(32) NOT NULL,
  low_age    INTEGER NOT NULL,
  high_age   INTEGER NOT NULL,
  price      INTEGER NOT NULL,
  PRIMARY KEY (product_id, low_age),
    CHECK (low_age < high_age));


SELECT prodect_id
    FROM PriceByAge
    GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;

- HAVING 구의 'high_age - low_age + 1' 로 각 레코드의 연령 범위에 있는 정수 개수를 구한다.

 

CREATE TABLE HotelRooms
( room_nbr    INTEGER,
  start_date  DATE,
  end_date    DATE,
      PRIMARY KEY(room_nbr, start_date));
      
SELECT room_nbr,
       SUM(end_date - start_date) AS working_days
   FROM HotelRooms
   GROUP BY room_nbr
HAVING SUM(end_date - start_date) >= 10;

 

---

13. 자르기

- GROUP BY 구는 집약 이외에도 한 가지 중요한 기능이 더 있다.

    ㆍ바로 '자르기' 라는 기능이다.

    ㆍ이는 원래 모집합인 테이블을 작은 부분 집합들로 분리하는 것이다.

- GROUP BY 구라는 것은 자르기, 집약 기능을 한꺼번에 수행하는 연산이다.

 

1. 자르기와 파티션

CREATE TABLE Persons
( name    VARCHAR(8) NOT NULL,
  age     INTEGER NOT NULL,
  height  FLOAT NOT NULL,
  weight  FLOAT NOT NULL,
      PRIMARY KEY (name));

# 첫 문자 알파벳마다 몇 명의 사람이 존재하는지 계산      
SELECT SUBSTRING(name, 1, 1) AS label,
        COUNT(*)
    FROM Persons
GROUP BY SUBSTRING(name, 1, 1);

 

파티션

-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;

-자르기의 기준이 되는 키를 GROUP BY 구와 SELECT 구 모두에 입력하는 것이 포인트이다.

- PostgreSQL과 MySQL에서는 SELECT 구 붙인 'age_class' 라는 별칭을 사용해 'GROUP BY age_class' 처럼 단순하게 작성할 수 있는데, 이런 방법은 표준에 없는 내용이므로 주의바란다.

 

- GROUP BY 구에서 CASE 식 또는 함수를 사용해도 실행 계획에는 영향이 없다는 것을 알 수 있다.

- 단순한 필드가 아니라 필드에 연산을 추가한 식을 GROUP BY 구의 키로 한다면 어느 정도 CPU 연산에 오버 헤드가 걸릴 것이다.

    ㆍ하지만 이는 데이터를 뽑아온 뒤의 이야기므로 데이터 접근 경로에는 영향을 주지 않는다.

 

- 사실 집약 함수와 GROUP BY 의 실행 계획은 성능적인 측면에서, 해시(또는 정렬)에 사용되는 워킹 메모리의 용량에 주의해야 한다.

# BMI로 자르기
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 구를 사용한 자르기

- 집약이라는 기능을 제외하면 GROUP BY 구와 PARTITION BY 구의 실질적인 기능에는 차이가 없다.

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

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(PATITION 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 구는 GROUP BY 구와 달리 집약 기능이 없으므로, 원래 Persons 테이블의 레코드가 모두 원래 형태로 나온다.

 

---

마치며..

- GROUP BY 구 또는 윈도우 함수의 PARTITION BY 구는 집합을 자를 때 사용

- GROUP BY 구 또는 윈도우 함수는 내부적으로 해시 또는 정렬 처리를 실행

- 해시 또는 정렬은 메모리를 많이 사용해 만약 메모리가 부족하면 일시 영역으로 저장소를 사용해 성능 문제를 일으킴

- GROUP BY 구 또는 윈도우 함수와 CASE 식을 함께 사용하면 굉장히 다양한 것을 표현할 수 있다.