SQL 레벨업 - 4장

4장 집약과 자르기

12강 집약

집약함수(aggregate function) : 여러 개의 레코드를 한 개의 레코드로 집약하는 기능

 ex) COUNT , SUM, AVG, MAX, MIN

 

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

한 사람과 관련된 정보가 여러 개의 레코드에 분산되어 있는 테이블은, 한 사람의 정보에 접근할 때 'WHERE id = 'jim''과 같은 SELECT 구문을 사용할 때 당연히 3개의 레코드가 선택. 하지만 한 개의 레코드로 얻는 것이 편함

SELECT ID, DATA_1, DATA_2
	FROM NonAggTbl
WHERE ID = 'JIM'
	AND DATA_TYPE = 'A';
    
SELECT ID, DATA_3, DATA_4
	FROM NonAggTbl
WHERE ID = 'JIM'
	AND DATA_TYPE = 'B';
    
SELECT ID, DATA_5, DATA_6
	FROM NonAggTbl
WHERE ID = 'JIM'
	AND DATA_TYPE = 'C';

.이러한 쿼리는 필드 수가 달라서 UNION으로 하나의 쿼리로 만들기 힘듦

 

- CASE 식과 GROUP BY 응용

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 = 'B' THEN DATA_2 ELSE NULL END) AS DATA_2,
    MAX(CASE WHEN DATA_TYPE = 'C' THEN DATA_3 ELSE NULL END) AS DATA_3,
FROM NonAggTbl
GROUP BY ID;

집약 함수가 적용되면 null을 제외하고 하나의 요소만 있는 집합이 만들어지고, MAX 함수를 사용하면 내부에 있는 하나의 요소를 선택할 수 있음. 결과를 따로 준비한 AggTbl에 INSERT 하면 구하고자 하는 레이아웃을 가진 테이블이 만들어짐

 

- 집약, 해시,정렬

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

 

13강 자르기

GROUP BY 구 : 집약 + 자르기

1.자르기 파티션

- 파티션 : GROUP BY 구로 잘라 만든 하나하나의 부분집합 (서로 중복되는 요소를 가지지 않는 집합)

SELECT CASE WHEN age < 20 WHEN '어린이'
			WHEN age BETWWEEN 20 AND 69 THEN '성인'
			WHEN age >= 70 THEN '노인',
		COUNT(*)
FROM PPERSON
GROUP BY CASE WHEN age < 20 WHEN '어린이'
			WHEN age BETWWEEN 20 AND 69 THEN '성인'
			WHEN age >= 70 THEN '노인'
			ELSE NULL END;

-> GROUP BY구에서 CASE식,함수를 사용해도 실행 계획에 영향이 없음

2.PARTITION BY 구를 이용한 자르기

- PARTITION BY : GROUP BY 구에서 집약 기능을 제외하고 자르기 기능만 남긴 것 ( 집약을 제외한 실질적인 기능에는 차이가 없음)

 

EX) BMI

- BMI는 weight/power(height/100,2) 로 구할 수 있음

- BMI 를 CASE식으로 구분해 분류 후 GROUP BY 구와 SELECT 구에 모두 적음

 

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