SELECT만으로는 부족한 실무 분석과 7가지 패턴

데이터를 DB에서 추출한 뒤 Pandas나 Python으로 가져와 이전 행과 값을 비교하거나 그룹별 최댓값을 찾기 위해 복잡한 for문을 돌리면, 데이터 규모가 커질수록 메모리 부하가 심해지고 코드가 길어진다. 이러한 전처리 과정을 DB 단에서 해결하면 데이터 파이프라인이 단순해지고 연산 속도가 빨라진다. 이번 가이드에서는 단순 집계를 넘어 실제 비즈니스 지표를 산출하는 7가지 SQL 분석 패턴을 다룬다.

실습을 위해 가상 SaaS(Software as a Service) 기업의 고객 트랜잭션 데이터를 사용한다. 분석 대상은 7명의 고객이 2023년 9월부터 2024년 6월까지 발생시킨 총 36건의 트랜잭션 내역이다. 쿼리를 실행하기 전 `seed.sql` 파일을 통해 데이터셋을 먼저 생성해야 한다. 이 데이터셋은 고객별 결제 시점과 금액, 플랜 변경 이력 등을 포함하고 있다.

실무에서는 단순 `SELECT`와 `GROUP BY`만으로 해결하기 어려운 과제가 많다. 고객의 갱신 주기나 이탈 신호를 포착하는 LAG/LEAD 패턴, 플랜 업그레이드 경로를 추적하는 Self-Join 기법, 고객별 최고 금액 트랜잭션을 추출하는 ROW_NUMBER, 지출 금액에 따라 고객 티어를 나누는 NTILE, 월별 매출 추세를 읽는 Rolling Average, 매출과 환불 건수를 동시에 산출하는 FILTER, 그리고 연속 활성 월을 계산하는 Streak Detection까지 총 7가지 패턴을 정의한다.

행 간 이동과 그룹 내 순위 산정: LAG, ROW_NUMBER, NTILE

SQL의 `LAG()` 함수를 쓰면 Self-Join 없이 바로 이전 행의 값에 접근해 이벤트 간 간격을 계산할 수 있다. 고객의 갱신 주기나 이탈 신호를 찾을 때 유용하며, 파티션 내 첫 번째 행은 참조할 이전 값이 없으므로 NULL로 반환된다. `LEAD()`는 반대로 다음 행의 값에 접근하므로 다음 갱신 시점이나 이탈 전 마지막 트랜잭션을 식별하는 데 쓰인다.

카테고리별로 가장 높은 금액의 트랜잭션을 단 한 건만 뽑는 Top-N 추출은 `ROW_NUMBER()`가 해결한다. 공통 테이블 식별자(CTE, Common Table Expression) 내에서 이 함수를 사용해 파티션별로 순번을 매긴 뒤, 외부 쿼리에서 1번 행만 필터링하는 방식이다. 이때 금액이 같은 행이 여러 개라면 `created_at DESC` 같은 보조 정렬 기준을 추가해 가장 최근 트랜잭션이 1번이 되도록 결정한다. 공동 순위를 유지하고 싶다면 `RANK()`(공동 1위 후 3위로 건너뜀)나 `DENSE_RANK()`(건너뜀 없이 2위로 이어짐)를 사용한다.

고객을 지출 금액 기준으로 4개 그룹으로 나누는 쿼타일(Quartile) 분석은 `NTILE(n)` 함수를 사용한다. 정렬된 전체 행을 n개의 동일한 버킷으로 분할해 각 행에 버킷 번호를 부여하는 원리다. `CASE WHEN` 문으로 정적 임계치를 직접 입력하는 방식과 달리, NTILE은 전체 분포를 기준으로 상대적인 위치를 정한다. 4분위수 분석에서 4번 버킷은 최상위 지출 고객, 1번 버킷은 최하위 고객이 된다. 새로운 고객이 추가되어도 버킷이 자동으로 재조정되므로 정적 기준점을 매번 수정할 필요가 없다.

상태 변화 추적과 조건부 집계: Self-Join과 FILTER

사용자의 상태가 Starter에서 Pro로 변하는 흐름을 포착하려면 행과 행 사이의 관계를 정의해야 한다. 동일한 테이블을 두 번 불러와 서로 비교하는 Self-Join을 쓰면 이 문제를 해결할 수 있다. 테이블에 `t1`과 `t2`라는 별칭을 붙여 각각 다른 시점의 데이터로 취급하고, `t2.created_at > t1.created_at` 조건을 걸어 시간 순서를 강제하면 실제 업그레이드 경로를 정확히 추적할 수 있다.

한 고객이 Pro 플랜으로 넘어가기 전 Starter 플랜을 여러 번 결제했다면 조인 과정에서 중복 행이 발생한다. 이때 `DISTINCT`를 사용해 중복을 제거하면 고객당 한 건의 업그레이드 기록만 남길 수 있다. 이 구조를 활용하면 다운그레이드나 이탈 후 재가입 같은 상태 변화를 모두 찾아낼 수 있다.

여러 조건의 집계를 위해 쿼리를 여러 번 짜거나 서브쿼리를 엮는 대신 `FILTER` 구문을 쓴다. 단일 쿼리 내에서 매출, 환불, 실패 건수를 각각 다른 컬럼으로 한 번에 산출할 수 있다. `FILTER`는 데이터 전체를 한 번만 스캔하여 각 조건에 맞는 값만 골라 집계하므로 처리 효율이 높다.

`FILTER`는 PostgreSQL과 BigQuery에서 표준으로 작동하지만, Snowflake 같은 환경에서는 `SUM(CASE WHEN status = 'completed' THEN amount END)` 형식을 대안으로 사용한다. 또한 FILTER 결과가 없는 달은 NULL로 표시되는데, 리포트 가독성을 위해 0으로 표시하고 싶다면 `COALESCE()` 함수로 감싸 처리한다.

시계열 노이즈 제거와 연속 활성 기간(Streak) 탐색

월별 매출처럼 변동성이 큰 시계열 데이터는 `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` 구문을 사용해 3개월 이동 평균을 계산한다. 현재 행과 앞선 두 행을 하나의 창으로 묶어 평균을 내는 방식이며 이는 `OVER` 절 내에서 정의된다. 데이터가 부족한 초기 두 행은 각각 1개월과 2개월 평균으로 처리된다. 이렇게 계산된 이동 평균은 단기적인 노이즈를 제거해 비즈니스 성장 곡선을 명확하게 보여준다.

정렬 기준이 되는 값에 중복이 있을 때는 `ROWS` 대신 `RANGE`를 사용한다. `RANGE`는 동일한 정렬 값을 가진 모든 행을 하나의 그룹으로 묶어 처리하므로 타임스탬프가 겹치는 데이터셋에서 유용하다. 윈도우 크기를 `5 PRECEDING`으로 수정하면 6개월 단위의 추세를 볼 수 있으며, 이를 통해 계절성 요인을 제어할 수 있다.

연속 활성 기간을 찾는 스트릭 탐색은 윈도우 함수를 활용한다. 먼저 고객별로 활성 월에 순차적인 행 번호를 부여하는 `ROW_NUMBER` 함수를 적용한 뒤, 월 날짜에서 이 행 번호를 뺀다. 날짜가 연속적이라면 행 번호와 날짜가 함께 증가하므로 뺄셈 결과는 항상 동일한 상수값이 나온다. 이 상수값이 하나의 스트릭을 식별하는 키가 되며, 중간에 공백이 생기면 상수값이 변하며 새로운 그룹이 형성된다.

실제로 완료된 트랜잭션이 하나라도 있는 월을 대상으로 이 기법을 적용하고, 날짜와 행 번호의 차이가 같은 행들을 그룹화하면 고객별 연속 활성 기간이 산출된다. 이는 파이썬의 for문으로 이전 행과 현재 행을 일일이 비교하며 상태를 저장하던 로직을 쿼리 한 줄로 대체하는 방식이다.

파이썬 전처리를 DB 쿼리로 옮겨야 하는 실무적 이유

데이터 전처리 위치를 DB로 옮기면 리소스 효율이 높아진다. 수백만 건의 원천 데이터를 파이썬 메모리로 옮겨 필터링하고 변환하는 방식은 네트워크 부하를 일으키고 애플리케이션 서버의 CPU 자원을 과도하게 점유한다. 반면 윈도우 함수나 `FILTER` 구문을 통해 DB 단에서 계산을 끝내면, 파이썬으로는 이미 계산이 완료된 최종 결과값만 전달되어 전송 데이터 양과 메모리 사용량이 줄어든다.

로직의 일관성 측면에서도 이점이 있다. 리텐션 분석이나 업그레이드 퍼널 추적 로직을 DB 쿼리에 내장하면, 분석가마다 서로 다르게 작성한 파이썬 전처리 코드 때문에 결과값이 달라지는 휴먼 에러를 방지할 수 있다. 동일한 SQL 기준의 데이터를 팀 전체가 빠르게 공유할 수 있어 분석 사이클이 단축된다.

전처리 단계를 SQL로 옮길지 결정하는 기준은 명확하다. 행 간의 값 비교, 그룹 내 순위 지정, 특정 윈도우 범위의 집계가 주된 작업이라면 SQL로 로직을 옮기는 것이 유리하다. 반면 외부 API 연동이나 복잡한 머신러닝 모델 적용이 필요할 때만 파이썬의 전처리 기능을 사용한다. 데이터 변환 로직을 DB 쿼리로 최대한 밀어 넣을수록 파이썬 코드는 간결해지며 유지보수 비용이 감소한다.

Pandas에서 for문을 돌리며 이전 행과 값을 비교하거나 그룹별 최댓값을 찾기 위해 씨름하던 시간은 이제 낭비에 가깝다. LAG, ROW_NUMBER, ROWS BETWEEN 같은 7가지 SQL 패턴을 활용해 분석 로직을 DB 단으로 밀어 넣으면, 파이썬 코드는 결과만 받아 출력하는 간결한 껍데기로 변한다.

데이터 전처리의 무게중심을 파이썬에서 DB로 옮기는 것만으로도 분석 환경의 복잡도는 획기적으로 줄어든다. 지금 바로 기존 파이프라인에서 반복문이 포함된 전처리 구간을 찾아 SQL 쿼리로 대체하며 코드의 응집도를 높이는 작업을 시작해 보길 권한다.