#10 10일차_윈도우 함수

01. 윈도우 함수

 

02. 윈도우 함수의 종류 및 문법

 -01 집계함수

 -02 이전/이후 행과 비교

---

01. 윈도우 함수

서브쿼리를 사용하며 select 절의 사용이 늘어나게 되면 성능이 떨어지게 된다.

다른 행과의 연산, 다른 열과의 비교를 보다 효율적으로 할 수 있게 도와주는 함수가 윈도우 함수이다.

group by와 같은 집계연산 수행 시 행의 수가 원래는 줄어드는 구조이지만 윈도우 함수를 사용 시 행의 수를 줄이지 않고 연산을 도와준다.

where절에서 사용이 불가능하며, 윈도우 함수를 사용한 조건 전달 시 인라인뷰가 필수이다.

---

02. 윈도우 함수의 종류 및 문법

 -01 집계함수

sum(대상) over([partition by] 
                          [order by ...]
                          [range|rows ...]);

over 뒤 각각의 절은 생략이 가능하다.

 

 

sum(대상) over([partition by ...])

집계함수는 group by와 같은 집계를 수행하나 행이 축소되지 않는다.

 

예제) emp 테이블을 사용하여 각 직원의 이름, 급여의 총합을 출력

 

해결1) 서브쿼리

select ename, (select sum(sal) from emp) as sum_sal
  from emp;

 

해결2) 윈도우함수

select ename, sum(sal) over() avg_sal
  from emp;

 

 

예제) professor 테이블을 사용하여 각 교수의 이름, 직급, 급여를 출력하되, 각 직급별 평균 급여를 함께 출력

 

스칼라 서브쿼리)
select p.name, p.position, p.pay, (select avg(p2.pay)
                                     from professor p2
                                    where p2.position = p.position) 
  from professor p
 order by p.position;
 
 
인라인뷰) 
select p.name, p.position, p.pay avg_pay
  from professor p, (select p2.position, avg(p2.pay) as avg_pay
                        from professor p2
                       group by p2.position) i
 where p.position = i.position;


윈도우 함수)
select name, position, pay, avg(pay) over(partition by position) as avg_pay
  from professor
 order by position;

 

 

 ** 누적합 연산

sum(대상) over(order by...] )

order by 절은 생략 가능하며, 지정한 대상의 누적합을 출력한다.

emp 테이블을 사용하여 sal의 누적합을 구하게 되면 쿼리는 다음과 같이 작성된다. 

 

select sal, sum(sal) over(order by sal) as 누적합
  from emp;

 

 

 

 그러나 4,5번 행을 보았을 시 sal의 값이 1250로 모두 같으며, 최종 누적합을 두 행 모두에 동일하게 출력한다.

이는 누적합 연산의 기본 연산범위가 range로 설정되어 있기 때문이며 누적합 연산 시 값이 같은 행이 연속적으로 존재할 시 함께 묶어 계산하기 때문이다.

 

 따라서 각 행 별로 누적합이 연속적으로 계산되기를 원한다면 order by 절에 따라오는 정렬 조건을 다른 행과 겹치지 않게 설정하는 방법이 있다.

 

select empno, sal, 
       sum(sal) over(order by sal, empno) as 누적합
  from emp;

 

 

 또는 연산 범위를 rows 단위로 설정하는 방법이 있다.

 range 사용 시 끝점과 and는 생략이 가능하나, row 사용시 범위는 생략이 불가능하다.

range|rows between 시작 and 종료

 

* range : 연산범위를 값이 가지는 크기로 설정 (default)
 * row : 연산범위를 물리적인 행으로 정의 (각 행씩 연산)
 * 시작 : Unbounded preceding (처음부터) (default) / current row (현재부터)/ N preceding (N 이전부터) 
 * 종료 : Unbounded following (끝까지) / current row (현재까지) / N preceding (N 이후까지)

 

select empno, sal, 
       sum(sal) over(order by sal, empno) as 누적합1,
       sum(sal) over(order by sal
                     range between unbounded preceding and current row) as 누적합2,
       sum(sal) over(order by sal 
                     range unbounded preceding) as 누적합3  
  from emp;

 

 

 

예제) order 테이블을 사용하여 월별 총 구매금액에 대한 누적합 조회

 

-- step1) 월별 총 구매금액 출력
select to_char(order_date, 'yyyy/mm'), sum(total_amount)
  from orders
 group by to_char(order_date, 'yyyy/mm')
 order by 1;
  
select * from orders;

--step2) 월별 총 구매금액 누적합 출력
select to_char(order_date, 'yyyy/mm') as 월, 
       sum(total_amount) as 월별총합,
       sum(sum(total_amount)) over(order by to_char(order_date, 'yyyy/mm')) as 월별누적합
  from orders
 group by to_char(order_date, 'yyyy/mm');

 

 

 

예제) movie 테이블을 사용하여 일자별 이용_비율의 총합에 대한 누적합 출력

 

select 년||lpad(월,2,'0')||lpad(일,2,'0') as 날짜, 
       sum(이용_비율) as 일자별_이용비율,
       sum(sum(이용_비율)) over(order by 년||lpad(월,2,'0')||lpad(일,2,'0')) as 일자별누적
  from movie
 group by 년||lpad(월,2,'0')||lpad(일,2,'0');
 

---

 -02 이전/이후 행과 비교

  (1) lag

select lag(대상[,N][,값]) over([partition by ...]
                        [order by...] );

 

 * 대상 : 이전 값을 가져올 컬럼
 * N : 행의 이동 수(default : 1)
 * 값 : 가져올 값이 없을 때 출력할 값((default:NULL))

 

 n의 defalt값은 1이며, 가져올 대상이 없는 경우 NULL로 출력한다.

 연산자가 있는 경우 NULL이 포함된 값들은 연산이 불가능하므로 치환하기를 원하는 값을 지정해주어야 한다.

 

예제) emp 테이블을 사용하여 입사날짜 순서대로 각 직원의 급여와 바로 이전에 입사한 직원의 급여차이 출력

 

select ename, sal, hiredate, 
       lag(sal,1,0) over(order by hiredate) as 직전입사자급여,
       lag(sal,1,0) over(order by hiredate) - sal as 급여차이
  from emp;

 

 

 

예제) 일자별 영화이용비율 총합을 출력한 뒤, 이전날짜대비 영화이용비율 증가율

 ( 증가율 : 현재값 - 이전값 / 이전값 * 100 )

 

select *
  from movie;
  
select 년||lpad(월,2,0)||lpad(일,2,0) as 날짜, 
       sum(이용_비율) as 이용비율,
       lag(sum(이용_비율)) over(order by 년||lpad(월,2,0)||lpad(일,2,0)) as 이전이용비율
  from movie
 group by 년||lpad(월,2,0)||lpad(일,2,0);
 
select 년||lpad(월,2,0)||lpad(일,2,0) as 날짜, 
       sum(이용_비율) as 이용비율,
       lag(sum(이용_비율)) over(order by 년||lpad(월,2,0)||lpad(일,2,0)) as 이전이용비율,
       round((sum(이용_비율) - lag(sum(이용_비율)) over(order by 년||lpad(월,2,0)||lpad(일,2,0))) /
       lag(sum(이용_비율)) over(order by 년||lpad(월,2,0)||lpad(일,2,0)) * 100, 2) as 증가비율
  from movie
 group by 년||lpad(월,2,0)||lpad(일,2,0);

 

 

  (2) lead

ratio_to_report(대상) over([partition by ...]);

 

cume_dist() over([partition by ... ]
                            [order by ...] )