Oracle Window function


윈도우 함수는 분석 함수를 사용할 때 행(Row)의 범위를 지정, 즉 윈도우 절(window clause)을 사용하는 함수들이다.

윈도우 절은 PARTITION BY 절로 명시된 전체 그룹에서 부분 집합인 윈도우를 지정하는데, 그 시작과 끝 범위는 BETWEEN a AND b 형태로 지정되며, a와 b 값에 따라 윈도우 함수가 그 계산을 수행하게 된다.

  • 윈도우 함수 (10g 기준): AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE 등

 

구분 형식

Function(WINDOW) OVER (PARTITION BY expr ORDER BY expr [ASC | DESC]

ROWS[RANGE] BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW] )

 

  • PARTITION BY: 분석 함수가 계산을 수행하는 전체 그룹
  • WINDOW: 전체 그룹에서 일부분을 제외시킨 부분 그룹 혹은 부분 집합
  • ROWS: 부분집합인 윈도우 크기를 물리적인 단위로 행 집합을 지정
  • RANGE: 논리적인 주소에 의해 행 집합을 지정
  • BETWEEN ~ AND: 윈도우의 시작과 끝 위치를 지정
  • UNBOUNDED PRECEDING: 윈도우 시작 위치가 첫 번째 Row임을 의미
  • UNBOUNDED FOLLOWING: 윈도우 마지막 위치가 마지막 Row임을 의미
  • CURRENT ROW: 윈도우 시작 위치가 현재 Row임을 의미

 

select dept, id, salary

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) col1

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW        ) col2

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING) col3

    from (

          select 10 dept, 100 id, 39000 salary from dual union all

          select 20 dept, 101 id, 30000 salary from dual union all

          select 20 dept, 102 id,  9000 salary from dual union all

          select 20 dept, 103 id, 17000 salary from dual union all

          select 20 dept, 104 id, 60000 salary from dual union all

          select 20 dept, 105 id,  9500 salary from dual union all

          select 20 dept, 106 id, 17000 salary from dual union all

          select 20 dept, 107 id, 37500 salary from dual union all

          select 20 dept, 108 id, 25000 salary from dual union all

          select 30 dept, 109 id,  5000 salary from dual union all

          select 40 dept, 110 id, 17000 salary from dual

         )

  where dept = 20

  ;

 

  ----------------------------------------------------------

  DEPT    ID      SALARY      COL1        COL2        COL3

  ----------------------------------------------------------

  20      101     30000       205000      30000       205000

  20      102     9000        205000      39000       175000

  20      103     17000       205000      56000       166000

  20      104     60000       205000      116000      149000

  20      105     9500        205000      125500      89000

  20      106     17000       205000      142500      79500

  20      107     37500       205000      180000      62500

  20      108     25000       205000      205000      25000

  ----------------------------------------------------------

  • COL1: 첫 번째 Row(UNBOUNDED PRECEDING)부터 마지막 Row(UNBOUNDED FOLLOWING) 까지의 합
  • COL2: 첫 번째 Row(UNBOUNDED PRECEDING) 부터 현재 Row(CURRENT ROW) 까지의 합
  • COL3: 현재 Row(CURRENT ROW) 부터 마지막 Row(UNBOUNDED FOLLOWING) 까지의 합

 

  select dept, id, salary

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) col1

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) col2

       , SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) col3

    from (

          select 10 dept, 100 id, 39000 salary from dual union all

          select 20 dept, 101 id, 30000 salary from dual union all

          select 20 dept, 102 id,  9000 salary from dual union all

          select 20 dept, 103 id, 17000 salary from dual union all

          select 20 dept, 104 id, 60000 salary from dual union all

          select 20 dept, 105 id,  9500 salary from dual union all

          select 20 dept, 106 id, 17000 salary from dual union all

          select 20 dept, 107 id, 37500 salary from dual union all

          select 20 dept, 108 id, 25000 salary from dual union all

          select 30 dept, 109 id,  5000 salary from dual union all

          select 40 dept, 110 id, 17000 salary from dual

         )

  where dept = 20

  ;

 

  ----------------------------------------------------------

  DEPT    ID      SALARY      COL1        COL2        COL3

  ----------------------------------------------------------

  20      101     30000       39000       30000       39000

  20      102     9000        56000       39000       26000

  20      103     17000       86000       26000       77000

  20      104     60000       86500       77000       69500

  20      105     9500        86500       69500       26500

  20      106     17000       64000       26500       54500

  20      107     37500       79500       54500       62500

  20      108     25000       62500       62500       25000

  ----------------------------------------------------------

  • COL1: 현재 Row를 중심으로 이전 Row(1 PRECEDING)와 다음 Row(1 FOLLOWING)까지의 합
  • COL2: 이전 Row(1 PRECEDING)와 현재 Row(CURRENT ROW)의 합
  • COL3: 현재 Row(CURRENT ROW)와 다음 Row(1 FOLLOWING)의 합

   ☞ 만약 1 대신 2를 명시하면 이전화 이후 2개의 Row의 합을 계산한다.

 

- FIRST_VALUE와 LAST_VALUE

 : 윈도우 절과 함께 사용되어 가장 첫 번째 Row와 가장 마지막 Row의 값을 반환한다.

 : 윈도우 함수를 사용하면서 계산이 어느 범위에서 이루어지는지 어리버리할 때 사용하면 좋다.

 

  select dept

       , id

       , salary

       , FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) col1_first

       , LAST_VALUE (salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) col1_last

       , FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW        ) col2_first

       , LAST_VALUE (salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW        ) col2_last

       , FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING) col3_first

       , LAST_VALUE (salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING) col3_last

    from (

          select 10 dept, 100 id, 39000 salary from dual union all

          select 20 dept, 101 id, 30000 salary from dual union all

          select 20 dept, 102 id,  9000 salary from dual union all

          select 20 dept, 103 id, 17000 salary from dual union all

          select 20 dept, 104 id, 60000 salary from dual union all

          select 20 dept, 105 id,  9500 salary from dual union all

          select 20 dept, 106 id, 17000 salary from dual union all

          select 20 dept, 107 id, 37500 salary from dual union all

          select 20 dept, 108 id, 25000 salary from dual union all

          select 30 dept, 109 id,  5000 salary from dual union all

          select 40 dept, 110 id, 17000 salary from dual

         )

  where dept = 20

  ;

 

  ---------------------------------------------------------------------------------------------

  DEPT    ID      SALARY  COL1_FIRST  COL1_LAST   COL2_FIRST  COL2_LAST   COL3_FIRST  COL3_LAST

  ---------------------------------------------------------------------------------------------

  20      101     30000   30000       25000       30000       30000       30000       25000

  20      102     9000    30000       25000       30000       9000        9000        25000

  20      103     17000   30000       25000       30000       17000       17000       25000

  20      104     60000   30000       25000       30000       60000       60000       25000

  20      105     9500    30000       25000       30000       9500        9500        25000

  20      106     17000   30000       25000       30000       17000       17000       25000

  20      107     37500   30000       25000       30000       37500       37500       25000

  20      108     25000   30000       25000       30000       25000       25000       25000

  ---------------------------------------------------------------------------------------------

 

 

☞ 보통 분석 함수를 사용하면서 위도우 절을 생략할 때가 많은데 이러한 경우에는 디폴트로 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING이 적용된다.

 

출처: https://sites.google.com/site/smcgbu/home/gongbu-iyagi/windouhamsu

댓글

댓글 쓰기

이 블로그의 인기 게시물

파이참에서 인터프리터 꼬인 경우 처리

이미지
아나콘다를 재설치 후 파이참을 실행하는데 자꾸 실패한다. 실행시 사용되는 파이썬 위치도 뭔가 이상하다. 어떻게 설정했는지 모르겠지만 이전에 인터프리터 설정을 가상환경으로 설정을 해서 문제가 발생했다. Files -> Settings 선택하면 Setting 팝업이 나온다. 메뉴 중  Project: source -> Project Interpreter를 선택한 후 우측 상단 톱니 바퀴를 클릭하면 Project에서 사용할 Interpreter를 추가하여나 삭제, 변경할 수 있다. Add를 누르면 아래처럼 팝업이 보여지고 왼쪽에 4가지 항목이 나온다.  첫 번째 Virtualenv Environment는 시스템 인터프리터 이외 필요한 실행 환경을 만들어 사용할 수 있도록 지정하는 항목이다.  내가 어떻게 했는지 기억나지 않지만 가상 환경을 만들고 사용하도록 해서 하다 해당 위치를 삭제하고 System Interpreter를 사용하도록 변경하였다.  그런데 변경 후에도 System Interpreter가 비어있다고 나오고 Virtualenv Environment로 지정되는 현상이 발생했다.  Virtualenv Environment 폴더가 있으면 Virtualenv Environment을 우선으로 하는 것 같아서 파이참을 닫고 해당 폴더를 삭제하고 다시 System Interpreter으로 설정하였다.  정상적으로 설정되어 그전에 실행하던 파일을 실행하니 실행되지 않는다.  실행되는 Interpreter를 확인하니 Virtualenv Environment에서 사용하던 Interpreter 이다.  Run -> Edit Configurations 를 클릭하면 아래 팝업이 나온다.  Python interpreter 에 기존 Virtualenv Environment의 interpreter가 ...
자세한 내용 보기

경사소실 문제 해결

이미지
1. 경사 소실 문제 출력층으로부터 되돌아가면 각 층의 가중치를 수정하려면 미분 값, 즉 기울기가 필요하다. 그런데 층이 늘어나면서 기울기가 중간에 0이 되어버리는 경사 소실(vanishing gradient) 문제가 발생한다. 이는 시그모이드 함수의 미분 최대치는 0.3, 즉 1보다 작은 값이기 때문이다. 은닉층이 많아질수록 기울기는 점점 0에 가까워지게 된다. 이를 해결하기 위해서 시그모이드 함수를 대체할 수 있는 활성화 함수를 사용하려면 시그모이드 함수와 모양이 비슷하고 경사가 소실되지 않는 함수를 사용한다.  1.1 쌍곡탄젠트 함수(Hyperbolic Tangent Function) tanh(x) tanh(x) 함수 정의와 그래프이다.  * 시그모이드 함수 : 입력값 − ∞ <x< ∞에 대하여 함수값의 범위는 0<σ(x)<1   * 쌍곡탄젠트 함수 : 입력값 − ∞ <x< ∞에 대하여 함수값의 범위는 − 1<tanh(x)<1     다음은 쌍곡탄젠트 함수의 경사를 구하기 위해 필요한 tanh(x) 의 도함수를 구하는 과정이다. 아래는 tanh′(x) 와 σ′(x) 의 그래프를 비교한 그림이다.  σ′(x) 의 최대값은 σ′(0)=0.3 이고 tanh′(x) 의 최대값은 tanh′(0)=1 이기 때문에 시그모이드 함수와 비교했을 때 경사가 소실되기 힘들다는 것을 알 수 있다 . * TensorFlow에서 구현은 tf.nn.tanh() 이다.  1.2 ReLU(Rectified Linear Unit) 함수 tanh(x) 함수를 사용하면 미분값의 범위가 확장된다. 그러나 고차원 데이터를 다룰 경우에는 값이 커질 수 있어 다시 경사가 소실될 수 있다. 복잡...
자세한 내용 보기

근무 시작시간과 근무시간 입력 받아 근무 종료시간 반환하는 함수

* 문제 근무 시작시간(i_t)과 총소요시간(i_h)을 입력 받아 근무 종료시간(o_t)을 반환하는 사용자 함수(f_Quiz)를 생성하는 문제 * 가정     - 통상 근무시간은 평일 오전 9시부터 오후 6시     - 오후 12시부터 1시는 점심시간으로 근무시간에서 제외     - 토, 일요일 및 휴일도 근무시간에서 제외     - 휴일은 별도의 테이블로 관리 <리스트 1> 휴일 테이블 생성 및 조회 CREATE TABLE t_holiday AS SELECT '20140101' dt FROM dual UNION ALL SELECT '20140130' FROM dual UNION ALL SELECT '20140131' FROM dual UNION ALL SELECT '20140201' FROM dual UNION ALL SELECT '20140301' FROM dual UNION ALL SELECT '20140505' FROM dual UNION ALL SELECT '20140506' FROM dual UNION ALL SELECT '20140606' FROM dual ; SELECT * FROM t_holiday; DT 20140130 20140131 20140201 20140301 20140505 20140506 20140606 <리스트 2> 사용자 함수 결과 조회 WITH t AS ( SELECT 1 no, '2014/01/01 12:30' t, 2 h FROM dual UNION ALL SELECT 2, '2014/01/30 12:30', 4 FROM dual UNION ALL SELECT 3, '2014/03/04 07:30', 2 FROM dual UNION ALL SELECT 4, '2014/03/04 12...
자세한 내용 보기