SQL 집계 함수 조건 활용법 평균의 함정에서 벗어나는 CASE WHEN 조건부 집계 (DataCamp 완강 후기)

단순한 전체 평균은 때로 진실을 가립니다. 모든 데이터를 합치는 것이 아니라, 내가 원하는 조건만 쏙쏙 골라 계산할 수 있어야 진짜 분석가입니다. 오늘은 지저분한 로우 데이터에서 날카로운 통찰을 뽑아내는 SQL 집계 함수 조건 활용법의 끝판왕을 소개합니다

[💡 SQL 집계 함수 조건 핵심 요약]

[❓ SQL 조건부 집계 자주 묻는 질문 (FAQ)]

전체 평균은 ‘가짜 지표’일 수 있다.

비행기 전체 평균 지연 시간이 5분이라고 해서 안심하고 있나요? 취소된 항공편을 빼고, 장거리 비행기만 따로 본다면 그 숫자는 완전히 달라질 수 있다. 진짜 문제를 찾아내기 위한 SQL 집계 함수 조건 활용 테크닉, 지금 시작합니다.

SQL 집계 함수 조건이라는 말이 나는 처음에 이해가 가지 않았다. 집계 함수는 아래를 보면 아마 기억이 떠 오를 수도 있다. 딱 보면 예전에 다 공부했던 것이다.

• AVG() : 평균
• SUM() : 합계
• COUNT() : 카운트 건수 세기
• MIN() : 최소값
• MAX() : 최대값

SQL 집계 함수 조건은 특정 데이터 값이 조건에 맞을 때, 위와 같은 집계 함수를 이용하여 유의미한 인사이트를 구하는 것을 말한다.

전체 합계, 전체 평균은 큰 덩어리 측면에서 이해하기에는 좋지만, 세부적으로 우리가 진짜 필요한 정보는 세심하게, 즉 조건을 붙여서 얻어내야 한다.

실제로 도착한 비행 편만의 평균 지연 시간

우리가 비행기를 타기 위해 공항에 가면 커다란 스케쥴 전광판에 수 많은 비행 편을 볼 수 있다. 그렇다고 이 비행기 전체가 다 운행 되는 것도 아닌다. 대부분은 목적지에 도착하지만, 갑자기 취소가 될 수도 있고, 가다가 다시 어떤 문제로 회항하는 경우도 있다.

우리가 비행기의 평균 지연 시간을 알고 싶다고 했을 때, 이렇게 취소되거나, 회항한 비행기의 데이터를 넣는 것이 맞는지에 대한 의문이 있다. 아닐 것이다. 정확한 데이터 결과를 얻기 위해서 목적지에 도착한 비행 편만 가지고 평균 지연 시간을 구하는 것이 합리적이다.

질문 1) 도착한 비행기 편에 대해서만, 항공사 별로 평균 지연 시간을 구하세요.

도착한 비행기 편에 대해서만 진행을 해야 하기에, 우리는 여기서 필터링이 필요하다. 그 부분이 바로 Where flight_status =’arrived’이다. 만약 SQL 필터링에 대한 공부가 필요하신 분들은 먼저 그거 부터 보고 오시라.

원래 여기에 cancelled 등이 있었는데 우리는 그것 필요없다 도착한 ‘arrived’만 필요하다. 여기 코드에서 arrived를 제외한 것들은 다 없어진다고 보면 된다.

항공사별의 평균 지연 시간이 필요하기에 다음 코드에서 Group by가 필요하고 그 기준으로 airline이 된다. 그리고 Select 에서 당연히 항공사 별이므로 airline 컬럼이 필요하고, 평균 지연 시간을 계산하는 Avg() 함수와 지연 시간 값이 있는 delay 컬럼이 사용했다.

마지막으로 평균 지연 시간이라는 새로운 컬럼 Avg_delay를 만들면 끝이다. 그 결과 값은 아래와 같다

Airjet 항공사의 평균 지연 시간은 약 11분 정도이고, Skywings는 평균적으로 도착 시간보다 일찍 도착하는 항공사이다.

질문 2) 평균적으로 일찍 도착하는 항공사와 5분 이내의 지연 시간을 가지고 있는 항공사는 큰 문제가 없다고 보고, 평균적으로 5분 이상 지연 시간을 가졌던 비행편의 항공사별로 평균 지연 시간을 뽑아보자.

집계 함수로 얻어진 값에 조건 붙이기 HAVING

집계 함수에 조건을 붙이는 코드에는 HAVING이 필요하다. 여기 HAVING 코드는 지연 시간의 평균 값을 구한 것이 5보다 큰 것만 구해라는 것이다.

우리가 항공사별로 지연 시간의 평균이 5분보다 큰 것을 구하면 아래와 같이 2개의 항공사만 나온다.

질문 3) 만약, 취소된 항공기 편을 제외하고, 비행기 기종(보잉 747 등)을 그룹화하여 기종 별 총 건수와 기종 별 항공사 몇 개인지를 구하고, 기종 별로의 평균 운행 거리를 구해라

[내가 생각하는 논리 전개]

• 만약, 취소된 항공기 편을 제외하고 : Where 필터링
• 비행기 기종(보잉 747 등)을 그룹화 : Group by plane_type
• 기종 별 총 건수와 기종 별 항공사 몇 개인지를 구하고, 기종 별로의 평균 운행 거리 : Select에서 Count(), Avg()를 이용

질문4) 그래도 운행이 많이 되지 않는 기종은 빼고 싶은데, 최소한 운행이 100편을 초과하는 기종 별 데이터를 알아보자 (질문 3 코드 이용)

이미 Group by로 기종별로 나눠져 있는 상태이다. 그 상태에서 HAVING 코드를 사용해서 각 기종별로 비행 운행(flight_id)의 건수를 카운트해서 그게 100이 넘는 것만 나타내도록 한 코드이다.

모든 항공사의 평균 지연과 지연된 항공편만의 평균 지연 알아보는 이유

조건부 집계(Conditional Aggregation)는 별도의 쿼리를 여러 번 실행하지 않고, 각각 고유한 필터 조건을 가진 여러 지표를 하나의 쿼리에서 계산할 수 있다. 항공사별로 아래 값을 구해보자

• 모든 항공편의 평균 지연
• 지연된 항공편만의 평균 지연

지연된 항공편만의 평균 지연을 알아보자고 하면, 언뜻 Where Avg(delay) > 5를 떠올리기 쉽다. 하지만, 이러면 이미 지연 시간 5분이 안되는 것들은 다 제외가 되어 버리기 때문에 모든 항공편의 평균 지연을 구할 수가 없다.

조건부 집계 함수는 AVG(CASE WHEN delay > 5 THEN delay END)이다. CASE WHEN으로 조건을 달고, 앞에는 집계 함수 AVG()를 사용하고 있다.

SELECT 코드에서 AVG(delay)는 모든 항공사의 지연 평균 시간을 구하는 것이고, 조건부 집계 함수는 지연 시간이 5분 초과할 때는 기존의 delay 값을 넣어라고 하고 있다.

만약 항공사의 그 행(flight_id)에서 delay 값이 7이면 이는 5보다 크기 때문에 그대로 7 값을 가지고, 만약 3이면 이는 5보다 작기 때문에 이때는 어떤 값도 지정하지 않았기에 집계함수(avg, sum 등)은 NULL로 인식하고 계산할 때 무시한다. 조금 더 자세히 보자

1) 집계 함수가 NULL을 어떻게 처리할까?

• AVG: NULL이 아닌 값만 분자와 분모에 기여합니다
• SUM: NULL이 아닌 값만 합산됩니다
• MIN/MAX: NULL이 아닌 값만 비교됩니다
• COUNT(column): 해당 컬럼의 NULL이 아닌 값만 계산하지만, 전체를 나타내는 *의 경우 NULL을 포함해서 계산합니다.

이것이 바로 CASE WHEN이 조건부 집계에서 작동하는 이유입니다 — 조건이 충족되지 않으면 CASE가 NULL을 반환하고, 그 NULL은 자동으로 제외됩니다.

Airjet은 모든 항공편 평균 지연 시간이 11분이고, 지연 시간이 5분 초과하는 항공편들만 모아 계산한 평균 지연 시간은 12분이다. 별 차이가 없다는 것은 고객들은 Airjet은 기본적으로 도착을 늦게 한다는 것을 예상한다는 뜻이다.

SkyWings는 모든 항공편의 평균 지연 시간은 -0.1분으로 이는 예상 도착 시간보다 일찍 도착한다는 것을 의미한다. 그런데, 지연 시간이 5분을 초과하는 것들만 모아서 계산한 평균 지연 시간은 약 7.8분이다.

이 말은 평소에는 SkyWings는 시간을 잘 지키지만, 한번 지연 비행이 발생하면 큰 차이로 지연이 생긴다는 것을 의미한다.

2) 비즈니스 측면에서 대응 전략

질문5) 질문3이 코드에서 운행거리가 3000이 넘는 비행의 평균 운행 거리를 구하세요

조건부 집계 함수를 이용해서 코드를 만들 수 있다. CASE WHEN을 통해 distance > 3000인 것은 distance 값 그대로 가지고, 아닌 것은 그냥 NULL으로 인식이 되는 것이다. 그야말로 조건에 맞는 값만 가지고 집계해라는 말이 아닌가!

지금까지는 장거리(>3000)를 주로 하는 각 기종별로의 평균 비행 거리를 구해보았다.

질문6) 각 기종별로 장거리를 뛰는 비행기 운행 건수는 얼마인지 알아보자

코드를 실행하면 아래와 같은 결과 값이 나온다

3) 기대치를 설정해야 하는 이유

기대치 설정은 품질 관리이다. 결과를 보고하기 전에 “이게 맞나?” 라고 스스로 검증하는 것이다. 기대치를 설정하면 데이터 값이나, 그 결과 값이 나왔을 때 이게 말이 돼? 하면서 한번 더 확인하는 작업이 될 수 있다. 기대치는 아래와 같은 기반으로 할 수 있다

• 값의 범위: 의미 있는 최소값과 최대값 — 예를 들어, 고객 연령을 분석한다면 평균 연령이 0세나 200세가 나오면 문제가 있는 것이다.
• 값들 간의 관계: 한 지표가 다른 지표보다 커야 함 — 예를 들어, 업무에서 “실제 구매 고객 수”는 “VIP 고객 수”보다 많아야 한다.
• 논리적 제약: 반드시 참이어야 하는 속성 — 예를 들어, 고객 수는 음수가 될 수 없다. 할인율은 100%를 초과할 수 없는 것도 마찬가지다.
• 수학적 관계: 부분의 합이 전체와 같아야 함 — 예를 들어, 각 지역별 매출을 합하면 전체 매출과 같아야 한다.

질문 7) 대형 항공편(좌석 >=300)에 대해 각 항공사 별로 총 운항 횟수, 장거리 운항 횟수(거리>3000), 평균 연료 효율(모든 항공편), 평균 연료 효율(장거리 항공편만) 구하라 (최소한 항공 운항 실적이 50회 이상은 되어야 한다)

코드를 실행하면 아래의 결과가 나온다.

4) SQL 실행 순서

실행 순서를 이해하는 것은 어떤 필터링 기법을 사용할지 결정하는 핵심이다.

WHERE는 GROUP BY 이전에 실행되어 행을 필터링하며, HAVING은 GROUP BY 이후이자 SELECT 이전에 실행되어 집계 결과를 바탕으로 그룹을 필터링한다.

마무리

오늘은 SQL 집계 함수 조건에 대해서 공부해 보았다. 필터링으로 기본적으로 필요 없는 데이터를 제거하고, Group by를 통해서 원하는 그룹 기준을 잡는다. Having으로 집계 함수 값에 조건을 줘서 아닌 그룹은 또 제거한다.

마지막으로 SELECT에서 CASE WHEN 조건절을 이용해서 원하는 조건에 맞는 데이터 값만 가지고 집계 값을 도출해 낸다. 이 강의를 마지막으로 DATACAMP의 Intermediate SQL를 마쳤다.

필요한 함수 및 코드를 만드는 것도 중요하지만, 비즈니스에 필요한 질문을 어떻게 할 것이고 그 질문들을 어떻게 세부화하여 SQL 코드를 변환시킬 수 있는지가 관건인 것 같다.

물론 그 결과로 인해서 비즈니스에 필요한 인사이트를 도출하는 것이 우리가 SQL를 배우는 이유 중에 중요한 요소가 아닌가 싶다.