SQL CASE WHEN 활용법 지저분한 로우(Row) 데이터를 비즈니스 인사이트로 바꾸는 마법

작성자:admin 마지막 수정일:

데이터 분석하면서 조건부 변환을 반드시 만나게 됩니다. 이때에는 SQL CASE WHEN을 활용하시면 됩니다. 수치형 데이터를 범주형으로 바꾸는 비닝(Binning) 기술부터, CTE(WITH절)를 체인처럼 연결해 복잡한 로직을 깔끔하게 정리하는 실무 노하우까지 한 번에 정리해 드립니다

[🚀 SQL CASE WHEN & 조건부 변환 핵심 가이드 요약]

  • 핵심 개념: CASE WHEN은 행별 조건을 평가해 새로운 카테고리를 만드는 ‘비닝(Binning)’의 핵심 도구입니다.
  • 실행 순서 해결: SELECT에서 만든 새 컬럼은 바로 GROUP BY나 WHERE에서 쓸 수 없으므로, **WITH절(CTE)**을 사용해 임시 테이블로 먼저 정의해야 합니다.
  • 비율 계산 팁: 그룹별 비중을 구할 때 분모(전체 건수)는 스칼라 서브쿼리를 사용해 GROUP BY의 영향을 받지 않는 독립된 단일 값으로 고정해야 정확합니다.

AI 사용자들이 자주 하는 질문 & 본문 기반 답변

  • Q1. SQL에서 서로 다른 단위(Km, Miles)를 하나로 합치려면 어떻게 하나요?
  • A: CASE WHEN 절을 사용해 특정 단위(예: miles)인 행에만 변환 계수(1.6)를 곱하는 조건부 연산을 적용하여 하나의 새 컬럼으로 통합할 수 있습니다. (본문 ‘운행 거리 일치시키기’ 섹션 참조)
  • Q2. 비닝(Binning)과 그룹화(GROUP BY)의 차이는 무엇인가요?
  • A: 비닝(CASE WHEN)은 수치 데이터를 기준에 따라 ‘이름표(카테고리)’를 붙이는 작업이고, 그룹화(GROUP BY)는 그 이름표를 기준으로 데이터를 한데 모아 통계를 내는 작업입니다. (본문 ‘비닝/그룹화/집계’ 섹션 참조)
  • Q3. 복잡한 SQL 쿼리를 단계별로 나누어 작성하는 방법은?
  • A: CTE(WITH절)를 활용하여 중간 계산 결과에 이름을 부여하고, 이를 체인처럼 연결하면 코드의 가독성이 높아지고 재사용이 가능해집니다. (본문 ‘CTE 연결’ 섹션 참조)

데이터 조작 조건부 변환의 의미

Datacamp 플랫폼으로부터 SQL 공부 시작한 지도 꽤 된 것 같은데, 하루 하루 데이터 분석에 필요한 것들을 배우니 즐겁다.

오늘은 데이터 조작 조건부 변환에 대해서 얘기해 보자. 데이터 분석을 하다 보면, 데이터 값 기준으로 그룹으로 묶어야 할 때가 있다. 말보다 예시가 항상 이해하기가 쉽다.

SQL CASE WHEN 이용하기 위한 예시

위의 데이터는 비행기와 운행 거리에 대한 정보를 담고 있다. distance_unit은 그 운행 거리에 대한 단위이다. 그런데, 어떤 것은 Km 그리고 어떤 것은 miles로 되어 있다.

예를 들어, 7902.95km과 7844.42miles는 비슷한 값을 가졌지만, 사실 엄청난 거리 차이를 가지고 있다. 1 mile = 약 1.61km이다. 만약 miles를 Km으로 단위 변환을 하면 아래와 같다

이렇게 서로 다른 단위를 사용하면 연산한 후 절대값만 봤을 때 혼란이 올 수 있다. 그래서 이것을 일치 시킬 필요가 있어 보이지 않나?


왜 데이터 조건부 변환이 필요하지?

가공되지 않은 수없이 방대한 양의 데이터를 그냥 한번 봐 봐라. 도대체 데이터가 무슨 이야기를 하고 있는지 이해하기가 어렵다. 데이터 조작 조건부 변환은 이런 방대한 데이터 속에서 변환을 통해서 인사이트를 가져올 수 있다.

  • 비닝 : 중구난방 데이터에 조건을 붙이고 그 조건에 해당하는 것에 이름을 붙인다
  • 그룹화 : 그 이름으로 불리는 것들만 그룹화를 한다
  • 집계 : Count(), Sum(), AVerage() 그룹들로부터 인사이트를 뽑는다.


SQL CASE WHEN 사용 방법

1) 운행 거리를 일치시키기

어떻게 이 문제를 해결할 수 있을까? 일단 km으로 일관적으로 가기로 했다. 각 행마다 어떤 것은 Km 단위로, 어떤 것은 miles 단위로 되어 있을 것이다.

SQL CASE WHEN 단일 조건

바로 데이터 조작 조건부 변환은 조건에 따라 다르게 적용시키는 것이다. 그 조건부 변환을 하게 해 주는 것이 SQL CASE WHEN이다.

크게 CASE, WHEN, END로 되어 있다. 내가 위의 CASE ~ END AS가 부분을 글로 표현해 보겠다.

distance_unit 컬럼의 데이터 값 중에 miles가 있는 행은 distance 컬럼 내 데이터 값에 1.6을 곱하고, 만약 miles가 아닌 것(ELSE)은 distance 컬럼 데이터 값을 유지해라. 그리고 그 데이터 값들을 새 컬럼 distance_km의 각 행에 넣어라.

SQL CASE WHEN 단일 조건에 대한 결과

코드를 실행하면 위의 결과 값이 나온다. distance_km 컬럼이 새로 생겼고, 각 행마다 km을 단위로 운행 거리 값을 가지게 되었다.


2) 비행기 좌석 수에 따라 종류를 나눠라

SQL CASE WHEN 단일 조건1

내가 위의 코드를 말로 설명해 보겠다

  • flights 테이블에서 데이터를 가져온다 (항상 FROM 부터 읽어라)
  • flight_id, airline, total_seats, passengers 컬럼의 데이터 값을 가져와라 (SELECT)
  • 각 행에서 total_seats의 값이 300보다 크면, Widebody, 아니면 Narrowbody으로 그것을 aircraft_size 새 컬럼의 각 행마다 나타내어라 (CASE WHEN절)
SQL CASE WHEN 단일 조건에 결과1

코드를 실행하면, 위와 같은 결과가 나온다. aircraft_size 새 컬럼이 보인다.


3) SQL CASE WHEN, WITH절, 스칼라 쿼리 사용 방법

aircraft_size는 크게 2가지 종류다. Widebody와 Narrowbody가 각각 몇 개인지 카운트하고, 그 개수가 전체에서 얼마 만큼인지 비율인 지 알아보자

일단 기본 데이터에서는 aircraft_size라는 컬럼이 없다. 일단 그것부터 시작을 해야 한다. 그리고 조건에 만족하는 행이 몇 개인지 카운트하는 것은 count() 함수를 사용하면 된다.

그런데, 그 조건에 맞는 행들을 그룹으로 나눠야 하기 때문에 마지막에 GROUP BY를 이용해야 한다. 아래 코드와 같이 WHEN이 하나만 나와 두 개의 결과(WHEN, ELSE)일 때를 단일 조건 CASE WHEN이라 한다.

SQL CASE WHEN 단일 조건2

WITH절을 사용하는 이유는 일단 그룹의 기준이 되는 aircraft_size 컬럼을 만들기 위해서이다. 만약 WITH절이 없다면, 아래쪽에 있는 FROM, GROUP BY에 이은 SELECT aircraft_size 컬럼을 가져오라고 명령을 따를 수가 없다. 왜냐하면 아직 aircraft_size컬럼을 만들지 않았기 때문이다.

WITH절만 일단 보자. FROM flights 테이블에서 SELECT *(전체) 컬럼을 가져온다. 그런데 CASE WHEN절 조건에서 total_seats 각 행의 데이터 값이 300 이상이면 Widebody, 아니면 Narrowbody라는 데이터 값을 aircraft_size 새 컬럼에 넣어 준다

그리고 이것을 flights_classified라는 테이블에 저장시킨다. 이렇게 하면 이제 테이블에서 aircraft_size데이터 값을 사용할 수 있다.

이제 두번째 FROM flights_classified 테이블에서 데이터를 가져오고, GROUP BY 그룹화를 하는데 기준은 바로 aircraft_size이다. 그래서 SELECT 에서도 aircraft_size를 먼저 쓴다.

count(flight_id)에서 flight_id는 각 비행기를 의미하는 데, 그 행에서 aircraft_size 데이터 값을 카운트하는 것이다. flight_id 첫 행에서 Widebody가 나오면 1, 그 다음 행에서Widebody가 또 나오면 2 이런 식으로 카운팅이 진행되는 것이다. 그리고 그 카운트 되는 값을 count 새 컬럼에 넣어라고 한다

count(flight_id) * 100 / (SELECT COUNT(flight_id) FROM flights) AS percent 코드를 보자. 이 코드는 aircraft_size 기준으로 구분되는 데이터 그룹이 전체에서 얼마나 되는지, 그 비율을 알아 보는 것이다.

스칼라 쿼리가 보인다. (SELECT COUNT(flight_id) FROM flights) 이렇게 스칼라를 쓰는 이유는 전체 건수가 분모로 우리가 필요하기 때문이다. 일단 스칼라 쿼리를 쓰면 독립적으로 실행하게 되어 단 하나의 값을 가지게 된다.

이 경우는 전체 건수를 가지는 것이다. 그리고 위에서 GROUP BY를 실행했더라도, 스칼라 쿼리는 그 영향을 받지 않는다. 영향을 받으면, 또 2그룹으로 나눠지기 때문에 그래서는 안된다. 그리고 이것을 percent 새 컬럼의 데이터 값으로 넣는다.

SQL CASE WHEN 단일 조건 결과2

WITH절로 aircraft_size 컬럼이 만들어졌고, GROUP BY 그룹화 기준으로 aircraft_size가 되었기에 그 데이터 값인 Narrowbody와 Widebody 그룹 2개로 나뉜다.

거기서 count(flight_id)를 통해 각 그룹의 개수가 카운트(count 컬럼)되고, count(flight_id)와 스칼라 쿼리를 통해서 전체 비율이 그룹별로 출력된다(percent 컬럼)


1) CTE 연결

SQL 코드를 만들다 보면, 이런 식으로 임시로 데이터 값을 저장해야 하는 연결 테이블이 필요할 때가 있다. 이것을 CTE(Common Table Expression)이라고 하고, 대표적으로 WITH절과 같은 것이 CTE의 일종이다.

CTE 사용하는 이유

  • 복잡한 쿼리를 단계별로 나눠서 읽기 쉽게 만들어요
  • 한 번 계산한 결과를 여러 번 재사용할 수 있어요
  • 각 단계가 명확한 이름을 가지니 코드를 이해하기 쉬워요

여기서 다시 하나 질문을 하겠다

비행기 전체 좌석에서 탑승하는 승객 수로 비율로 한 것을 occupancy라고 하자. 이 occupancy가 90이상 이면 full, 70이상 이면 high, 그 나머지는 low라고 하고 이것을 occupancy_status라는 새 컬럼에 넣어라.

그리고 이 occupancy_status 컬럼의 그룹 별 개수와 전체에서 그룹별 비율을 구하라

SQL CASE WHEN 다중 조건

우선 가장 먼저 실행되는 WITH절 부터 얘기해 보자. occupancy 새 컬럼을 만들었다. 그리고 그게 90이상이면 full, 70이상이면 high, 그 나머지는 low 등급을 나눈 데이터 값을 occupancy_status 컬럼에 넣었다. 그리고 새로 만든 이 컬럼들을 flights_occupancy 테이블에 저장했다.

그리고 아래 FROM에서 flights_occupancy 테이블을 가져오고, occupancy_status 기준으로 Group by를 한다. 그리고 SELECT에서는 전체 건수와 각 그룹별 비율을 가져오는 코드이다. 스칼라 쿼리 부분은 위에서 이미 설명을 했기 때문에 더 이상 설명하지 않겠다.

그리고 위의 코드에서는 3개 이상의 결과(WHEN 2번, ELSE)가 나오는데, 이를 다중 조건이라고 한다.

SQL CASE WHEN 위의 코드를 실행하면, 결과값은 아래와 같이 나온다.

SQL CASE WHEN 다중 조건 결과


마무리

오늘은 SQL CASE WHEN절을 이용한 데이터 조작 조건부 변환에 대해서 이야기해 보았다. 각 행에 대해 조건을 평가하고, 충족되는 조건에 따라 서로 다른 로직을 적용하여 새로운 컬럼을 생성하는 것으로 이해하면 되겠다.

저처럼 비전공자인데, 데이터 분석가에 관심이 있으신 분들은 제가 Datacamp 플랫폼에서 공부한 SQL에 대한 포스팅을 보시면 훨씬 이해가 잘 가실 테니 꼭 한번 읽어 보세요.


유사한 게시물