SQL: 조건 적용해 유의미한 기준점 뽑기, CASE WHEN

 

 

 

 

CASE WHEN(CASE 문이라고도 한다.)은 SQL의 조건문이다. 기준 컬럼 A가 B라는 조건을 만족하면 C라는 값을 출력하는 형태로 'CASE WHEN A = B THEN C'와 같이 표현된다. "조건에 맞는 답을 출력"하는 식이어서 주로 SELECT 절이나 집계함수에서 쓰인다. 이번 포스트에서는 샘플 데이터베이스 기반으로 아직 집계되지 않은 유의미한 지표를 찾아 반환해 보고 이를 통해 CASE WHEN의 SELECT절 용례를 살펴보려 한다.

 

 

 

사용 데이터베이스

 

아래로 한 데이터베이스가 있다. (mySQL에서 기본적으로 제공하는 샘플이다.) 우리는 이 데이터베이스의 테이블들을 살펴보고 CASE WHEN을 통해 회사 운영 전반에 유의미한 정보를 만들어 볼 것이다. 이 데이터베이스에는 여덟 개의 테이블이 있다. customers, employees, offices, orderdetails, orders, payments, productline, products...... 이 회사에서 무슨 일을 하는지는 아직 알 수 없지만 프로덕트 정보, 주문 정보를 가진 것으로 보아 어떤 프로덕트를 판매하는 커머스임을 우선 알 수 있다. 그게 유형의 제품이든, 무형의 제품이든간에. 우선 products를 한 번 볼까.

 

 

 

 

 

 

 

위 사진은 products의 데이터를 일부 출력한 것이다. 익숙한 자동차/ 오토바이 브랜드가 보이는데, 전반적으로 엄청 옛날 것이고(productName) 재고 수량도 너무 많다. 좀 더 살펴 보니 productScale이라는 컬럼이 있다. 1:10, 1:12...... 자동차 미니어처를 파는 회사임을 알 수 있다. 제조 벤더사(productVender)가 여럿인 것으로 봐선 제조사보다는 유통사에 가까운 회사겠다.

 

중요한 부분은 주로 뒤에 있다. 재고 수량(quantityInstock), 구매원가(buyPrice), 판매가(MSRP). 판매 데이터와 함께 보면 뜯어볼 부분이 많아 보인다. 하지만 이 회사 데이터베이스엔 판매 데이터가 없기 때문에 주문 데이터를 통해 추출해내야 할 것 같다.

 

 

 

 

다음으로 살펴 볼 테이블은 orderdetails 테이블이다. 제품의 개당 단가와 주문 수량이 있고 productCode로 products 테이블과 연결돼 있다. 주문 수량이 쭉 나열돼 있기 때문에 products와 orders를 함께 보면 제품의 평균 주문 수량이나 총 매출액, 잔여 재고수량, 안전 재고 등의 확인이 가능하겠다. 아쉬운 것은, 주문 일자가 없다. 기간별로 그룹해 볼 수 없으니 유의미한 정보를 만들어낼 수는 없다. 그러면 테이블 하나가 더 필요하겠다. 

 

 

 

 

바로 orders 테이블이다. 이 테이블에는 주문 일자(orderDate)와 주문한 사람의 고객 번호(customerNumber), 그리고 주문 번호가 적혀 있다. 테이블을 살펴 보니 2003년 01월부터 2005년 05월까지의 데이터가 저장돼 있다. 이걸로 필요한 데이터는 모두 모아졌다. 이제 데이터를 예쁘게 다듬어보자.

 

 

 

 

INNER JOIN: 여러 테이블 모아서 보기

 

CASE WHEN은 조건을 다는 데 수량 제한이 없다. 원하는 수량만큼 충분히 덧붙일 수 있단 소리다. SELECT 절에서 CASE WHEN을 사용하면 새로운 컬럼에서 구간에 맞춰 값을 다시 부여함으로써 로우들을 세분화할 수 있다. 하지만 한 테이블에서만 진행하기는 조금 어렵다. 각 정보값이 두 테이블에서 공유하는 값(기본키)을 기준으로 나눠져 있기 때문이다. 이를 테면, orderdetails에서는 제품의 값과 주문 수량이 있지만 주문 일자와 주문자명이 없다. 이 두 정보값은 orders 테이블에서 가져와야 한다.

 

우리는 이미 이전 포스트에서 JOIN에 관해 배웠다. INNER JOIN을 활용하면 각기 두 테이블에서 원하는 컬럼을 각각 가져와 재조립해 SELECT 할 수 있다. 아래와 같이 말이다.

 

SELECT ods.orderDate
, ods.customerNumber
, odd.orderNumber
, odd.productCode
        , odd.quantityOrdered
        , odd.priceEach
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber

 

 

 

 

이렇게 INNER JOIN으로 정보를 각각 가져와 주면, 일자별로 어떤 상품이 누구에게 얼마나 팔렸는지 알 수 있다. 이 테이블을 기준으로 2003년 기준으로 제품별 매출 순위를 뽑아 볼 수 있을 것이다.

 

 

WHERE, GROUP BY: 1년치 제품별 매출 순위 뽑기

 

SELECT ods.orderDate
, ods.customerNumber
, odd.orderNumber
, odd.productCode
        , odd.quantityOrdered
        , odd.priceEach
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
ORDER BY productCode;

 

 

 

 

WHERE 절에 조건을 하나 더 추가해 줬다. 2003년으로 시작하는 문자열 전체를 반환하는 조건이다. ORDER BY는 productCode로 줬다. 제품 코드 별로 데이터가 정렬된 게 보인다. (시기나 고객 집단의 차이에 따라 최종 판매가는 오르내리는 게 보인다.) 여기다 상품별 연매출액을 뽑고 정렬해 주면 예쁘게 볼 수 있겠다.

 

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode
ORDER BY year_sales DESC;

 

 

 

 

훨씬 보기 편해졌다. 여기서 들 수 있는 의문을 해소하고 갈까. 왜 priceEach는 평균을 구하고 quantity는 합계 값을 구하는가가 궁금할 수 있다. 그건 priceEach가 구매액(개당 단가*수량)이 아니라 개당 단가여서 그렇다. 해당 회사의 경우 도매로만 거래하기 때문에 제품 가격이 고정되어 있지 않고 협의 여부와 상황에 따라 유동적으로 단가가 조정된다. 그래서 연매출, year_sales를 뽑기 위해서는 이 개당 실판매가의 평균과 판매 개수를 곱해야만 했다.

 

 

CASE WHEN: 상품 세분화해서 각기 다른 프로모션 전략 짜기

 

보면 알겠지만, 매출 1위 상품과 2위 상품의 편차가 크다. 쭉 내려보면 막순위 상품은 1만 달러 정도로 1위와 거의 10배 가량 매출 차이가 나는데 상위권 상품 대비해서 제품 단가도 낮고 판매 수량 좀 부진하다. 전반적으로 보면 그룹별로 나눠서 관리해줘야 할 필요가 있다. 

 

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
    , ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) AS sales_rank
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode
ORDER BY year_sales DESC;

 

 

 

 

year_sales를 조금 더 직관적으로 보고, 차후에 다른 년도별 세분화 진행 시에도 사용 가능하도록 순위를 먼저 뽑았다. 이 데이터를 기반으로 상품을 'high', 'medium', 'low' 세 가지 집단으로 나눠볼 것이다. 'high'는 매출 상위권으로 재고를 지속 관리해줘야 하며 판매 동향을 지속적으로 체크해야 할 상위 십 퍼센트, 반대로 'low'는 재고가 과다하다면 최우선으로 소진하고 재고를 감축 운영해야 할 하위 십 퍼센트다.

 

퍼센트를 직접 집계하는 쿼리를 짜고 싶었는데 서칭 실력이 부족했던 건지 결국 못 찾아서, 전체 리스트를 10으로 나눠서 위 아래로 11위씩 빼 줬다(...)

 

 

작품명 미스터 아날로그......

 

 

SELECT productCode
, ROUND(AVG(priceEach), 2) AS avg_priceEach
    , SUM(quantityOrdered) AS sum_quantity
    , (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) AS year_sales
    , ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) AS sales_rank
, CASE 
WHEN ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) BETWEEN 1 AND 11 THEN 'high'
WHEN ROW_NUMBER () 
OVER (
ORDER BY (ROUND(AVG(priceEach), 2) * SUM(quantityOrdered)) DESC
            ) BETWEEN 98 AND 109 THEN 'low'
ELSE 'medium'
END AS sales_seg
FROM orderdetails odd, orders ods
WHERE odd.orderNumber = ods.orderNumber
AND orderDate LIKE '2003%'
GROUP BY productCode;

 

 

 

 

 

결과적으론 잘 나왔다! 가독성 위해 BETWEEN으로 값을 지정해 줬는데 연산부호로 잡아 줘도 무방하겠다. 굳이 순위를 집계한 까닭은 이후에 일일이 범위를 지정할 필요가 없어서다. year_sales를 조건으로 잡다 보니 많이 길어졌는데, 더 효율 좋게 짤 수 있는 방법이 분명 있지 않나 싶다. 차후 알게 되면 와서 덧붙여 보겠다.

 

CASE WHEN을 사용할 때 주의할 점이 몇 가지 있다. 1) 상위 조건이 우선순위로 잡혀 있어서 우선순위 설정 시 겹치지 않게 넣어 줘야 한다는 거. 만약에 위 쿼리를 연산기호로 조건을 겹칠 수 있게 잡아 뒀다면 먼저 선언된 한 가지 집단에 값들이 다 몰려 버리는 결과가 나올 수 있다. 해당 문법은 한 번 포함된 이후의 만족 조건을 무시하기 때문이다. 2) ELSE를 넣지 않아도 동작하지만 지정해주지 않으면 세분화되지 않는 로우는 전부 다 NULL 값이 들어간다는 거다. 해당 두 가지를 주의해서 짜 주자.

 

 

 

 

 

 

 

 

참고

suy379님 티스토리

mySQL 샘플 데이터베이스