SQL 레벨업 - 5장 반복문 (2/2)

2018-11-08

16강 SQL에서는 반복문을 어떻게 표현할까?

1. 포인트는 CASE 식과 윈도우 함수

SQL에서 반복을 대신하는 수단은 CASE 식과 윈도우 함수이다. CASE 식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE 구문에 대응하는 기능이다.

SELECT company, year, sale, 
	CASE SIGN (sale - MAX(sale)
               OVER (PARTITION BY company
                     ORDER BY year
                     ROWS BETWEEN 1 PRECEDING AND PRECEDING)
              )
              WHEN 0 THEN '='
              WHEN 1 THEN '+'
              WHEN -1 THEN '-'
              ELSE NULL END AS var
FROM Sales;

SIGN 함수는 매개변수를 받아 음수면 -1, 양수면 1, 0이면 0을 반환한다.

-- 실행 계획
WindowAgg (cost ~)
	-> Sort (cost ~)
		Sort Key: company, year
		-> Seq Scan sales (cost ~)

Sales 테이블을 풀 스캔하고 윈도우 함수를 정렬로 실행한다. 비교대상 레코드를 1개 전 레코드가 아닌 2개 전 레코드로 하고싶다면

ROWS BETWEEN 2 PRECEDING AND PRECEDING 로 변경하면 된다. 이러한 유연함은 윈도우 함수가 보급되기 이전에 사용하던 상관 서브쿼리로는 하기 힘들다.

-- 상관 서브쿼리로 작성한 SQL
SELECT company, year, sale, 
	(
        SELECT company 
        FROM Sales S2
        WHERE S1.company = S2.company
        AND year = (
            SELECT MAX(year) FROM Sales S3
            -- 상관 쿼리 결합 조건
            WHERE S1.company = S3.company
            ANd S1.year > S3.year
        )
    ) AS pre_company
FROM Sales S1
  • 중첩 집합 모델

SQL에서 계층 구조를 나타내는 방법은크게 3가지가 있다.

  1. 인접 리스트 모델
  2. 중첩 집합 모델
  3. 경로 열거 모델

1은 RDB가 탄생하기 이전부터 계층 구조를 표현하는 전통적인 방법이다. 3은 갱신이 거의 발생하지 않는 경우에 힘을 발휘하는 방법이다. 2의 중요한 점은 각 레코드의 데이터를 집합으로 보고, 계층 구조를 집합의 중첩 관계로 나타내는 것이다.

포스팅 참조