SQL -- Analytic Function

○ Analytic Function

1. 정의

① 쿼리를 수행하는 구문 중에 가장 마지막에 실행된다. (order by 보다는 먼저 실행)

② 쿼리 구문을 수행하고 2차로 값을 가공한다.

2. 구문

select column_name, column_name over([partition by column_name], [order by column_nae, ... ]) from table;

3. 누적합을 구하는 Analytic Function

select deptno, empno, ename, sal, sum(sal) over (partition by deptno order by sal) as dept_avg_sal from emp; : deptno별 누적 sal을 구하고 sal로 order by 하는데 같은 값의 row가 있으면 제대로 된 값이 안나올 수 있으므로 order에 구분할 컬럼을 더 해준다. select deptno, empno, ename, sal, sum(sal) over (partition by deptno order by sal, deptno) as dept_avg_sal from emp; DEPTNO EMPNO ENAME SAL DEPT_AVG_SAL 10 7934 MILLER 1300 1300 10 7782 CLARK 2450 3750 10 7839 KING 5000 8750 20 7369 SMITH 800 800 20 7876 ADAMS 1100 1900 20 7566 JONES 2975 4875

4. rank를 구하는 Analytic Function

① rank() : 순서를 정하는데 같은 값은 똑같은 순서로 정하고, 다음 순번은 없앤다. (1,2,2,4)

② dense_rank() : 다음 순번을 없애니 않는다. (1,2,2,3)

③ row_number() : 같은 값이라도 위치나 이름에 따라 순번을 정한다. (1,2,3,4)

select deptno, empno, ename, sal, rank() over(order by sal) "Rank", dense_rank() over(order by sal) "Dense Rank", row_number() over(order by sal) "Rownum" from emp; >> select deptno, empno, ename, sal, rank() over(partition by deptno order by sal) "Rank", dense_rank() over(partition by deptno order by sal) "Dense Rank", row_number() over(partition by deptno order by sal) "Rownum" from emp; >>

5. Moving Aggregates

select empno, sal, avg(sal) over (order by sal, empno) as ret1, --- 누적된 row의 평균 avg(sal) over (order by sal, empno rows 2 preceding) as ret2, --- 자신을 포함한 위의 2 row의 평균 avg(sal) over (order by sal, empno rows between current row and 2 fllowing) as ret3 -- 자신을 포함한 밑의 2 row의 평균 from emp; >> EMPNO SAL RET1 RET2 RET3 7369 800 800 800 950 7900 950 875 875 1100 7876 1100 950 950 1200 7521 1250 1025 1100 1266.66667 7654 1250 1070 1200 1350 7934 1300 1108.33333 1266.66667 1466.66667

6. LAG , LEAD

① 자신의 row를 기준으로 위와 아래의 row

② example

select empno, ename, sal, lag(sal, 1, 0) over (order by sal) as prevsal from emp; : 현재 row의 1칸 위의 sal, 처음의 row는 위에 값이 없을 때, null을 return하므로 숫자를 넣을 수 있다. select empno, ename, sal, lead(sal, 1, 0) over (order by sal) as prevsal from emp; : 현재 row의 1칸 밑의 sal, 마지막 row는 밑에 값이 없을 때, null을 return하므로 숫자를 넣을 수 있다.

7. First Value , Last Value

① 조건의 처음의 값과 마지막 값을 return

② Last Value는 버그가 발생 이에 관한 자료 참고

http://blog.naver.com/gseducation?Redirect=Log&logNo=20095625332

- last_value 대신 first_value를 사용하여 last_value의 결과값을 구할 수 있다.

select deptno, empno, ename, first_value(sal) over(partition by deptno order by sal) as dept_min_sal from emp; >> select deptno, empno, ename, first_value(sal) over(partition by deptno order by sal desc) as dept_min_sal from emp; : last_value대신 first_value에서 order by의 desc를 사용하여 구할 수 있다. >> [출처] SQL -- Analytic Function|작성자 도도리