[데이터베이스개론·SQL] 250122

이슬비·2025년 1월 22일

데이터베이스개론·SQL

목록 보기

7/9

그룹 함수

데이터를 특정 기준으로 그룹핑하여 요약 및 집계하는 데 사용한다. 데이터 분석 및 보고서를 생성할 때 유용한 기술로, 다차원 데이터를 요약하는 데 활용한다.

그룹 함수	설명
`ROLLUP`	특정 컬럼 순서대로 부분 합계 및 총합계를 계산하는 함수
`CUBE`	모든 컬럼의 가능한 조합에 대해 다차원 집계를 계산하는 함수
`GROUPING SETS`	지정된 그룹핑 조합에 대해서만 집계를 계산하는 함수
`GROUPING`	집계 데이터와 실제 데이터를 구분하는 데 사용하는 함수

`ROLLUP`

소계와 총계를 한 번에 생성하는 그룹 함수이다. 지정된 컬럼 순서대로 그룹핑하며, 컬럼의 순서에 따라 결과 데이터가 달라진다.
주로 보고서나 요약 데이터를 생성할 때 사용한다.

맨 처음 명시한 컬럼에 대해서만 소계를 구한다.

SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

--ROLLUP(JOB_ID)
SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY ROLLUP(JOB_ID)
ORDER BY JOB_ID;

SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY JOB_ID, DEPARTMENT_ID
ORDER BY JOB_ID, DEPARTMENT_ID;

--ROLLUP(JOB_ID, DEPARTMENT_ID)
SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY ROLLUP(JOB_ID, DEPARTMENT_ID)
--GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY JOB_ID, DEPARTMENT_ID;

`CUBE`

소계와 총계를 한 번에 생성하는 그룹 함수이다. 모든 가능한 조합에 대한 집계를 생성하며, 컬럼의 순서가 달라져도 결과 데이터는 동일하다.
다차원 데이터 분석에 적합하여 ROLLUP보다 더 많은 집계 결과를 생성한다.

SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

--CUBE(JOB_ID)
SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY CUBE(JOB_ID)
ORDER BY JOB_ID;

SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY JOB_ID, DEPARTMENT_ID
ORDER BY JOB_ID, DEPARTMENT_ID;

--CUBE(JOB_ID, DEPARTMENT_ID)
SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY CUBE(JOB_ID, DEPARTMENT_ID)
ORDER BY JOB_ID, DEPARTMENT_ID;

`GROUPING SETS`

원하는 조합에 대해서만 집계를 생성한다. ROLLUP과 CUBE보다 더 유연한 방식을 제공하며, 불필요한 집계 생성을 방지하여 성능을 향상시킨다.
서로 다른 기준으로 그룹핑 한 데이터 셋을 UNION ALL한 결과와 동일한 데이터를 출력한다.

SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

--GROUPING SETS(JOB_ID)
SELECT JOB_ID, COUNT(*) AS CNT
FROM EMPLOYEES
GROUP BY GROUPING SETS(JOB_ID)
ORDER BY JOB_ID;

SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY JOB_ID, DEPARTMENT_ID
ORDER BY JOB_ID, DEPARTMENT_ID;

--GROUPING SETS(JOB_ID, DEPARTMENT_ID)
SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY GROUPING SETS(JOB_ID, DEPARTMENT_ID)
ORDER BY JOB_ID, DEPARTMENT_ID;

--UNION ALL을 사용한 GROUPING SETS
SELECT JOB_ID, NULL DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY JOB_ID
UNION ALL
SELECT NULL JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY JOB_ID, DEPARTMENT_ID;

--GROUPING SETS(JOB_ID, DEPARTMENT_ID, ())
SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY GROUPING SETS(JOB_ID, DEPARTMENT_ID, ())
ORDER BY JOB_ID, DEPARTMENT_ID;

`GROUPING`

집계 결과가 실제 데이터인지, 집계 데이터인지 확인한다. 집계된 행이면 1을 반환하고, 아니면 0을 반환한다.

SELECT JOB_ID, COUNT(*) AS CNT, GROUPING(JOB_ID)
FROM EMPLOYEES
GROUP BY ROLLUP(JOB_ID)
ORDER BY JOB_ID;

SELECT JOB_ID, DEPARTMENT_ID, COUNT(*) AS CNT, GROUPING(JOB_ID), GROUPING(DEPARTMENT_ID)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY ROLLUP(JOB_ID, DEPARTMENT_ID)
ORDER BY JOB_ID, DEPARTMENT_ID;

SELECT CASE
  WHEN GROUPING(JOB_ID) = 0 THEN JOB_ID
  WHEN GROUPING(JOB_ID) = 1 THEN '총합'
  END JOB_ID,
  COUNT(*) AS CNT,
  GROUPING(JOB_ID)
FROM EMPLOYEES
GROUP BY ROLLUP(JOB_ID)
ORDER BY JOB_ID;

그룹 함수 정리

ROLLUP(A) : A 그룹핑 + 총계.

ROLLUP(A, B) : A 그룹핑 + B 그룹핑 + A 소계 + 총계.

CUBE(A, B) : A와 B의 모든 그룹핑 조합.

GROUPING SETS(A, B) : A 그룹핑 + B 그룹핑.

GROUPING SETS(A, B, ()) : A 그룹핑 + B 그룹핑 + 총계.

뷰

뷰(`VIEW`)

SQL 쿼리 결과를 기반으로 만들어지는 가상의 테이블이다. 실제 데이터를 저장하지 않고 테이블처럼 동작한다. 복잡한 쿼리를 단순화하고 데이터 보안을 강화한다.

뷰의 장점은 다음과 같다.

복잡한 쿼리를 단순화하여 사용자가 쉽게 접근 가능.
특정 열 또는 행에 대한 접근을 제한하여 보안 강화.
기본 테이블 구조를 추상화하여 데이터 변경 시 유연성 제공.
재사용 가능한 쿼리 인터페이스 제공.

뷰의 단점은 다음과 같다.

복잡한 뷰는 성능 저하를 초래.
특정 뷰에서는 제한된 DML 작업만 가능.
기본 테이블 구조 변경 시 유지 관리 필요.

뷰의 유형은 다음과 같다.

단순 뷰 : 단일 테이블을 기반으로 함.
복합 뷰 : 여러 테이블, 함수 또는 그룹화를 포함.
물리적 뷰 : 쿼리 결과를 물리적으로 저장하여 성능 향상.

뷰 생성

CREATE VIEW 뷰_이름 AS
  SELECT 컬럼1, 컬럼2 FROM 테이블 WHERE 조건;
  
--예시
CREATE OR REPLACE VIEW EMPLOYEE_VIEW AS
  SELECT EMPLOYEE_ID, NAME, JOB_ID, DEPARTMENT_ID, SALARY
  FROM EMPLOYEES
  WHERE MOD(DEPARTMENT_ID, 2) = 0;

--뷰 조회
SELECT *
FROM EMPLOYEE_VIEW
WHERE SALARY > 5000;

뷰 업데이트

단일 테이블 기반의 뷰는 업데이트가 가능하며, 업데이트는 기본 테이블에 반영된다.

UPDATE EMPLOYEE_VIEW
SET SALARY = 6000
WHERE EMPLOYEE_ID = 101;

기본 조건은 다음과 같다.

뷰가 단일 테이블을 기반으로 해야 함.
Primary Key 또는 고유 식별 컬럼이 포함되어 있어야 함.
뷰에 포함된 열이 기본 테이블의 실제 열이어야 함.
GROUP BY, DISTINCT, 또는 집계 함수가 포함되지 않아야 함.

뷰를 업데이트 할 수 없는 경우는 다음과 같다.

그룹화(GROUP BY)가 포함된 경우.
집계 함수가 포함된 경우.
DISTINCT가 포함된 경우.
계산된 열이 포함된 경우.
다중 테이블을 포함한 경우.

뷰 삭제

DROP VIEW 뷰_이름;

DROP VIEW EMPLOYEE_VIEW;

물리적 뷰

쿼리 결과를 물리적으로 저장하여 성능을 향상시킨다. 대규모 데이터 분석 및 보고서 생성에 유용하다.

뷰 vs 테이블

테이블 : 데이터를 물리적으로 저장하며, 저장 공간이 필요.
뷰 : 가상 테이블로 데이터 저장이 없음.
테이블은 모든 DML 작업 가능하며 뷰는 제한적.

뷰의 실무 활용 사례

민감 데이터 제한.
보고서 및 대시보드용 단순화된 데이터 제공.
복잡한 멀티 조인 쿼리를 간소화.

뷰 사용 시 권장 사항

반복적인 쿼리를 단순화할 때 뷰를 사용.
실시간 작업에서는 과도한 조인 포함 뷰 사용 자제.
기본 테이블 스키마 변경 시 뷰를 점검.

시퀀스

시퀀스(`SEQUENCE`)

자동으로 고유한 숫자를 생성하는 오라클 객체이다. 주로 기본 키나 고유한 값이 필요한 컬럼에 사용하며 CREATE SEQUENCE 명령어를 통해 생성한다.

시퀀스의 특징은 다음과 같다.

고유한 숫자 생성 보장.
숫자의 증가/감소 방향 설정 가능.
START WITH, INCREMENT BY 등을 사용하여 생성 규칙 정의 가능.
캐싱(CACHE)을 사용하여 성능 최적화 가능.

시퀀스 생성

CREATE SEQUENCE sequence_name
  START WITH initial_value
  INCREMENT BY step_value
  MAXVALUE maximum_value
  CYCLE | NOCYCLE
  CACHE | NOCACHE;

--시퀀스 생성 예제
CREATE SEQUENCE EMPLOYEE_SEQ
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 99999
  NOCACHE;

NEXTVAL : 다음 값을 반환.
CURRVAL : 현재 값을 반환.

SELECT EMPLOYEE_SEQ.NEXTVAL FROM DUAL;
SELECT EMPLOYEE_SEQ.CURRVAL FROM DUAL;

시퀀스 조회

시스템 뷰를 통해 Sequence를 조회한다.

SELECT sequence_name, LAST_NUMBER
FROM user_sequences;

--시퀀스 조회 예시
SELECT SEQUENCE_NAME, LAST_NUMBER
FROM USER_SEQUENCES;

시퀀스 변경

ALTER SEQUENCE 명령어를 사용하여 변경 가능하다.

ALTER SEQUENCE EMPLOYEE_SEQ
INCREMENT BY 5;

시퀀스 삭제

DROP SEQUENCE 명령어를 사용하여 삭제한다.

DROP SEQUENCE EMPLOYEE_SEQ;

시퀀스 사용 시 유의사항

DELETE로 삭제된 값은 재사용되지 않음.
캐싱된 값은 장애 발생 시 손실 가능.
NOCACHE 사용 시 성능 저하 가능.

시퀀스 실제 활용 사례

주문 번호 자동 생성.
고객 ID, 계좌 번호 생성.
대량 데이터 삽입 시 고유 키 생성.

이슬비

이전 포스트

[데이터베이스개론·SQL] 250121

다음 포스트

[데이터베이스개론·SQL] 250122