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

 

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

 

 

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

- SQL에서 반복을 대신하는 수단은 바로 CASE 식과 윈도우 함수임 

-> 정확하게 말하면 CASE 식은 절차 지향형 언어에서 말하는 IF-THEN-ELSE 구문에 대응하는 기능임

-> SQL에서 CASE 식과 윈도우 함수를 함께 사용하면 다음과 같음

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

- SELECT 구문의 실행 계획을 살펴보면, 

  일단 Sales 테이블을 풀스캔하고, 윈도우 함수를 정렬로 실행합니다. 

 

- 윈도우 함수로 '직전 회사명'과 '직전 매상'도 검색할 수 있습니다. 

SELECT company,
       year,
       sale,
       MAX(company)
           OVER (PARTITION BY company
                     ORDER BY year
                 ROWS BETWEEN 1 PRECEDING
                          AND 1 PRECEDING) AS pre_company,
       MAX(sale)
           OVER (PARTITION BY company 
                     ORDER BY year
                 ROWS BETWEEN 1 PRECEDING
                          AND 1 PRECEDING) AS pre_sale
FROM Sales;

 

2. 최대 반복 횟수가 정해진 경우

- '인접한 우편 번호 찾기'라는 문제를 풀어볼 수 있습니다.

   우선, 우편 번호를 관리하는 테이블을 만들고, 우편 번호들을 입력합니다. 

CREATE TABLE PostalCode
(pcode CHAR(7),
 district_name VARCHAR(256),
      CONSTRAINT pk_pcode PRIMARY KEY(pcode));
      
INSERT INTO PostalCode VALUES ('4130001', '시즈오카 아타미 이즈미');
INSERT INTO PostalCode VALUES ('4130002', '시즈오카 아타미 이즈산');
INSERT INTO PostalCode VALUES ('4130003', '시즈오카 아타미 아지로');
INSERT INTO PostalCode VALUES ('4130041', '시즈오카 아타미 아오바초');
INSERT INTO PostalCode VALUES ('4380824', '시즈오카 이와타 아카');

- 기본적인 방법은 다음과 같습니다.

-> 일단 우편번호 '4130033'이 테이블에 있는지를 찾습니다.

-> 그런데 테이블에 없다면 이어서 '413003*'가 있는지를 찾습니다.

-> 마찬가지로 없다면 이어서 '41300**'가 있는지를 찾습니다. 

-> 3개의 우편번호가 일치하므로, 이것을 출력하면 됩니다. 

 

- 이 문제의 포인트는 순위입니다. 가장 가까운 우편번호 순위를 0, 가장 먼 우편 번호 순위를 6으로 나타낸다면

  다음과 같습니다. 

SELECT pcode,
       district_name,
       CASE WHEN pcode='4130033' THEN 0
       WHEN pcode LIKE '413003%' THEN 1
       WHEN pcode LIKE '41300%' THEN 2
       WHEN pcode LIKE '4130%' THEN 3
       WHEN pcode LIKE '413%' THEN 4
       WHEN pcode LIKE '41%' THEN 5
       WHEN pcode LIKE '4%' THEN 6
       ELSE NULL END AS rank
FROM PostalCode;

 

- 결론적으로, 가까운 우편번호를 구하는 쿼리는 다음과 같습니다.

SELECT pcode,
       district_name,
WHERE  CASE WHEN pcode='4130033' THEN 0
       		WHEN pcode LIKE '413003%' THEN 1
       	    WHEN pcode LIKE '41300%' THEN 2
            WHEN pcode LIKE '4130%' THEN 3
            WHEN pcode LIKE '413%' THEN 4
            WHEN pcode LIKE '41%' THEN 5
            WHEN pcode LIKE '4%' THEN 6
       ELSE NULL END =
       	   (SELECT MIN(CASE WHEN pcode = '4130033' THEN 0
                            WHEN pcode LIKE '413003%' THEN 1
                            WHEN pcode LIKE '41300%' THEN 2
                            WHEN pcode LIKE '4130%' THEN 3
                            WHEN pcode LIKE '413%' THEN 4
                            WHEN pcode LIKE '41%' THEN 5
                            WHEN pcode LIKE '4%' THEN 6
                            ELSE NULL END)
		   FROM PostalCode);

- 위의 쿼리는 테이블 스캔이 2회 발생합니다.

-> 왜냐하면 바로 순위의 최솟값을 서브쿼리에서 찾기 때문입니다.

-> 고전적인 방법이지만 다음과 같이 윈도우 함수를 사용하면 스캔 횟수를 줄일 수 있습니다. 

SELECT pcode,
       district_name
FROM (SELECT pcode,
             district_name,
             CASE WHEN pcode = '4130033' THEN 0
                  WHEN pcode LIKE '413003%' THEN 1
                  WHEN pcode LIKE '41300%' THEN 2
                  WHEN pcode LIKE '4130%' THEN 3
                  WHEN pcode LIKE '413%' THEN 4
                  WHEN pcode LIKE '41%' THEN 5
                  WHEN pcode LIKE '4%' THEN 6
                  ELSE NULL END AS hit_code,
             MIN(CASE WHEN pcode='4130033' THEN 0
                      WHEN pcode LIKE '413003%' THEN 1
                      WHEN pcode LIKE '41300%' THEN 2
                      WHEN pcode LIKE '4130%' THEN 3
                      WHEN pcode LIKE '413%' THEN 4
                      WHEN pcode LIKE '41%' THEN 5
                      WHEN pcode LIKE '4%' THEN 6
                      ELSE NULL END)
             OVER(ORDER BY CASE WHEN pcode = '4130033' THEN 0
                                WHEN pcode LIKE '413003%' THEN 1
                                WHEN pcode LIKE '41300%' THEN 2
                                WHEN pcode LIKE '4130%' THEN 3
                                WHEN pcode LIKE '413%' THEN 4
                                WHEN pcode LIKE '41%' THEN 5
                                WHEN pcode LIKE '4%' THEN 6
                                ELSE NULL END) AS min_code
             FROM PostalCode) Foo
 WHERE hit_code = min_code;

 

17장 바이어스의 공죄

- 누구나 적어도 한 번 이상은 반복에 의존하는 병에 걸립니다. 

-> 이는 절차 지향형 언어와 파일 시스템의 심리 모델이기 때문입니다. 

-> 프로그래밍을 공부할 때, 다들 절차 지향형 언어로 공부를 시작합니다.

-> 절차 지향형 언어는 대부분 파일을 열고, 레코드를 한 줄씩 읽고 

    비즈니스 로직을 처리하고, 최종적으로 파일을 닫습니다.

-> 이러한 처리 모델이 절차 지향형 프로그래밍의 기본입니다. 

 

- SQL은 서두에 소개한 Codd의 말처럼 '절차 지향형에서의 탈출'을 

  목표로 설계된 언어입니다. 

  DBMS도 내부적으로는 절차 지향형 언어로 만들어져,

  실제로 물리 데이터 접근 등은 모두 절차 지향적인 방법으로 수행됩니다. 

-> 이러한 절차적 계층을 은폐하는 것이 SQL의 이념입니다. 

 

참고

- SQL 레벨업