[SQL] Oracle, 오라클 다중행 함수

다중행함수

: 여러 행들을 입력값으로 받아 하나의 값으로 출력

 

-SUM

SELECT SUM(COMM)
FROM EMP;
--NULL값을 제외하고 계산한다.

SELECT SUM(DISTINCT SAL)
    --중복 값 제거
    , SUM(SAL)
FROM EMP;

 

-COUNT

--COUNT
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO=30;

SELECT COUNT(DISTINCT SAL)
    --중복 값 제거
    , COUNT(SAL)
FROM EMP;

SELECT COUNT(COMM)
FROM EMP;
--4
----NULL값을 제외하고 계산한다.

 

-MAX, MIN

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=10;

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO=10;

 

-AVG

SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=10;

---

GROUP BY와 HAVING

 

- GROUP BY

:그룹화를 해준다.

SELECT AVG(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;

-HAVING

: GROUP BY절을 통해 그룹화된 결과 값의 범위를 제한 

SELECT DEPTNO, JOB, AVG(SAL) 
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO, JOB;

- WHERE vs HAVING

WHERE: 출력 대상 행을 제한

HAVING: 그룹화된 대상을 출력에서 제한

SQL이 적용되는 순서

---

그 외 함수

 

- 그룹별 소계 및 총계를 구하는 함수: ROLLUP, CUBE

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

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

- 그룹화 열이 여러개 필요한 경우: GROUPING SETS

- 그룹핑 체크 함수: GROUPING, GROUPING_ID

- 가로로 출력을 보고 싶을 때: LISTAGG

SELECT DEPTNO
    , LISTAGG(ENAME, ', ') WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;

- 기존 테이블 열을 행으로 변경해 출력: PIVOT, UNPIVOT

SELECT *
FROM(SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT(MAX(SAL) FOR DEPTNO IN (10, 20, 30))
ORDER BY JOB;

SELECT *
FROM(SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT(MAX(SAL) FOR JOB IN ('CLEAK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST'))
ORDER BY DEPTNO;

1

2

---

종합예제

--1
SELECT DEPTNO
    , TRUNC(AVG(SAL)) AS AVG_SAL
    , MAX(SAL) AS MAX_SAL
    , MIN(SAL) AS MIN_SAL
    , COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO;

--2
SELECT JOB, COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*)>=3;

--3
SELECT TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR
    , DEPTNO
    , COUNT(*) AS CNT
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY'), DEPTNO;

--4
SELECT EXIST_COMM
        , COUNT(*) AS CNT
FROM (SELECT NVL2(COMM, 'O', 'X') AS EXIST_COMM
        FROM EMP)
GROUP BY EXIST_COMM;

SELECT NVL2(COMM, 'O', 'X') AS EXIST_COMM
    , COUNT(*) AS CNT
FROM EMP
GROUP BY NVL2(COMM, 'O', 'X');

--5
SELECT DEPTNO
    , TO_CHAR(HIREDATE, 'YYYY') AS HIRE_YEAR
    , COUNT(*) AS ANT
    , MAX(SAL) AS MAX_SAL
    , SUM(SAL) AS SUM_SAL
    , AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY ROLLUP (DEPTNO, TO_CHAR(HIREDATE, 'YYYY'));