[Oracle] 분석함수, 윈도함수 - GROUPING SETS ( ), LISTAGG( ), PIVOT( ), UNPIVOT( ) 함수

GROUPING SETS( )

 

- 그룹핑 조건이 여러 개일 경우 유용하게 사용됨

 

- 하나의 테이블에 대한 여러 가지 그룹 함수가 사용될 경우 해당 함수를 사용하는 것이 좋음

 

- 예시. 학년별, 학과별 인원수와 키, 몸무게 각각의 합계 동시 출력

 

SELECT grade, deptn01, COUNT(*), SUM(height), SUM(weight)

FROM STUDENT

GROUP BY grouping sets (grade, deptn01)

ORDER BY grade, deptn01;

---

LISTAGG( ) 함수

- Oracle 11g에서 추가된 함수

 

- 일정 기준과 구분자를 통해 데이터가 한 줄로 연결되어 출력됨

 

- LISTAGG( 컬럼 이름, '구분자' ) WITHIN GROUP ( ORDER BY 컬럼명 ) as "별칭"

 

• LISTAGG 함수에 나열하고 싶은 '컬럼 이름' 작성하고, 구분할 구분자를 작은 따옴표 사이에 기록
  
  
• WITHIN GROUP 사이에 가로로 나열하고 싶은 규칙을 ORDER BY로 적어주면 됨

- WITHIN GROUP ( ) 절에 아무 조건도 주지 않을 경우 에러 발생

 

- 두 번째 파라미터(구분자)로 예약어 사용 불가

 

- RETURN 값이 4,000 byte가 넘을 경우 오류 발생 => 이 경우는 XMLAGG XML 함수 사용 권장

 

- 예시. 직원을 부서별로 출력하되, 각 부서마다 먼저 입사한 직원의 이름을 순서대로 ' -> '로 구분하여 출력하시오.

 

SELECT deptno, LISTAGG(ename, ' -> ') WITHIN GROUP ( ORDER BY hiredate )

FROM emp

GROUP BY deptno;

---

XMLAGG XML 함수 (10g 이상)

- 만약 저장된 4,000바이트가 넘는 출력 결과가 나올 수 있다면 그 형태는 VARCHAR2 형태나 CLOB 형태가 있음

- 각 형태에 따라 쿼리가 달라지는데 문법이 어려움

 

1.  Retun type : VARCHAR2 일 때,

 

SELECT deptno, SUBSTR ( XMLAGG ( XMLELEMENT ( X, ',', ename) ORDER BY ename ).EXTRACT('//text( )').getStringVal( ), 2) aS DEPT_ENAME_LIST
FROM emp A

GROUP BY deptno;

 

• XMLELEMENT 함수
  - XML 태그명과 구분자, 대상 컬럼 입력
  - 정렬의 기준은 대상 컬럼이 됨
  
  
• XML 태그 값은 내부적으로 사용하는 컬럼이기에 아무 문자열을 입력하면 됨

2.  Retun type : CLOB 일 때,

 

SELECT deptno, SUBSTR ( XMLAGG ( XMLELEMENT ( X, ',', ename) ORDER BY ename ).EXTRACT('//text( )').getClobVal( ), 2) aS DEPT_ENAME_LIST
FROM emp A

GROUP BY deptno;

---

PIVOT( ) 함수 (11g 버전에서 추가된 함수)

- PIVOT( ) : Row -> Column

- 사용법

    SELECT 집계함수의 열 + 기준열의 값

    FROM (

           SELECT 원본테이블 FROM 테이블

    )   AS TEMP

    PIVOT (

        집계함수(열) FOR 기준열 IN (기준열의 값)

    ) AS PVT

---

[ 예시 1 ] Pivot 기능을 사용하지 않고 decode 함수를 활용해 달력 만들기

 

SELECT  MAX(DECODE("DAY", 'SUN', DAYNO)) AS "SUN",
  MAX(DECODE("DAY", 'MON', DAYNO)) AS "MON", 
  MAX(DECODE("DAY", 'TUE', DAYNO)) AS "TUE",
  MAX(DECODE("DAY", 'WED', DAYNO)) AS "WED",
  MAX(DECODE("DAY", 'THU', DAYNO)) AS "THU",
  MAX(DECODE("DAY", 'FRI', DAYNO)) AS "FRI",
  MAX(DECODE("DAY", 'SAT', DAYNO)) AS "SAT"
FROM CAL c 
GROUP BY WEEKNO 
ORDER BY WEEKNO ;

 

• DECODE( )
  요일에 해당하는 숫자를 전부 출력하기 위함
  
  
• MAX( )
  
  SELECT   MAX( DECODE("DAY", 'SUN', DAYNO)) AS "SUN", 
    MAX(DECODE("DAY", 'MON', DAYNO)) AS "MON",
    MAX(DECODE("DAY", 'TUE', DAYNO)) AS "TUE",
    MAX(DECODE("DAY", 'WED', DAYNO)) AS "WED",
    MAX(DECODE("DAY", 'THU', DAYNO)) AS "THU",
    MAX(DECODE("DAY", 'FRI', DAYNO)) AS "FRI",
    MAX(DECODE("DAY", 'SAT', DAYNO)) AS "SAT" 
  FROM CAL c ;
  
  - GROUP BY를 사용하므로 그룹 함수를 통한 값 반환이 필요하기에 사용
  - 위 코드와 같이 MAX만 사용할 경우 사람들이 인식하는 큰 수가 나오지 않는데, 이는 숫자가 아닌 문자열로 비교하기 때문임. 그렇기에 ASCII 코드로 값의 크기를 비교해 결과 값을 반환함.
  
  
• GROUP BY 
  주별로 그룹핑하여 주별 각 요일의 날짜를 표현하기 위함

[ 예시 2 ] Pivot 기능을 사용해 달력 만들기

 

SELECT *

FROM ( SELECT weekno "WEEK", DAY, dayno

 FROM CAL c )

PIVOT(

  MAX(dayno) FOR DAY IN ( 'SUN' AS "SUN",

  'MON' AS "MON",

  'TUE' AS "TUE",

  'WED' AS "WED",

  'THU' AS "THU",

  'FRI' AS "FRI",

  'SAT' AS "SAT")

)

ORDER BY "WEEK";

 

[ 예시 3 ] EMP 테이블에서 부서별로 각 직급별 인원이 몇 명인지 계산하기

 

(1) DECODE 사용 방법

SELECT DEPTNO ,

  COUNT(DECODE(JOB, 'CLERK',  '0')) AS "CLERK",

  COUNT(DECODE(JOB, 'SALESMAN',  '0')) AS "SALESMAN",

  COUNT(DECODE(JOB, 'PRESIDENT',  '0')) AS "PRESIDENT",

  COUNT(DECODE(JOB, 'MANAGER',  '0')) AS "MANAGER",

  COUNT(DECODE(JOB, 'ANALYST',  '0')) AS "ANALYST"

FROM EMP e 

GROUP BY DEPTNO 

ORDER BY DEPTNO ;

 

(2) PIVOT 사용 방법 

SELECT *

FROM (

  SELECT job, deptno

  FROM EMP e 

)

PIVOT(

  COUNT(job) FOR job IN

  ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')

)

ORDER BY deptno;

---

PIVOT 그룹핑 여러 개 조건 사용 가능

부서별 인원수와 각 인원수별 급여 합계 출력

→ 그룹화하여 카운트하고 계산하기 편한 기준을 잡기 위해 job 사용

---

UNPIVOT( ) 함수

- PIVOT의 반대 개념으로 합쳐 있는 것을 풀어서 보여주는 역할

- UNPIVOT( ) : Column -> Row

- 사용법

    SELECT *

    FROM UNPIVOT 대상 테이블

    UNPIVOT (

        집계함수 기준 컬럼 FOR 기준 컬럼의 이름 IN (기준 컬럼들)

    ) AS PVT