[SQLD] SQL 활용 - 서브쿼리, 뷰, 집합연산자, 그룹함수, 윈도우함수, Top-N 쿼리, 계층쿼리

서브쿼리(Subquery)

• 서브쿼리란 하나의 쿼리 안에 존재하는 또 다른 쿼리이다.
  
  • SELECT 절 : 스칼라 서브 쿼리(Scalar Subquery). 주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다. 컬럼 대신 사용되므로 반드시 하나의 값만 반환해야 하며 그렇지 않은 경우 에러를 발생시킨다.
  • FROM 절 : 인라인 뷰(Inline View) - FROM 절 등 테이블 명이 올 수 있는 위치에 사용 가능하다.
  • WHERE 절, HAVING 절 : 중첩 서브쿼리(Nested Subquery)
• 중첩 서브쿼리 분류
  
  • 메인쿼리와의 관계에 따른 분류
    • 비연관 서브쿼리 : 서브쿼리 내에 메인쿼리의 컬럼이 존재하지 않는다.
    • 연관 서브쿼리 : 서브쿼리 내에 메인쿼리의 컬럼이 존재한다.
  • 반환하는 데이터 형태에 따른 분류
    
    • 단일 행 서브쿼리 : 항상 1건 이하의 결과만 반환하며, 단일 행 비교 연산자(=, <, >, <=, >=, <>)와 함께 사용한다.
    • 다중 행 서브쿼리 : 2건 이상의 행을 반환하며, 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)와 함께 사용한다.
    • 다중 컬럼 서브쿼리 : 여러 컬럼의 데이터를 반환한다.

 

뷰(View)

• 뷰란 특정 SELECT 문에 이름을 붙여서 재사용이 가능하도록 저장해놓은 오브젝트이다.
• 뷰는 가상 테이블로 실제 데이터를 저장하지 않고 해당 데이터를 조회해오는 SELECT 문만 가지고 있다.

뷰의 특징

• 보안성 : 보안이 필요한 컬럼이 가진 테이블일 경우 해당 컬럼을 제외한 별도의 뷰를 생성하여 제공함으로써 보안을 유지할 수 있다.
• 독립성 : 테이블 스키마가 변경되었을 경우 애플리케이션은 변경하지 않고 관련 뷰만 수정한다.
• 편리성 : 복잡한 쿼리 구문을 뷰명으로 단축시킴으로써 가독성을 높이고 편리하게 사용할 수 있다.

 

집합 연산자

• 집합 연산자란 각 쿼리의 결과 집합을 가지고 연산을 하는 명령어이다.
  
  • UNION ALL : 합집합 (중복 제거 X)
  • UNION : 합집합 (중복 제거)
  • INTERSECT : 교집합
  • MINUS/EXCEPT : 앞에 있는 쿼리의 결과 집합에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합 (중복 제거)

 

그룹 함수

• 그룹 함수란 데이터를 GROUP BY하여 나타낼 수 있는 데이터를 구하는 함수이다.

집계 함수

• COUNT, SUM, AVG, MAX, MIN

소계(총계) 함수

• ROLLUP : 소그룹 간의 소계 및 총계를 계산하는 함수이다.
  
  • ROLLUP (A) : A로 그룹핑 / 총합계
  • ROLLUP (A, B) : A, B로 그룹핑 / A로 그룹핑 / 총합계
  • ROLLUP (A, B, C) : A, B, C로 그룹핑 / A, B로 그룹핑 / A로 그룹핑 / 총합계
• CUBE : 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수로, 조합할 수 있는 모든 그룹에 대한 소계를 집계한다.
  
  • CUBE (A) : A로 그룹핑 / 총합계
  • CUBE (A, B) : A, B로 그룹핑 / A로 그룹핑 / B로 그룹핑 / 총합계
  • CUBE (A, B, C) : A, B, C로 그룹핑 / A, B로 그룹핑 / A, C로 그룹핑 / B, C로 그룹핑 / A로 그룹핑 / B로 그룹핑 / C로 그룹핑 / 총합계
• GROUPING SETS : 특정 항목에 대한 소계를 계산하는 함수로, 인자값으로 ROLLUP이나 CUBE를 사용할 수 있다.
  
  • GROUPING SETS (A, B) : A로 그룹핑 / B로 그룹핑
  • GROUPING SETS (A, B, ( )) : A로 그룹핑 / B로 그룹핑 / 총합계
  • GROUPING SETS (A, ROLLUP(B)) : A로 그룹핑 / B로 그룹핑 / 총합계
  • GROUPING SETS (A, ROLLUP(B, C)) : A로 그룹핑 / B, C로 그룹핑 /B로 그룹핑 / 총합계
  • GROUPING SETS (A, B, ROLLUP(C)) : A로 그룹핑 / B로 그룹핑 / C로 그룹핑 / 총합계
  • ROLLUP 함수는 인수의 순서에 따라 결과가 달라지며 CUBE 함수와 GROUPING SETS 함수는 인수의 순서가 바뀌어도 같은 결과를 출력한다.
• GROUPING : ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰이며 소계를 나타내는 Row를 구분할 수 있게 해준다. GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트를 출력할 수 있다.

 

윈도우 함수

• 윈도우 함수란 OVER 키워드와 함께 사용되며, 순위 함수 / 집계 함수 / 행 순서 함수 / 비율 함수로 나뉜다.
• 순위 함수
  
  • RANK : 순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다. (1, 2, 2, 4, 5, 5, 7 …)
  • DENSE_RANK : 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. (1, 2, 2, 3, 4, 4, 5 …)
  • ROW_NUMBER : 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다. (1, 2, 3, 4, 5, 6, 7 …)
• 집계 함수
  
  • SUM : 합계 (인자값으로 숫자형만)
    • Oracle의 경우 OVER 절 내에 ORDER BY 절을 써서 데이터의 누적값을 구할 수 있다.
    • SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주면, RANGE UNBOUNDED PRECEDING 구문이 없어도 누적합이 된다.
  • MAX : 최댓값
  • MIN : 최소값
  • AVG : 평균값
  • COUNT : 건수
• 행 순서 함수
  
  • FIRST_VALUE : 파티션별 가장 선두에 위치한 데이터를 구하는 함수
  • LAST_VALUE : 파티션 별 가장 끝에 위치한 데이터를 구하는 함수
  • LAG(컬럼명, 숫자) : 파티션별로 특정 수만큼 앞선 데이터를 구하는 함수. 두 번째 인자값을 생략하면 디폴트는 1이다.
  • LEAD(컬럼명, 숫자) : 파티션별로 특정 수만틈 뒤에 있는 데이터를 구하는 함수. 두 번째 인자값을 생략하면 디폴트는 1이다.

• 비율 함수
  
  • RATIO_TO_REPORT : 파티션별 합계에서 차지하는 비율을 구하는 함수
  • PERCENT_RANK : 해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
  • CUME_DIST : 해당 파티션에서 누적 백분율을 구하는 함수로 결과값은 0보다 크고 1보다 작거나 같은 값을 가짐
  • NTILE : 주어진 수만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수. 할당할 행이 남을 경우 맨 앞의 그룹부터 하나씩 더 채워진다.

 

Top-N 쿼리

• ROWNUM
  • 수도(Pseudo) 컬럼으로 실제로 존재하지 않는 가짜 컬럼이라고 할 수 있다.
  • 행이 반환될 때마다 순번이 1씩 증가하기 때문에 WHERE ROWNUM = 5와 같은 건너뛰기 조건은 성립할 수 없다. 항상 < 조건이나, <= 조건으로 사용해야 한다.
  • FROM 절에서 ORDER BY 조건을 걸어줘야 한다.
• 윈도 함수의 순위 함수
  
  • ROW_NUMBER
  • RANK
  • DENSE_RANK

 

계층 쿼리

• 계층 쿼리란 테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다.
  
  • LEVEL : 현재의 DEPTH를 반환한다. 루트 노드는 1이 된다.
  • SYS_CONNECT_BY_PATH : 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수이다.
  • START WITH : 경로가 시작되는 루트 노드를 생성해주는 절이다.
  • CONNECT BY : 루트노트로부터 자식 노드를 생성해주는 절이다. 조건에 만족하는 데이터가 없을 때까지 노드를 생성한다.
  • PRIOR : 바로 앞에 있는 부모 노드의 값을 반환한다.
  • CONNECT_BY_ROOT : 루트 노드의 주어진 컬럼 값을 반환한다.
  • CONNECT_BY_ISLEAF : 가장 하위 노드인 경우 1을 반환하고, 그 외에는 0을 반환한다.
  • ORDER BY 절을 사용하면 계층과 상관없이 전체 정렬이 되기 때문에 계층 구조를 유지할 수 없다. 대신 ORDER SIBLINGS BY 절을 써서 같은 레벨끼리 정렬을 수행하기 때문에 계층 구조를 유지할 수 있다.

 

Reference

• 2024 SD에듀 유선배 SQL개발자(SQLD) 과외노트