728x90
반응형
  • 서브쿼리
    • 서브쿼리의 종류
      • 동작 방식에 따라: 비연관 서브쿼리, 연관 서브쿼리
      • 위치에 따라: 스칼라 서브쿼리, 인라인뷰, where 절 서브쿼리
  • 집합함수: UNION, UNION ALL, INTERSECT, MINUS
  • 그룹함수
    • COUNT, AVG, SUM, MIN/MAX, VARIANCE, STDDEV
    • GROUP BY FUNCTION: GROUPING SETS(), ROLLUP(), CUBE()
  • 윈도우함수
    • 그룹함수 형태(OVER 절 사용): SUM OVER, AVG OVER, MIN/MAX OVER, COUNT OVER
    • 순위 관련 함수 → ORDER BY 필수
      • RANK(순위): RANK() WITHIN GROUP(), RANK() OVER(), DENSE_RANK(), ROW_NUMBER()
      • LAG, LEAD
      • FiRST_VALUE, LAST_VALUE
      • NTILE
    • 비율 관련 함수: RATIO_TO_REPORT, CUME_DIST, PERCENT_RANK
  • Top N 쿼리
    • ROWNUM
    • FETCH
  • 계층형 질의: START WITH [시작조건] CONNECT BY PRIOR [연결조건]
    • 계층형 질의 가상 컬럼: LEVEL, CONNECT_BY_ISLEAF
    • 계층형 질의 가상 함수: CONNECT_BY_ROOT 컬럼명, SYS_CONNECT_BY_PATH(컬럼, 구분자), ORDER SIBLINGS BY 컬럼
  • PIVOT & UNPVIOT
  • 정규표현식
    • REGEXP_REPLACE
    • REGEXP_SUBSTR
    • REGEXP_INSTR
    • REGEXP_LIKE
    • REGEXP_COUNT

서브 쿼리

  • 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문으로, 반드시 괄호로 묶어야 한다.
  • GROUP BY 절에서는 활용 불가능
  • 주의 사항
    • 특별한 경우(TOP-N 분석 등)을 제외하고는 서브 쿼리절에 ORDER BY 절을 사용 불가
    • 단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자 선택 중요

서브쿼리의 종류

  • 동작하는 방식에 따라
    • 비연관 서브쿼리: 서브쿼리가 메인 쿼리 컬럼을 가지고 있지 않은 형태의 서브쿼리
      • 주로 메인쿼리에 값을 제공하기 위한 목적
    • 연관 서브쿼리: 서브쿼리가 메인 쿼리 컬럼을 가지고 있는 형태의 서브쿼리
  • 위치에 따라
    • 스칼라 서브쿼리
      • SELECT 절에서 사용하는 서브쿼리로
      • 하나의 컬럼처럼 표현하기 위해 사용
      • 각 행마다 스칼라 서브쿼리 결과가 하나여야 함(단일행 서브쿼리 형태)
      • 조인의 대체 연산
      • 대체적으로 성능 별로
      -- EMP의 각 직원의 사번, 이름과 부서이름을 출력(부서이름을 스칼라 서브쿼리로)
SELECT EMPNO, ENAME, (SELECT DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
FROM EMP E
WHERE DEPTNO = 10;
    • 인라인 뷰
      • FROM 절에 사용하는 서브쿼리
      • 쿼리 안의 뷰의 형태로 테이블처럼 조회할 데이터를 정의하기 위해 사용
      • 다른 테이블과 조인 시 반드시 테이블 별칭 명시
      • 서브쿼리 결과를 메인 쿼리의 어느 절에서도 사용할 수 있음
      • 모든 연산자 사용 가능
      -- EMP 테이블에서 부서별 최대 급여자를 출력하되, 최대 급여와 함께 출력
SELECT E.EMPNO, E.ENMAME, E.SAL, I.MAX_SAL
FROM EMP E, (SELECT DEPTNO, MAX(SAL) AS MAX_SAL
                         FROM EMP
                         GROUP BY DEPTNO) I
WHERE E.DEPTNO = I.DEPTNO
            AND E.SAL = I.MAX_SAL;
    • WHERE 절 서브 쿼리
      • 단일행 서브쿼리: 서브쿼리 결과가 1개의 행이 리턴되는 형태
      • 다중행 서브쿼리: 서브쿼리 결과가 여러 행이 리턴되는 형태
        • 비교 연산자 사용 불가
        • 다중행 서브쿼리 연산자 사용 (IN, > ANY, < ANY, > ALL, < ALL)
      • 다중컬럼 서브쿼리: 서브쿼리 결과가 여러 컬럼이 리턴되는 형태
        • 대소 비교 전달 불가
        • SQL Server에서는 다중컬럼 서브쿼리를 지원하지 않는다.
      • 상호연관 서브쿼리: 메인쿼리와 서브쿼리의 비교를 수행하는 형태(비교할 집단이나 조건은 서브쿼리에 명시
      -- 대소 비교할 컬럼을 메인쿼리에, 일치 조건을 서브 쿼리에 전달
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP E1
WHERE SAL > (SELECT AVG(SAL)
            FROM E2
            WHERE E1.DEPTNO = E2.DEPTNO
            GROUP BY DEPTNO);

집합 연산자

  • SELECT 문 결과를 하나의 집합으로 간주, 그 집합에 대한 합집합, 교집합, 차집합 연산
  • 두 집합의 컬럼이 동일하게 구성되어야 함(각 컬럼의 데이터 타입과 순서 일치 필요)
  • 전체 집합의 데이터 타입과 컬럼명은 첫번째 집합에 의해 결정됨

집합 연산자의 종류

  • 합집합
    • UNION: 중복된 데이터를 제거하기 위해 내부적으로 정렬 수행
    • UNION ALL: 중복된 데이터도 전체 출력
  • 교집합
    • INTERSECT: 두 집합의 교집합(공통으로 있는 행) 출력
  • 차집합
    • MINUS(혹은 EXCEPT): 두 집합의 차집합(한 쪽 집합에만 존재하는 행) 출력

집합 연산자 사용 시 주의 사항

  • 두 집합의 컬럼 수 일치
  • 두 집합의 컬럼 순서 일치
  • 두 집합의 각 컬럼의 데이터 타입 일치
  • 각 컬럼의 사이즈는 달라도 됨
  • 집합 연산자를 사용한 SQL의 ORDER BY절은 최종결과를 정렬하며, 가장 마지막 줄에 한번만 사용할 수 있다.

그룹 함수

  • 숫자함수 중 여러 값을 전달하여 하나의 요약값을 출력하는 다중행 함수
  • GROUP BY 절에 의해 그룹별 연산 결과를 리턴 함
  • 반드시 한 컬럼만 전달
  • NULL은 무시하고 계산

그룹함수의 종류

  • COUNT
    • 행의 수를 세는 함수
    • 대상 컬럼은 * 또는 단 하나의 컬럼만 전달 가능(* 사용 시 모든 컬럼의 값이 널일 때만 COUNT 제외)
    • 문자, 숫자, 날짜 컬럼 모두 전달 가능
    • 행의 수를 세는 경우 NOT NULL 컬럼을 찾아 세는 것이 좋음(PK 컬럼)
    • COUNT(*) 함수는 조건절이 거짓일 때 0을 반환
  • SUM: 총 합 출력
  • AVG: 평균 출력, NULL을 제외한 대상의 평균을 리턴하므로 전체 대상 평균 연산 시 주의
  • MIN/MAX
    • 최대, 최소 출력
    • 날짜, 숫자 문자 모두 가능(오름차순 순서대로 최소, 최대 출력)
  • VARIANCE/STDDEV: 평균과 표준 편차
  • GROUP BY FUNCTION: GROUP BY 절에 사용하는 함수로 여러 GROUP BY 결과를 동시에 출력(합집합)하는 기능
    • GROUPING SETS(A, B, ...)
      • A별, B별 그룹 연산 결과 출력
      • 나열 순서 중요 X
      • 전체 총계 출력 → NULL 혹은 () 사용GROUPING SETS(A, B, …)
    • ROLLUP(A, B)
      • A별, B별, (A,B)별, 전체 그룹 연산 결과 출력됨
      • 나열 순서 중요 X
      • 기본적으로 전체 총 계가 출력됨
    • CUBE(A, B)
      • A별, (A,B)별, 전체 그룹 연산 결과 출력
      • 나열 대상의 순서가 중요
      • 기본적으로 전체 총 계가 출력됨

윈도우 함수(WINDOW FUNCTION)

  • 원래는 하나의 행을 읽으면 다른 행을 통해서 알 수 있는 정보는 함께 출력하거나 비교가 불가능
  • -- 에러(전체를 출력하는 컬럼과 그룹함수 결과는 함께 출력할 수 없음) SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) FROM EMP;
  • ⇒ 서브 쿼리 or 조인으로 극복 → SELECT 여러개.. 성능 저하
  • ⇒ 서로 다른 행의 비교나 연산을 위해 만든 함수
  • GROUP BY를 쓰지 않고 그룹 연산 가능 → by PARTITION BY
SELECT 윈도우함수([대상]) OVER([PARTITION BY 컬럼]
    [ORDER BY 컬럼 ASC|DESC]
    [ROWS|RANGE BETWEEN A AND B]);
  • PARTITION BY 절: 출력할 총 데이터 수 변화없이 그룹연산 수행할 GROUP BY 컬럼
  • ORDER BY 절
    • RANK의 경우 필수(정렬 컬럼 및 정렬 순서에 따라 순위 변화)
    • SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용
  • ROWS|RANGE BETWEEN A AND B
    • 연산 범위 설정
    • ORDER BY 절 필수

그룹 함수의 형태

→ OVER 절을 사용하여 윈도우 함수로 사용 가능

  • SUM OVER(): 전체 총 합, 그룹별 총 합 출력 가능
  • -- 서브쿼리 사용(스칼라 서브쿼리) -> 성능 저하 SELECT EMPNO, ENAME, SAL, DEPTNO, (SELECT SUM(SAL) FROM EMP) AS TOTAL FROM EMP; -- 윈도우 함수 사용 SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) OVER() AS TOTAL FROM EMP;
  • AVG OVER()
  • MIN/MAX OVER()
  • COUNT
  • 윈도우함수의 연산 범위: 집계 연산 시 행의 범우 설정 가능
    • ROWS, RANGE 차이
      • ROWS: 값이 같더라도 각 행씩 연산
      • RANGE: 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산(DEFAULT)
    • BETWEEN A AND B
      • 시작점 정의
        • CURRENT ROW: 현재행부터
        • UNBOUNDED PRECEDING: 처음부터 (DEFAULT)
        • N PRECEDING: N 이전부터
      • 마지막 시점 정의
        • CURRENT ROW: 현재행부터 (DEFAULT)
        • UNBOUNDED FOLLOWING: 마지막까지
        • N FOLLOWING: N 이후까지

순위 관련 함수

  • RANK(순위)
    • RANK WITHIN GROUP: 특정값에 대한 순위를 확인하는 함수로, 일반 함수이다. + ORDER BY 필수
    • SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼 ASC|DESC);
    • RANK() OVER()
      • 전체 중/특정 그룹 중 값의 순위 확인
      • ORDER BY 절 필수
      • 순위를 구할 대상을 ORDER BY 절에 명시(여러 개 나열 가능)
      • 그룹 내 순위 구할 시 PARTITION BY 절 사용
      SELECT RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);
    • DENSE_RANK: 누적 순위로, 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
    • ROW_NUMBER: 연속된 행 번호로, 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴
  • LAG, LEAD
    • 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD) 가져오기
    • ORDER BY 절 필수
    SELECT LAG(컬럼, [N], OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);
  • FIRST_VALUE, LAST_VALUE
    • 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
    • 순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
    • PARTITION BY, GROUP BY 생략 가능
  • NTILE
    • 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
    • 그룹 번호가 리턴됨
    • ORDER BY 필수
    • 행 수가 정확하게 나누어 떨어지지 않을 시 최대한 번호가 작은 쪽부터 채워가며 분포를 비슷하게 만듦
    • PARTITION BY를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리 가능
    • SELECT NTILE(N) OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);

비율관련 함수

  • RATIO_TO_REPORT: 각 값의 비율 리턴
    • ORDER BY 불가
    RATIO_TO_REPORT(대상) OVER([PARTITION BY 컬럼])
  • CUME_DIST: 각 값의 누적 비율 리턴
    • ORDER BY를 사용하여 누적비율을 구하는 순서 정할 수 있음
    • ORDER BY 필수
    • 특정 값이 아닌 행의 비율 의미
    CUME_DIST() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);
  • PERCENT_RANK: PERCENTILE(분위수) 출력
    • 전체 COUNT 중 상대적 위치 출력(0~1 범위 내)
    • ORDER BY 필수
    PERCENT_RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC);

Top N 쿼리

  • 페이징 처리를 효과적으로 수행하기 위해 사용
  • 전체 결과에서 특정 N개 추출

방법

  • ROWNUM
    • 출력된 데이터 기준으로 행 번호 부여
    • 절대적인 행 번호가 아닌 가상의 번호이므로 특정 행을 지정할 수 없음(=연산 불가)
    • 첫번째 행이 증가한 이후 할당되므로 ‘>’ 연산 사용 불가(0은 가능) ⇒ 1을 포함하지 않는 ROWNUM 연산은 불가
    -- EMP 테이블에서 급여가 높은 순서대로 4 ~ 6번째 해당하는 직원 정보 출력

-- 에러: 시작값(1)이 정의되지 않았으므로 1을 건너띄고 그 다음 행번호에 대한 추출 불가
SELECT *
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM BETWEEN 4 AND 6
ORDER BY SAL DESC;

-- 해결: 인라인 부에서 각 행마다의 순위를 직접 부여
SELECT *
FROM (SELECT ROWNUM AS RN, A.*
            FROM (SELECT *
                        FROM EMP
                        ORDER BY SAL DESC) A
            ) B
WHERE RN BETWEEN 4 AND 6
ORDER BY SAL DESC;

-- 해결: 윈도우 함수의 RANK 사용
SELECT *
FROM (SELECT
                EMP.*,
                RANK() OVER(ORDER BY SAL DESC) AS RN -- ROWUM과 동일
            FROM EMP) A
WHERE RN BETWEEN 4 AND 6
ORDER BY SAL DESC;
  • FETCH 절
    • 출력될 행의 수를 제한하는 절
    • ORACLE 12C 이상부터 제공
    • SQL Server 사용 가능
    • ORDER BY 절 뒤에서 사용(내부 파싱 순서도 ORDER BY 뒤)
    SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
OFFSET N {ROW|ROWS}
FETCH {FIRST|NEXT} N {ROW|ROWS} ONLY
    • OFFSET: 건너뛸 행의 수
    • FIRST: OFFSET을 쓰지 않았을 때 처음부터 N 행 출력 명명
    • NEXT: OFFSET을 사용했을 경우 제외한 행 다음부터 N행 출력 명명
    • ROW|ROWS: 행의 수에 따라 하나일 경우 단수, 여러값이면 복수형(특별히 구분하지 않아도 됨)

계층형 질의

  • 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층(depth)을 표현하는 기법
  • PRIOR의 위치에 따라 연결하는 데이터가 달라짐
SELECT
FROM 테이블명
START WITH 시작 조건      -- 시작점을 지정하는 조건 전달
CONNECT BY PRIOR 연결조건 -- 시작점을 기준으로 하위 계급을 찾아가는 조건

ex)

SELECT D.*, LEVEL
FROM DEPT2 D
START WITH PDEPT IS NULL           -- LEVEL이 1일 조건
CONNECT BY PRIOR DCODE = PDEPT;    -- 먼저 정해져야 하는 값의 방향에 PRIOR 전달
  • 계층형 질의 가상 컬럼
    • LEVEL: 각 DEPTH를 표현(시작점부터 1)
    • CONNECT_BY_ISLEAF: LEAF NODE(최하위노드) 여부(참: 1, 거짓: 0)
  • 계층형 질의 가상 함수
    • CONNECT_BY_ROOT 컬럼명: 루트노드의 해당 컬럼명의 값 출력
    • SYS_CONNECT_BY_PATH(컬럼, 구분자): 이어지는 경로 출력
    • ORDER SIBLINGS BY 컬럼: 같은 LEVEL일 경우 정렬 수행 → 계층형으로 된 데이터값 기준으로 정렬

Oracle과 SQL Server의 계층형 질의문 비교

- SQL Server의 계층형 질의문

  • CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.
  • 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.

- Oracle의 계층형 질의문

  • WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는 데 활용된다.
  • PRIOR 키워드는 SELECT, WHERE, CONNECT BY 절 모두에서 사용할 수 있으며 ‘PRIOR 자식 = 부모’ 형태로 사용하면 순방향 전개로 수행된다. 반대는 부모 역방향 전개

PIVOT 절과 UNPIVOT 절

데이터의 구조

  • LONG DATA(Tidy data)
  • WIDE DATA(Cross table)
    • 행과 컬럼에 유의미한 정보 번달을 목적으로 작성하는 교차표
    • 하나의 속성값이 여러 컬럼으로 분리되어 표현
    • 값이 추가될 때마다 컬럼이 추가돼야 하므로 비효율적!
    • 조인 연산이 불가
    • 주로 데이터 요약 목적

데이터 구조 변경

  • PIVOT: LONG → WIDE
    • STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼의 정의가 중요
    • FROM 절에 STACK, UNSTACK, VALUE 컬럼명만 정의. → 필요 시 서브쿼리 사용하여 필요 컬럼 제한
      • FROM 절에 선언된 컬럼 중 PIVOT 절에서 선언한 VALUE 컬럼, UNSTACK 컬럼을 제외한 모든 컬럼은 STACK 컬럼이 됨
    • PIVOT 절에 UNSTACK, VALUE 컬럼명 정의
    • PIVOT 절 IN 연산자에 UNSTACK 컬럼 값을 정의
    • SELECT * FROM 테이블명 또는 서브쿼리 PIVOT (VALUE컬럼명 FOR UNSTACK컬럼명 IN (값1, 값2, ...));
  • UNPIVOT: WIDE → LONG
    • STACK 컬럼: 이미 UNSTACK 되어 있는 여러 컬럼을 하나의 컬럼으로 STACK 시 새로 만들 컬럼명(사용자 정의) (UNSTACK → STACK)
    • VALUE 컬럼: 교차표에서 셀 자리(VALUE) 값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명(사용자 정의)
    • 값1, 값2, …: 실제 UNSTACK 되어 있는 컬럼이름들
    • SELECT * FROM 테이블명 또는 서브쿼리 UNPIVOT (VALUE컬럼명 FOR STACK컬럼명 IN (값1, 값2, ...));

정규 표현식

  • 문자열의 공통된 규칙을 보다 일반화 하여 표현하는 방법
  • 정규표현식 종류
  • 정규 표현식 사용 가능한 문자함수 제공
    • REGEXP_REPLACE(대상, 찾을문자열, [바꿀문자열], [검색위치], [발견횟수], [옵션])
      • 정규식 표현을 사용한 문자열 치환 가능
      • 특징
        • 바꿀문자열 생략 시 문자열 삭제
        • 검색위치 생략 시 1
        • 발견횟수 생략 시 0(모든)
      • 옵션
        • c: 대소 구분 검색
        • i: 대소 구분 X 검색
        • m: 패턴을 다중라인으로 선언 가능
    • REGEXP_SUBSTR(대상, 패턴, [검색위치], [발견횟수], [옵션], [추출그룹])
      • 정규식 표현식을 사용한 문자열 추출
      • 특징
        • 검색위치 생략 시 1
        • 발견횟수 생략 시 1
        • 추출 그룹은 서브패턴을 추출 시 그 중 추출할 서브패턴 번호
        • REGEXP_REPLACE와 옵션 동일
    • REGEXP_INSTR(대상, 찾을문자열, [시작위치], [발견횟수])
      • 주어진 문자열에서 특정 패턴의 시작 위치를 반환
      • 특징
        • 시작위치 생략 시 처음부터 확인(기본값: 1)
        • 발견횟수 생략 시 처음 발견된 문자열 위치 리턴
    • REGEXP_LIKE(대상, 찾을문자열, [옵션])
      • 주어진 문자열에서 특정패턴을 갖는 경우 반환(WHERE 절만 사용 가능)
      • 특징
        • REGEXP_REPLACE와 옵션 동일
    • REGEXP_COUNT(대상, 찾을문자열, 시작위치, [옵션])
      • 주어진 문자열에서 특정패턴의 횟수를 반환
      • 특징
        • REGEXP_REPLACE와 옵션 동일
728x90
반응형

'Database' 카테고리의 다른 글

[Database] SQL Trigger  (0) 2024.10.05
[Database] Stored Function & Stored Procedure  (2) 2024.10.05
[Database] SQL 기초  (0) 2024.10.05
[Database] Relational Database  (0) 2024.10.05
[Database] 데이터베이스 기본 개념  (1) 2024.10.05

티스토리툴바