[SQL] 집약

SQL에는 집약 함수(aggeregate function)라고 합니다.

 

COUNT

SUM

AVG

MAX

MIN

 

너무 기초이므로 다 다루지는 않겠습니다.

 

NonAggTbl 테이블 생성

id	data_type	data_1	data_2	data_3	data_4	data_5	data_6
Jim	A	100	10	34	346	54
Jim	B	45	2	167	77	90	157
Jim	C		3	687	1355	324	457
Ken	A	78	5	724	357		1
Ken	B	123	12	178	346	85	235
Ken	C	45		23	46	687	33
Beth	A	75	0	190	25	356
Beth	B	435	0	183		4	325
Beth	C	96	128		0	0	12

 

데이터 타입 'A'의 레코드에 대한 쿼리

SELECT id, data_1, data_2
FROM NonAggTbl
WHERE id = 'Jim'
	AND data_type = 'A';

 

데이터 타입 'B'의 레코드에 대한 쿼리

SELECT id, data_3, data_4, data_5
FROM NonAggTbl
WHERE id = 'Jim'
	AND data_type = 'B';

 

데이터 타입 'C'의 레코드에 대한 쿼리

SELECT id, data_6
FROM NonAggTbl
WHERE id = 'Jim'
	AND data_type = 'C';

 

CASE 식과 GROUP BY응용

 

 

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 = 'B' 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 NonAggTbl
    GROUP BY id
    ORDER BY id DESC;

 

실행계획

 

집약에 대한 다른 문제 1

 

PriceByAge

reserve_id	low_age	high_age	price
제품1	0	50	2000
제품1	51	100	3000
제품2	0	100	4200
제품3	0	20	500
제품3	31	70	800
제품3	71	100	1000
제품4	0	99	8900

 

제품 범위가 모든 연령 범위인것만 고르기

 

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

 

 

집약에 대한 다른 문제 2

 

사람들이 숙박한 날이 10일 이상인 방 조회

 

HotelRooms 테이블

room_nbr	start_date	end_date
101	2020-02-01	2020-02-06
101	2020-02-06	2020-02-08
101	2020-02-10	2020-02-13
202	2020-02-06	2020-02-08
202	2020-02-08	2020-02-11
202	2020-02-11	2020-02-12
303	2020-02-03	2020-02-17

 

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