일반 집계 함수

일반 집계 함수

 

 

* SUM 함수

   

 - SUM 함수를 이용하여 파티션별 윈도우의 합을 구할 수 있습니다.

 

 EX)

 SELECT MGR
     , ENAME
     , SAL
     , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

- PARTITION BY MGR 구문을 통해 매니저별로 데이터를 파티션화 합니다.

 

MGR	ENAME	SAL	MGR_SUM
7566	FORD	3000	6000
7566	SCOTT	3000	6000
7839	BLAKE	2850	8275
7839	JONES	2975	8275
7839	CLARK	2450	8275
7902	SMITH	800	800

 

 EX) RANGE UNBOUNDED PRECEDING 활용 ( * 윈도우 함수 참고 )

 

SELECT MGR
     , ENAME
     , SAL
     , SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING)
FROM EMP;

- OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력합니다.

 

MGR	ENAME	SAL	MGR_SUM
7698	JAMES	950	950
7698	WARD	1250	3450
7698	MARTIN	1250	3450
7698	TURNER	1500	4950
7698	ALLEN	1600	6550

 

- WARD와 MARTIN의 급여가 같으므로, 같은 ORDER로 취급하여 950+1250+1250=3450의 값이 됩니다.

 

 

* MAX 함수

   

 - MAX 함수를 이용하여 파티션별 윈도우의 최대값을 구할 수 있습니다.

 

 EX)

 

SELECT MGR
     , ENAME
     , SAL
     , MAX(SAL) OVER (PARTITION BY MGR) AS MGR_MAX
FROM EMP;

MGR	ENAME	SAL	MGR_MAX
7698	JAMES	3000	3000
7698	WARD	3000	3000
7698	MARTIN	950	1600
7698	TURNER	1600	1600
7698	ALLEN	1250	1600

 

- 파티션별 최대값을 가진 행만 추출할 수 있습니다.

 

 EX) INLINE VIEW 활용

 

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

 

- SCOTT, FORD는 같은 최대값을 가지므로, WHERE SAL=IV_MAX_SAL 조건에 의해 두건이 추출됩니다.

 

 

* MIN 함수

   

 - MIN 함수를 이용하여 파티션별 윈도우의 최소값을 구할 수 있습니다.

 

 EX)

 

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	1981-12-03	3000	3000
7698	ALLEN	1981-02-20	1600	950
7698	WARD	1981-02-22	1250	950
7698	MARTIN	1981-09-28	1500	950
7698	JAMES	1981-12-03	950	950

 

 

* AVG 함수

   

 - AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있습니다.

 

 EX) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 형을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정합니다.

 

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	1981-12-03	3000	3000
7698	ALLEN	1981-02-20	1600	1425
7698	WARD	1981-02-22	1250	1450
7698	TURNER	1981-09-08	1500	1333
7698	MARTIN	1981-09-28	1250	1233
7698	JAMES	1981-12-03	950	1100

 

- ALLEN의 경우 앞의 데이터가 없기 때문에 뒤의 데이터와 평균을 집계합니다. (1600+1250) / 2 = 1425

  WARD의 경우 앞, 현재 형, 뒤 데이터의 평균값을 구합니다. (1600+1250+1500) / 3 = 1450

 

 

* COUNT 함수

   

 - COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있습니다.

 

 EX) 

 

ELECT 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	( 750 ~ 950 )
JAMES	950	2	( 900 ~ 1100 )
ADAMS	1100	3	( 1050 ~ 1250 )
WARD	1250	3	( 1200 ~ 1400 )
MARTIN	1250	3	( 1200 ~ 1400 )

 

-  (- 50 ~ + 150) 기준에 맞는지 검사하게 됩니다. ORDER BY SAL로 정렬이 되어 있으므로 비교 연산이 쉬워집니다.

   ADAMS의 경우 SALARY 1100을 기준으로 -50에서 +150의 값을 가진 ADAMS, WARD, MARTIN 3명의 데이터 건수를 구합니다.