데이터베이스 SQL - Multi-row Function (다중 행 함수)

다중 행 함수 (Multi-row Function) : 여러 행의 그룹에 대해 적용되는 함수

다중 행 함수의 종류

> 그룹 함수

  - 집계 함수 : COUNT, SUM, AVG, MAX / MIN 등

  - 고급 집계 함수 (행별 소계 냄) : ROLLUP, CUBE, GROUPING SETS

> 윈도우 함수

 

집계 함수

 

집계 함수

- 여러 행의 그룹에 대한 연산을 통해 하나의 결과를 반환

- SELECT, HAVING, ORDER BY 절에 사용

- GROUP BY 절을 통해 그룹핑 기준 명시

- NULL을 제외하고 계산

 

집계함수 사용 예시

SELECT COUNT(*) AS CNT FROM PLAYER; -- 481 : NULL 제외 전체 행의 수 출력
-- 111 : 컬럼 조건 걸어주고 THEN 사용해 일치할 때 1 더해준다.
SELECT COUNT(CASE WHEN JOIN_YYYY > 2010 THEN 1 END) AS CNT FROM PLAYER;
-- ...

 

GROUP BY

집계 함수는 일반적으로 GROUP BY 절을 사용해 그룹별 연산을 수행한다.

(테이블 전체가 하나의 그룹일 경우 GROUP BY절 생략 가능)

SELECT POSITION, COUNT(*) 전체행수, COUNT(HEIGHT) 키건수, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION;

 

GROUP BY 퀴즈

Q. 아래 두 질의가 오류를 발생시키는 이유는?

SELECT POSITION, PLAYER_NAME, COUNT(*) 전체행수, COUNT(HEIGHT) 키건수, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION;

SELECT POSITION, COUNT(*) 전체행수, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER

A. 

조회하는 컬럼과 그룹을 적용하는 컬럼의 차이로 인해서 발생한다.

group by 문을 사용할 때는 반드시 그룹 함수 외에 조회하는 column 모두를 group by 문에 표시해야 한다.

 

 

GROUP BY ~ HAVING  ~

포지션별 평균키 출력

-- 포지션별 평균키 출력
SELECT POSITION, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION;

-- 집계 함수에 조건을 부여 (포지션별 평균키 180 이상만 출력)
SELECT POSITION, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION
HAVING AVG(HEIGHT) >= 180;

 

WHERE 절 사용 시 오류 발생한다. (WHERE 절이 GROUP BY 절 보다 먼저 수행되기 때문)

-- 오류 발생
SELECT POSITION, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
WHERE AVG(HEIGHT) >= 180
GROUP BY POSITION

 

GROUP BY ~ HAVING  ~ 퀴즈

Q. 포지션별 평균키를 출력하되, 해당 포지션의 키의 최대값이 190CM 이상인경우만 출력하라

SELECT POSITION, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION
HAVING MAX(HEIGHT) >= 190;

 

GROUP BY - 그룹핑 기준이 2개인 경우

-- 부서 정보인 DEPT 테이블, 사원 정보인 EMP 테이블
-- 그룹핑 기준은 부서이름(DNAME), 직업(JOB) 2개
SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) AS 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB;

 

SELECT 문의 구조 및 실행 순서

 

- SELECT 문장 구조

SELECT POSITION, ROUND(AVG(HEIGHT), 2) AS 평균키
FROM PLAYER
WHERE HEIGHT IS NOT NULL
GROUP BY POSITION
HAVING AVG(HEIGHT) > 180
ORDER BY AVG(HEIGHT) DESC;

- 실제 실행 순서

SELECT POSITION, ROUND(AVG(HEIGHT), 2) AS 평균키 -- 5번째 : 데이터 값 계산 및 출력
FROM PLAYER -- 1번째 : 질의 대상 테이블 참조
WHERE HEIGHT IS NOT NULL -- 2번째 : 반환 대상 아닌 데이터 제거
GROUP BY POSITION -- 3번째 : 행 그룹화
HAVING AVG(HEIGHT) > 180 -- 4번째 : 반환 대상 아닌 그룹 제거
ORDER BY AVG(HEIGHT) DESC; -- 6번째 : 출력 데이터 정렬

 

 

ROWNUM 활용

-- ROWNUM을 사용해 키가 가장 작은 3명의 선수 조회
SELECT PLAYER_NAME, HEIGHT, ORGNO, ROWNUM
FROM (SELECT PLAYER_NAME, HEIGHT, ROWNUM AS ORGNO FROM PLAYER ORDER BY HEIGHT)
WHERE ROWNUM < 4;

 

ROWNUM 활용 시 주의사항

잘못된 쿼리 : WHERE문이 ORDER BY문보다 먼저 수행되기 때문

-- 잘못된 쿼리
SELECT PLAYER_NAME, HEIGHT, ROWNUM
FROM PLAYER
WHERE ROWNUM < 4
ORDER BY HEIGHT;

 

칼럼의 유효 범위

-- 관계형 데이터베이스는 데이터를 행 단위로 메모리에 복사
-- SELECT 절에서 명시되지 않은 칼럼도 WHERE, ORDER BY 절에서 사용 가능
SELECT PLAYER_NAME, HEIGHT
FROM PLAYER
WHERE POSITION = 'MF'
ORDER BY TEAM_ID;

-- ORDER BY가 사용되는 경우
-- SELECT절에서 명시되지 않은 집계 칼럼을 HAVING, ORDER BY에 사용 가능
SELECT TEAM_ID, COUNT(*) AS 인원
FROM PLAYER
GROUP BY TEAM_ID
HAVING AVG(HEIGHT) > 178
ORDER BY AVG(HEIGHT);

 

- 인라인 뷰가 사용되는 경우

-- 인라인 뷰가 사용되는 경우
-- 새로운 테이블 구조가 생성된 것으로 이해해야 한다.
-- 인라인 뷰의 SELECT 절에 명시되지 않은 칼럼은 메인 쿼리에서 사용 불가

-- 오류 발생
SELECT PLAYER_NAME, HEIGHT
FROM (SELECT PLAYER_NAME, HEIGHT FROM PLAYER WHERE POSITION = 'MF')
ORDER BY POSITION;

-- ↓ 다음과 같이 수정
SELECT PLAYER_NAME, HEIGHT
FROM (SELECT PLAYER_NAME, HEIGHT, POSITION FROM PLAYER WHERE POSITION = 'MF')
ORDER BY POSITION;

 

 

고급 집계 함수

 

- ROLLUP : 소그룹 별 소계 계산 추가 (순서 중요)

 

일반 GROUP BY

SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) AS 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB;

 

ROLLUP

-- 소그룹 별 소계 계산 추가
-- 각 DNAME / JOB 별 집계
-- 각 DNAME 별 집계
-- 전체 집계
SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) AS 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB;

 

-- 각 DNAME / JOB / MGR 별 집계
-- 각 DNAME / JOB 별 집계
-- 각 DNAME 별 집계
-- 전체 집계
SELECT DNAME, JOB, MGR, COUNT(*) AS 직원수, SUM(SAL) AS 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB, MGR)
ORDER BY DNAME, JOB, MGR;

 

ROLLUP

- GROUPING 함수의 사용

GROUPING 함수는 소계, 합계로 집계된 행의 컬럼 NULL을 구분할 수있다.

NULL인 경우 1을 반환하고 아닌경우 0을 반환한다.

SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) AS "Total Empl", SUM(SAL) AS "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);

 

ROLLUP

- GROUPING + CASE 사용

SELECT
    CASE GROUPING(DNAME)
        WHEN 1 THEN 'ALL Departments'
        ELSE DNAME
    END AS DNAME, -- Alias
    
    CASE GROUPING(JOB)
        WHEN 1 THEN 'ALL Jobs'
        ELSE JOB
    END AS JOB,
    
    COUNT(*) AS "Total Emp", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

 

CUBE : 다차원 소계 계산 추가 (순서 무관) , 모든 조합의 집계 계산 (시스템 부하가 큼)

SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB) -- GROUP BY CUBE(JOB, DNAME)해도 결과 동일
ORDER BY DNAME, JOB;

표시한 영역은

GROUP BY ROLLUP (DNAME, JOB) 했을 때 계산되지 않았던 것들이다.

 

ROLLUP으로 위의 CUBE를 구현하려면

SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB)

UNION

SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(JOB, DNAME)
ORDER BY DNAME, JOB;

 

GROUPING SETS

여러 칼럼 각각에 대해 반복적으로 그룹화

SELECT DNAME, JOB, COUNT(*) AS 직원수, SUM(SAL) 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS(DNAME, JOB);

 

GROUPING SETS + DECODE

DECODE 함수는 프로그래밍에서의 if else 와 비슷한 기능을 수행한다.

ex) DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3...) 

SELECT
    -- IF GROUPING(DNAME) == 1 RETURN 'All Departments' ELSE DNAME
    DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
    DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
    COUNT(*) AS 직원수, SUM(SAL) AS 급여합
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS(DNAME, JOB);