12강 집약
SQL에는 5개의 집약함수가 존재한다.
•
COUNT
•
SUM
•
AVG
•
MAX
•
MIN
‘집약’ 이라는 접두사가 붙은 이유는 여러 개의 레코드를 한개의 레코드를 집약하는 기능을 가지고 있기 때문이다.
1. 여러 개의 레코드를 한 개의 레코드로 집약
데이터 타입별로 data 값을 조회하려면
가장 기본적인 접근법은 WHERE 구문에 type 조건을 걸고 UNION으로 합치는 것이다.
SELECT id, data1, data2
FROM NonAggTbl
WHERE id = 'jim' AND type = 'A'
UNIONSELECT id, data3, data4, data5
FROM NonAggTbl
WHERE id = 'jim' AND type = 'B'
UNIONSELECT id, data6
FROM NonAggTbl
WHERE id = 'jim' AND type = 'C'
SQL
복사
UNION으로 여러 개의 쿼리를 머지하는것은 성능적으로 안티 패턴이다.
CASE 식과 GROUP BY 응용
SELECT id,
CASE WHEN tpye = 'A' THEN data1 ELSE NULL END AS data1,
CASE WHEN tpye = 'A' THEN data2 ELSE NULL END AS data2,
CASE WHEN tpye = 'B' THEN data3 ELSE NULL END AS data3,
CASE WHEN tpye = 'B' THEN data4 ELSE NULL END AS data4,
CASE WHEN tpye = 'B' THEN data5 ELSE NULL END AS data5,
CASE WHEN tpye = 'C' THEN data6 ELSE NULL END AS data6
FROM NonAggTbl
GROUP BY id;
SQL
복사
이 쿼리는 문법 오류가 발생한다.
GROUP BY 구로 집약했을때 SELECT 구에 입력할 수 있는 것은 다음과 같은 세 가지 뿐이다.
•
상수
•
GROUP BY 구에 사용한 집약키
•
집약 함수
따라서 아래와 같이 수정하면 된다.
SELECT id,
MAX(CASE WHEN tpye = 'A' THEN data1 ELSE NULL END) AS data1,
MAX(CASE WHEN tpye = 'A' THEN data2 ELSE NULL END) AS data2,
MAX(CASE WHEN tpye = 'B' THEN data3 ELSE NULL END) AS data3,
MAX(CASE WHEN tpye = 'B' THEN data4 ELSE NULL END) AS data4,
MAX(CASE WHEN tpye = 'B' THEN data5 ELSE NULL END) AS data5,
MAX(CASE WHEN tpye = 'C' THEN data6 ELSE NULL END) AS data6,
FROM NonAggTbl
GROUP BY id;
SQL
복사
집약, 해시, 정렬
HashAggregate (cost ~)
-> Seq Scan on nonaggtbl (~)
SQL
복사
•
NonAggTbl 모두 스캔하고 GROUP BY로 집약을 수행하는 단순한 실행 계획이다.
◦
중요한 점은 집약 조작에 해시 알고리즘을 사용하고 있다는 점이다. 집약할때 경우에 따라 정렬을 사용하기도 하지만 최근에는 정렬보다 해시를 사용하는 경우가 많다.
◦
GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해 해시키로 변환하고, 같은 해시키를 가진 그룹을 모아 집약하는 방법이다.
◦
해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.
•
TEMP 탈락
◦
하지만 정렬과 해시 모두 메모리를 많이 사용하므로, 충분한 해시용 워킹 메모리가 확보되지 않으면 스왑이 발생한다. 따라서 저장소 위의 파일이 사용되면서 굉장히 느려진다.
◦
예를 들어, 오라클에서 정렬 또는 해시를 위해 PGA라는 메모리 영역을 사용한다. 이때 PGA 크기가 집약 대상 데이터양에 비해 부족하면 일시 영역(저장소)을 사용해 부족한 만큼 채운다.
◦
위 현상을 TEMP 탈락이라 한다.
▪
이 현상이 발생하면 메모리만으로 처리가 끝나는 경우와 비교해 극단적으로 성능이 떨어지게 된다.
▪
메모리와 저장소(일반적으로 디스크)의 접근속도가 굉장히 차이나기 때문이다.
▪
PostgreSQL에서는 work_mem, Microsoft SQL Server에서는 Workspace Memory라는 메모리 영역이 정렬 또는 해시에 사용됩니다. 모두 메모리 영역이 부족해지면 부족한 만큼보충하고자 일시 영역(물리적으로는 저장소의 파일)을 사용
13강 자르기
GROUP BY구라는 것은 자르기 와 집약을 한꺼번에 수행하는 연산이다.
1. 자르기와 파티션
•
다음과 같이 개인 신체정보를 저장하고 있는 테이블이 있다고 생각합시다.
•
> 이 때, 모집합 Persons을 S1~S4의 부분 집합으로 나누고 각각의 부분 집합에
몇 명의 사람이 있는지 알아봅니다.
CREATE TABLE Persons
(name VARCHAR(8) NOT NULL,
age INTEGER NOT NULL,
height FLOAT NOT NULL,
weight FLOAT NOT NULL,
PRIMARY KEY(name));
SQL
복사
•
집합의 요소 수를 구할 때는 당연히 COUNT를 사용합니다.
◦
name 필드는 기본키이므로 NULL인 경우를 따로 생각할 필요가 없습니다.
(기본 키를 구성하는 필드는 NULL일 수 없습니다.)
◦
이어서 앞 글자를 GROUP BY 구의 키로 지정하면 자르기 완료입니다.
SELECT SUBSTRING(name, 1, 1) AS label,
COUNT(*)
FROM Persons
GROUP BY SUBSTRING(name, 1, 1);
SQL
복사
•
이렇게 GROUP BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로는
'파티션(partition)'이라고 부릅니다.
◦
파티션은 서로 중복되는 요소를 가지지 않는 부분집합입니다.
◦
같은 모집합이라도 파티션을 만드는 방법은 굉장히 많습니다.
◦
GROUP BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로는 ‘파티션’이라고 부른다.
▪
자르기의 기준이 되는 키를 GROUP BY 구와 SELECT 구 모두 입력하는것이 포인트다.
•
예를 들어, 나이를 기준으로 어린이(20세 미만), 성인(20세~69세), 노인(70세 이상)으로 나눈다면
•
다음과 같습니다.
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;
age_class | COUNT(*)
어린이 1
성인 6
노인 2
SQL
복사
◦
GROUP BY 구에서 CASE 식 또는 함수를 사용해도 실행 계획에는 영향이 없다.
◦
단순한 필드가 아니라 필드에 연산을 추가한 식이라면 CPU 연산에 오버헤드가 걸리겠지만, 데이터를 뽑아온 뒤의 이야기 이므로 데이터 접근 경로에는 영향을 주지 않는다.
◦
집약 함수와 GROUP BY 의 실행 계획은 성능적인 측면에서. 해시(또는 정렬)에 사용되는 워킹 메모리의 용량에 주의하라는 것 이외에 따로 할 말은 없다
•
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 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;
BMI | COUNT(*)
저체중 2
정상 4
과체중 3
SQL
복사
•
GROUP BY 구에는 필드 이름만 적을 수 있다고 생각하는 사람들이 많은데,
이렇게 복잡한 수식을 기준으로도 자를 수 있다는 것을 꼭 기억해야 합니다.
PARTITION BY 구를 이용한 자르기
•
GROUP BY 구에서 집약 기능을 제외하고 자르기 기능만 남긴 것이 PARTITION BY이다.
◦
원래 Persons 테이블의 레코드가 모두 원래 형태로 나오는 것을 주목
•
집약을 제외한 실질적인 기능에는 차이가 없다.
•
PARTITION BY 구는 GROUP BY 구와 달리 집약기능이 없으므로
◦
원래 Persons 테이블의 레코드가 모두 원래 형태로 나오는 것을 주목
•
GROUP BY는 입력 집합을 집약하므로 전혀 다른 레벨의 출력으로 변환하지만, PARTITION BY구는 입력에 정보를 추가할 뿐, 원본 테이블 정보를 완전히 그대로 유지합니다.