수수료 정산 불일치를 SQL 집계로 원인 분석한 감사 보고서 작성

수수료 정산 불일치 분석은 개발팀보다 사업 부서에서 먼저 뛰어오는 이슈다. "왜 이 금액이 안 맞느냐"는 질문이 올 때 로그만 들여다보면 시간을 허비한다. 전체 집계가 보여야 한다.

이번 감사 보고서(20260424 1948 fee compliance audit)를 만든 계기도 그거였음. 수치 불일치 신호가 감지됐고, 범위가 어디까지인지, 어떤 케이스에서 터지는지를 코드 수정 전에 데이터로 확인해야 했다. 코드 먼저 건드리면 원인이 흐려지기 때문에, 집계 결과가 나오기 전까지는 DB 읽기 전용으로만 접근했음.

정산 도메인의 특성 상 단순 재현이 어렵다. 특정 케이스 하나를 재현하면 통과해도, 특정 기간 전체 배치를 집계하면 오차가 드러나는 경우가 많음. 그래서 "재현 가능한 예시 하나 찾기" 전에 "어느 범위에서 오차가 발생하는지 먼저 지도 그리기" 단계를 넣는다.

SQL 집계로 원인 지도 그리기

수수료 불일치 분석에서 쓰는 쿼리 패턴은 거의 항상 이 형태다. 기대값을 SQL 안에서 재현하고, 실제 값과 나란히 놓아서 차이를 뽑음:

SELECT
  order_id,
  SUM(base_amount * fee_rate)        AS expected_fee,
  SUM(settled_fee)                   AS actual_fee,
  ROUND(
    SUM(base_amount * fee_rate)
    - SUM(settled_fee), 2
  )                                  AS diff
FROM fee_orders
WHERE settled_at BETWEEN '2026-04-01' AND '2026-04-24'
GROUP BY order_id
HAVING ABS(SUM(base_amount * fee_rate) - SUM(settled_fee)) > 0.01
ORDER BY ABS(diff) DESC;

HAVING 절의 허용 오차(0.01)는 소수점 반올림을 감안해서 설정함. 부동소수점 비교 특성 상 완전히 0이 아닌 경우가 있어서, 의미 있는 차이만 걸러내는 게 낫다. ORDER BY ABS(diff) DESC로 가장 큰 불일치부터 보면 어느 케이스가 심각한지 바로 보인다.

불일치 분포를 보면 원인 가설이 자연스럽게 좁혀진다. 특정 fee_rate 값에 오차가 몰려 있으면 rate 적용 로직 문제이고, 특정 날짜 이후 전체가 틀어지면 설정 변경이나 마이그레이션이 원인일 가능성이 높음. 케이스별 분포를 보는 것만으로도 절반은 원인이 좁혀진다.

누락 건 탐지는 별도 쿼리로 같이 돌렸음. 정산됐어야 하는데 레코드 자체가 없는 케이스:

SELECT o.order_id, o.base_amount, o.fee_rate
FROM orders o
LEFT JOIN fee_orders f ON o.order_id = f.order_id
WHERE o.created_at BETWEEN '2026-04-01' AND '2026-04-24'
  AND o.status = 'completed'
  AND f.order_id IS NULL;

이 두 쿼리를 나란히 돌리면 "계산이 틀린 것"과 "아예 누락된 것"을 분리할 수 있다. 원인이 다르기 때문에 픽스도 다르게 접근해야 하고, 섞어서 보면 방향이 흐려짐.

HTML 보고서로 정리하는 이유

SQL 결과를 터미널에서만 보면 공유가 안 된다. 팀장 입장에서 분석 결과는 "내가 이해했다"로 끝내면 안 되고, 후속 대응의 근거 문서로 남아야 한다. HTML 파일 하나로 정리해두면 비개발 직군도 브라우저에서 바로 열 수 있고, 감사 기록으로도 보관된다.

이번 보고서 구성은 이 순서를 따랐음:

• 분석 범위 및 기간 명시
• 기대값 vs 실제값 비교 테이블
• 이상 항목 목록 + 원인 가설
• 재현 케이스 정리
• 후속 액션 항목

재현 케이스 정리를 보고서 중간에 넣는 게 특히 중요하다. "어떤 조건에서 이 불일치가 발생하는지" 재현 경로를 기재해두면, 나중에 픽스 PR 리뷰할 때도 참조할 수 있고, 동일 패턴이 다른 도메인에 있는지 점검 기준이 된다.

이번 분석에서 나온 발견 사항을 정리하면:

엣지 케이스는 항상 마지막에 드러난다. 수수료 0원 건, 취소 후 재정산 건, 소수점 반올림 기준이 다른 건 같은 것들이 집계 쿼리를 돌려야 보임. 이번에도 그랬음. 단위 테스트에서는 잡히지 않았던 케이스들이 집계에서 올라왔다. 이런 케이스들은 정상 입력으로 테스트하면 통과하지만, 실제 운영 데이터의 분포를 따라가면 터진다.

보고서 → 수정 → 재검증 사이클

분석 보고서를 쓰고 끝내는 게 아니라, 여기서 도출된 항목을 별도 커밋으로 픽스하고, 동일 집계 쿼리를 다시 돌려서 diff가 0이 되는지 확인하는 것까지가 한 사이클임. 이 과정 자체가 데이터 기반 검증이다.

사이클을 반복하면 점점 "예상 밖의 케이스"가 줄어든다. 처음에는 HAVING 조건을 걸면 수십 건이 나오다가, 픽스를 거치면서 0건으로 수렴함. 수렴하면 그 집계 쿼리 자체가 회귀 테스트 역할을 한다. 주기적으로 도는 모니터링 쿼리로 전환하면, 다음 번 불일치를 조기에 감지할 수 있는 탐지기가 생기는 거임.

정산 도메인 감사는 신중하게 다뤄야 한다. 금액이 틀리면 재정산이 필요하고, 재정산은 또 다른 불일치를 만들 여지가 생긴다. 픽스 커밋 이후에 보고서 쿼리를 다시 돌리는 루틴을 고집하는 이유가 그거다. "고쳤다"는 확신은 데이터에서 나와야 한다.

가장 단순한 원칙은 결국 "내가 예상한 숫자"와 "실제 DB 숫자"를 나란히 놓고 비교하는 거다. 차이가 있는 곳이 버그나 로직 오류의 위치를 가리켜준다. 예상과 실제가 같으면 로직이 맞는 것이고, 다르면 그 SQL 표현식이 재현하려는 애플리케이션 로직 안에 문제가 있다는 뜻임. 코드를 보는 것보다 데이터를 보는 게 훨씬 직접적이고, 실제 운영 데이터에서 발현된 오차는 집계 쿼리가 더 빠르게 찾아낸다. 이 방식을 계속 쓰는 이유가 그거다.