[SQL] 09. SQL에서의 반복 (CASE&WINDOW함수 + 예시2)

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

앞선 포스팅에 이어 SQL에서의 반복에 관한 두번째 예시를 소개하려 한다.

 

 

 

 

[ Example 2 - 인접한 우편 번호 찾기 ]

다음은 PosatlCode 테이블에서 [4130033 - 시즈오카 아타미 아타미]와 가장 가까운 지역의 우편번호를 찾는 예시이다. 여기서 제시된 우편번호의 앞자리부터 순차적으로 똑같은 우편번호의 지역이 가까운 지역이라 할 수 있겠다.

 

- PostalCode 테이블 -

 

 

절차 지향형 사고방식이라면 레코드 하나에 대해 7번의 비교를 반복을 하게 되며, 이는 레코드가 많아질수록 비효율적인 성능을 초래하게 된다.

 

 

 

- CASE 함수의 활용 : (사용자 지정)순위 계산하기

이 예시의 경우는 제시된 [4130033 - 시즈오카 아타미 아타미]의 우편번호와 레코드를 앞자리부터 비교하여 순위를 계산하는 쿼리를 만들어야 한다.

 

우선, pcode가 4130033과 완벽하게 일치하는 순위를 0으로 시작하여 다음과 같은 쿼리를 작성할 수 있다.

 

- CASE 함수를 활용한 순위 계산 쿼리 -

 

 

이렇게 각각의 레코드에서 계산한 순위 중 최소의 값(순위가 가장 높은 값)을 추출하면 다음과 같다.

 

- CASE 함수를 활용한 순위의 최솟값 추출 쿼최종 -

 

 

따라서 순위를 계산하는 쿼리와 순위의 최솟값을 계산하는 쿼리를 조합하면, 다음과 같은 쿼리를 만들어낼 수 있다.

 

- CASE 함수를 활용한 최종 결과 쿼리 -

 

 

WHERE 구에서 만들어진 순위와 WHERE 구 안의 서브쿼리에서 추출된 순위의 최솟값을 비교하여, 가장 인접한 지역의 우편번호(pcode)와 지역이름(district_name)을 추출할 수 있다. 

 

하지만 위의 쿼리는 서브쿼리를 활용하여 순위의 최솟값을 추출하기 때문에, 결과적으로 테이블이 2번 스캔하게 된다. 위의 예시의 경우는 레코드 수가 많지 않아 성능에 큰 차이가 없으나 간과해서는 안된다.

 

 

 

 

- WINDOW 함수의 활용 : 서브쿼리를 대체하여 성능 높이기

테이블 스캔을 줄여 성능을 높이는 방법으로 WINDOW 함수를 활용하는 방법이 있다. (쿼리가 복잡해 보이지만 흐름대로 천천히 읽어보면 이해할 수 있을 것이다.)

 

아래에서 순위의 최솟값을 구할때 위와는 달리 WINDOW 함수 내에서 정렬된 결과로 최솟값을 뽑아내기 때문에, 중간에 테이블 풀스캔(집약의 과정)이 없어 그 성능을 높일 수 있다. 물론 이 예시에서는 테이블의 크기가 작아 많은 차이는 없다. 

 

- CASE 함수에 WINDOW 함수를 더하여 성능을 높인 쿼리 -

 

 

이와 같이 CASE와 WINDOW 함수를 활용하여 성능을 높인 쿼리를 작성해 보았다. 위의 예시에서는 애초에 우편번호가 6자리이기 때문에 반복이 7번으로 정해져 있어 반복의 횟수가 고정적이였다.

 

다음 포스팅에서는 반복의 횟수가 정해지지 않은 경우의 예시를 설명해 볼 것이다.