[SQLD] 자격시험 - 학습 보강사항 3

2024.1.3

## ROLLUP, GROUPING SETS, CUBE 함수 

• 공통점:  Group by에서 사용
• 사용 목적: 소계(합계)를 구하고자 할때 사용

1. Rollup

• 컬럼의 순서가 수행결과에 영향을 미침
• Rollup에 되어 있는 컬럼을 우측부터 하나씩 없애면서 그룹(소계, 합계)을 만드는 형태
  ㄴ 테이블 내에서 소계(row별 소계/중간합계)를 구할때 주로 사용
• 괄호가 묶여도 묶인데로 결과값이 나옴
• 컬럼이 반영된 곳만 Rollup함수가 영향을 미침

Group by ROLLUP(COL1, COL2, COL3)
--결과
group by 결과1: COL1, COL2, COL3
group by 결과2: COL1, COL2
group by 결과3: COL1
group by 결과4: () --전체합계

Group by ROLLUP(COL1, (COL2, COL3))
--결과
group by 결과1: COL1,(COL2,COL3)		--괄호가 묶이면, 묶인데로 수행
group by 결과2: COL1
group by 결과3: () --전체합계

Group by COL1, ROLLUP((COL2, COL3))		--괄호가 2개라서 1개의 변수로 인식
--결과
group by 결과1: COL1, (COL2, COL3)
group by 결과2: COL1

(연습1) 아래 표(지역별 월별 이용량)에 맞는 SQL 구문은?

문제를 보자마자 ROLLUP함수를 사용한걸 캐치 해야 됨

지역별 월별합계를 표시한 부분으로 ROLLUP함수를 이용한걸 체크해야 됨.

select (case grouping(B.지역ID) when 1 then '지역전체'
		else min(B.지역명) end ) 
        as 지역명, 
        (case grouping(to_char(A.이용일시, 'YYYY.MM'))
        	when 1 then '월별합계'
            else to_char(A.이용일시, 'YYYY.MM') end )
            as 이용월,
            sum(A.이용량) as 이용량
from 이용내역 A inner join 지역 B on(A.지역ID = B.지역ID)
group by ROLLUP(B.지역ID, to_char(A.이용일시, 'YYYY.MM'));

 

2. Grouping 함수

• 컬럼 순서에 영향 X
• ROLLUP, CUBE, GROUPING SET 함수랑 함께 쓰이며,
  GROUP by에서 쓰인 소계함수 결과, case에서 빠진 컬럼에 대해 1을 반환한다.(NULL값도 1을 반환)
  ㄴ 
• 결과값이 나온 후, 다음 결과값은 NULL로 반환 됨 --> 집계된 값 이외는 모두 NULL로 반환됨

GROUP BY ROLLUP(COL1, COL2, COL3)
--결과
group by 결과1: COL1, COL2, COL3
group by 결과2: COL1, COL2, NULL        -- COL3(마지막 값) null로 반환
group by 결과3: COL1, NULL, NULL		-- COL2,COL3을 NULL로 반환
group by 결과4: ()		--전체합계

(연습2) SQL실행 결과는?

select case when grouping(A.서비스ID) = 0 then A.서비스ID
	else '합계' end as 서비스ID,
       case when grouping(B.가입일자) = 0 then ISNULL(B.가입일자, '-')
       	else '소계' end as 가입일자,
       count(B.회원번호) as 가입건수
from 서비스 A left outer join 서비스가입 B
	on (A.서비스 ID = B.서비스ID)
    and B.가입일자 between '2013-01-01' and '2013-01-31'
group by ROLLUP(A.서비스ID, B.가입일자);		--ROLLUP은 우측부터 값을 줄이면서 호출 됨

결과: 

서비스ID	가입일자	가입건수
001	2013-01-01	3
001	소계	3
002	2013-01-02	3
002	소계	3
003	2013-01-03	2
003	소계	2
004	-	0
004	소계	0
합계	소계	8

 

3. Grouping Sets

• 컬럼 순서에 영향 X
• 원하는 컬럼만 지정해서 소계를 구함
• UNION ALL과 결과 동일.
  ㄴ 중복 값을 제거, 2개의 테이블을 연결해주는 역할.

GROUPING BY GROUPING SETS(COL1, COL2)
--결과
group by 결과1: COL1
group by 결과2: COL2

GROUPING BY GROUPONG SETS( COL1, COL2), COL3, () )	--() 전체합계가 들어가야 됨
--결과
group by 결과1: (COL1, COL2)
group by 결과2: COL2
group by 결과3: ()

(연습3) grouping sets 함수에 들어갈 컬럼을 작성하시오.

select case when grouping(자재번호) = 1 then '자재전체'
		else 자재번호 end as 자재번호,
        case when grouping(발주처ID) = 1 then '발주처전체'
        else 발주처ID end as 발주처ID,
        case when grouping(발주일자) = 1 then '발주일자전체'
        else 발주일자 end as 발주일자,
        sum(발주수량) as 발주수량합계
from 자재발주
group by grouping sets(자재번호, (발주처ID, 발주일자))		--발주처ID,발주일자 묶어야 됨
order by 자재번호, 발주처ID, 발주일자

 

4. Cube

• 컬럼 순서가 수행결과에 영향을 미치지 않음  --> 컬럼 순서에 영향 없음.
• 모든 경우의 컬럼을 모두 반환함 
  • 시스템 과부하가 올 수 있다.
• 전체합계 값을 반환함

GROUP BY CUBE(COL1, COL2)
--결과
group by 결과1: COL1, COL2
group by 결과2: COL1
group by 결과3: COL2
group by 결과4: ()		--전체합계

GROUP BY CUBE(COL1)
group by 결과1: COL1
group by 결과2:() 		-- 전체합계

(연습4) 설비, 에너지사용량 테이블로 나온 결과를 SQL로 작성하시오.

전체합계가 들어가면 CUBE 함수를 써야 됨

-- 1안(정답)  V
select A.설비ID, B.에너지코드, sum(B.사용량) as 사용량합계
from 설비 A INNER JOIN 에너지사용량 B ON (A.설비ID = B.설비ID)
group by CUBE(A.설비ID, B.에너지코드)	--전체합계도 들어갈땐 CUBE사용.
order by A.설비ID, B.에너지코드;

--2안(오답)
select A.설비ID, B.에너지코드, sum(B.사용량) as 사용량합계
from 설비 A INNER JOIN 에너지사용량 B on (A.설비ID = B.설비ID)
group by cube( (A.설비ID), (B.에너지코드), A.설비ID, B.에너지코드) -- 이런 형태의 CUBE함수는 사용하지 않음
order by A.설비ID, B.에너지코드;

--3안(정답) V
select A.설비ID, B.에너지코드, sum(B.사용량) as 사용량합계
from 설비 A INNER JOIN 에너지사용량 B ON (A.설비ID = B.설비ID)
group by GROUPING SETS( (A.설비ID),(B.에너지코드), (A.설비ID, B.에너지코드), () ) -- () 전체합계가 들어가야 맞음.
order by A.설비ID, B.에너지코드;

--4안(오답)  
select A.설비ID, B.에너지코드, sum(B.사용량) as 사용량합계
from 설비 A INNER JOIN 에너지사용량 B ON (A.설비ID = B.설비ID)
group by GROUPING SETS( (A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드) )	-- 전체합계가 없어서 오답
order by A.설비ID, B.에너지코드;

 

## ROLLUP, GROUPING, GROUPING SETS, CUBE에 대한 차이.. 

• 일반 그룹함수를 사용하여 CUBE, GROUPING SETS와 같은 그룹함수와 동일한 결과를 추출할 수 있으나, ROLLUP 그룹함수와 동일한 결과는 추출할 수 있다 --> UNION 이나 UNION ALL함수도 사용 가능 
    
• GROUPING SETS 함수의 경우, 함수의 인자로 주어진 컬럼의 순서에 따라 결과가 달라지므로 컬럼의 순서가 중요하지 않다. --> 컬럼에 순서에 영향을 미치는 함수는 ROLLUP임.
  
  
• CUBE, ROLLUP, GROUPING SETS 함수들의 대상 컬럼 중, 집계된 컬럼 이외의 대상 컬럼 값은 NULL로 셋팅됨. 
  
  
• CUBE 그룹함수는 인자로 주어진 컬럼의 결합 가능한 모든 조건에 대해서 집계를 수행하므로 다른 그룹함수에 비해 시스템에 대한 부하가 크다 --> 모든 컬럼을 계산하므로... 

 

##윈도우함수

• select 결과에 대하여 행과 행간의 관계를 파악.
• select 결과에 윈도우함수를 써도 행수는 그대로 임.
• from절 앞에 사용하며, 주로 select절에서 선언함.
• Group by절에서도 사용 가능함.
• Order by절에 집계함수( Count, SUM, MAX, MIN, AVG )를 사용해도 됨.

형태: 윈도우함수 () ORDER   partition by 컬럼 order by 컬럼 asc or desc ;

• 순위, 집계, 행의 순서, 비율 함수

순위 함수	ROW_NUMBER  (동점자 처리 X) 1,2,3,4,5,6,7,8,9... RANK                  (동정자 처리 후, 동점자 수 만큼 건너뜀) 1,2,2,4,5 ... DENSE_RANK   (동점자 처리 후, 그 다음 순위)   1,2,2,3,4,5...
집계 함수	COUNT SUM MAX MIN AVG
행의 순서 함수	LAG                     (이전 값) LEAD                   (다음 값) FIRST_VALUE    (가장 처음에 나온 값) LAST_VALUE     (가장 나중에 나온 값)
비율 함수	RATIO_TO_REPORT CUM_DIST NTILE PERCENT_RANK

• OVER: 윈도우 함수에서 꼭 들어가야 하며, OVER내부에 PARTITION BY절과 ORDER BY절이 온다.
• PARTITION BY : 전체집합을 어떤 기준(컬럼)에 따라 나눌지를 결정, 행을 그룹화 하는 형태
• ORDER BY : 어떤 항목(컬럼)을 기준으로 순위를 정할 지 결정하는 부분, 결과 값을 집계하는 형태(기존과 동일)

 

# 윈도우함수 추가사항

형태: 윈도우함수() OVER (Partition by 컬럼 Order by 컬럼 asc or desc  RANGE BETWEEN A AND B
                                                                                                                                        or
                                                                                                                      ROWS BETWEEN A AND B

DEFAULT 셋팅: RANGE BETWEEN Unbounded preceding AND current row   
                          ㄴ(해석) RANGE BETWEEN A AND B는 현재 컬럼 값 기준, 연산에 참여할 행을 선택하는 의미

-- Unbounded proceding : 최종 출력될 값의 맨 처음 row의 값, 숫자가 올 수 있음
-- current row: 현재 row의 값
-- Unbounded Following : 최종 출력 될 값의 맨 마지막 row의 값(AND 뒤에 옴)

 

 

# 윈도우함수, 정의에 대한 응용 이해

• partition과 group by 구문은 의미적으로 유사.
  
  
• partition 구문이 없으면, 전체 집합을 하나의 partition으로 정의한 것과 동일.
  ㄴ partition by없이 order by로 해도 동일함. 전체행이 집계 대상이 된다.
  
  
• 윈도우 함수 처리로 인해 결과 건수가 줄어 들지 않음.
  
  
• 윈도우 함수 적용 범위는 partition을 넘을 수 없다.

 

(예시1) 순위
형태: 윈도우함수() OVER  ( order by 컬럼 asc or desc )

(예시2) 집계
형태: 윈도우함수(집계컬럼) OVER ( partition by 컬럼 Order by컬럼 asc or desc )

(예시3) 

select A.고객번호 as A_고객번호,
	A.고객명 as A_고객명,
   	B.고객번호 as B_고객번호
   	B.매출액 as B_매출액
    RANK() OVER (ORDER BY 매출액 DESC) as 순위  		--순위함수 RANK()
    sum(B.매출액) OVER (partition by A.고객번호, A.고객명) as 누적합계  --집계함수 sum()
from 고객 A INNER JOIN 월별매출 B
	on(A.고객번호=B.고객번호)
order by A_고객번호;

 

(연습5)  게임상품ID별로 10등까지 선별하여 사은행사를 진행하려는데, SQL구문으로 적절한 것은?
             (단, 활동점수가 동일한 고객은 동일등수로 한다)

게임상품 ID	고객 ID	활동 점수	점수(순위)
001	121	150	1
001	111	150	1
001	234	110	3
001	212	100	4
001	455	100	4
001	182	90	6
001	199	80	7
001	109	70	8
001	876	70	8
001	232	40	10
001	901	40	10

점수(순위)를 보면 RANK 함수임을 눈치채야 된다.

select 게임상품ID, 고객ID, 활동점수, 순위
from (select RANK() OVER ( Partition by 게임상품 ORDER BY 활동점수 DESC ) as 순위
	, 고객ID
   	, 게임상품ID
    	, 활동점수
    	from 고객활동 )

 

(연습6) 추천내역 테이블에서 SQL 수행 결과는?

select 추천경로, 추천인, 피추천인, 추천점수
from (select 추천경로, 추천인, 피추천인, 추천점수,
		ROW_NUMBER() OVER (Partition by 추천경로 Order by 추천점수 desc) as 순위
        from 추천내역)
where 순위 = 1;

결과

ROW_NUMBER 순서