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칸 뒤
SUM
sum 함수를 사용하여 같은 매니저를 둔 사원들의 급여의 합을 구하는 예제
SELECT MANAGER_ID, FIRST_NAME , SALARY ,
SUM(SALARY) OVER (PARTITION BY MANAGER_ID) MGR_SUM
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |SALARY|MGR_SUM|
----------+-----------+------+-------+
100|Neena | 17000| 155400|
100|Lex | 17000| 155400|
100|Den | 11000| 155400|
100|Matthew | 8000| 155400|
100|Adam | 8200| 155400|
100|Payam | 7900| 155400|
100|Shanta | 6500| 155400|
100|Kevin | 5800| 155400|
100|John | 14000| 155400|
100|Karen | 13500| 155400|
100|Alberto | 12000| 155400|
100|Gerald | 11000| 155400|
100|Eleni | 10500| 155400|
100|Michael | 13000| 155400|
101|Jennifer | 4400| 44916|
101|Susan | 6500| 44916|
101|Nancy | 12008| 44916|
...
ROWS UNBOUNDED PRECEDING 라는 윈도우 절을 사용하여 이전 급여에 새로운 급여를 차례로 더하는 누적 값을 보여주는 예제
ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지 범위를 지정한다.
(WINDOWING 절은 나중에 따로 자세히 다루도록 함)
SELECT MANAGER_ID, FIRST_NAME , SALARY ,
SUM(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY SALARY ROWS UNBOUNDED PRECEDING ) MGR_SUM
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |SALARY|MGR_SUM|
----------+-----------+------+-------+
100|Kevin | 5800| 5800|
100|Shanta | 6500| 12300|
100|Payam | 7900| 20200|
100|Matthew | 8000| 28200|
100|Adam | 8200| 36400|
100|Eleni | 10500| 46900|
100|Den | 11000| 57900|
100|Gerald | 11000| 68900|
100|Alberto | 12000| 80900|
100|Michael | 13000| 93900|
100|Karen | 13500| 107400|
100|John | 14000| 121400|
100|Lex | 17000| 138400|
100|Neena | 17000| 155400| --> 위의 MGR_SUM 컬럼과 같은 값이 되었다.
101|Jennifer | 4400| 4400|
101|Susan | 6500| 10900|
101|Hermann | 10000| 20900|
101|Shelley | 12008| 32908|
101|Nancy | 12008| 44916| --> 위의 MGR_SUM 컬럼과 같은 값이 되었다.
102|Alexander | 9000| 9000|
...
MAX
같은 매니저를 둔 사원들중 가장 큰 급여를 보여주는 예제
SELECT MANAGER_ID, FIRST_NAME , SALARY ,
MAX(SALARY) OVER (PARTITION BY MANAGER_ID) SAL_MAX
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |SALARY|SAL_MAX|
----------+-----------+------+-------+
100|Neena | 17000| 17000|
100|Lex | 17000| 17000|
100|Den | 11000| 17000|
100|Matthew | 8000| 17000|
100|Adam | 8200| 17000|
100|Payam | 7900| 17000|
100|Shanta | 6500| 17000|
100|Kevin | 5800| 17000|
100|John | 14000| 17000|
100|Karen | 13500| 17000|
100|Alberto | 12000| 17000|
100|Gerald | 11000| 17000|
100|Eleni | 10500| 17000|
100|Michael | 13000| 17000|
101|Jennifer | 4400| 12008|
101|Susan | 6500| 12008|
101|Nancy | 12008| 12008|
101|Shelley | 12008| 12008|
101|Hermann | 10000| 12008|
102|Alexander | 9000| 9000|
인라인 뷰를 사용해 같은 매니저를 둔 사원들중 가장 높은 급여를 받는 사원들을 조회하는 예제
SELECT MANAGER_ID, FIRST_NAME , SALARY
FROM (
SELECT MANAGER_ID, FIRST_NAME , SALARY ,
MAX(SALARY) OVER (PARTITION BY MANAGER_ID) SAL_MAX
FROM EMPLOYEES e )
WHERE SALARY = SAL_MAX
ORDER BY MANAGER_ID;
MANAGER_ID|FIRST_NAME|SALARY|
----------+----------+------+
100|Neena | 17000|
100|Lex | 17000|
101|Nancy | 12008|
101|Shelley | 12008|
102|Alexander | 9000|
103|Bruce | 6000|
...
MIN
같은 매니저를 둔 사원들 중 가장 적은 급여를 보여주는 예제
SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY ,
MIN(SALARY) OVER (PARTITION BY MANAGER_ID ) SAL_MIN
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |HIRE_DATE |SALARY|SAL_MIN|
----------+-----------+-----------------------+------+-------+
100|Neena |2005-09-21 00:00:00.000| 17000| 5800|
100|Lex |2001-01-13 00:00:00.000| 17000| 5800|
100|Den |2002-12-07 00:00:00.000| 11000| 5800|
100|Matthew |2004-07-18 00:00:00.000| 8000| 5800|
100|Adam |2005-04-10 00:00:00.000| 8200| 5800|
100|Payam |2003-05-01 00:00:00.000| 7900| 5800|
100|Shanta |2005-10-10 00:00:00.000| 6500| 5800|
100|Kevin |2007-11-16 00:00:00.000| 5800| 5800|
100|John |2004-10-01 00:00:00.000| 14000| 5800|
100|Karen |2005-01-05 00:00:00.000| 13500| 5800|
100|Alberto |2005-03-10 00:00:00.000| 12000| 5800|
100|Gerald |2007-10-15 00:00:00.000| 11000| 5800|
100|Eleni |2008-01-29 00:00:00.000| 10500| 5800|
100|Michael |2004-02-17 00:00:00.000| 13000| 5800|
101|Jennifer |2003-09-17 00:00:00.000| 4400| 4400|
101|Susan |2002-06-07 00:00:00.000| 6500| 4400|
101|Nancy |2002-08-17 00:00:00.000| 12008| 4400|
101|Shelley |2002-06-07 00:00:00.000| 12008| 4400|
101|Hermann |2002-06-07 00:00:00.000| 10000| 4400|
102|Alexander |2006-01-03 00:00:00.000| 9000| 9000|
...
같은 매니저를 둔 사원중 입사일을 기준으로 정렬되어(ASC) 해당 로우의 입사일자를 포함 이전 날짜중에 가장 적은 급여를 보여주는 예제
SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY ,
MIN(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE) SAL_MIN
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |HIRE_DATE |SALARY|SAL_MIN|
----------+-----------+-----------------------+------+-------+
100|Lex |2001-01-13 00:00:00.000| 17000| 17000|
100|Den |2002-12-07 00:00:00.000| 11000| 11000|
100|Payam |2003-05-01 00:00:00.000| 7900| 7900|
100|Michael |2004-02-17 00:00:00.000| 13000| 7900|
100|Matthew |2004-07-18 00:00:00.000| 8000| 7900|
100|John |2004-10-01 00:00:00.000| 14000| 7900|
100|Karen |2005-01-05 00:00:00.000| 13500| 7900|
100|Alberto |2005-03-10 00:00:00.000| 12000| 7900|
100|Adam |2005-04-10 00:00:00.000| 8200| 7900|
100|Neena |2005-09-21 00:00:00.000| 17000| 7900|
100|Shanta |2005-10-10 00:00:00.000| 6500| 6500|
100|Gerald |2007-10-15 00:00:00.000| 11000| 6500|
100|Kevin |2007-11-16 00:00:00.000| 5800| 5800|
100|Eleni |2008-01-29 00:00:00.000| 10500| 5800|
101|Hermann |2002-06-07 00:00:00.000| 10000| 6500|
101|Shelley |2002-06-07 00:00:00.000| 12008| 6500|
101|Susan |2002-06-07 00:00:00.000| 6500| 6500|
101|Nancy |2002-08-17 00:00:00.000| 12008| 6500|
101|Jennifer |2003-09-17 00:00:00.000| 4400| 4400|
102|Alexander |2006-01-03 00:00:00.000| 9000| 9000|
...
AVG
같은 매니저를 둔 사원들의 급여의 평균을 구하는 예제 ( 반올림 )
SELECT MANAGER_ID, FIRST_NAME, SALARY ,
ROUND( AVG(SALARY) OVER (PARTITION BY MANAGER_ID )) SAL_AVG
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |SALARY|SAL_AVG|
----------+-----------+------+-------+
100|Neena | 17000| 11100|
100|Lex | 17000| 11100|
100|Den | 11000| 11100|
100|Matthew | 8000| 11100|
100|Adam | 8200| 11100|
100|Payam | 7900| 11100|
100|Shanta | 6500| 11100|
100|Kevin | 5800| 11100|
100|John | 14000| 11100|
100|Karen | 13500| 11100|
100|Alberto | 12000| 11100|
100|Gerald | 11000| 11100|
100|Eleni | 10500| 11100|
100|Michael | 13000| 11100|
101|Jennifer | 4400| 8983|
101|Susan | 6500| 8983|
101|Nancy | 12008| 8983|
101|Shelley | 12008| 8983|
101|Hermann | 10000| 8983|
102|Alexander | 9000| 9000|
같은 매니저를 둔 사원들을 입사일로 정렬 하여 자기 자신 포함 이전 로우 하나와 다음 로우 하나의 ( 3 rows) 평균을 구하는 예제
SELECT MANAGER_ID, FIRST_NAME , HIRE_DATE, SALARY ,
ROUND (AVG(SALARY) OVER (PARTITION BY MANAGER_ID ORDER BY HIRE_DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )) SAL_AVG
FROM EMPLOYEES e ;
MANAGER_ID|FIRST_NAME |HIRE_DATE |SALARY|SAL_AVG|
----------+-----------+-----------------------+------+-------+
100|Lex |2001-01-13 00:00:00.000| 17000| 14000|
100|Den |2002-12-07 00:00:00.000| 11000| 11967|
100|Payam |2003-05-01 00:00:00.000| 7900| 10633|
100|Michael |2004-02-17 00:00:00.000| 13000| 9633|
100|Matthew |2004-07-18 00:00:00.000| 8000| 11667|
100|John |2004-10-01 00:00:00.000| 14000| 11833|
100|Karen |2005-01-05 00:00:00.000| 13500| 13167|
100|Alberto |2005-03-10 00:00:00.000| 12000| 11233|
100|Adam |2005-04-10 00:00:00.000| 8200| 12400|
100|Neena |2005-09-21 00:00:00.000| 17000| 10567|
100|Shanta |2005-10-10 00:00:00.000| 6500| 11500|
100|Gerald |2007-10-15 00:00:00.000| 11000| 7767|
100|Kevin |2007-11-16 00:00:00.000| 5800| 9100|
100|Eleni |2008-01-29 00:00:00.000| 10500| 8150|
101|Hermann |2002-06-07 00:00:00.000| 10000| 11004|
101|Shelley |2002-06-07 00:00:00.000| 12008| 9503|
101|Susan |2002-06-07 00:00:00.000| 6500| 10172|
101|Nancy |2002-08-17 00:00:00.000| 12008| 7636|
101|Jennifer |2003-09-17 00:00:00.000| 4400| 8204|
102|Alexander |2006-01-03 00:00:00.000| 9000| 9000|
103|David |2005-06-25 00:00:00.000| 4800| 4800|
103|Valli |2006-02-05 00:00:00.000| 4800| 4600|
103|Diana |2007-02-07 00:00:00.000| 4200| 5000|
103|Bruce |2007-05-21 00:00:00.000| 6000| 5100|
...
COUNT
같은 매니저를 둔 사원들의 수를 구하는 예제
SELECT MANAGER_ID, EMPLOYEE_ID, FIRST_NAME, SALARY ,
count(EMPLOYEE_ID) OVER (PARTITION BY MANAGER_ID ) SAL_CNT
FROM EMPLOYEES e ;
MANAGER_ID|EMPLOYEE_ID|FIRST_NAME |SALARY|SAL_CNT|
----------+-----------+-----------+------+-------+
100| 101|Neena | 17000| 14|
100| 102|Lex | 17000| 14|
100| 114|Den | 11000| 14|
100| 120|Matthew | 8000| 14|
100| 121|Adam | 8200| 14|
100| 122|Payam | 7900| 14|
100| 123|Shanta | 6500| 14|
100| 124|Kevin | 5800| 14|
100| 145|John | 14000| 14|
100| 146|Karen | 13500| 14|
100| 147|Alberto | 12000| 14|
100| 148|Gerald | 11000| 14|
100| 149|Eleni | 10500| 14|
100| 201|Michael | 13000| 14|
101| 200|Jennifer | 4400| 5|
101| 203|Susan | 6500| 5|
101| 108|Nancy | 12008| 5|
101| 205|Shelley | 12008| 5|
101| 204|Hermann | 10000| 5|
102| 103|Alexander | 9000| 1|
...
사원 급여 순으로 정렬 후 본인 월급의 -50 이하 부터 +200 이상인 월급을 가진 로우수(해당 로우 포함)를 구하는 예제
SELECT ENAME, SAL,
COUNT(*) OVER (ORDER BY SAL
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM EMP ;
MANAGER_ID|FIRST_NAME |SALARY|SAL_CNT|
----------+-----------+------+-------+
121|TJ | 2100| 3|
120|Steven | 2200| 4|
122|Hazel | 2200| 4|
120|James | 2400| 12|
122|Ki | 2400| 12|
114|Karen | 2500| 12|
121|James | 2500| 12|
123|Joshua | 2500| 12|
124|Peter | 2500| 12|
120|Martha | 2500| 12|
122|Randall | 2500| 12|
114|Guy | 2600| 10|
124|Randall | 2600| 10|
124|Donald | 2600| 10|
124|Douglas | 2600| 10|
120|Irene | 2700| 9|
123|John | 2700| 9|
...
728x90
반응형
'Oracle > SQL' 카테고리의 다른 글
| [SQL] 계층형 쿼리 ( CONNECT BY ) (0) | 2024.03.05 |
|---|---|
| [SQL] 윈도우 함수(Window Function)의 종류(4)- 비율 관련 함수(CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT) (1) | 2023.04.18 |
| [SQL] 윈도우 함수(Window Function)의 종류(3) - 행 순서 관련 함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD) (1) | 2023.04.17 |
| [SQL] 윈도우 함수(Window Function)의 종류(1) - 순위 관련 함수(RANK, DENSE_RANK, ROW_NUMBER) (1) | 2023.04.05 |
| [SQL] NULL 처리 (0) | 2023.04.03 |
