[SQL] 데이터의 그룹화 (GROUP BY)

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

열 이름으로 그룹화

---

CLASS_SCORE 테이블

이름	ENG	MATH	GENDER
김필호	28	49	남자
이치훈	68	98	남자
김영숙	87	87	여자
성은영	65	78	여자

CREATE TABLE CLASS_SCORE
(
NAME CHAR(20) NOT NULL,
ENG INT NOT NULL,
MATH INT NOT NULL,
GENDER CHAR(20) NOT NULL,
PRIMARY KEY('NAME')
);

INSERT INTO CLASS_SCORE
VALUES
('김필호', 28, 49, '남자'),
('이치훈', 68, 98, '남자'),
('김영숙', 87, 87, '여자'),
('성은영', 65, 78, '여자');

 

1) AVG 함수를 이용해서 영어점수(ENG)와 수학점수(MATH)의 평균을 구해 테이블을 요약하는 경우

SELECT AVG(ENG) AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE;

ENG_AVERAGE	MATH_AVERAGE
62.0	78.0

 

2) 성별에 따라 영어점수와 수학점수의 평균을 구해 분석하고자 하는 경우

SELECT GENDER,
       AVG(ENG) AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE
GROUP BY GENDER;

GENDER	ENG_AVERAGE	MATH_AVERAGE
남자	48.0	73.5
여자	76.0	82.5

• GROUP BY 를 사용하여 성별(Gender)로 평균값을 계산 및 출력

 

예제 

---

2013년 12월 고객별 보유상품 테이블: PPC_20131

• 열 설명 : 고객아이디, 고객등급, 카드상품보유여부, 대출상품, 보험상품, 수신상품, 펀드상품, 연 수익 

CREATE TABLE PPC_201312
(
CUST_ID INT NOT NULL,
SEG CHAR(20) NOT NULL,
CARD_FLG INT NOT NULL,
LOAN_FLG INT NOT NULL,
INSURANCE_FLG INT NOT NULL,
CTB_FLG INT NOT NULL,
FUND_FLG INT NOT NULL,
ANNL_REV INT NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO PPC_201312
VALUES
(54615, 'SILVER', 1, 1, 1, 1, 1, 1000),
(46780, 'GOLD',   0, 0, 1, 1, 0, 20000),
(23748, 'GOLD',   1, 1, 0, 1, 1, 30000),
(56432, 'DIAMOND',1, 0, 1, 1, 1, 100000),
(89647, 'SILVER', 0, 0, 1, 1, 0, 3000),
(52333, 'SILVER', 1, 1, 0, 1, 0, 2500),
(89669, 'GOLD',   1, 0, 1, 1, 0, 60000),
(21004, 'SILVER', 0, 0, 1, 1, 0, 1000),
(17890, 'DIAMOND',1, 1, 0, 1, 1, 300000);

 

Q1. 고객등급별 인당 평균 연 수익

SEG	AVG(ANNL_REV)
DIAMOND	200000
GOLD	36666.667
SILVER	1875

SELECT SEG, AVG(ANNL_REV) 
FROM PPC_201312
GROUP BY SEG;

 

Q2. 고객등급별 고객 수와 해당 등급별 카드 보유여부(CARD_FLG) 확인

SEG	CNT	SUM(CARD_FLG)
DIAMOND	2	2
GOLD	3	2
SILVER	4	2

SELECT SEG, COUNT(*) AS CNT,
       SUM(CARD_FLG)
FROM PPC_201312
GROUP BY SEG;

 

Q3. 카드상품과 대출상품 보유 간의 관계

CARD_FLG	LOAN_FLG	CNT
0	0	3
1	0	2
1	1	4

SELECT CARD_FLG, LOAN_FLG, COUNT(*) AS CNT
FROM PPC_201312
GROUP BY CARD_FLG, LOAN_FLG;

 

 

참고

• 원하는 열을 GROUP BY 절에 포함시킬 수 있음
  • 열이 한개씩 증가할수록 차원(Dimension)이 한개씩 증가하고 좀 더 세부적으로 데이터 분석 가능 (위 Q3. 참고)
• GROUP BY 절은 WHERE 조건 절 뒤, ORDER BY 절 앞에 위치

SELECT SEG, COUNT(*), AS CNT, SUM(CARD_FLG) AS CARD_FLG
FROM PPC_201312
GROUP BY SEG
ORDER BY SEG;