SQL 레벨업 4장 - 집약과 자르기

12강 집약

 
집약 함수
-COUNT
-SUM
-AVG
-MAX
-MIN
 
 
1. 여러 개의 레코드를 한 개의 레코드로 집약
   필드 수가 다를 때 UNION으로 하나의 쿼리로 집약하는 것은 불가능

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 = 'C' THEN data_5 ELSE NULL END AS data_5,
        CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END AS data_6
    FROM NonAggTnl
  GROUP BY id;

-> 안타깝게도 오류가 발생하는 쿼리
     GROUP BY 구로 집약했을 떄 SELECT 구에 입력할 수 있는 것은 1. 상수 2. GROUP BY 구에서 사용한 집약 키 3.집약함수

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 = 'C' 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 NonAggTnl
  GROUP BY id;

-> 모든 구현에서 작동하는 정답
 
-집약, 해시, 정렬
  최근에는 GROUP BY 를 사용하는 집약에서 정렬보다 해시를 사용하는 경우가 많음
   GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해 해시 키로 변환하고, 같은 해시 키를 가진 그룹을 모아 집약
   하는 방법
   but 해시 또는 정렬은 메모리를 많이 사용해 만약 메모리가 부족하면 일시 영역으로 저장소를 사용해 성능 문제를 일으킴
 
 
2. 합쳐서 하나

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

-> 여러 개의 레코드에서 운영된 날을 연산
     HAVING 절은 숙박한 날이 10일 이상인 방을 선택한 것
 
 

13강 자르기

1. 자르기와 파티션
 

이 이미지를 바탕으로 쿼리 작성

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

-> 사람들의 이름 앞 글자를 사용해 명단을 정리
    이름 첫 글자를 사용해 특정한 알파벳으로 시작하는 이름을 가진 사람이 몇 명인지 집계
 
 
-파티션
  GROUP BY구로 잘라 만든 하나하나의 부분 집합
  파티션을 서로 중복되는 요소를 가지지 않는 부분 집합
 
-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 구를 사용한 자르기

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;

PARTITON BY 구는 GROUP BY 구와 달리 집약 기능이 없으므로, 원래 Persons 테이블의 레코드가 모두 원래 형태로 나옴
GROUP BY 구는 입력 집합을 집약하므로 전혀 다른 레벨의 출력으로 변환하지만, PARTITON BY 구는 입력에 정보를 추가할 뿐이므로 원본 테이블 정보를 완전히 그대로 유지