집계 기초: SUM/COUNT로 잔액·기간별 적립/사용 리포트 만들기

포인트 시스템의 “진짜 쓸모”는 원장(point_history)에 기록된 사실을 사람이 이해할 수 있는 형태로 요약하는 데서 나옵니다. 화면에서는 잔액 한 줄로 보이지만, 그 뒤에는 적립/사용/만료/취소가 뒤섞인 수많은 기록이 존재합니다.

그래서 포인트 시스템에서 SQL의 핵심은 단순 조회(SELECT)보다 집계(aggregation)입니다. 집계는 원장 데이터를 합치고(SUM), 개수를 세고(COUNT), 기준별로 묶어서(GROUP BY) “의미 있는 숫자”로 바꾸는 과정입니다.

이 단원의 목적: 원장 데이터로 ‘요약 결과’를 만드는 습관 만들기

이번 글에서는 아래 3가지를 목표로 합니다.

  1. SUM으로 잔액을 만들고, 기간별 합계를 안정적으로 계산한다
  2. COUNT로 이벤트 건수를 집계해 “양”을 파악한다
  3. 집계를 할 때 자주 발생하는 실수(NULL, 숨김 데이터, 중복 기준)를 피한다

준비: 집계를 위한 최소 데이터 점검

집계는 데이터가 적으면 티가 안 나지만, 데이터가 쌓이면 작은 실수가 큰 오차로 확대됩니다. 그래서 집계 전에 “필수 조건”을 먼저 확인해 두면 좋습니다.

  • amount가 NULL이 아닌가
  • 숨김 데이터(hide=1)가 섞여 있다면 집계에서 제외할 것인가
  • 부호 정책(적립 + / 차감 -)이 일관적인가

아래 예시는 hide 컬럼이 있다고 가정하고, 집계에서는 hide=0만 포함하는 패턴으로 진행합니다. 

SET @uid := '11111111-1111-1111-1111-111111111111';

1) 잔액 계산의 기본: SUM(amount)

가장 기본적인 잔액 조회는 “해당 사용자의 amount를 전부 더하는 것”입니다. 이 방식의 장점은 단순함과 신뢰성입니다. 원장이 올바르면, 잔액은 항상 재현 가능합니다. 

SELECT uid, SUM(amount) AS balance FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid;

여기서 GROUP BY가 꼭 필요한 이유는 “사용자별”로 묶기 위해서입니다. uid 조건을 특정 사용자로 고정했더라도, 습관적으로 GROUP BY를 넣으면 나중에 “여러 사용자 잔액을 한 번에 뽑는 쿼리”로 확장하기 쉬워집니다.

2) 기간별 합계: “최근 30일 동안 얼마나 변했나”

잔액은 누적 결과이고, 기간별 합계는 “변화량”입니다. 운영에서는 보통 잔액보다 변화량을 더 자주 봅니다. 예를 들어 “이번 달에 적립이 비정상적으로 늘었나?” 같은 질문이 여기에 해당합니다. 

SELECT uid, SUM(amount) AS delta_30d FROM point_history WHERE uid = @uid AND hide = 0 AND created_at >= NOW() - INTERVAL 30 DAY GROUP BY uid;

주의: created_at의 의미가 정책에 따라 달라질 수 있다

created_at이 “기록 시간”인지 “이벤트 발생 시간”인지에 따라, 기간 집계 결과는 달라집니다. 시스템에서 두 시간이 다를 수 있다면 별도 컬럼을 두는 설계도 고려할 수 있습니다. 지금 단계에서는 created_at을 기준으로 통일하고, 이후 만료 배치/정산 단원에서 “시간 컬럼 설계”를 더 다룹니다.

3) 적립/차감만 따로 집계하기: CASE로 분리하기

amount의 부호 정책을 썼다면, 단순히 amount > 0, amount < 0으로 나눌 수도 있습니다. 다만 포인트 시스템은 정책상 예외가 생기기 쉬워서, action_type 기준으로 분리하는 편이 더 명확한 경우가 많습니다.

action_type 기준으로 “적립 합계”와 “차감 합계”를 한 번에

SELECT uid, SUM(CASE WHEN action_type IN ('CHARGE','USE_CANCEL','ADJUST') THEN amount ELSE 0 END) AS earned, SUM(CASE WHEN action_type IN ('USE','REFUND','EXPIRE') THEN amount ELSE 0 END) AS spent FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid;

여기서 spent는 음수 합계가 됩니다. 화면에 “사용/만료로 빠져나간 총액”을 양수로 보여주고 싶다면, ABS를 씌우거나 -1을 곱하는 방식으로 표현을 맞출 수 있습니다. 단, DB에 저장된 원장 데이터 자체를 바꾸는 것이 아니라, 표현 단계에서만 바꾸는 편이 안전합니다. 

SELECT uid, SUM(CASE WHEN action_type IN ('USE','REFUND','EXPIRE') THEN -amount ELSE 0 END) AS spent_positive FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY uid;

4) COUNT: “얼마나 자주” 발생했나

포인트 운영에서는 금액 합계뿐 아니라 “건수”도 중요합니다. 예를 들어 적립이 갑자기 늘었는데 금액이 아니라 건수가 늘었다면, 중복 이벤트가 발생했을 가능성을 의심할 수 있습니다. 

SELECT action_type, COUNT(*) AS cnt FROM point_history WHERE uid = @uid AND hide = 0 AND created_at >= NOW() - INTERVAL 30 DAY GROUP BY action_type ORDER BY cnt DESC;

COUNT(*) vs COUNT(column)

COUNT(*)는 행 수를 셉니다(가장 일반적). COUNT(column)은 그 column이 NULL이 아닌 행만 셉니다. 예를 들어 ref_id가 NULL일 수 있다면, COUNT(ref_id)는 “ref_id가 있는 건수”만 계산합니다. 

-- ref_id가 채워진 이벤트의 수만 보고 싶을 때 SELECT COUNT(ref_id) AS with_ref_id, COUNT(*) AS total_rows FROM point_history WHERE uid = @uid AND hide = 0;

5) 월별 리포트: GROUP BY로 “기간 단위”를 만든다

“월별 적립/사용/만료”는 포인트 운영의 기본 리포트 중 하나입니다. 핵심은 created_at을 “월” 단위로 잘라서 그룹핑하는 것입니다. DBMS별 함수가 조금씩 다르지만, MySQL 계열에서는 DATE_FORMAT이 흔히 쓰입니다. 

SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(amount) AS net_delta, COUNT(*) AS rows_cnt FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY ym ASC;

net_delta는 월별 순변동(적립-차감)이 됩니다. 여기서 적립/차감을 분리하면 더 읽기 쉬운 리포트가 됩니다. 

SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(CASE WHEN action_type IN ('CHARGE','USE_CANCEL','ADJUST') THEN amount ELSE 0 END) AS earned, SUM(CASE WHEN action_type IN ('USE','REFUND','EXPIRE') THEN -amount ELSE 0 END) AS spent, SUM(amount) AS net_delta FROM point_history WHERE uid = @uid AND hide = 0 GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY ym ASC;

6) 집계에서 자주 터지는 실수 4가지

  1. 숨김 데이터 포함
    hide=1이 집계에 섞이면 리포트가 왜곡됩니다. “집계 기준”을 고정해야 합니다.
  2. 기간 조건의 경계
    월별 집계에서 종료일 포함/미포함을 잘못 잡으면 중복/누락이 생깁니다.
  3. 부호 정책 흔들림
    어떤 이벤트는 +, 어떤 이벤트는 - 규칙이 깨지면 SUM 결과가 무의미해집니다.
  4. 그룹 기준 불일치
    화면은 action_type 기준인데, 쿼리는 amount 부호 기준이면 서로 해석이 달라질 수 있습니다.

7) 집계 결과를 바로 검증하는 “3종 세트”

집계 쿼리는 만들기보다 “검증”이 더 중요합니다. 아래 3개를 세트로 가지고 있으면, 리포트가 이상할 때 원인을 빠르게 좁힐 수 있습니다. 

-- (1) 잔액 SELECT SUM(amount) AS balance FROM point_history WHERE uid = @uid AND hide = 0; -- (2) 최근 30일 변화량 SELECT SUM(amount) AS delta_30d FROM point_history WHERE uid = @uid AND hide = 0 AND created_at >= NOW() - INTERVAL 30 DAY; -- (3) 최근 30일 액션별 합계/건수 SELECT action_type, SUM(amount) AS total_amount, COUNT(*) AS cnt FROM point_history WHERE uid = @uid AND hide = 0 AND created_at >= NOW() - INTERVAL 30 DAY GROUP BY action_type ORDER BY cnt DESC;

이 세트를 반복해서 사용하다 보면, 데이터가 커져도 “내가 만든 숫자가 맞는지”를 스스로 확인할 수 있게 됩니다.

다음 글 예고: GROUP BY/HAVING으로 “이상 거래”와 “활동 많은 유저”를 뽑아내기

다음 글에서는 집계를 한 단계 더 확장합니다. GROUP BY로 묶은 결과에 조건을 걸고(HAVING), “사용이 너무 많은 유저”, “만료가 과도한 유저”, “특정 기간에 비정상적으로 이벤트가 폭증한 케이스”처럼 운영에서 실제로 필요한 이상 징후 탐지 쿼리를 만들어봅니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

JOIN 기초: users와 point_history를 합쳐 ‘회원별 요약(잔액/최근 활동)’ 만들기

포인트 원장(point_history)만으로도 잔액과 통계를 만들 수 있지만, 실제 화면이나 운영 도구에서는 거의 항상 “사용자 정보”가 함께 필요합니다. 예를 들어 이런 질문들은 point_history만으로는 답이 부족합니다. 이 uid가 누구인지(가입일, 상태 등) 함께 보고 싶다 회원 목록을 보면서 각 회원의 잔액/최근 활동을 한 줄로 요약하고 싶다 최근 7일 동안 사용이 많은 회원을 “회원 정보와 함께” 추려 보고 싶다 이때 필요한 것이 JOIN입니다. JOIN은 테이블을 “합치는” 기능이지만, 정확히는 두 테이블의 행을 연결 해 새로운 결과를 만드는 방식입니다. 이번 글에서는 포인트 시스템에서 가장 흔한 JOIN 패턴(회원 + 원장 요약)을 중심으로, INNER JOIN/LEFT JOIN의 차이와 실전에서 헷갈리는 포인트를 정리합니다. 이 단원의 목적: “회원 목록 + 포인트 요약”을 안정적으로 뽑아내기 JOIN은 문법을 외우는 것보다 “어떤 결과를 만들고 싶은지”를 먼저 그리는 것이 중요합니다. 이번 글의 목표는 아래 3가지입니다. INNER JOIN과 LEFT JOIN의 차이를 이해하고 상황에 맞게 선택한다 집계(GROUP BY)와 JOIN을 결합해 ‘회원별 잔액/통계’를 만든다 JOIN에서 자주 발생하는 오류(행 폭증, 중복 집계)를 피하는 패턴을 익힌다 준비: users에 최소한의 컬럼을 더해 “JOIN 결과가 읽히게” 만들기 이전 글에서는 users가 uid와 created_at만 있었지만, JOIN 결과를 보기 편하게 하려면 display_name 같은 컬럼이 하나 있으면 좋습니다. 실제 서비스라면 이메일/닉네임/상태값이 있을 텐데, 여기서는 학습 목적상 간단히 display_name을 추가합니다. -- 이미 있다면 생략 ALTER TABLE users ADD COLUMN display_name VARCHAR(50) NULL; -- 예시 값 입력(학습용) UPDATE users SET display_n...
자세한 내용 보기

점검 SQL: “원장 합계(SUM) vs balance” 불일치를 찾아내고 원인을 좁히는 방법

포인트 시스템은 “정상 처리”만 잘 만들면 끝나는 것처럼 보이지만, 실제로는 시간이 지날수록 다양한 이유로 데이터가 어긋날 수 있습니다. 예를 들어 이런 상황이 생깁니다. 중간 장애로 트랜잭션이 일부만 반영된 것처럼 보이는 케이스(재처리/수동 개입 포함) 운영 정정(ADJUST)이나 소프트 삭제(hide) 처리 기준이 흔들린 케이스 배치 작업(만료/정산)이 예상과 다른 시간에 들어간 케이스 코드 변경 이후 특정 action_type이 누락/중복 집계된 케이스 그래서 “점검 쿼리”는 기능 쿼리만큼 중요합니다. 특히 balance 테이블(잔액 캐시)을 함께 쓰는 구조라면, 주기적으로 원장 합계와 balance가 같은지 확인해야 합니다. 이 단원의 목적: 불일치를 “발견”하고 “원인”을 좁힐 수 있는 쿼리 세트 만들기 이번 글에서는 아래 3가지를 목표로 합니다. 원장 합계(SUM)와 balance를 비교해 불일치 UID를 뽑는다 불일치가 발견되면 어디서부터 어긋났는지 범위를 좁힌다 자주 발생하는 유형(숨김 처리, 중복 요청, 배치 만료)을 점검 항목으로 만든다 1) 1차 점검: uid별 “원장 잔액”과 “balance 잔액” 비교 가장 기본은 uid별로 두 값을 나란히 놓고 비교하는 것입니다. 여기서는 hide=0만 원장 합계에 포함한다고 가정합니다. SELECT b.uid, b.balance AS balance_cache, COALESCE(l.ledger_balance, 0) AS balance_ledger, b.balance - COALESCE(l.ledger_balance, 0) AS diff FROM point_balance b LEFT JOIN ( SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid ) l ON l.uid = b.uid WHERE b.balance <> COALESCE(l.ledger_...
자세한 내용 보기

EXPLAIN 기초: 점검/리포트 쿼리가 느려질 때 “왜 느린지” 확인하는 방법

포인트 시스템을 만들어가면서 새삼 느끼는것은 생성형AI가 있어서 설계하고 구상하는게 너무 쉬워졌다는것입니다. 혼자서 했다면 아마도 어려움이 많았을거 같은데 AI와 대화를 하며 개선점을 찾아가는 방식으로 진행하다보니 생각보다 기능이 치밀하게 설계를 할 수 있었습니다. 지금까지 하나하나 학습범위를 늘려가며 왔는데 아직도 갈길이 먼거 같습니다.  포인트 원장(point_history)은 시간이 지날수록 커집니다. 데이터가 커지면, 기능 쿼리(내역/잔액)뿐 아니라 점검 쿼리(원장 vs balance 비교, 월별 집계, ref_id 그룹핑)도 점점 느려집니다. 이때 흔히 “인덱스를 더 만들면 되지 않을까?”라고 생각하지만, 인덱스는 무조건 늘린다고 좋아지지 않습니다. 어떤 쿼리가 어떤 방식으로 실행되는지 모르면 잘못된 인덱스를 만들거나, 효과 없는 튜닝을 반복할 가능성이 큽니다. 그래서 필요한 도구가 EXPLAIN 입니다. EXPLAIN은 “이 쿼리를 DB가 어떤 순서로 실행할지”를 보여주는 실행 계획입니다. 즉, 느린 이유를 추측하지 않고 눈으로 확인 할 수 있게 해줍니다. 이 단원의 목적: 실행 계획을 보고 “어디를 고칠지” 결정하기 이번 글의 목표는 아래 3가지입니다. EXPLAIN 결과에서 최소한의 핵심 항목을 읽을 수 있게 된다 포인트 시스템 쿼리에서 자주 나오는 느린 패턴(풀 스캔, filesort)을 구분한다 인덱스를 “조회 패턴에 맞게” 설계하는 감각을 만든다 1) EXPLAIN 사용법: SELECT 앞에 붙이면 된다 가장 기본은 SELECT 앞에 EXPLAIN을 붙이는 것입니다. EXPLAIN SELECT id, action_type, amount, created_at FROM point_history WHERE uid = '11111111-1111-1111-1111-111111111111' AND hide = 0 ORDER BY created_at DESC, id DESC LIMIT 50; 실행 결과는 ...
자세한 내용 보기