[SQL] 05. 조건 분기(CASE),집합 연산, 윈도우 함수(WINDOW)

SQL에서의 검색 조건 응용

이번 포스팅에서는 SQL 구문을 좀더 유연하게 다룰 수 있는  조건 분기(CASE),집합 연산(UNION, INTERSECT, EXCEPT) , 윈도우 함수(WINDOW) 검색 조건에 대해 설명해보고자 한다.

 

 

 

- 조건분기(CASE 식)

보통 절차 지향형 프로그래밍 언어에서는 조건분기(조건문)을 사용하기 위해서 IF 조건문, SWITCH 조건문을 활용한다.

SQL에서는 위와같은 조건문과 같은 '문장'이 아닌, CASE 식를 활용한 '식'을 사용함으로서 조건분기를 실현할 수 있다.

 

 

아래는 ADDRESS 테이블을 활용하여 CASE 식를 적용한 예시이다. 테이블의 address 컬림이 '서울시'인 경우 DISTRICT는 '경기'로, '부산'인 경우 '영남'으로, '인천'인 경우 '경기'로 값이 만들어지는 조건분기(CASE 식)을 구현했다.

 

- ADDRESS 테이블을 활용한 CASE 식 활용 -

 

 

 

이처럼 CASE 식의 작동은 WHEN 구(평가식,주황색상자)와 THEN 구(리턴식,노란색상자)으로 구성된다.

WHEN 구에서 지정된 평가식으로부터 레코드가 평가되고, 조건에 맞으면 THEN 구에서 지정한 식이 리턴되며 CASE 식 전체를 빠져나오게 된다. 만일 WHEN 구를 모두 거치고 나서도 조건에 맞는 값이 리턴이 되지 않을 경우는 ELSE 의 식을 리턴하게 된다.

 

SQL에서의 조건분기가 절차 지향형 언어와 다른 점은, 바로 조건에 맞는 특정한 값이 리턴된다는 것이다.(절차 지향형에서는 리턴값이 필수가 아니다.)

 

또한, 조건분기가 CASE "식"이라는 점에서 다양한 쓰임새를 얻을 수 있다. 바로 "식"이기 때문에 SELECT, WHERE, GROUP BY, HAVING, ORDER BY 같은 "식"을 활용할 수 있는 곳에서는 어디서나 적용이 가능하다. 이런 점에서 SQL의 CASE 식은 쿼리의 성능을 높이는데 큰 기여를 한다.

 

 

 

 

 

- 집합연산(UNION, INTERSECT, EXCEPT)

SQL에서는 테이블을 활용한 집합 연산을 하는 다양한 기능이 있다.

 

 

1. UNION : 합집합

아래는 테이블 구조가 같은 두개의 테이블 (ADDRESS, ADDRESS2) 의 합집합을 구하는 과정이다. UNION은 WHERE 구에서 사용하는 OR 옵션과 같은 역할을 한다. 테이블이 합해지는 과정에서 중복되는 두 레코드인 'username:인성/address:서울시'와 'username:민/address:인천시'의 중복을 제거한다.

 

- 집합 연산 : UNION -

 

 

 

2. INTERSECT : 교집합

다음은 WHERE 구에서 사용하는 AND 옵션에 해당하는 교집합 INTERSECT의 예시이다.

 

- 집합 연산 : INTERSECT -

 

 

 

 

3. EXCEPT(MINUS in Oracle) : 차집합

집합 연산의 마지막인 차집합의 예시는 다음과 같다. 책에서 설명하는 대로 아래의 예시처럼 EXCEPT를 사용하면 된다. (Oracle에서는 차집합에 대해서 독자적으로 MINUS라는 연산자를 사용한다.)

 

- 집합 연산 :  EXCEPT(MINUS in Oracle) -

 

 

 

여기서 주의할 점은, UNION이나 INTERSECT 같은 합집합이나 교집합에 대해서는 테이블의 순서가 중요하지 않지만, 앞의 테이블 레코드에서 뒤의 테이블 레코드의 중복을 제거하는 차집합의 경우 테이블의 순서가 중요하다.

 

 

 

 

 

- WINDOW 함수(PARTITON BY)

책에서는 WINDOW 함수를 "집약 기능이 없는 GROUP BY 구"라고 정리한다.

앞 포스팅에서 살펴보았던 GROUP BY 구에서는 '자르기'와 '집약' 기능을 가지고 있지만, WINDOW 함수는 '자르기' 기능만을 가지고 있다. 때문에 WINDOW 함수를 GROUP BY 구에 대신해 활용하면서 쿼리의 성능을 높일 수 있다.

 

두 경우를 비교하는 예시가 아래에 있다.

 

- GROUP BY 구  vs  WINDOW 함수 -

 

 

 

위의 두 쿼리는 address 필드를 기준으로 테이블을 '자르는' 과정은 같으나, GROUP BY 구에서는 잘라서 연산된 레코드를 '집약'하여 결과로 보여준다. 집약 기능이 없는 WINDOW 함수를 사용하면 레코드 수에는 변함이 없다. 

 

 

 

 

 

- WINDOW 함수(ORDER BY)

WINDOW 함수에서는 SUM 이나 COUNT 같은 일반 함수 외에도 RANK나 ROW_NUMBER(추후 따로 활용하여 설명) 같은 순서를 활용한 함수를 사용할 수 있다.

 

아래는 ORDER BY 옵션을 활용하여 레코드를 잘라 RANK된 순서대로 붙이는 예시를 나타낸 것이다.

 

- WINDOW 함수의 ORDER BY 옵션 활용 -

 

 

RANK 함수는 AGE가 같으면 같은 순위를 표시해주며, DENSE_RANK 함수를 사용하게 되면 순위가 건너뛰는 작업 없이 순위가 매겨진다.

(위의 예시에서는 AGE가 같은 레코드가 없어 결과가 같다.)

 

 

 

다음 포스팅에서는 조건 분기에 관해서 좀 더 다양하게 활용하는 방법을 설명하고자 한다.