[SQL] 08. SQL에서의 반복 (개념 + CASE&WINDOW함수 + 예시1)

SQL에서의 '반복'(반복문X) - CASE & WINDOW 함수 활용1

 

- SQL의 반복문?

 간단하게 말하면, 기본적인 SQL에는 반복문이 없다.

 

자바나 C#에서 사용하는 절차형 프로그래밍(IF문, Loop문 등)의 개념을 기본 베이스로 알고있는 일반 개발자들에게는 이런 점에서 SQL이 매우 불편한 언어라고 생각된다. SQL을 활용한 절차적 데이터 베이스 언어이 PL/SQL을 쓰거나 다른 언어를 결합하여 사용하면 크게 문제는 없지만, 이 나름대로 불편한 점이 아닐 수 없을 것이다.

 

 

 

- 반복계 코드 VS 포장계 코드

책에서 SQL에서의 반복이라는 개념을 설명할 때 '반복계 코드'와 '포장계 코드'라는 용어를 활용한다.

 

다음은 두 용어를 정리한 표이다. (책의 개념을 필자 나름대로 요약한 것인데, 이 부분은 이해가 부족한 점이 있어 설명이 원할하지 못할 수 있다는 점은 양해를 바란다.)

 

 

	반복계 코드	포장계 코드
정의	테이블 단위를 레코드 단위까지 쪼개어, 나눠진 레코드를 하나씩 반복문에 적용하는 방법	테이블의 여러 행을 한꺼번에 처리하는 방법
개념	절차 지향형 언어의 개념을 이용	절차 지향형 언어의 개념을 이용하지 않음
이용방안	SQL + 절차형 언어(자바 및 C# 등) PL/SQL(절차적 데이터베이스 언어)	SQL(CASE + WINDOW 함수)
장점	1. SQL을 잘 모르더라도 사용이 가능하다. 2. 실행 계획이 안정적이다. (SQL 내부에서의 결합 알고리즘 변경에 영향을 받지 않음) 3. 예상 처리 시간을 정밀하게 알 수 있다. 4. 트랜잭션 제어가 편리하다.	1. 프로그램의 생산성이 향상된다. (한번에 처리 가능) 2. 병렬 분산에 용이하다. 3. SQL을 활용한 DBMS 내에서의 검색 및 연산으로 타 처리가 필요 없다.
단점	1. 성능 및 생산성이 떨어진다.(한 레코드씩 처리 + 튜닝 불가) 2. 데이터 검색 및 연산하는 외의 다른 처리가 필요하다. (서버연결, 네트워크 열결, SQL 파싱 등) 3. 병렬 분산이 힘들다. 4. DBMS 성능 고도화의 혜택을 받기 힘들다.(타 절차형 언어와 함께 쓰이기도 하므로)	1. 비즈니스 로직인 구문에 들어가면, 구조가 복잡해져 유지 보수성이 떨어진다. 2. 쿼리의 예상 처리 시간을 예측하기 힘들다. 3. 튜닝이 되지 않은 SQL 쿼리 상태라면 반복계 코드보다 성능이 좋다고 말할 수 없다.

- 반복계 코드와 포장계 코드 비교 -

 

SQL에서 지향하는 코드는 "포장계 코드"이지만, 개발 환경이나 비즈니스 환경에 따라서 본인에게 맞는 코드를 선택하여 진행하면 될 것 같다.

 

 

---

 

 

- SQL에서의 반복 : CASE 와 WINDOW 함수의 조합

 SQL에서 반복을 대신하는 수단은 CASE 와 WINDOW 함수이다. 

CASE 구문은 보통 절차 지향형 언어에서 말하는 IF-THEN-ELSE구문을 대신하며, 세부 조건을 설정하는데 WINDOW 함수를 이용한다.

 

 

SQL의 반복을 설명하기 위해 세가지 예시를 들어 설명한다.

 

 

[ Example 1 - 회사별 전년도 매출 비교 ]

다음은 SALES 테이블에서 회사의 전년도 매출 대비 상승이면 변화(var)가 +, 하락이면 -, 변동이 없으면 =, 전년도 데이터가 없으면 Null을 반환하는 SALES2 테이블을 생성하는 예시이다.

 

- SALES 테이블 -

 

 

 

 

- WINDOW 함수의 활용 : ROWS BETWEEN 옵션

 우선, 전체 결과 코드를 설명하기 전에 다음 WINDOW 함수 활용 코드를 설명하는 것이 좋을 것 같다.

 

아래는 '같은 회사의 직전 매상'을 리턴하여 SALES 테이블에 적재시키는 코드이다. 이때 사용하는 중요한 옵션이 바로 ROWS BETWEEN 옵션이다.

 

ROWS BETWEEN 옵션은 대상 범위의 직전 레코드를 반환하는 옵션으로, 아래의 경우 "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING"을 적용하여 "현재 레코드에서 1개 이전부터 1개 이전까지의 레코드"를 반환하도록 설계하였다.

 

따라서, 다음 예제의 경우 "PARTITION BY company" 및 "ORDER BY year"의 조건이 함께 적용되어, 같은 회사 내의 직전 년도 매출이 반환되는 것이다.

 

- WINDOW 함수의 옵션인 ROWS BETWEEN을 활용한 결과 -

 

 

 

 WINDOW 함수 옵션을 활용하여 결과적으로 도출하고 싶었던 SALES2 테이블을 다음과 같이 만들어 낼 수 있다.

 

- CASE 와 WINDOW 함수를 활용한 SQL 반복 쿼리 -

 

 

 

- CASE 함수의 활용 : SIGN 함수

이 쿼리의 CASE 함수에서 사용된 SIGN 함수는 숫자 자료형을 매개변수로 받아 음수일때 -1, 양수일때 1, 0일때는 0을 리턴해주는 함수이다. 각 회사의 직전 년도 매출과 비교할 때 사용된 함수이다.

 

이 함수로 인해, CASE식의 조건 내에서 WINDOW 함수를 여러번 호출하지 않아도 되는 편리함을 제공받는다.