일반 집계 함수
일반 집계함수에도 OVER(), PARTITION BY를 사용할수있다!
하지만 SQL SERVER의 경우 ORDER BY를 지원하지 않는다
그룹별 소계를 구할때 유용하게 사용할수 있을것 같다.
SUM 함수
SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | SQL > SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP; MGR ENAME SAL MGR_SUM ---------- ---------- ---------- ---------- 7566 FORD 3000 6000 7566 SCOTT 3000 6000 7698 JAMES 950 6550 7698 ALLEN 1600 6550 7698 WARD 1250 6550 7698 TURNER 1500 6550 7698 MARTIN 1250 6550 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 7839 BLAKE 2850 8275 7839 JONES 2975 8275 7839 CLARK 2450 8275 7902 SMITH 800 800 KING 5000 5000 14 rows selected. SQL> SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM FROM EMP; MGR ENAME SAL MGR_SUM ---------- ---------- ---------- ---------- 7566 SCOTT 3000 6000 7566 FORD 3000 6000 7698 JAMES 950 950 7698 WARD 1250 3450 7698 MARTIN 1250 3450 7698 TURNER 1500 4950 7698 ALLEN 1600 6550 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 7839 CLARK 2450 2450 7839 BLAKE 2850 5300 7839 JONES 2975 8275 7902 SMITH 800 800 KING 5000 5000 14 rows selected. (SQL Server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.) | cs |
MAX 함수
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | SQL> SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX FROM EMP; MGR ENAME SAL MGR_MAX ---------- ---------- ---------- ---------- 7566 FORD 3000 3000 7566 SCOTT 3000 3000 7698 JAMES 950 1600 7698 ALLEN 1600 1600 7698 WARD 1250 1600 7698 TURNER 1500 1600 7698 MARTIN 1250 1600 7782 MILLER 1300 1300 7788 ADAMS 1100 1100 7839 BLAKE 2850 2975 7839 JONES 2975 2975 7839 CLARK 2450 2975 7902 SMITH 800 800 KING 5000 5000 14 rows selected. SQL> SELECT MGR, ENAME, SAL FROM (SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL FROM EMP) WHERE SAL = IV_MAX_SAL ; MGR ENAME SAL ---------- ---------- ---------- 7566 FORD 3000 7566 SCOTT 3000 7698 ALLEN 1600 7782 MILLER 1300 7788 ADAMS 1100 7839 JONES 2975 7902 SMITH 800 KING 5000 8 rows selected. | cs |
MIN 함수
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP; MGR ENAME HIREDATE SAL MGR_MIN ---------- ---------- --------- ---------- ---------- 7566 FORD 03-DEC-81 3000 3000 7566 SCOTT 09-DEC-82 3000 3000 7698 ALLEN 20-FEB-81 1600 1600 7698 WARD 22-FEB-81 1250 1250 7698 TURNER 08-SEP-81 1500 1250 7698 MARTIN 28-SEP-81 1250 1250 7698 JAMES 03-DEC-81 950 950 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 12-JAN-83 1100 1100 7839 JONES 02-APR-81 2975 2975 7839 BLAKE 01-MAY-81 2850 2850 7839 CLARK 09-JUN-81 2450 2450 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000 14 rows selected. | cs |
AVG 함수
EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데,
조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
SQL Server에서는 Windowing절이 없다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG FROM EMP; MGR ENAME HIREDATE SAL MGR_AVG ---------- ---------- --------- ---------- ---------- 7566 FORD 03-DEC-81 3000 3000 7566 SCOTT 09-DEC-82 3000 3000 7698 ALLEN 20-FEB-81 1600 1425 7698 WARD 22-FEB-81 1250 1450 7698 TURNER 08-SEP-81 1500 1333 7698 MARTIN 28-SEP-81 1250 1233 7698 JAMES 03-DEC-81 950 1100 7782 MILLER 23-JAN-82 1300 1300 7788 ADAMS 12-JAN-83 1100 1100 7839 JONES 02-APR-81 2975 2913 7839 BLAKE 01-MAY-81 2850 2758 7839 CLARK 09-JUN-81 2450 2650 7902 SMITH 17-DEC-80 800 800 KING 17-NOV-81 5000 5000 | cs |
COUNT 함수
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP; ENAME SAL SIM_CNT ---------- ---------- ---------- SMITH 800 2 JAMES 950 2 ADAMS 1100 3 WARD 1250 3 MARTIN 1250 3 MILLER 1300 3 TURNER 1500 2 ALLEN 1600 1 CLARK 2450 1 BLAKE 2850 4 JONES 2975 3 SCOTT 3000 3 FORD 3000 3 KING 5000 1 14 rows selected. | cs |
참고(COUNT)
COUNT(*)는 그룹지은 행의 총 개수를 리턴한다. 이는 NULL값과 같은 값도 포함한다.
COUNT(ALL expression) 는 그룹지은 행의 총개수를 리턴하는데 NULL값은 제외한 행의 개수를 리턴한다.
COUNT(DISTINCT expression)는 그룹지은 행의 총개수를 리턴하느데 NULL값과 같은 값을 가진 행을 제외한 행의 개수를 센다.
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26744104
https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql
'SQL > MS-SQL' 카테고리의 다른 글
| LEFT OUTER JOIN / WHERE (0) | 2017.09.19 |
|---|---|
| NULL 비교하기 (0) | 2017.09.19 |
| 순위관련 WINDOW FUNCTION (0) | 2017.09.07 |
| 트리거(Trigger) (0) | 2017.09.06 |
| 커서(Cursor) (0) | 2017.09.06 |