[SQL 레벨업] 12강 집약

12강 | 집약

정리

1. 집약 함수
   • COUNT
   • SUM
   • AVG
   • MAX
   • MIN
2. 여러 개의 레코드를 한 개의 레코드로 집약
   • CASE 식과 GROUP BY를 응용한다
     • CASE를 이용해 해당 레코드에 필요한 필드면 데이터를 반환 아니면 NULL을 반환
3. 집약,해시,정렬
   • 집약시 경우에 따라 정렬 또는 해시를 사용한다.
   • 최근 Group By를 사용하는 집약에서 정렬보다 해시를 사용하는 경우가 많다
   • 해시 이용 : GROUP BY에서 지정한 필드를 해시함수로 해시 키로 변환한 후, 동일한 해시 키를 가진 그룹끼리 집약한다.
   • 정렬과 해시는 메모리를 만히 사용하므로, 충분한 해시용(정렬용) 워킹 메모리가 확보되지 않으면 스왑 발생

---

예제문제 1

data_type 별로 필요한 데이터 값을 구해야 한다. 이때 data_type이 A일때는 data_1과 data_2 값만 필요하며, B일때는 data_3과 data_4 값만이 필요하다

• NonAggTbl 테이블

 

• 테이블 생성 및 데이터 추가

//테이블 생성
CREATE TABLE NonAggTbl (
    id VARCHAR(50),
    data_type VARCHAR(1),
    data_1 INT,
    data_2 INT,
    data_3 INT,
    data_4 INT
);

//데이터 추가
INSERT INTO NonAggTbl (id, data_type, data_1, data_2, data_3, data_4) VALUES 
    ('Jim', 'A', 100, 10, 34, 346),
    ('Jim', 'B', 45, 2, 167, 77),
    ('Ken', 'A', 78, 5, 724, 457),
    ('Ken', 'B', 123, 12, 178, 346);

 

 

• 집약을 이용한 문제 해결

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
FROM NonAggTbl
GROUP BY id;

 

 

• 결과

 

 

 

• 실행 계획

“Using temporary”는 임시 테이블을 사용한다는 의미로, 정렬 또는 그룹화 수행에 사용한다.

 

 

 

 

 

 

예제문제 2

아래 테이블은 제품 대상 연령별 가격을 관리하는 테이블이다. 이때 모든 연령이 이용할 수 있는 제품을 찾아야 한다.

• PriceByAge

 

 

• 테이블 생성 및 데이터 추가

CREATE TABLE PriceByAge (
    product_id VARCHAR(50),
    low_age INT,
    high_age INT,
    price INT
);

INSERT INTO PriceByAge (product_id, low_age, high_age, price) VALUES 
    ('제품1', 0, 50, 2000),
    ('제품1', 51, 100, 3000),
    ('제품3', 0, 20, 500),
    ('제품3', 31, 70, 800),
    ('제품3', 71, 100, 1000);

 

 

• 집약을 이용한 해결

각 대상 연령의 상한과 하한의 격차 합을 구했을 때, 100이 나와야 전 연령을 포함시킨 제품을 찾을 수 있다.

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

 

• 결과

제품3은 21부터 30까지의 연령을 포함하지 않음으로 제품1만 나온다.

 

 

 

---

아직 집약을 능숙하게 다루지 못하는 것 같다.. 절차지향적으로 생각하지 말고 구문을 식으로 생각하는 연습을 더 해야할 것 같다. (11강은 구문 기반에서 식 기반으로 생각하는 것이 중요하다는 내용이었습니다 ! 정리할 큰 내용이 없어서 스킵했습니다 ~)