Transcript 12장. 요약과 집계

요약과 집계
학습 목표
각종 요약과 집계 기법을 이해한다.
TOP N과 각종 집계 함수를 이해한다.
구식 요약 방법인 COMPUTE와 COPUTE BY 절을 이해한다.
GROUP BY 절, HAVING 절, ROLLUP 및 CUBE 연산자, GROUP 함수
등을 이해한다.
요약과 집계
1. 집계 함수
2. GROUP BY 절
3. COMPUTE와 COMPUTE BY 절
4. ROLLUP와 CUBE 절
요약과 집계
집계 함수 >> 집계함수 COUNT
● COUNT 함수는 행의 개수를 돌려준다.
● 구문
COUNT ( { * | [ ALL | DISTINCT ] expression } ) )
● 예제
SELECT count(*)
FROM titles
SELECT count(title_id)
FROM titles
요약과 집계
집계 함수 >> 집계함수 COUNT
● 수치 데이터에 NULL이 있는 경우
SELECT count(price)
FROM titles
● ISNULL
ISNULL( 표현식, null일 떄의 값)
SELECT count(ISNULL(price,0))
FROM titles
요약과 집계
집계 함수 >> 집계 함수 SUM과 AVG
● SUM과 AVG 함수는 열 값들의 합계 및 평균 값을 돌려준다.
● 구문:
SUM ( [ ALL | DISTINCT ] expression )
AVG ( [ ALL | DISTINCT ] expression )
요약과 집계
집계 함수 >> 집계 함수 MIN과 MAX
● MIN과 MAX 함수는 열 값들 중의 최소 값 또는 최대 값을 돌려준다.
● 구문
MIN ( [ ALL | DISTINCT ] expression )
MAX ( [ ALL | DISTINCT ] expression )
요약과 집계
집계 함수 >> 집계 함수 기타 집계 함수
● 지금까지 살펴본 것 외에도 BINARY_CHECKSUM, CHECKSUM,
CHECKSUM_AGG, COUNT_BIG, GROUPING, STDEV, STDEVP, VAR,
VARP 등의 집계 함수가 있다.
● COUNT_BIG은 COUNT 함수와 같으며 단지 반환하는 데이터 형이 bigint
형이라는 점만 다르다(약 21억 개가 넘는 행 수를 셀 때 사용한다).
요약과 집계
종합문제
● 다음의 테이블을 생성하시오
테이블명: 성적
일련번호
학년
이름
국어
영어
수학
1
1
홍길동
80
90
80
2
1
장동건
70
50
70
3
1
원빈
60
40
55
4
2
이병헌
60
90
50
5
2
이영애
90
80
40
6
3
차승원
80
90
50
7
3
전도연
70
50
90
● 3학년 학생의 인원수를 출력하시오
● 1학년 중에 수학 성적이 가장 높은 학생의 점수를 출력하시오
● 1학년 학생들의 영어 성적의 총점과 평균을 출력하시오
요약과 집계
GROUP BY 절 >> GROUP BY 절
● GROUP BY 절은 특정 열(들)을 그룹화하고 집계 및 요약하기 위해 SELECT
문에 포함하여 사용한다.
● GROUP BY 절의 개념도
● 구문(부분):
GROUP BY [ ALL ] group_by_expression [ ,...n ]
요약과 집계
GROUP BY 절 >> GROUP BY 절
● 예제
SELECT title_id, sum(qty) AS ‘sum of qty’
FROM sales
GROUP BY title_id
요약과 집계
GROUP BY 절 >> HAVING 절
● HAVING 절은 GROUP BY 절에 의해 만들어지는 행들을 제약하는 데 사용한
다. 이것은 WHERE 절이 SELECT 문에 의해 만들어지는 행을 제약하는 것
과 비슷한 관계다.
● 구문:
HAVING < search_condition >
● 예제
SELECT title_id, sum(qty) AS ‘sum of qty’
FROM sales
GROUP BY title_id
HAVING sum(qty) >= 30
요약과 집계
GROUP BY 절 >> HAVING 절
● 예제
SELECT title_id, sum(qty) AS ‘sum of qty’
FROM sales
WHERE qty > 20
GROUP BY title_id
HAVING sum(qty) >= 30
요약과 집계
COMPUTE와 COMPUTE BY 절 >> COMPUTE 절
● COMPUTE 절은 SELECT 문에 포함되어, 결과 집합 뒤에 단일 값으로 전체
행에 대한 요약 결과 집합을 만들어준다.
● 구문(부분):
{ COMPUTE aggregate_function( expression ) } [ , ...n ]
● 예제
SELECT avg(price)
FROM titles
SELECT type, title_id, price
FROM titles
COMPUTE avg(price)
요약과 집계
COMPUTE와 COMPUTE BY 절 >> COMPUTE 절
● COMPUTE BY 절은 SELECT 문에 포함되어, 그룹화 대상 열 값이 바뀔 때마다 단일
값으로 중간 요약 결과 집합을 만들어준다.
● 구문(부분):
ORDER BY column_list1
{ COMPUTE aggregate_function( expression ) BY column_list2 } [ , ...n ]
● 예제
SELECT type, avg(price)
FROM titles
GROUP BY type
SELECT type, title_id, price
COMPUTE BY 절을 사용시에 반드시 ORDER
BY 절이 앞에 와야하고 항목의 순서가
FROM titles
일치해야함(부분 항목도 가능)
ORDER BY type
COMPUTE avg(price) BY type
요약과 집계
COMPUTE와 COMPUTE BY 절 >> COMPUTE 절
● 실행 가능한 구문
SELECT type, pub_id, title_id, price
FROM titles
ORDER BY type, pub_id, title_id
COMPUTE avg(price) BY type, pub_id
● 실행 불가능한 구문
SELECT type, pub_id, title_id, price
FROM titles
ORDER BY type, pub_id, title_id
COMPUTE avg(price) BY pub_id, type
SELECT type, pub_id, title_id, price
FROM titles
ORDER BY type, pub_id, title_id
COMPUTE avg(price) BY title_id
요약과 집계
GROUP BY 절 >> ROLLUP 연산자
● ROLLUP 연산자는 GROUP BY 절의 일부로 사용되어, 집계 값을 요약하는
데 사용한다. ROLLUP 연산자는 COMPUTE와 COMPUTE BY 절과 비슷한
요약 결과를 얻으면서도 결과 집합을 하나의 테이블 형태로 돌려준다는 장
점이 있다.
● ROLLUP 연산자의 개념도
● 구문(부분):
GROUP BY group_by_expression [ , ...10 ]
WITH { ROLLUP | CUBE }
요약과 집계
GROUP BY 절 >> ROLLUP 연산자
● 예제
SELECT type, pub_id, AVG(price)
FROM titles
GROUP BY type, pub_id
SELECT type, pub_id, AVG(price)
FROM titles
GROUP BY type, pub_id
WITH ROLLUP
요약과 집계
GROUP BY 절 >> CUBE 연산자
● CUBE 연산자는 ROLLUP 연산자와 비슷하지만 더 완전하게 요약해 준다.
● CUBE 연산자의 개념도
● 구문: ROLLUP 연산자의 구문 참조
요약과 집계
GROUP BY 절 >> CUBE 연산자
● 예제
SELECT type, pub_id, AVG(price)
FROM titles
GROUP BY type, pub_id
WITH CUBE
요약과 집계
종합문제
● 앞에서 만든 ‘성적’ 테이블에 ‘총점’이라는 컬럼을 삽입하고 각 학생들의 총점
을 계산해서 저장하라.
● 각 학년별 1등인 학생의 학년과 점수를 출력하라.
● 학년별 1등인 학생 중에 250점 이상인 데이터만 출력하라.
● 모든 학생의 이름과 총점을 출력하고 학년별 총점의 평균을 출력하여라.
● 각 학년을 진급시키고 졸업자는 삭제하여라.