[MS-SQL] 항목별 비율값 집계하기 (CAST, ROUND, CASE WHEN 구문)
SQL에서는 GROUP BY와 집계함수를 활용해 어떤 항목 값의 개수, 합계, 최댓값, 최솟값 등등을 쉽계 집계할 수 있습니다.
하지만 비율을 구하려면 조금의 쿼리 수정이 필요한데요!
이를 어떻게 하는지 바로 알아보겠습니다.
--조회
SELECT * FROM [RFM_BASE_SEG2]
오늘 사용할 테이블은 [RFM_BASE_SEG2] 입니다.
위의 테이블을 보면, 고객별 구매한 횟수(ord_cnt 열), 구매한 총 금액(tot_amt 열), 그리고 고객별 등급(seg)이 나와 있는데요!
각 고객별 값이 아니라, 각 등급별(seg)로 집계를 해보려고 합니다.
| Q. 등급별(seg별) 고객 수 및 매출 비중을 파악하시오.
SELECT seg, COUNT(mem_no) AS seg_cnt, SUM(tot_amt) AS seg_amt
FROM [RFM_BASE_SEG2]
GROUP BY seg
ORDER BY 1
seg별로 고객이 몇명인지, 구매금액은 총 얼마인지를 알아보려면 GROUP BY와 집계함수를 이용해 쉽게 구할 수 있습니다.
여기서 궁금한 것은 바로 "비율" 입니다.
예를 들어, '1_VVIP' 등급의 고객 수(seg_cnt) 8명은 전체 고객 중 몇 퍼센트(%)에 해당할까요?
'4_SILVER' 등급의 매출(seg_amt) 129,897,960,000원은 전체 매출 중 몇 퍼센트(%)에 해당할까요?
seg_cnt의 값과 seg_amt 값을 비율로 알아보겠습니다.
✔Table of Contents
1. 등급별 매출 비율 구하기
먼저 등급별로 매출(seg_amt)의 비율을 구하겠습니다.
직관적으로 구하면 되는데요! 등급별 매출을 구한 값에, 전체 매출 합계로 나눠주면 되겠죠?
앞서 만든 쿼리를 조금만 변형합니다.
SELECT seg,
ROUND((SUM(tot_amt) / (SELECT SUM(tot_amt) FROM [RFM_BASE_SEG2]))*100, 2) AS seg_rate
FROM [RFM_BASE_SEG2]
GROUP BY seg
ORDER BY 1
SELECT문의 2번째 값을 보시면, 전체 매출 합계를 구하기 위해 SELECT문 안에 새로운 SELECT절 서브 쿼리를 만들었습니다.
(SELECT SUM(tot_amt) FROM [RFM_BASE_SEG2]) ← 바로 이 부분만 따로 실행해보면 전체 매출 합계가 나옵니다.
(SELECT SUM(tot_amt) FROM [RFM_BASE_SEG2])
그래서, 각 seg별 매출 합계 값에 1152185730000를 나누어 비율을 구했다고 보시면 됩니다.
참고로 100을 곱해준 이유는 백분율로 보여주기 위해서이며, ROUND 함수를 사용해 2자리수까지만 표현했습니다.
각 seg별(등급별) 매출값과, 매출 비율 모두를 뽑아봅니다.
SELECT seg, SUM(tot_amt) AS seg_amt,
ROUND((SUM(tot_amt) / (SELECT SUM(tot_amt) FROM [RFM_BASE_SEG2]))*100, 2) AS seg_rate
FROM [RFM_BASE_SEG2]
GROUP BY seg
ORDER BY 1
2. 등급별 고객 수 비율 구하기
이번엔 조금 복잡합니다. 먼저, 위에서 구한 것처럼 고객 수 비율 쿼리를 만들어보겠습니다.
SELECT seg, COUNT(mem_no) AS seg_cnt,
ROUND((COUNT(mem_no) / (SELECT COUNT(*) FROM [RFM_BASE_SEG2]))*100, 2) AS seg_rate2
FROM [RFM_BASE_SEG2]
GROUP BY seg
ORDER BY 1
위와 같이 제대로 된 비율이 구해지지 않습니다(seg_rate2 열).
결론부터 말하자면 그 이유는 바로 COUNT 함수 때문입니다.
COUNT 함수는 위와 같이 비율을 구하고자 할 때 값이 제대로 집계되지 않습니다. (제 생각은 MS-SQL에서만 발생하는 오류인 듯 합니다. 만일 빅쿼리(BigQuery)를 쓰고 계신다면 잘 계산됩니다!)
그래서 비율을 구하고자 한다면, 반드시 1번에서 했던 예제처럼 SUM 함수를 사용해주셔야 합니다.
그런데, 고객 수를 구하는데 어떻게 COUNT가 아니라 SUM 함수를 쓰냐구요?
CASE WHEN 구문을 사용하여, 각 등급별(seg별) 고객에 대해 1로 두고 1을 전부 SUM하면 COUNT와 같은 값이 됩니다.
먼저 예시로 '1_VVIP' 등급의 고객 수를 CASE WHEN으로 뽑아봅니다.
SELECT SUM(CASE WHEN seg = '1_VVIP' THEN 1 ELSE 0 END) AS seg1
FROM [RFM_BASE_SEG2]
8명으로, 정확히 구해졌습니다.
이제 위에서 봤던 대로 전체 고객 수인 COUNT(*) 로 각 등급별 고객 수를 나눠주겠습니다.
SELECT ROUND(SUM(CASE WHEN seg = '1_VVIP' THEN 1 ELSE 0 END) / COUNT(*)*100, 2) AS SEG1,
ROUND(SUM(CASE WHEN seg = '2_VIP' THEN 1 ELSE 0 END) / COUNT(*)*100, 2) AS SEG2
FROM [RFM_BASE_SEG2]
하지만 이것도 역시 0으로 제대로 된 집계가 안됩니다. ㅠㅠ
그 이유는 비율을 구해주기 위해선 COUNT(*)의 값이 float 형태로 되어 있어야 하기 때문입니다.
그래서 CAST 함수를 사용해 COUNT(*) 를 float로 바꿔봅시다.
SELECT ROUND(SUM(CASE WHEN seg = '1_VVIP' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100,2)AS SEG1,
ROUND(SUM(CASE WHEN seg = '2_VIP' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100, 2) AS SEG2,
ROUND(SUM(CASE WHEN seg = '3_GOLD' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100, 2) AS SEG3,
ROUND(SUM(CASE WHEN seg = '4_SILVER' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100, 2) AS SEG4,
ROUND(SUM(CASE WHEN seg = '5_BRONZE' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100, 2) AS SEG5,
ROUND(SUM(CASE WHEN seg = '6_POTENTIAL' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100, 2) AS SEG6
FROM [RFM_BASE_SEG2]
결과는 이렇게 잘 나옵니다.
CASE WHEN을 사용했기에 새로운 열로 만들어져서, 열 6개가 생깁니다.
각 등급별 고객 수의 비율입니다.
그리고 '%' 값을 붙이고 싶다면 CONCAT을 사용해 이어주면 됩니다.
여기서 조심해야 할 점은, 지금은 데이터 형태가 float 형인데요. '%' 값은 문자이기에 그냥은 연결되지 않습니다.
그래서 비율 값을 CAST 함수를 활용해 문자(varchar)형으로 바꾼 후 이어주겠습니다.
-- %를 굳이 붙인다면
SELECT CONCAT(CAST(ROUND(SUM(CASE WHEN seg = '1_VVIP' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)*100,2) AS VARCHAR), '%')
FROM [RFM_BASE_SEG2]
예시로 seg1일 때의 비율에 '%'를 붙여준 값입니다.