SQL - 오라클 함수(다중행 함수)

다중행 함수

-- SUM 함수
SELECT SUM(SAL),
       SUM(DISTINCT SAL),
       SUM(ALL SAL)
FROM EMP;

-- COUNT 함수
SELECT COUNT(SAL),
       COUNT(DISTINCT SAL),
       COUNT(ALL SAL)
FROM EMP;

-- NULL 데이터는 반환 개수에서 제외
SELECT COUNT(COMM)
FROM EMP;

-- 위 결과와 동일
SELECT COUNT(COMM)
FROM EMP
WHERE COMM IS NOT NULL;

-- MAX, MIN 함수: 숫자, 날짜 데이터
SELECT MAX(HIREDATE)
FROM EMP
WHERE DEPTNO = 20;

-- AVG 함수
SELECT AVG(DISTINCT SAL)
FROM EMP
WHERE DEPTNO = 30;

GROUP BY절

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

-- 위와 결과는 동일
SELECT AVG(SAL), '10' AS DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT AVG(SAL), '20' AS DEPTNO
FROM EMP
WHERE DEPTNO = 20
UNION ALL
SELECT AVG(SAL), '30' AS DEPTNO
FROM EMP
WHERE DEPTNO = 30;

-- HAVING절: GROUP BY절에 조건을 줄 때 사용
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;

그룹화 관련 함수

-- ROLLUP 함수
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP (DEPTNO, JOB);

-- LISTAGG 함수: 데이터 가로 나열
SELECT DEPTNO,
       LISTAGG(ENAME, ', ')
               WITHIN GROUP ( ORDER BY SAL DESC ) AS ENAMES
FROM EMP
GROUP BY DEPTNO;

-- PIVOT, UNPIVOT 함수: 행 -> 열, 열 -> 행
SELECT DEPTNO, JOB, MAX(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

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