Filtering and Expressions

목표

BETWEEN, IN, LIKE, COALESCE, CASE, IS DISTINCT FROM, LIMIT/OFFSET 을 실제 데이터에 적용해 원하는 집합을 정확히 서술할 수 있게 됩니다.

왜 중요한가

SQL 에서 조건을 쓴다는 것은 집합을 정의하는 일입니다. 그리고 이 집합 연산에는 다른 언어에 없는 세 번째 진리값이 끼어듭니다. NULL 이 관여한 비교는 참도 거짓도 아닌 미지가 되고, WHERE 는 참인 행만 통과시키므로 미지는 조용히 걸러집니다.

이 성질 때문에 city = '서울' 과 city <> '서울' 의 행 수를 더해도 전체가 되지 않습니다. 어느 쪽에도 속하지 않는 NULL 행이 있기 때문입니다. 이런 종류의 버그는 오류를 내지 않고 숫자만 조용히 틀리기 때문에 발견이 늦습니다. 이번 실습에서 직접 확인해 두면 나중에 리포트 숫자가 안 맞을 때 가장 먼저 의심할 곳이 생깁니다.

단계

1. price 가 50000 이상 150000 이하인 상품을 담는 뷰 v_mid_price 를 만듭니다.
2. status 가 paid 또는 shipped 인 주문을 담는 뷰 v_multi_status 를 만듭니다.
3. name 에 프로 라는 글자가 들어간 상품을 담는 뷰 v_pro_products 를 만듭니다.
4. 고객의 id 와 city 두 컬럼만 담되 city 가 비어 있으면 미상 으로 바꾼 뷰 v_city_filled 를 만듭니다. 컬럼 이름은 id, city 입니다.
5. 주문의 id 와 규모 분류를 담는 뷰 v_order_size 를 만듭니다. 컬럼은 id, size 이며, total_amount 가 1000000 이상이면 대형, 300000 이상이면 중형, 나머지는 소형 입니다.
6. city 가 서울 이 아닌 고객을 담는 뷰 v_not_seoul 을 만듭니다. city 가 비어 있는 고객도 포함되어야 합니다.
7. 상품을 price 내림차순, 같으면 id 오름차순으로 정렬했을 때의 3페이지(한 페이지 20건)를 담는 뷰 v_page3 를 만듭니다.
8. country 가 KR, is_active 가 참, signup_date 가 2024-07-01 이후(같은 날 포함), tier 가 gold 또는 vip 인 고객의 id, name, tier, signup_date 를 담는 뷰 v_target 을 만듭니다. 정렬은 signup_date 내림차순, 같으면 id 오름차순입니다.

참고

• 접속: PGPASSWORD=lab psql -h 127.0.0.1 -U lab -d labdb
• 뷰의 컬럼 이름과 순서까지 채점하므로 별칭을 정확히 맞추세요.
• 흔한 실수 1: BETWEEN 은 양 끝값을 포함합니다. 미만으로 착각하면 경계 행이 어긋납니다.
• 흔한 실수 2: 6번을 city <> '서울' 로 쓰면 NULL 인 고객이 빠집니다. 행 수를 세어 확인해 보세요.

가격 구간으로 좁히기

price 가 50000 이상 150000 이하인 상품을 담는 뷰 v_mid_price 를 만듭니다.

구간 조건을 한 번에 쓰는 키워드가 있습니다. 양 끝값이 포함되는지 확인하세요.

여러 값 중 하나 고르기

status 가 paid 또는 shipped 인 주문을 담는 뷰 v_multi_status 를 만듭니다.

OR 을 여러 번 쓰는 대신 목록으로 표현하는 키워드가 있습니다.

이름에 특정 문자열이 든 상품 찾기

name 에 프로 라는 글자가 들어간 상품을 담는 뷰 v_pro_products 를 만듭니다.

부분 일치를 찾으려면 와일드카드를 앞뒤로 모두 붙여야 합니다.

빈 값을 기본값으로 채우기

고객의 id 와 city 두 컬럼만 담되 city 가 비어 있으면 미상 으로 바꾼 뷰 v_city_filled 를 만듭니다. 컬럼 이름은 id, city 입니다.

NULL 일 때만 대체값을 돌려주는 함수가 있습니다. 원래 값이 있는 행은 그대로 두어야 합니다.

조건에 따라 분류하기

주문의 id 와 규모 분류를 담는 뷰 v_order_size 를 만듭니다. 컬럼은 id, size 이며, total_amount 가 1000000 이상이면 대형, 300000 이상이면 중형, 나머지는 소형 입니다.

CASE WHEN 은 위에서부터 처음 참인 가지를 고릅니다. 큰 구간부터 쓰면 겹침을 피할 수 있습니다.

NULL 까지 포함해 뒤집기

city 가 서울 이 아닌 고객을 담는 뷰 v_not_seoul 을 만듭니다. city 가 비어 있는 고객도 포함되어야 합니다.

일반 부등호로는 NULL 인 행이 걸러지지 않습니다. NULL 을 하나의 값처럼 비교해 주는 연산자가 있습니다.

3페이지 가져오기

상품을 price 내림차순, 같으면 id 오름차순으로 정렬했을 때의 3페이지(한 페이지 20건)를 담는 뷰 v_page3 를 만듭니다.

건너뛸 행 수와 가져올 행 수를 따로 지정합니다. 한 페이지가 20건이면 3페이지는 몇 건을 건너뛰어야 할까요.

조건 네 개를 조합한 타깃 목록

country 가 KR, is_active 가 참, signup_date 가 2024-07-01 이후(같은 날 포함), tier 가 gold 또는 vip 인 고객의 id, name, tier, signup_date 를 담는 뷰 v_target 을 만듭니다. 정렬은 signup_date 내림차순, 같으면 id 오름차순입니다.

앞에서 쓴 조건들을 AND 로 묶고, 필요한 컬럼만 순서대로 고르고, 정렬 기준을 두 개 지정합니다.