[SQLD] 2.2.4~8 SQL 활용

윈도우 함수

---

윈도우함수 개요

• 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 WINDOW FUNCTION
• 종류
  • 순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER
  • 집계(AGGREGATE) 관련 함수 : SUM, MAX, COUNT 등
  • 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
  • 선형 분석을 포함한 통계분석 함수 : CORR, COVAR_POP, STDDEV 등
• WINDOW FUCNTION SYNTAX
  • 윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.

SELECT WINDOW_FUNCTION (...) OVER ([PARTITION BY] [ORDER BY] [WINDOWING])
FROM TABLE_NAME

• PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY : 어떤 항목에 대해 순위를 지정할지 ORDER BY 절을 기술한다.
• WINDOWING : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. / SQL SERVER는 미지원

 

그룹 내 순위함수

RANK함수

• 특정 컬럼에 대한 순위를 구하는 함수다.
• 이 때 , PARTITION 내에서 순위를 구할수도 있고, 전체 데이터에 대한 순위를 구할수도 있다.
• 동일한 값에 대해서는 동일한 순위를 부여한다.
• 중복 순위 갯수만큼 다음 순위 값을 증가시킨다.
  • EX. 1, 2, 2, 4 ...
• p.326

SELECT RANK () OVER (ORDER BY COL1 DESC) AS ALL_RANK
     , RANK () OVER (PARTITION BY COL2 ORDER BY COL1 DESC) AS COL2_RANK
FROM TBL

 

DENSE_RANK 함수

• RANK와 유사하나, 동일한 순위를 하나의 건수로 취급한다.
• 동일한 순위를
• 중복 순위가 존재해도 순차적으로 다음 순위값을 표시한다.
  • EX. 1, 2, 2, 3 ...

SELECT DENSE_RANK () OVER (ORDER BY COL1 DESC) AS ALL_RANK
FROM TBL

 

ROW_NUMBER

• 동이로한 값이라도 고유한 순위를 부여한다.
• 동일한 값에 대한 순서를 관리하고 싶다면, ORDER BY 절을 적절히 활용해라.

SELECT ROW_NUMBER () OVER (ORDER BY COL1 DESC) AS ALL_RANK
FROM TBL

 

일반 집계함수

SUM 함수

• PARTITION 별 윈도우의 합을 구할 수 있다.
• p.331

SELECT SUM (COST) OVER (PARTITION BY COL1) AS SUM
FROM TBL

+ MAX(), MIN(), AVG(), COUNT()
++ RANGE

 

그룹 내 행 순서 함수

FIRST_VALUE

• 파티션별 윈도우에서 가장 먼저 나온 값을 구하는 함수
• SQL Server에서는 지원하지 않는다.
• MIN 함수를 활용해 같은 결과를 얻을 수 있다.
• 공동 등수를 인정하지 않고, 처음 나온 행만 처리한다.
  의도적으로 세부 항목을 정렬하고 싶다면, OVER() 내의 ORDER BY 절에 컬럼을 추가하거나 별도의 정렬 조건을 가진 INLINE VIEW를 사용해야 한다.

+ LAST_VALUE (MAX)

 

LAG

• 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져오는 함수
• SQL Server에서는 지원하지 않는다.

-- 하나 앞의 행
SELECT ...
     , LAG (COL1) OVER (ORDER BY ...) AS COL1
FROM TBL1

-- N번째 앞의 행, 없을경우 NULL 대신 0
SELECT ...
     , LAG (COL1, N, 0) OVER (ORDER BY ...) AS COL1
FROM TBL1

 

LEAD

• 파티션별 윈도우에서 이후 몇 번재 행의 값을 가져오는 함수
• SQL Server에서는 지원하지 않는다.

-- N번째 이후의 행 (DEFAULT 1), 없으면 NULL 대신 0
SELECT ...
     , LEAD (COL1, N, 0) OVER (ORDER BY ...) AS COL1
FROM TBL1

 

그룹 내 비율함수

RATIO_TO_REPORT

• 파티션 내 전체 SUM() 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구하는 함수
• 0 < 결과값 <= 1
• 개별 RATIO의 합을 구하면 1

SELECT ...
     , ROUND (RATIO_TO_REPORT (COL1) OVER (), 2) AS COL1
FROM TBL1

 

PERCENT_RANK

• 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 해, 값이 아닌 행의 순서별 백분율을 구하는 함수
• 0 <= 결과값 <= 1
• SQL Server는 지원하지 않는다.

 

CUME_DIST

• 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구하는 함수
• 0 < 결과값 <= 1
• SQL Server는 지원하지 않는다.

 

NTILE

• 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구하는 함수
• 전체 파티션 행 갯수를 NTILE(N)의 N개의 그룹으로 분할한다는 소리다.
  M개의 사람을 N개의 조로 나눈다고 생각ㄱ

 

TOP N 쿼리

---

ROWNUM PSEUDO COLUMN

• ORACLE의 ROWNUM 은 컬럼과 비슷한 성격의  Pseudo Column
• SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호
• 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때, WHERE 절에서 행 개수 제한하는 목적으로 사용
• ORACLE의 경우 데이터가 일부 추출된 후, 정렬작업이 일어나므로 주의해야 한다.

SELECT ...
FROM TBL_A
WHERE ROWNUM < 5

 

TOP 절

• SQL Server는 TOP절을 사용해 결과 집합으로 출력되는 행의 수를 제한할 수 있다.

TOP (Expression) [PERCENT] [WITH TIES]

SELECT TOP(5) 
      , COL_A 
      , COL_B
      ...
FROM TBL_A

• Expression : 반환할 행 수를 지정하는 숫자
• PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨
• WITH TIES : ORDER BY  절이 지정된 경우만 사용 가능, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정 가능

 

ROW LIMITING 절

• ANSI 표준 SQL 문법

[OFFSET offset {ROW | ROWS}]
[FETCH {FIRST | NEXT} [{rowcount | percent PERCENT}] {ROW | ROWS} {ONLY | WITH TIES}]

SELECT ...
FROM TBL_A
ORDER BY COL1, ...
FETCH FIRST 5 ROWS ONLY

SELECT ...
FROM TBL_B
ORDER BY COL1, ...
OFFSET 5 ROWS

• OFFSET : 건너뛸 행의 개수를 지정한다.
• FETCH : 반환할 행의 개수나 백분율을 지정한다.
• ONLY : 지정된 행의 개수나 백분율 만큼 행을 반환한다.
• WITH TIES : 마지막 행에 대한 동순위를 포함해 반환한다.

 

계층형 질의와 셀프조인

---

계층형 데이터

• 동일 테이블에 계층적으러 상위와 하위 데이터가 포함된 데이터
• ex) 사원, 조직, 메뉴
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생
• 순환관계 데이터모델은 셀프 조인이나 계층형 질의로 조회할 수 있다.

 

셀프조인

• 동일 테이블 사이의 조인
• 테이블명, 컬럼 명이 모두 동일하므로, 식별을 위해 반드시 Alias를 사용해야 한다.
• 순방향전개
• 역방향전개

 

계층형 질의

• Oracle

 SELECT ...
   FROM TALBE1
   ...
  START WITH condition
    AND condition
CONNECT BY [NOCYCLE] condition
    AND condition
[ORDER SIBLINGS BY column, ...]

• START WITH : 계층구조 전개의 시작 위치를 지정하는 구문
• CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문
•  PRIOR : CONNECT BY 절에 사용되면, 현재 읽은 컬럼을 지정
• NOCYCLE
  - 데이터를 전개하며 이미 나타났던 동일한 데이터가 전개중에 다시 나타나면, 이것을 가리켜 사이클이라고 한다.
  - 사이클이 발생한 데이터는 런타임 오류가 발생한다.
  - NOCYCLE을 추가하면 오류를 발생시키지 않고, 사이클이 발생한 이후의 데이터를 전개하지 않는다.
•