[SQL]분석함수(analytic function)_기본함수(COUNT, MIN, MAX, SUM, AVG)

기본 함수

 

• 기본 함수는 자주 사용하는 분석 함수다. 행의 개수, 최저, 최고, 합계, 평균 등을 반환한다.

 

| COUNT 함수

COUNT 함수는 전체 행의 개수나 expr의 개수를 반환한다.

COUNT({* | [DISTINCT | ALL] expr}) OVER(analytic_clause)

 

ex )

SELECT job, COUNT(*) OVER(PARTITION BY job) AS c1
 FROM emp
WHERE deptno = 30
ORDER BY 1;

 

 

| MIN 함수

MIN 함수는 expr의 최저 값을 반환한다.

MIN(expr) OVER(analytic_clause)

 

ex )

SELECT empno, sal, comm
      , MIN(comm) OVER(ORDER BY sal, empno ROWS UNBOUNDED PRECEDING) AS c1
 FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

 

| MAX 함수

MAX 함수는 expr의 최고 값을 반환한다.

MAX(expr) OVER(analytic_clause)

ex )

SELECT empno, sal, comm, MAX(comm) OVER(ORDER BY sal) AS c1
 FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

| SUM 함수

SUM 함수는 expr의 합계 값을 반환한다.

SUM( [DISTINCT | ALL] expr) OVER(analytic_clause)

 

ex )

SELECT empno, sal
    , SUM(sal) OVER() AS c1
    , SUM(sal) OVER(ORDER BY sal, empno) AS c2
    , EXP(SUM(LN(sal)) OVER(ORDER BY sal, empno)) AS c3
 FROM emp
WHERE deptno = 30
ORDER BY 2, 1;

 

| AVG 함수

AVG 함수는 expr의 평균 값을 반환한다.

AVG([DISTINCT | ALL] epxr) OVER(analytic_clause)

 

ex 1 )

SELECT empno, ename,sal
    , AVG(sal) OVER(ORDER BY sal, empno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c1
    , COUNT(*) OVER(ORDER BY sal, empno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c2
 FROM emp
WHERE deptno = 30
ORDER BY 3, 1;

ex 2 )

SELECT empno, ename, sal
    , AVG(sal) OVER(ORDER BY sal RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) AS c1
    , COUNT(*) OVER(ORDER BY sal RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING) AS c2
 FROM emp
WHERE deptno = 30
ORDER BY 3, 1;

* 날짜 값이 연속되지 않은 경우 윈도우의 유형(ROWS, RANGE)에 따라 쿼리의 결과가 달라질 수 있다.

ex 3 )

-- 날짜데이터 테이블 생성
DROP TABLE t1 PURGE;
​
CREATE TABLE t1 AS
SELECT dt, ROWNUM * 100 AS v1
 FROM (SELECT DATE '2050-01-01' + ROWNUM - 1 AS dt FROM XMLTABLE ('1 to 31'))
WHERE TO_CHAR (dt, 'D') NOT IN ('1','7');SELECT dt, v1
      ,① AVG(v1) OVER(ORDER BY dt ROWS 9 PRECEDING) AS c1
      ,② AVG(v1) OVER(ORDER BY dt RANGE 9 PRECEDING) AS c2
  FROM t1
ORDER BY 1;

① AVG(v1) OVER(ORDER BY dt ROWS 9 PRECEDING) AS c1 : dt를 기준으로 오름차순으로 하고 현재 행부터 앞쪽으로 9개 행까지 v1 평균값을 구한다.

② AVG(v1) OVER(ORDER BY dt RANGE 9 PRECEDING) AS c2 : dt를 기준으로 오름차순을 하고 현재 v1 값부터 앞쪽으로 9 확장( ex ) 50/01/13 -9 = 50/01/04 )하여 평균값을 구한다.