SQLD 대비 (11) - 계층형 SQL, 집계함수

SQLD 대비 (10) - 서브쿼리(Sub Query), 비등가 조인, 집합연산자

이전 글과 이어지는 글입니다. 해당 정리글은 아래의 유튜브 영상을 참고하여 만들었습니다. 문제 시 삭제하겠습니다.
빨간색 형광펜은 강의에서 강조한 부분, 노란색 형광펜은 추가 서치로 내용을 '직접' 보강한 부분입니다.

 

계층형 SQL

• 한 테이블안에 계층적인 데이터 구조를 가진 테이블에서 쉽게 데이터를 출력하기 위한 SQL문법.
• 스크립트 실행

SELECT 컬럼명
FROM 테이블
WHERE 조건절
START WITH 시작조건
CONNECT BY [NOCYCLE] PRIOR 관계 방향
ORDER [SIBLINGS] BY 정렬 조건

계층형 전용 컬럼 및 함수

LEVEL	ROOT 부터 한단계씩 내려가면서 증가하는 번호
CONNECT_BY_ISLEAF	각 전개별 최하위 리프에 1, 아니면 0
CONNECT_BY_ISCYCLE	내부적인 값 오류 등으로 자식노드가 다시 내 부모노드가 되는 경우
SYS_CONNECT_BY_PATH	ROOT로 부터 각 ROW까지 전개된 데이터 경로를 출력
CONNECT_BY_ROOT	각 전개한 데이터의 루트 데이터를 출력

오랫동안 이해를 못했었는데, 계층형 구조를 이해할때 매우 도움된 영상입니다.

 

SELECT C3
FROM TAB1
START WITH C2 IS NULL
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC

1. 읽는 순서는 START WITH부터! C2가 NULL인 것부터 추출합니다. 
2. CONNECT BY PRIOR C1 = C2는 START WITH의 행들을 순서대로 적용하는데, 추출된 행의 C1과 같은 C2값을 갖는 나머지 행을 추출하게 됩니다. (prior: 이전의, 즉 이전의 c1과 나머지 c2가 같은 행 추출)
3. START WITH에서 추출된 행의 LEVEL은 1, 그다음은 2, ... 
4. ORDER SIBLINGS BY C3 DESC는 '같은 LEVEL' 끼리 C3를 내림차순으로 정렬한다는 이야기입니다.

 

그룹함수

• 테이블내 데이터들을 각 컬럼 별로 그룹화 하여 그룹별 결과를 출력하는 함수
• UNION, UNION ALL으로 대체 가능

ROLLUP	해당 컬럼의 소계 및 총계를 출력
GROUPING	컬럼의 소계 여부를 출력
GROUPING SETS	집계 대상 컬럼에 대한 소계 출력
CUBE	결합 가능한 모든 경우의 수에 대한 집계 출력

 

ROLLUP

• 계층 구조이기에 기준 컬럼수가 2개 이상일 때 순서에 따라 출력이 달라진다. (ROLLUP밖에 없음)

소계: 그룹별 집계함수 결과
총계: 전체 집계함수 결과

예시 테이블, GPT로 생성

SELECT Department, JobTitle, SUM(Salary) AS TotalSalary
FROM employee
GROUP BY ROLLUP (Department, JobTitle);

이 쿼리는 Department와 JobTitle별로 Salary의 합계를 계산합니다. 부서별, 직책별, 그리고 전체의 합계를 포함합니다.

-- 결과
Department   JobTitle      TotalSalary
Sales        Manager       70000
Sales        Salesperson   105000
Sales        (null)        175000 -- 소계
HR           Manager       75000
HR           Recruiter     48000
HR           (null)        123000 -- 소계
Engineering  Engineer      122000
Engineering  (null)        122000 -- 소계
(null)       (null)        420000 -- 총계

 

GROUPING

SELECT Department, JobTitle, SUM(Salary) AS TotalSalary,
       GROUPING(Department) AS DeptGrouping
FROM employee
GROUP BY ROLLUP (Department, JobTitle);

여기서 DeptGrouping 컬럼은 Department가 집계된 행인지를 나타냅니다. 1은 집계 행, 0은 일반 행을 의미합니다.

-- 결과
Department   JobTitle      TotalSalary  DeptGrouping
Sales        Manager       70000        0
Sales        Salesperson   105000       0
Sales        (null)        175000       1
HR           Manager       75000        0
HR           Recruiter     48000        0
HR           (null)        123000       1
Engineering  Engineer      122000       0
Engineering  (null)        122000       1
(null)       (null)        420000       1

 

GROUPING SETS

• 집계 대상 컬럼에 대한 소계를 구할 수 있는 그룹함수
• 기준 컬럼의 순서와 결과는 상관 없음

SELECT Department, JobTitle, SUM(Salary) AS TotalSalary
FROM employee
GROUP BY GROUPING SETS ((Department), (JobTitle), (Department, JobTitle));

이 쿼리는 별도의 집계 그룹을 생성합니다: Department별, JobTitle별, 그리고 둘 다의 조합.

-- 결과
Department   JobTitle      TotalSalary
Sales        (null)        175000
HR           (null)        123000
Engineering  (null)        122000
(null)       Manager       145000
(null)       Salesperson   105000
(null)       Recruiter     48000
(null)       Engineer      122000
Sales        Manager       70000
Sales        Salesperson   105000
HR           Manager       75000
HR           Recruiter     48000
Engineering  Engineer      122000

 

CUBE

• 결합 가능한 모든 값에 대한 다차원 집계를 출력.
• 기준 컬럼의 순서와 결과는 상관 없음
• 다른 그룹 함수들 보다 성능상 불리

SELECT Department, JobTitle, SUM(Salary) AS TotalSalary
FROM employee
GROUP BY CUBE (Department, JobTitle);

CUBE는 모든 가능한 조합에 대한 집계를 생성합니다.

-- 결과
Department   JobTitle      TotalSalary
Sales        Manager       70000
Sales        Salesperson   105000
Sales        (null)        175000
HR           Manager       75000
HR           Recruiter     48000
HR           (null)        123000
Engineering  Engineer      122000
Engineering  (null)        122000
(null)       Manager       145000
(null)       Salesperson   105000
(null)       Recruiter     48000
(null)       Engineer      122000
(null)       (null)        420000