SQLD 문제풀이 14 - 집계함수

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))
GROUP BY 결과 1 : COL1, (COL2, COL3)
GROUP BY 결과 2 : COL1

오른쪽부터 없어짐
컬럼의 순서가 수행 결과에 영향을 미침

 

GROUPING

ROLLUP, CUBE, GROUPING SETS 함수랑 함께 쓰이며, 
GROUP BY에서 쓰인 소계 함수 결과 CASE에서 빠진 컬럼에 대해 1을 반환한다.

GROUP BY ROLLUP(COL1, COL2, COL3)
GROUP BY 결과 1 : COL1, COL2, COL3
GROUP BY 결과 2 : COL1, COL2, 1
GROUP BY 결과 3 : COL1, 1, 1
GROUP BY 결과 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 : 전체합계


컬럼의 순서가 수행결과에 영향을 미치지 않음

 

GROUPING SETS

원하는 컬럼만 지정해서 소계를 구함
UNION ALL과 결과가 동일함

GROUP BY GROUPING SETS(COL1, COL2)
GROUP BY 1 : COL1
GROUP BY 2 : COL2

GROUP BY GROUPING SETS((COL1, COL2), COL2, ())
GROUP BY 1 : (COL1, COL2)
GROUP BY 2 : COL2
GROUP BY 3 : ()

 

1. 다음 중 아래의 테이블에서 sql을 실행할 때 결과로 가장 적절한 것은?

서비스

서비스id	서비스명
001	서비스1
002	서비스2
003	서비스3
004	서비스4

서비스가입

회원번호	서비스id	가입일자
1	001	2013-01-01
1	002	2013-01-02
2	001	2013-01-01
2	002	2013-01-02
2	003	2013-01-03
3	001	2013-01-01
3	002	2013-01-02
3	003	2013-01-03

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.가입일자)

서비스 a 
left outer join 서비스가입 b on (a.서비스id = b.서비스id)
and b.가입일자 between '2013-01-01' and '2013-01-31'

서비스id	서비스명	회원번호	서비스id	가입일자
001	서비스1	1	001	2013-01-01
001	서비스1	2	001	2013-01-01
002	서비스2	1	002	2013-01-02
002	서비스2	2	002	2013-01-02
002	서비스2	3	002	2013-01-02
003	서비스3	2	003	2013-01-03
003	서비스3	3	003	2013-01-03
004	서비스4	null	null	null

 

select a.서비스id, b.가입일자, 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 a.서비스id, b.가입일자

서비스id	가입일자	가입건수
001	2013-01-01	2
002	2013-01-02	3
003	2013-01-03	2
004	null	0

 

select a.서비스id, b.가입일자, 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.가입일자);

서비스id	가입일자	가입건수
001	2013-01-01	3
001	null	3
002	2013-01-02	3
002	null	3
003	2013-01-03	2
003	null	2
004	null	0
004	null	0
null	null	8

 select
a.서비스id,
b.가입일자,
count(b.회원번호) as 가입건수,
grouping(a.서비스id) as 그룹핑서비스id,
Grouping(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.가입일자);

서비스id	가입일자	가입건수	그룹핑서비스id	그룹핑가입일자
001	2013-01-01	3	0	0
001	null	3	0	1
002	2013-01-02	3	0	0
002	null	3	0	1
003	2013-01-03	2	0	0
003	null	2	0	1
004	null	0	0	0
004	null	0	0	1
null	null	8	1	1

 

결과

서비스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

 

2. 아래의 데이터 모델에서 SQL을 이용하여 표(지역별 월별 이용량)과 같은 형식의 데이터를 추출하려고 할 때 올바른 sql문장은?

지역명	이용월	이용량
서울	2014.01	1000
서울	2014.02	1000
서울	월별합계	2000
경기	2014.01	1000
경기	2014.03	2000
경기	월별합계	3000
대전	2014.05	1500
대전	2014.06	1000
대전	월별합계	2500
지역전체	월별합계	7500

서울 ▶︎ 경기 ▶︎ 대전 ▶︎ 전체 : ROLLUP

--1
select 
(case 
	grouping(b.지역id) when 1 then '지역전체'
	else 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'))

 

3. 아래의 결과를 얻기 위한 SQL 문에서 ㄱ에 들어갈 함수를 작성하시오.

select 구매고객, 구매월, count(*) "총 구매건", sum(구매금액) "총 구매액"
from 구매이력
group by [ㄱ] (구매고객, 구매월)

 

ROLLUP

 

4. 다음 설명 중 가장 적절한 것은?

 

1) 일반 그룹 함수를 사용하여 CUBE, GROUPING SETS와 같은 그룹 함수와 동일한 결과를 추출할 수 있으나, ROLLUP그룹함수와 동일한 결과는 추출할 수 없다.

▶︎ 일반 그룹 함수를 사용하여 union 이나 uion all을 사용하면 동일한 결과를 추출할 수 있다.

2) grouping sets 함수의 경우에는 함수의 인자로 주어진 컬럼의 순서에 따라 결과가 달라지므로 컬럼의 순서가 중요하다.

▶︎ rollup만 순서 중요

3) cube, rollup, grouping sets 함수들의 대상 컬럼 중 집계된 컬럼 이외의 대상 컬럼 값은 해당 컬럼의 데이터 중 가장 작은 값을 반환한다.

▶︎ null로 세팅이 된다

4) cube 그룹 함수는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해 시스템에 대한 부하가 크다.

 

5. 아래와 같이 설비와 에너지사용 테이블을 이용하여 결과를 나타내려 할 때 sql으로 가장 적절한 것을 2개 고르시오.

1 합계 , 2합계, 3합계, 총합, 바람합계, 용수합계, 전기합계 ▶︎ 모든 경우의수 : cube

select a.설비id,
	b.에너지코드,
    sum(b.사용량) as 사용량합계
from 실버 a inner join 에너지사용량 b on (a.실버id = b.실버id)
group by cube(a.설비id, b.에너지코드)
order by a.설비id, b.에너지코드;

GROUP BY CUBE(a.설비id, b.에너지코드)

GROUP BY 결과 1 : a.설비id, b.에너지코드
GROUP BY 결과 2 : a.설비id
GROUP BY 결과 3 : b.에너지코드
GROUP BY 결과 4 : 전체합계

 

grouping sets로도 표현 가능

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.에너지코드;

Grouping sets((a.설비id), (b.에너지코드), (a.설비id, b.에너지코드), ())

GROUP BY 1 : (a.설비id)
GROUP BY 2 : (b.에너지코드)
GROUP BY 3 : (a.설비id, b.에너지코드)
GROUP BY 4 : ()

 

6. 자재발주 테이블에 sql을 수행하여 아래와 같은 결과를 얻었다. 다음 ㄱ에 들어갈 문장으로 옳은것은?

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, 발주일자))
--
order by 자재번호, 발주자id, 발주일자

 

7. 다음 중 월별매출 테이블을 대성으로 아래 sql을 수행한 결과인것은?

상품id	월	매출액
p001	2014.10	1500
p001	2014.11	1500
p001	2014.12	2500
p002	2014.10	1000
p002	2014.11	2000
p002	2014.12	1500
p003	2014.10	2000
p003	2014.11	1000
p003	2014.12	1000

select 상품id, 월, sum(매출액) as 매출액
from 월별매출
where 월 between '2014.10' and '2014.12'
group by grouping sets((상품Id, 월));

정답은 동일