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 레벨업