<SQL> DML -SELECT-(2)group by, having,window

그룹함수

WINDOW 함수

GROUP BY 절

HAVING 절

 

기본형태

SELECT [테이블명].속성명 [,그룹함수(속성명)] [,WINDOW함수] FROM 테이블명 WHERE 조건 GROUP BY 속성명 HAVING 

★그룹함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술함

함수
COUNT(속성명)	그룹별 튜플 수를 구함
SUM(속성명)	그룹별 합계
AVG(속성명)	그룹별 평균
MAX(속성명)	그룹별 최대값
MIN(속성명)	그룹별 최소값
STDDEV(속성명)	그룹별 표준편차
VARIANCE(속성명)	그룹별 분산
ROLLUP(속성명,속성명..)	*인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수 *속성의 개수가 N개이면 N+1 레벨까지, 하위 레벨에서 상위레벨 순으로 데이터가 집계됨
CUBE(속성명,속성명..)	*인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함 *속성의 재수가 N개이면 2^N레벨까지, 상위 레벨에서 하위 레벨 순으로 데이터가 집계됨

 

★WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성의 값을 집계할 함수를 기술함

-PARTITION BY : WINDOW 함수의 적용 범위가 될 속성을 지정함

-ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정함

[함수]

-ROW_NUMBER() : 각 레코드에 대한 일련번호 반환

-RANK() : 순위 반환, 공동 순위 반영

-DENSE_RANK() : 윈도우 별로 순위를 반환하며, 공동 순위를 무시하고 순위 부여

 

★GROUP BY 절 : 특정 속성을 기준을 그룹화하여 검색할 때 사용 일반적으로 GROUOP BY절은 그룹함수와 함께 사용된다.

 

★HAVING절: GROUP BY 와 함께 사용되며, 그룹에 대한 조건을 지정함

 

사용 예시

 

사용 테이블

(1)window 함수 이용 검색

 

▶prize 테이블에서 대회별로 상금에 대한 일련 번호를 구하시오

 

▶select test,money, ROW_NUMBER() OVER (PARTITION BY test ORDER BY money desc) from prize;

▶prize 테이블에서 대회별로 상금에 대한 순위를 구하시오

 

▶select test,money,rank() over(PARTITION by test order by money desc) from prize;

(2) 그룹 지정 검색

 

▶prize 테이블에서 역할 별 상금의 평균을 구하시오.

 

▶select part,avg(money) as AVERAGE from prize group by part;

▶prize 테이블에서 역할별 튜플 수 검색

 

▶select part,count(*) as 구성원 from prize group by part;

▶prize 테이블에서 'part','test','money'에 대해 part별 상금 소계와 전체 합계를 검색하시오

 

▶select part,test,sum(money) as total from prize group by rollup(part,test);

▶prize 테이블의 part, test,money에 대해 part별 test별 소계와 전체 합계를 검색하시오

 

▶select part,test,sum(money) as total from prize group by cube(part,test);

(3) 집합 연산자를 이용한 통합 질의

 

집합 연산자	설명	종류
UNION	*두 SELECT문의 조회 결과를 통합하여 출력 *중복된 행은 한번만 출력	합집합
UNION ALL	*두 SELECT문의 조회결과를 통합하여 출력 *중복행도 그대로 출력	합집합
INTERSECT	두 SELECT문의 공통행만 출력	교집합
EXCEPT	첫번째 SELECT문의 조회결과에서 두번째 SELECT문의 조회 결과를 제외한 행을 출력	차집합

 

사용예시

 

사용한 테이블

MEMBER

PEOPLE

▶<MEMBER> 테이블과 <PEOPLE> 테이블을 통합하는 질의문을 작성

▶SELECT * FROM MEMBER UNION SELECT * FROM PEOPLE;

 

▶<MEMBER>테이블과 <PEOPLE>테이블에 공통으로 존재하는 레코드만 통합하는 질의문 작성

▶SELECT * FROM MEMBER INTERSECT SELECT * FROM PEOPLE;