SQL 기본 및 활용 (2)

윈도우 함수

윈도우 함수 문법

SELECT
	윈도우함수(인자) OVER (PARTITION BY 칼럼 ORDER BY 칼럼)
	윈도우절(ROWS|RANGE BETWEEN 
													UNBOUND PRECEDING|CURRENT ROW
												AND UNBOUNDED FOLLOWING|CURRENT ROW
				)
	FROM 테이블명;

• OVER : 윈도우 함수 사용시 OVER는 반드시 들어가야 함. 어떤 데이터에 대해 윈도우 함수를 적용할 것인지를.

• PARTITION BY : FROM 절 이하에서 나온 결과 집합을 특정 칼럼(들)을 기준으로 그룹화할 수 있다.

• 윈도우절 : 전체 결과집합의 연산을 하고 싶다면 윈도우절 필요 x. 특정 행의 범위를 지정하고 싶을 때 이용
• ROWS BETWEEN A AND B : 조회된 ROW 하나 하나를 대상으로 연산
• RANGE BETWEEN A AND B : ORDER BY를 통해 정렬된 칼럼에 같은 값이 존재하는 행이 여러 개일 경우 동일한 칼럼값을 가지는 모든 행들을 묶어서 연산을 함
• UNBOUNDED PRECEDING : 행 범위의 시작 위치가 전체 행 범위에서 첫 번째 행임을 뜻함. eg. 첫번째 행부터 지금까지의 합계
• UNBOUNDED FOLLOWING : 행 범위의 마지막 위치가 전체 행 범위에서 마지막 행임을 뜻함. eg. 지금 행에서 마지막행 까지.
• CURRENT ROW : 행 범위의 시작 위치가 현재 행
• 1 PRECEDING : 행 범위의 시작 위치가 1만큼 이전 행
• 1 FOLLOWING : 행 범위의 마지막 위치가 1만큼 다음 행

윈도우 함수 종류

순위함수

• RANK : 1, 2, 3, 4, 4, 6

• DENSE_RANK : 1, 2, 3, 4, 4, 5

• ROW_NUMBER : 1, 2, 3, 4, 5, 6

SELECT *
  FROM (
         SELECT A.ADSTRD_CD
              , B.ADSTRD_NM
              , A.STD_YM
              , A.POPLTN_SE_CD
              , A.AGRDE_SE_CD
              , A.POPLTN_CNT
              , RANK() OVER(ORDER BY A.POPLTN_CNT DESC) AS RANK
              , DENSE_RANK() OVER(ORDER BY A.POPLTN_CNT DESC) AS DENSE_RANK
              , ROW_NUMBER() OVER(ORDER BY A.POPLTN_CNT DESC) AS ROW_NUMBER
           FROM TB_POPLTN A
              , TB_ADSTRD B
          WHERE A.AGRDE_SE_CD = '100'
            AND A.STD_YM = '202010'
            AND A.POPLTN_SE_CD = 'F'
            AND A.ADSTRD_CD = B.ADSTRD_CD
          ORDER BY A.POPLTN_CNT DESC
       )
 WHERE ROWNUM <= 10
;

집계관련 함수

• MAX(A.POPLTN_CNT) OVER(PARTITION BY A.AGRDE_SE_CD) : 연령대구분코드별 최대 인구수를 구함

• MIN(A.POPLTN_CNT) OVER(PARTITION BY A.AGRDE_SE_CD) : 연령대구분코드별 최소 인구수를 구함

• ROUND(AVG(A.POPLTN_CNT) OVER(PARTITION BY A.AGRDE_SE_CD),2) : 연령대구분코드별 평균 인구수를 구함

• ROUND(AVG(A.POPLTN_CNT) OVER(PARTITION BY A.AGRDE_SE_CD ORDER BY A.POPLTN_CNT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),2)
• 연령대구분코드별 평균 인구수를 구함. 평균을 구할 때 이전 행, 현재 행(자기자신), 다음 행의 평균값을 구함(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

행 순서 관련 함수

• FIRST_VALUE : 그 범위에서 첫번째 값

• LAST_VALUE : 그 범위에서 마지막 값

• LAG : 이전 행의 값. 인자로 몇 번 이전의 행인지 조절 가능

• LEAD : 다음 행의 값. 인자로 몇 번 이후의 행인지 조절 가능

그룹 내 비율 관련 함수

• RATIO_TO_REPORT : 전체 대비 비중의 값을 나타냄

• PERCENT_RANK : 백분율을 계산. 가장 낮은 행이 0 가장 높은 행이 1

• CUME_DIST : 각 순서의 누적 백분율. 4개 행이라면 첫째 행은 0.25, 둘째 행은 0.5, 셋째 행은 0.75, 넷째 행은 1

• NTILE : 인자값으로 들어가는 값으로 전체 데이터를 나눔. 4개 행에 대해 NTILE(2)면 1, 1, 2, 2 의 값을 갖게 됨

절차형 SQL

• 절차형 SQL을 사용하면 SQL 문의 연속적인 실행이나 조건에 따른 분기 처리를 수행하는 모듈을 생성할 수 있음

• 오라클 DBMS 기준으로 이러한 절차형 모듈의 종류는 사용자 정의함수, 프로시저, 트리거가 있으며, 이러한 절차형 모듈을 PL/SQL 이라고 부름

• 여러 SQL 문장을 Block으로 묶고 한번에 Block 전부를 서버로 보내기 때문에 네트워크 패킷수를 감소시킴. 사용자가 DBMS에 접속하여 각각의 SQL 문을 호출할 때마다 네트워크 통신을 해야 하는데 PL/SQL은 내부적으로 SQL 문을 처리한 후, 결과를 리턴하기 때문에 네트워크 패킷수가 감소되는 것

PL/SQL Block 구조

• DECLARE(선언부)

• 프로시져

• 사용자 정의 함수 : 프로시저와 차이점은 반드시 1건을 되돌려줘야 한다는 것과 SELECT 문과 결합하여 호출할 수 있다는 것임

• 트리거 : 특정 테이블에 INSERT, UPDATE, DELETE를 수행할 때 DBMS 내에서 자동으로 동작하도록 작성된 프로그램. 즉, 사용자가 직접 호출하는 것이 아니고 DBMS가 자동적으로 수행함
• 트리거는 트리거 내부에서 트랜잭션 처리를 하지 않고 트리거를 호출하게 된 INSERT 문을 호출한 세션에서 롤백을 수행하면 트리거가 실행한 내역도 함께 롤백으로 취소됨

프로시져와 트리거의 차이점

• 프로시저와 트리거의 가장 큰 차이점은 프로시저는 프로시저 내부에서 커밋 혹은 롤백을 수행하지만, 트리거는 트리거가 발생된 원인이 된 SQL문이 커밋 혹은 롤백되는지에 따라서 트리거의 실행 결과가 데이터베이스에 최종 적용될지 말지가 결정된다는 것임