728x90
1. Window Function 개요
윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다.
WINDOW FUNCTION 종류
| 구분 | 함수 | 지원 정보 |
| 그룹 내 순위(RANK) 관련 함수 | RANK, DENSE_RANK, ROW_NUMBER | ANSI/ISO SQL 표준과 Oracle, SQL Sever등 대부분의 DBMS에서 지원 |
| 그룹 내 집계(AGGREGATE) 관련 함수 | SUM ,MAX, MIN, AVG, COUNT | ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원 SQL Server 의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지 않음 |
| 그룹 내 행 순서 관련 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | ORACLE에서만 지원 |
| 그룹 내 비율 관련 함수 |
CUME_DIST, PERCENT_RANK | ANSI/ISO SQL 표준과 Oracle, MS SQL에서 지원 |
| NTILE | ORACLE, MS SQL에서 지원 | |
| RATIO_TO_REPORT | ORACLE 에서만 지원 |
SYNTAX
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절])
FROM 테이블 명;
-- WINDOW_FUNCTION : 윈도우함수
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
--ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
--WINDOWING절은 SQL Server에서는 지원하지 않는다.WINDOWING 절 SYNTAX 상세
ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]
ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다
WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING
UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들
SELECT empno, ename, sal ,
SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;
UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들
n PRECEDING : 현재행을 기준으로 n칸 앞
n FOLLWING : 현재행을 기준으로 n칸 뒤
2. 그룹 내 순위 함수
순위 함수 요약
RANK : 1 2 2 4
DENSE_RANK : 1 2 2 3
ROW_NUMBER : 1 2 3 4
RANK 함수
- order by를 포함한 QUERY문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수
- 특정 범위(Partition) 내에서 혹은 전체 데이터에 대한 순위 구함
- 동일한 값에 대해서는 동일한 순위를 부여하고 별개의 건수로 취급한다.
- 위의 동일한 순위가 부여가 됐다면 아래 순위는 해당 (중복 개수 -1) 만큼의 순위를 건너뛰고 순위를 부여한다.
- ex) 3위가 3명이라면 다음 순위는 6위가 된다.
-- 사원 데이터에서 급여가 높은 순서와 job별로 급여가 높은 순서를 같이 출력
-- 동일 salary에서 같은 순위 부여
--## ORACLE
SELECT JOB_ID , FIRST_NAME, SALARY,
rank() OVER ( ORDER BY SALARY DESC ) all_rank,
rank() OVER ( PARTITION BY JOB_ID ORDER BY SALARY desc) job_rank
FROM hr.EMPLOYEES e ;
-- order by sal desc와 partition by job order by salary desc 가 충돌하여
-- 전체 order by sal desc 기준으로 정렬
JOB_ID |FIRST_NAME |SALARY|ALL_RANK|JOB_RANK|
----------+-----------+------+--------+--------+
AD_PRES |Steven | 24000| 1| 1|
AD_VP |Neena | 17000| 2| 1|
AD_VP |Lex | 17000| 2| 1|
SA_MAN |John | 14000| 4| 1|
SA_MAN |Karen | 13500| 5| 2|
MK_MAN |Michael | 13000| 6| 1|
FI_MGR |Nancy | 12008| 7| 1|
AC_MGR |Shelley | 12008| 7| 1|
SA_MAN |Alberto | 12000| 9| 3|
SA_REP |Lisa | 11500| 10| 1|
PU_MAN |Den | 11000| 11| 1|
SA_REP |Ellen | 11000| 11| 2|
SA_MAN |Gerald | 11000| 11| 4|
SA_REP |Clara | 10500| 14| 3|
SA_MAN |Eleni | 10500| 14| 5|
SA_REP |Peter | 10000| 16| 4|
PR_REP |Hermann | 10000| 16| 1|
SA_REP |Harrison | 10000| 16| 4|
SA_REP |Janette | 10000| 16| 4|
SA_REP |Tayler | 9600| 20| 7|
SA_REP |Patrick | 9500| 21| 8|
SA_REP |Danielle | 9500| 21| 8|
SA_REP |David | 9500| 21| 8|
FI_ACCOUNT|Daniel | 9000| 24| 1|
IT_PROG |Alexander | 9000| 24| 1|
SA_REP |Allan | 9000| 24| 11|
SA_REP |Peter | 9000| 24| 11|
SA_REP |Alyssa | 8800| 28| 13|
SA_REP |Jonathon | 8600| 29| 14|
SA_REP |Jack | 8400| 30| 15|
AC_ACCOUNT|William | 8300| 31| 1|
FI_ACCOUNT|John | 8200| 32| 2|
ST_MAN |Adam | 8200| 32| 1|
SA_REP |Christopher| 8000| 34| 16|
ST_MAN |Matthew | 8000| 34| 2|
SA_REP |Lindsey | 8000| 34| 16|
ST_MAN |Payam | 7900| 37| 3|
FI_ACCOUNT|Jose Manuel| 7800| 38| 3|
FI_ACCOUNT|Ismael | 7700| 39| 4|
SA_REP |Nanette | 7500| 40| 18|
SA_REP |Louise | 7500| 40| 18|
SA_REP |William | 7400| 42| 20|
SA_REP |Elizabeth | 7300| 43| 21|
SA_REP |Mattea | 7200| 44| 22|
SA_REP |Oliver | 7000| 45| 23|
SA_REP |Kimberely | 7000| 45| 23|
SA_REP |Sarath | 7000| 45| 23|
FI_ACCOUNT|Luis | 6900| 48| 5|
...
...
--## PostgreSQL
SELECT JOB_ID , FIRST_NAME, SALARY,
rank() OVER ( ORDER BY SALARY DESC ) all_rank,
rank() OVER ( PARTITION BY JOB_ID ORDER BY SALARY desc) job_rank
FROM hr.EMPLOYEES e ;
-- order by sal desc와 partition by job order by salary desc 가 충돌하여 partition by job
-- 기준으로 모이고 그 그룹안에서 salary 순위로 정렬
job_id |first_name |salary |all_rank|job_rank|
----------+-----------+--------+--------+--------+
AC_ACCOUNT|William | 8300.00| 31| 1|
AC_MGR |Shelley |12008.00| 7| 1|
AD_ASST |Jennifer | 4400.00| 61| 1|
AD_PRES |Steven |24000.00| 1| 1|
AD_VP |Lex |17000.00| 2| 1|
AD_VP |Neena |17000.00| 2| 1|
FI_ACCOUNT|Daniel | 9000.00| 24| 1|
FI_ACCOUNT|John | 8200.00| 32| 2|
FI_ACCOUNT|Jose Manuel| 7800.00| 38| 3|
FI_ACCOUNT|Ismael | 7700.00| 39| 4|
FI_ACCOUNT|Luis | 6900.00| 48| 5|
FI_MGR |Nancy |12008.00| 7| 1|
HR_REP |Susan | 6500.00| 50| 1|
IT_PROG |Alexander | 9000.00| 24| 1|
IT_PROG |Bruce | 6000.00| 56| 2|
IT_PROG |Valli | 4800.00| 59| 3|
IT_PROG |David | 4800.00| 59| 3|
IT_PROG |Diana | 4200.00| 62| 5|
MK_MAN |Michael |13000.00| 6| 1|
MK_REP |Pat | 6000.00| 56| 1|
PR_REP |Hermann |10000.00| 16| 1|
PU_CLERK |Alexander | 3100.00| 78| 1|
PU_CLERK |Shelli | 2900.00| 84| 2|
PU_CLERK |Sigal | 2800.00| 87| 3|
PU_CLERK |Guy | 2600.00| 93| 4|
PU_CLERK |Karen | 2500.00| 97| 5|
PU_MAN |Den |11000.00| 11| 1|
SA_MAN |John |14000.00| 4| 1|
SA_MAN |Karen |13500.00| 5| 2|
SA_MAN |Alberto |12000.00| 9| 3|
...
...-- 전체 SALARY 순위 구하는 ALL RANK 컬럼 제외, 업무별로 SALARY 순위를 구하는 JOB_RANK만 출력
-- 결과적으로 JOB과 SALARY 별로 정렬
SELECT JOB_ID , FIRST_NAME, SALARY,
rank() OVER ( PARTITION BY JOB_ID ORDER BY SALARY desc) job_rank
FROM hr.EMPLOYEES e ;
--## ORACLE
JOB_ID |FIRST_NAME |SALARY|JOB_RANK|
----------+-----------+------+--------+
AC_ACCOUNT|William | 8300| 1|
AC_MGR |Shelley | 12008| 1|
AD_ASST |Jennifer | 4400| 1|
AD_PRES |Steven | 24000| 1|
AD_VP |Lex | 17000| 1|
AD_VP |Neena | 17000| 1|
FI_ACCOUNT|Daniel | 9000| 1|
FI_ACCOUNT|John | 8200| 2|
FI_ACCOUNT|Jose Manuel| 7800| 3|
FI_ACCOUNT|Ismael | 7700| 4|
FI_ACCOUNT|Luis | 6900| 5|
FI_MGR |Nancy | 12008| 1|
HR_REP |Susan | 6500| 1|
IT_PROG |Alexander | 9000| 1|
IT_PROG |Bruce | 6000| 2|
IT_PROG |David | 4800| 3|
IT_PROG |Valli | 4800| 3|
IT_PROG |Diana | 4200| 5| --위에 3위가 두명이니 4위를 건너뛰고 바로 5위를 준다.
MK_MAN |Michael | 13000| 1|
MK_REP |Pat | 6000| 1|
PR_REP |Hermann | 10000| 1|
PU_CLERK |Alexander | 3100| 1|
PU_CLERK |Shelli | 2900| 2|
PU_CLERK |Sigal | 2800| 3|
PU_CLERK |Guy | 2600| 4|
PU_CLERK |Karen | 2500| 5|
PU_MAN |Den | 11000| 1|
SA_MAN |John | 14000| 1|
SA_MAN |Karen | 13500| 2|
SA_MAN |Alberto | 12000| 3|
SA_MAN |Gerald | 11000| 4|
SA_MAN |Eleni | 10500| 5|
SA_REP |Lisa | 11500| 1|
SA_REP |Ellen | 11000| 2|
SA_REP |Clara | 10500| 3|
SA_REP |Janette | 10000| 4|
SA_REP |Peter | 10000| 4|
SA_REP |Harrison | 10000| 4|
SA_REP |Tayler | 9600| 7| --4위가 3명이니 5,6위를 건너뛰고 7위를 부여한다.
SA_REP |David | 9500| 8|
SA_REP |Danielle | 9500| 8|
SA_REP |Patrick | 9500| 8|
SA_REP |Peter | 9000| 11|
SA_REP |Allan | 9000| 11|
SA_REP |Alyssa | 8800| 13|
SA_REP |Jonathon | 8600| 14|
...
...
--## PostgreSQL
-- ORACLE과 동일한 결과
DENSE_RANK 함수
- RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급
- 특정 범위(PARTITION) 내에서 혹은 전체 데이터에 대한 순위 구함
- 동일한 값에 대해서는 동일한 순위를 부여함
SELECT JOB_ID , FIRST_NAME, SALARY,
rank() OVER ( ORDER BY SALARY DESC ) RANK,
dense_rank() OVER ( ORDER BY SALARY DESC ) DENSE_RANK
FROM hr.EMPLOYEES e ;
--## ORACLE
JOB_ID |FIRST_NAME |SALARY|RANK|DENSE_RANK|
----------+-----------+------+----+----------+
AD_PRES |Steven | 24000| 1| 1|
AD_VP |Neena | 17000| 2| 2|
AD_VP |Lex | 17000| 2| 2|
SA_MAN |John | 14000| 4| 3| -- DENS_RANK는 RANK와 달리 2순위를 복수로 취급하지 않는다.
SA_MAN |Karen | 13500| 5| 4|
MK_MAN |Michael | 13000| 6| 5|
FI_MGR |Nancy | 12008| 7| 6|
AC_MGR |Shelley | 12008| 7| 6|
SA_MAN |Alberto | 12000| 9| 7|
SA_REP |Lisa | 11500| 10| 8|
PU_MAN |Den | 11000| 11| 9|
SA_MAN |Gerald | 11000| 11| 9|
SA_REP |Ellen | 11000| 11| 9|
SA_MAN |Eleni | 10500| 14| 10|
SA_REP |Clara | 10500| 14| 10|
SA_REP |Janette | 10000| 16| 11|
SA_REP |Peter | 10000| 16| 11|
PR_REP |Hermann | 10000| 16| 11|
SA_REP |Harrison | 10000| 16| 11|
SA_REP |Tayler | 9600| 20| 12|
SA_REP |Danielle | 9500| 21| 13|
SA_REP |David | 9500| 21| 13|
SA_REP |Patrick | 9500| 21| 13|
...
...
--## PostgreSQL
-- 위와 동일
ROW_NUMBER
- RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여함
--## ORACLE
SELECT JOB_ID , FIRST_NAME, SALARY,
rank() OVER ( ORDER BY SALARY DESC ) RANK,
dense_rank() OVER ( ORDER BY SALARY DESC ) DENSE_RANK ,
row_number() OVER ( ORDER BY SALARY DESC ) ROW_NUMBER
FROM hr.EMPLOYEES e ;
JOB_ID |FIRST_NAME |SALARY|RANK|DENSE_RANK|ROW_NUMBER|
----------+-----------+------+----+----------+----------+
AD_PRES |Steven | 24000| 1| 1| 1|
AD_VP |Neena | 17000| 2| 2| 2|
AD_VP |Lex | 17000| 2| 2| 3| -- RANK, DENSE_RANK와 다른 부분
SA_MAN |John | 14000| 4| 3| 4|
SA_MAN |Karen | 13500| 5| 4| 5|
MK_MAN |Michael | 13000| 6| 5| 6|
FI_MGR |Nancy | 12008| 7| 6| 7|
AC_MGR |Shelley | 12008| 7| 6| 8|
SA_MAN |Alberto | 12000| 9| 7| 9|
SA_REP |Lisa | 11500| 10| 8| 10|
PU_MAN |Den | 11000| 11| 9| 11|
...
...
-- ## PostgreSQL
-- 위와 동일다음글에서는 그룹 내 집계 관련 함수에 대해 알아보겠습니다.
728x90
반응형
'Oracle > SQL' 카테고리의 다른 글
| [SQL] 계층형 쿼리 ( CONNECT BY ) (0) | 2024.03.05 |
|---|---|
| [SQL] 윈도우 함수(Window Function)의 종류(4)- 비율 관련 함수(CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT) (0) | 2023.04.18 |
| [SQL] 윈도우 함수(Window Function)의 종류(3) - 행 순서 관련 함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD) (0) | 2023.04.17 |
| [SQL] 윈도우 함수(Window Function)의 종류(2) - 집계 관련 함수(SUM, MAX, MIN, AVG, COUNT) (0) | 2023.04.13 |
| [SQL] NULL 처리 (0) | 2023.04.03 |
