GROUP BY

GROUP BY

2023. 10. 26. 14:52ㆍDatabase/Oracle

GROUP BY 구문은 특정 컬럼을 기준으로 집계를 나타내기 위해 사용된다.

GROUP BY 구문은 NULL값인 경우는 해당 ROW를 제외한 결과를 출력하고, WHERE절이 수행된 이후 실행된다

GROUP BY 구문에서 SELECT 뒤에 나오는 컬럼들은 GROUP BY 뒤에 모두 동일하게 와야만 한다.

윈도우 함수와 같이 사용할 수 없다

HAVING 구문은 GROUP BY구문에 대한 조건(SELECT구문에서 WHERE절 같은 성격)을 걸기 위해 사용된다.

GROUP BY구분에서는 집계함수와 그룹함수를 사용할수 있다.

SELECT * FROM EMP
ORDER BY DEPTNO;

위와 같은 테이블의 결과를 보면

DEPTNO가 동일하게 겹치는 부분이 있다.

이렇게 공통되는 부분을 모아 결과를 보여줄수 있는게 GROUP BY절이다.

기본적으로 공통되는 부분이 있는 컬럼을 기준으로 GROUP BY절을 사용하면 되는데

위에서 말했듯이 GROUP BY를 사용한다면 집계함수를 제외한 SELECT 뒤에 오는 컬럼들이 GROUP BY뒤에 동일하게 존재해야만 한다는 것이다.

SELECT DEPTNO FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

GROUP BY뒤에 오는 컬럼들이 SELECT 뒤에 오는 컬럼과 같지 않다면 오류를 발생한다

SELECT * FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

그러나 GROUP BY절에 있는 컬럼이 SELECT 뒤에 동일하게 존재해야하진 않아도 된다.

SELECT DEPTNO FROM EMP
GROUP BY DEPTNO, JOB  
ORDER BY DEPTNO;

그런데 공통된 부분에 추가적으로 공통되는 부분이 없는 다른 컬럼을 GROUP BY에 넣는다면 어떤 결과를 보여줄까?

EMP테이블에는 DEPTNO, JOB를 제외한 모든 부분이 사실 공통되는 부분을 갖고 있지 않는다.

그 중 이름(ENAME)을 추가해서 GROUP BY를 돌려 결과를 보자.

SELECT DEPTNO, ENAME FROM EMP
GROUP BY DEPTNO, ENAME 
ORDER BY DEPTNO;

오류는 발생하지 않으나 사실상 GROUP BY를 한 이유가 없어졌다고 느껴졌다.

집계하려는 기준이 어떤것인지를 판단하고 그 기준이 될 컬럼을 정하고 사용해야 할 것으로 생각된다.

GROUP BY절에선 SELECT 뒤에 컬럼이 GROUP BY에 모두 와야하는게 맞으나 집계함수는 GROUP BY절에 동일하게 작성할 필요가 없다.

그렇다면 집계함수는 무엇이고 어떤것이 있을까?

집계함수

집계 함수는 데이터베이스 테이블의 열(칼럼)에 대해 계산을 수행하고, 결과를 단일 값으로 반환하는 함수이다. 이러한 함수들은 주로 GROUP BY 절과 함께 사용되어 그룹화된 데이터의 집계 작업을 수행하거나 전체 데이터셋에 대한 요약 정보를 생성하는 데 사용된다.

함수명	설명
COUNT	지정된 열의 행 수를 계산한다
SUM	지정된 숫자 열의 합계를 계산한다
AVG	지정된 숫자열의 평균을 계산한다
MIN	지정된 열에서 가장 작은 값을 찾는다
MAX	지정된 열에서 가장 큰 값을 찾는다
STDDEV	지정된 숫자 열에 대한 표준 편차를 계산한다
VARLANCE	지정된 숫자 열에 대한 분산을 계산한다

집계 함수는 SELECT 문 내에서 사용되며, 하나 이상의 열(칼럼)을 입력으로 받아 해당 값들을 처리하여 단일 결과 값을 반환한다.

집계함수는 GROUP BY절이 존재하는 경우만 사용할수 있는 것은 아니고 그냥 SELECT 문에서도 사용가능하다.

SELECT 
	COUNT(SAL),
	SUM(SAL), 
	AVG(SAL), 
	MIN(SAL), 
	MAX(SAL), 
	STDDEV(SAL), 
	VARIANCE(SAL)  
FROM EMP;

이때는 한 열만 출력되기에 테이블에 있는 컬럼을 동시에 조회하는것은 불가능하다.

SELECT 
	SAL ,
	COUNT(SAL),
	SUM(SAL), 
	AVG(SAL), 
	MIN(SAL), 
	MAX(SAL), 
	STDDEV(SAL), 
	VARIANCE(SAL)  
FROM EMP;

집계 함수와 해당 테이블의 컬럼을 동시에 보여주지 못하는 이유는 SQL 질의 언어의 구조와 데이터베이스 엔진의 동작 원리에 기인한다.

집계 함수는 여러 행을 하나로 요약하여 단일 값을 반환하는 함수이기에 집계 함수를 사용할 때, 해당 열(칼럼)들이 그룹화되거나 계산에 활용되는 방식으로 처리된다. 결과적으로, 집계 함수를 사용하면 개별 행 수준의 데이터가 요약된 결과 값으로 대체되기 때문에 해당 컬럼 값을 볼 수 없게 된다.

GROUP BY 가 없는 SELECT절에서의 집계함수와 GROUP BY가 있는 SELECT절에서의 집계함수의 차이는 아래를 보면 알 수 있다.

SELECT 
	COUNT(SAL),
	SUM(SAL), 
	AVG(SAL), 
	MIN(SAL), 
	MAX(SAL), 
	STDDEV(SAL), 
	VARIANCE(SAL)  
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

GROUP BY를 통해 DEPTNO(부서번호)로 묶인 그룹끼리의 집계함수를 그 그룹행 하나씩에 대한 결과로 보여준다.

GROUP BY에 DEPTNO를 같이 보면 더 쉽게 이해할 수 있다.

SELECT 
	DEPTNO ,
	COUNT(SAL),
	SUM(SAL), 
	AVG(SAL), 
	MIN(SAL), 
	MAX(SAL), 
	STDDEV(SAL), 
	VARIANCE(SAL)  
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

GROUP BY절은 DEPTNO(부서번호)를 기준으로 데이터를 그룹화하고, 함수들은 그룹화된 데이터를 기반으로(부서별로) 결과를 만들어 값을 반환하기에 그룹핑한 컬럼과 그에 맞는 함수 값이 나올수 있기에 동시에 그룹핑한 컬럼을 보여주는것 또한 가능하다.

그룹함수 vs ROLLUP, CUBE, GROUPING SETS

다른 글들을 보면 ROLLUP과 CUBE를 그냥 그룹함수라고 묶어서 표현하는데 정확하게 그룹함수로 지칭할 수는 없는 것으로 보이기에 따로 그룹함수라고 지칭하지 않으려고 한다. 그냥 ROLLUP, CUBE, GROUPING SETS라고 이름을 그대로 사용하겠다.

ROLLUP, CUBE, GROUPING SETS

ROLLUP, CUBE, GROUPING SETS는 그 자체로도 SQL 쿼리에 사용될 수 있지만, 이들은 주로 집계 함수와 함께 사용되어 그룹화된 데이터의 요약 정보를 제공하는데 의미가 있다

ROLLUP과 CUBE, GROUPING SETS 는 GROUP BY 절의 일부로 사용되며 컬럼들에 대해 다양한 수준의 그룹화를 수행한다. 그런 다음 그룹화된 결과에 대해 집계 함수(AVG, SUM, COUNT 등)를 적용하여 각 그룹의 요약 정보(집계 결과)를 얻을수 있다

1. ROLLUP

ROLLUP 연산자는 계층적으로 데이터를 그룹화한다.
예를 들어, "도시", "국가", "대륙" 순서로 데이터를 그룹화한다고 가정해보자. 먼저 각 도시별로 데이터를 그룹화하고, 이후에는 국가별로 그리고 마지막으로 대륙별로 데이터를 요약한다.
즉, ROLLUP은 지정된 컬럼 순서에 따라 상위 레벨부터 하위 레벨까지의 집계 결과를 제공한다.

DEPTNO(부서번호)를 기준으로 SAL(봉급)의 소계와 합계를 보기 위해선 기준이될 DEPTNO는 ROLLUP에 들어 있어야만 한다.

SELECT 
	DEPTNO,
	SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP(DEPTNO);

부서별 봉급의 합계와 전체 봉급의 합계가 같이 출력되는 것을 볼 수 있다.

그러면 기준을 하나 더 추가하려면 어떻게 해야하며 그 결과는 어떻게 나올까

부서별 직종별 봉급의 합계를 확인해보는 쿼리를 작성해보자

그룹핑이될 부서번호, 직종은 ROLLUP함수의 내부에 작성되어야만 조회가 가능하다.

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP(DEPTNO, JOB);

1. 부서별 + 직업별 봉급합계

2. 부서별 봉급합계

3. 전체 봉급합계

의 세가지 결과를 보여준다.

SELECT 
	조건컬럼1, 조건컬럼2,..., 집계함수(집계하려는 컬럼)
FROM
	테이블
GROUP BY ROLLUP(조건컬럼1, 조건컬럼2, ...)

일 경우 결과는

1.조건1+조건2+...+조건n의 집계 컬럼의 결과

2.조건1+조건2+...+조건n-1의 집계 컬럼의 결과

...

n-1.조건1의 집계컬럼의 결과

n .전체의 집계컬럼의 결과

와 같은 방식으로 나오게 되어 있다.

2.CUBE

CUBE 연산자는 모든 가능한 조합에 대해 데이터를 그룹화한다.
예를 들어, "도시", "국가", "대륙" 세 가지 컬럼이 있다면 이들의 모든 가능한 조합(도시만, 국가만, 대륙만, 도시+국가 등)에 대해 별도의 결과 세트(집계 결과)을 생성한다.
즉, CUBE는 다차원 분석을 위한 요약 정보 제공에 유용하다.

SELECT 
	DEPTNO,
	SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO);

그룹핑 하는 컬럼이 단독일 경우 해당 그룹전체와 그룹핑된 각각의 데이터에 대한 집계함수의 결과를 보여준다

여기선 부서전체의 급여의 합과 부서별 급여의 합을 보여준다.

ROLLUP과 다른점은 전체 그룹의 합계를 제일 위에 보여준다는 점뿐인것 같아보인다.

그렇다면 그룹핑 하는 컬럼을 두개 이상을 둔다면 어떨까 ?

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

ROLLUP과 다른점은 그룹핑한 컬럼이 두개일 때 각각의 그룹핑에 대한 결과를 준다는 것이다.

이게 어떤것이냐 하면

A,B가 그룹핑을 했고 그 합에 대한 결과를 CUBE로 보여준다면

1.전체에 대한 결과

2. A에서 그룹핑된 각각의 그룹들에 대한 결과

3. B에서 그룹핑된 각각의 그룹들에 대한 결과

4. A에서 그룹핑된 그룹중 B조건에 대해서 그룹핑된 그룹에 대한 결과

를 보여준다는 것이다.

ROLLUP과 다른점은 두번째 그룹핑 조건으로 걸린 컬럼에 대한 소계를 지원한다는 점이다.

여기선 그룹핑 컬럼으로 두개이지만 이 갯수가 늘어나면 그거에 대한 소계를 또 지원하게 된다

그니까 모든 조건들을 조회한다

전체 결과, A인 것들의 결과, B인것들의 결과, A이면서 B인것들의 결과와 같이 모든 결과를 보여준다

하나를 더 본다면..

SELECT 
	DEPTNO,
	JOB,
	MGR,
	SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB, MGR);

보면 각 컬럼, 컬럼끼리에 대한 그룹핑에 대한 결과를 출력해준다, 가능한 모든 조합으로 결과를 보여준다.

3. GROUPING SETS

GROUPING SETS 연산자는 원하는 특정 조합에 대해서만 데이터를 그룹화할 수 있게 해준다.
예를 들어 "도시"와 "국가" 각각 별개로 집계하려면 GROUP BY GROUPING SETS ((city), (country))와 같이 사용할 수 있다.
즉, GROUPING SETS는 여러 차원의 요약 정보 중 원하는 특정 부분만 선택하여 제공받을 때 유용하다.

GROUPING SETS는 ROLLUP과 CUBE에 들어 있는 기능을 선택해서 분리해서 사용할수 있는 기능으로 보인다.

1. GROUPING SETS(그룹컬럼1) - 그룹컬럼1의 각각의 그룹에 대한 결과를 보여줌

2. GROUPING SETS(그룹컬럼1, () ) - () => 모든 그룹의 합/ 그룹컬럼1의 각각의 그룹에 대한 결과와 전체의 결과를 보여준다.

3. GROUPING SETS(그룹컬럼1, 그룹컬럼2 ) - 그룹컬럼1의 각각의 그룹에 대한 결과, 그룹컬럼2의 각각의 그룹에 대한 결과를 보여줌

4. GROUPING SETS((그룹컬럼1, 그룹컬럼2)) - 그룹컬럼1+그룹컬럼2의 그룹들에 대한 각각의 결과를 보여준다.

1. GROUPING SETS(그룹컬럼1) - 그룹컬럼1의 각각의 그룹에 대한 결과를 보여줌

SELECT 
	DEPTNO,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO)
ORDER BY DEPTNO;

2. GROUPING SETS(그룹컬럼1, () ) - () => 모든 그룹의 합/ 그룹컬럼1의 각각의 그룹에 대한 결과와 전체의 결과를 보여준다.

SELECT 
	DEPTNO,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO,())
ORDER BY DEPTNO;

3. GROUPING SETS(그룹컬럼1, 그룹컬럼2 ) - 그룹컬럼1의 각각의 그룹에 대한 결과, 그룹컬럼2의 각각의 그룹에 대한 결과를 보여줌

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO,JOB)
ORDER BY DEPTNO;

4. GROUPING SETS((그룹컬럼1, 그룹컬럼2)) - 그룹컬럼1+그룹컬럼2의 그룹들에 대한 각각의 결과를 보여준다.

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB))
ORDER BY DEPTNO;

ROLLUP의 경우를 GROUPING SETS로 표현한다면

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB),DEPTNO,());

CUBE를 GROUPING SETS로 표현한다면

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB),DEPTNO,JOB,());

위에 있는 결과들과 비교하면 동일한것을 볼수 있다

* GROUPING() 함수

GROUPING 함수는 그룹화된 결과 집합에서 NULL 값을 나타내기 위해 사용되는 함수이다. 이 함수는 ROLLUP 또는 CUBE와 함께 사용될 때 유용하며, 다차원 그룹화된 결과를 생성할 때 NULL 값을 구분하기 위해 사용된다.

GROUPING 함수는 다음과 같은 특징을 가지고 있다

GROUPING 함수는 그룹화된 결과 집합에서 NULL 값을 나타낸다. NULL 값은 그룹화된 컬럼이 NULL인 경우에만 반환된다.
GROUPING 함수는 0 또는 1의 값을 반환한다. 0은 NULL이 아닌 값을 나타내고, 1은 NULL 값을 나타낸다.

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL),
	GROUPING(DEPTNO)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB),DEPTNO,JOB,());

주의할 점은 소계한 결과값이 NULL인 것에 대해서만 해당한다는 것이다 .

원래 값자체가 NULL인 그룹의 경우는 1이 아닌 0을 나타내게 되어 있다.

SELECT 
	COMM 
FROM EMP ;

이 COMM을 GROUP BY로 묶는다면 NULL인 그룹도 묶이게 되어 있는데 이것은 소계된 결과가 아니기 때문에 1이 아닌 0으로 나오게 되어 있다.

SELECT 
	COMM, 
	GROUPING(COMM) 
FROM EMP 
GROUP BY COMM;

*GROUPING_ID()함수

GROUPING_ID 함수는 오라클에서 그룹화된 결과 집합에서 그룹의 식별자를 생성하기 위해 사용되는 함수이다. 이 함수를 사용하면 각 그룹의 식별자를 구성할 수 있으며, 다중 열에 대한 그룹 식별자도 생성할 수 있다.

GROUPING_ID 함수는 다음과 같은 특징을 가지고 있다

GROUPING_ID 함수는 그룹화된 결과 집합에서 그룹의 식별자를 생성합니다. 각 그룹마다 고유한 숫자로 식별된다.
GROUPING_ID 함수는 이진수로 표현된 값이다. 각 그룹화된 컬럼에 대해 해당 컬럼이 그룹화에 사용되었을 경우에는 0, 그렇지 않은 경우에는 1로 표현된다.

SELECT 
	DEPTNO,
	JOB,
	SUM(SAL),
	GROUPING_ID(DEPTNO,JOB)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO,JOB),DEPTNO,JOB,());

결과는 2진수로 확인한 결과를 10진수로 변경해서 나타내며 NULL이 있는 컬럼을 1로 없는 컬럼을 0으로 계산하여 2진수를 만들고 10진수로 변환하여 나타낸다.

10진수의 결과를 보면 어디 컬럼의 소계가 NULL로 비어 있는지 확인이 가능하다.

'Database > Oracle' 카테고리의 다른 글

숫자로 된 문자열의 정렬 문제 해결 (0)	2023.12.18
SELECT의 실행순서 (0)	2023.10.25

hustle_D

hustle_D

태그

최근글

댓글

공지사항

아카이브

'Database > Oracle' 카테고리의 다른 글

관련글

티스토리툴바