[SQLD] SQL 개발자 공부하기 Day 9 : 윈도우 함수 / Top N 쿼리

목차

1. 윈도우 함수 (Window Function)
2. Top N 쿼리

---

1. 윈도우 함수 (Window Function)

 

 

오늘도 Live SQL을 사용해서 공부를 할 것이다.

 

 

위의 표는 내가 임의로 만든 표이다. 이것을 사용해서 예시를 만들 것이다.

 

 

윈도우 함수 Window Fuction

‣ 행과 행 간의 관계를 정의하기 위해서 제공되는 함수.

‣ 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

 

(1) 윈도우 함수 구조

구조	설명
ARGUMENTS(인수)	‣ 윈도우 함수에 따라서 0~N개의 인수를 설정한다.
PARTITION BY	‣ 전체 집합을 기준에 의해 소그룹으로 나눈다.
ORDER BY	‣ 어떤 항목에 대해서 정렬한다.
WINDOWING	‣ 행 기준의 범위를 정한다.

 

 

(2) WINDOWING

구조	설명
ROWS	‣ 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다.
RANGE	‣ 논리적인 주소에 의해 행 집합을 지정한다.
BETWEEN~AND	‣ 윈도우의 시작과 끝의 위치를 지정한다.
UNBOUNDED PRECEDING	‣ 윈도우의 시작 위치가 첫 번째 행임을 의미한다.
UNBOUNDED FOLLOWING	‣ 윈도우의 마지막 위치가 마지막 행임을 의미한다.
CURRENT ROW	‣ 윈도우 시작 위치가 현재 행임을 의미한다.

 

 

---

 

SELECT EMPNO, ENAME, SAL,
 SUM(SAL) OVER( ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTAL
FROM EMP;

 

 

‣  TOTSAL은 처음 행부터 마지막 행까지 SAL의 합계를 나타낸다.

 

 

---

 

SELECT EMPNO, ENAME, SAL,
 SUM(SAL) OVER( ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTSAL
FROM EMP;

 

 

‣ TOTSAL은 처음 행부터 현재 행까지의 합계를 계산한다.

 

 

---

 

SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER( ORDER BY SAL ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) TOTSAL FROM EMP;

 

 

‣ TOTSAL은 현재 행부터 마지막 행까지의 합계를 계산한다.

 

 

(3) 순위 함수 Rank Function

‣ 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.

순위 함수	설명
RANK	‣ 특정 항목 및 파티션에 대해서 순위를 계산한다. (동일한 순위는 동일한 값.)
DENSE_RANK	‣ 특정 항목 및 파티션에 대해서 순위를 계산한다. (동일한 순위는 하나의 건 수.)
ROW_NUMBER	‣ 특정 항목 및 파티션에 대해서 순위를 계산한다. (동일한 순위는 고유의 순위.)

 

 

---

 

SELECT ENAME, SAL,
 RANK() OVER(ORDER BY SAL DESC) ALL_RANK,
 RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

 

 

‣ ALL_RANK는 SAL을 내림차순으로 조회해서 순위를 계산한다.

‣ JOB_RANK는 JOB으로 파티션을 만들고 JOB별로 순위를 매긴다. 

 

 

---

 

SELECT ENAME, SAL,
 RANK() OVER(ORDER BY SAL DESC) ALL_RANK,
 DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

 

 

‣ DENSE_RANK는 동일한 순서는 하나의 건 수로 인식해서 조회한다.

반응형

---

 

SELECT ENAME, SAL,
 RANK() OVER(ORDER BY SAL DESC) ALL_RANK,
 ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUM
FROM EMP;

 

 

‣ 동일한 순위에 대해서 고유의 순위를 부여한다.

 

 

(4) 집계 함수 AGGREGATE Function

집계 함수	설명
SUM	‣ 파티션 별로 합계를 계산한다.
AVG	‣ 파티션 별로 평균을 계산한다.
COUNT	‣ 파티션 별로 행 수를 계산한다.
MAX / MIN	‣ 파티션 별로 최댓값/최솟값을 계산한다.

 

 

(5) 행 순서 관련 함수

‣ 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있다.

‣ 특정 위치의 행을 출력할 수 있다.

행 순서	설명
FIRST_VALUE	‣ 파티션에서 가장 처음에 나오는 값을 구한다.
LAST_VALUE	‣ 파티션에서 가장 나중에 나오는 값을 구한다.
LAG	‣ 이전 행을 가지고 온다.
LEAD	‣ 윈도우에서 특정 위치의 행을 가지고 온다.

 

 

---

 

SELECT DEPTNO, ENAME, SAL,
 FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_A
FROM EMP;

 

 

‣ DEPT_A는 DEPTNO 별로 파티션을 만들고 파티션 내에서 SAL이 가장 높은 사람의 ENAME을 출력한다.

 

 

---

 

SELECT DEPTNO, ENAME, SAL,
 LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_A
FROM EMP;

 

 

‣ DEPT_A는 DEPTNO 별로 파티션을 만들고 파티션 내에서 SAL이 가장 낮은 사람의 ENAME을 출력한다.

 

 

---

 

SELECT DEPTNO, ENAME, SAL,
 LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;

 

 

‣ PRE_SAL은 SAL을 내림차순 한 후 해당 행의 바로 앞에 위치한 행의 SAL의 값을 나타낸다.

 

 

---

 

SELECT DEPTNO, ENAME, SAL,
 LEAD(SAL,2) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;

 

 

‣ PRE_SAL은 SAL을 내림차순 한 후 해당 행의 2번째에 위치한 행의 SAL의 값을 나타낸다.

 

 

(6) 비율 관련 함수

‣ 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있다.

비율 함수	설명
CUME_DIST	‣ 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다.
PERCENT_RANK	‣ 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회한다.
NTILE	‣ 파티션 별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 조회한다.
RATIO_TO_REPORT	‣ 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회한다.

 

728x90

---

 

 

SELECT DEPTNO, ENAME, SAL,
 PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERCENT_SAL
FROM EMP;

 

 

‣ PERCENT_SAL은 DEPTNO로 파티션을 나눈 후 SAL값의 가장 큰 값을 0으로 칭하고 제일 작은 값을 1로 칭한 후, 같은 DEPTNO에서 순서별 백분율을 나타낸다.

 

 

---

 

SELECT DEPTNO, ENAME, SAL,
 NTILE(5) OVER(ORDER BY SAL DESC) AS N_TILE
FROM EMP;

 

 

‣ N_TILE은 SAL이 높은 순으로 5등분으로 분할한다.

 

 

 

---

2. Top N 쿼리

 

Top N 쿼리

(1) ROWNUM

‣ ORACLE 데이터베이스의 SELECT문 결과에 대해서 논리적인 일렬번호를 부여한다.

‣ 조회되는 행 수를 제한할 때 많이 사용된다.

‣ 화면에 데이터를 출력할 때 부여되는 논리적 순번이다.

‣ 페이지 단위 출력을 하기 위해서는 인라인 뷰를 사용해야 한다.

 

SELECT *
FROM EMP
WHERE ROWNUM <= 1;

 

 

‣ EMP 표에서 단 하나의 행만 나타내라.

 

---

 

SELECT *
FROM (SELECT ROWNUM LIST, ENAME FROM EMP)
WHERE ROWNUM <= 5;

 

 

‣ EMP 표에서 순번과 ENAME을 선택하고 순번이 5 이하인 행만 나타내라.

 

 

(2) ROWID

‣ ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다.

‣ SELECT 문으로 확인할 수 있다.

‣ 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다.

 

 

(3) ROWID 구조

구조 / 길이	설명
오브젝트 번호 / 1~6	‣ 오브젝트 별로 유일한 값을 가지고 있으며, 해당 오브젝트가 속해 있는 값이다.
상대 파일 번호 / 7~9	‣ 테이블스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호이다.
블록 번호 / 10~15	‣ 데이터 파일 내부에서 어느 블록에 데이터가 있는지 알려준다.
데이터 번호 / 16~18	‣ 데이터 블록에 데이터가 저장되어 있는 순서를 의미한다.