오라클 GROUP BY, HAVING 절과 응용

GROUP BY

오라클 SQL에서 GROUP BY 절을 사용하여 그룹별 건수나 합계를 얻을 수 있다.

예제

SELECT STY_NM
       , SUM(SALE_QTY) AS SALE_QTY
 FROM STY
 WHERE 1=1
 GROUP BY STY_NM

위와 같이 GROUP BY를 사용하여 스타일별 판매수량을 구할 수 있다.

• GROUP BY로 집계된 결과 중 원하는 값을 조회하기 위해
  HAVING절을 통해 조건절을 추가할 수 있다.

HAVING

SELECT 문에서 HAVING을 쓰는 경우 대게 많은 것 같다.
HAVING 절은 GROUP BY로 집계된 값 중 WHERE 절 처럼 조건을 추가할 수 있다.

기본 문법

SELECT [GROUP BY 절에 지정된 컬럼명1], [GROUP BY별로 집계할 컬럼명2]
FROM [테이블명]
WHERE 1=1 <WHERE 생략이 가능하다.>
GROUP BY [그룹으로 묶을 컬럼명]
HAVING [조건]

예제

SELECT STY_NM
       , SUM(SALE_QTY) AS SALE_QTY
 FROM  STY
WHERE 1=1 
GROUP BY STY_NM
HAVING SUM(SALE_QTY) BETWEEN 500 AND 700

예제를 보면,
HAVING 절이 없다면 그룹으로 묶인 모든 과일과 판매수량이 조회되지만, HAVING SUM(SALE_QTY) BETWEEN 500 AND 700을 추가하여 STY_NM 판매수량이 500과 700사이의 값만 조회된 것을 볼 수 있다.

중복 데이터 조회

SELECT CUST_ID,
       CUST_NM,
       COUNT(*)
FROM TEMP_TEST
WHERE 1=1 
GROUP BY CUST_ID, CUST_NM
HAVING COUNT(*) > 1

중복된 데이터를 찾고자하는 컬럼으로 GROUP BY한 후, HAVING절에 COUNT(*) > 1의 조건을 걸어주어 중복된 데이터를 조회한다.

ROLLUP

ROLLUP구문은 GROUP BY절과 같이 사용되며, GROUP BY절에 의해서 그룹 지어진
집합 결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행한다.
SELECT절에 ROLLUP을 사용하여 보통의 SELECT된 데이터와 그 데이터의 총계를 구할 수 있다.

소계와 합계

SELECT SALE_DT       AS 판매날짜
       , FRUIT_CD      AS 품목
       , SUM(SALE_QTY) AS 판매수량
FROM FRUIT_TABLE
WHERE 1=1
GROUP BY ROLLUP(SALE_DT,FRUIT_CD)

결과를 보면 ROLLUP(SALE_DT,FRUIT_CD)을 통해 합계와 소계가 구해진 것을 볼 수 있다.
ROLLUP첫번째 컬럼의 합산 데이터와 전체 합산 데이터를 출력한다.
SALE_DT와 FRUIT_CD위치를 바꾸게 되면 FRUIT_CD의 합산 데이터와
전체 합산 데이터를 출력한다.

합계 출력

SELECT SALE_DT       AS 판매날짜
       , FRUIT_CD      AS 품목
       , SUM(SALE_QTY) AS 판매수량
FROM FRUIT_TABLE
WHERE 1=1
GROUP BY ROLLUP((SALE_DT,FRUIT_CD))

ROLLUP((SALE_DT,FRUIT_CD))을 통해 전체 합계 데이터를 출력할 수 있다.

GROUPING, GROUPING_ID

GROUPING 함수는 1개의 매개변수만 있으며, GROUPING_ID함수는 여러개의 매개변수를 입력할 수 있다.

예제

SELECT DECODE(GROUPING_ID(FRUIT_NUM,FRUIT_CD),3,'합계',FRUIT_NUM) AS 과일번호,
       CASE WHEN GROUPING_ID(FRUIT_NUM,FRUIT_CD) = 1 THEN 
       		 DECODE(GROUPING_ID(FRUIT_NUM,FRUIT_CD),1,'',FRUIT_CD)
       ELSE DECODE(GROUPING_ID(FRUIT_NUM,FRUIT_CD),1,'소계',FRUIT_CD)
       END 과일
       , SUM(SALE_QTY) AS 판매수량
       , GROUPING_ID(FRUIT_NUM,FRUIT_CD) AS GROUPING_ID복수
FROM FRUIT_TABLE
WHERE 1=1
GROUP BY ROLLUP(FRUIT_NUM,FRUIT_CD)

GROUPING_ID 함수는 여러 칼럼을 매개변수로 사용할 수 있다.
매개변수의 컬럼 순서에 맞게 해당 컬럼이 NULL인 경우 1을 반환하고 한 행을 2진수라고 생각하면 된다.

예시 이미지를 보면

• 3행을 보게되면 과일번호는 데이터가 있어서 0 과일의 데이터는 없어 1
  즉) 0001로 1이 반환되는 것을 알 수 있다.(11행도 같은 내용이다)

• 20행을 보게되면 과일번호와 과일의 데이터가 없어 1과1
  즉) 0011로 3이 반환되는 것을 알 수 있다.

• GROUPING_ID를 이용하여 NULL인 부분에 DECODE를 이용하여 합계와 소계를 추가해 주어 데이터를 쉽게 확인 할 수 있다.