SQL GROUP BY | 1000PAGE

게임 KPI는 SQL 문법보다 분자·분모·시간대·중복 단위를 먼저 고정해야 정확해진다. 같은 DAU도 로그인 행 수를 세는지, 하루에 한 번이라도 유효 활동을 한 고유 사용자를 세는지에 따라 전혀 다른 숫자가 된다. 이 글은 PostgreSQL의 GROUP BY, HAVING, FILTER를 이용해 네 지표를 재현 가능한 정의와 쿼리로 묶는다.

분석 계약부터 고정한다

항목	이 글의 정의	바꾸면 함께 기록할 것
하루 경계	Asia/Seoul 현지 날짜 00:00~24:00	UTC·서버 시간·사용자별 시간대
활성 사용자	play 이벤트가 1회 이상인 고유 user_id	로그인·매치 시작 등 포함 이벤트 목록
매출	성공 결제의 원화 총액, 환불은 음수	세금·플랫폼 수수료·환율 기준
잔존 기준	가입 현지 날짜의 정확히 +N일에 play	24시간 경과 방식·N일까지 누적 방식

운영 DB에는 시각을 timestamptz로 저장하고, 집계할 때 명시적으로 업무 시간대로 변환한다. 세션 TimeZone 설정에 우연히 의존하면 실행 환경에 따라 날짜가 달라질 수 있다.

실행할 예제 데이터 만들기

아래 데이터에는 한국 시각 자정 경계, 같은 날 중복 플레이, 비활성 결제자, 환불, D1·D7 복귀가 의도적으로 섞여 있다. PostgreSQL 빈 데이터베이스에서 순서대로 실행한다.

CREATE TABLE app_user (
  user_id bigint PRIMARY KEY,
  signed_up_at timestamptz NOT NULL
);

CREATE TABLE game_event (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES app_user(user_id),
  event_name text NOT NULL CHECK (event_name IN ('play', 'login')),
  occurred_at timestamptz NOT NULL
);

CREATE TABLE payment (
  payment_id text PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES app_user(user_id),
  amount_krw integer NOT NULL,
  status text NOT NULL CHECK (status IN ('succeeded', 'refunded')),
  paid_at timestamptz NOT NULL
);

INSERT INTO app_user VALUES
(1, '2026-08-01 00:10+09'), (2, '2026-08-01 23:50+09'),
(3, '2026-08-02 08:00+09'), (4, '2026-08-02 10:00+09'),
(5, '2026-08-03 12:00+09'), (6, '2026-08-03 13:00+09');

INSERT INTO game_event(user_id, event_name, occurred_at) VALUES
(1,'play','2026-08-01 01:00+09'), (1,'play','2026-08-01 20:00+09'),
(2,'play','2026-08-01 23:55+09'),
(1,'play','2026-08-02 00:30+09'), (3,'play','2026-08-02 09:00+09'),
(4,'play','2026-08-02 11:00+09'),
(3,'play','2026-08-03 09:00+09'), (5,'play','2026-08-03 13:00+09'),
(6,'play','2026-08-03 14:00+09'),
(1,'play','2026-08-08 07:00+09'), (2,'play','2026-08-08 22:00+09'),
(3,'play','2026-08-09 09:00+09');

INSERT INTO payment VALUES
('p1',1,1000,'succeeded','2026-08-01 02:00+09'),
('p2',1,500,'succeeded','2026-08-01 03:00+09'),
('p3',2,2000,'succeeded','2026-08-01 23:58+09'),
('p4',3,3000,'succeeded','2026-08-02 09:30+09'),
('p5',4,1000,'refunded','2026-08-02 12:00+09'),
('p6',6,700,'succeeded','2026-08-02 23:59+09');

GROUP BY의 입력 행부터 고유하게 만든다

한 사용자가 하루에 열 번 플레이해도 DAU에는 한 번만 포함되어야 한다. 따라서 날짜와 user_id의 중복을 먼저 제거한 daily_active를 여러 KPI가 공유한다. PostgreSQL의 timezone 함수는 timestamptz를 지정한 지역의 timestamp로 바꾸며, date 캐스팅이 현지 날짜 버킷을 만든다.

WITH daily_active AS (
  SELECT DISTINCT
         timezone('Asia/Seoul', occurred_at)::date AS activity_date,
         user_id
  FROM game_event
  WHERE event_name = 'play'
)
SELECT activity_date, COUNT(*) AS dau
FROM daily_active
GROUP BY activity_date
ORDER BY activity_date;

activity_date | dau
--------------+----
2026-08-01    | 2
2026-08-02    | 3
2026-08-03    | 3
2026-08-08    | 2
2026-08-09    | 1

DAU_d=\left|\{u\mid u\text{가 날짜 }d\text{에 유효 활동}\}\right|

로그 행 수가 아니라 고유 사용자 집합의 크기다. 봇·테스터·운영 계정 제외 규칙이 있다면 daily_active 단계에 일관되게 적용해야 다른 KPI의 분모도 맞는다.

ARPDAU와 결제 전환율

ARPDAU_d=\frac{Revenue_d}{DAU_d}

Payment\ Conversion_d=\frac{|Payers_d\cap Active_d|}{|Active_d|}\times100\%

ARPDAU의 분모는 결제자 수가 아니라 모든 DAU다. 결제 전환율은 정의에 따라 ‘그날 결제한 모든 사용자/DAU’로도 만들 수 있지만 그러면 비활성 결제자가 분자에 들어가 100%를 넘을 수 있다. 여기서는 같은 날 활성 사용자와 결제자의 교집합을 사용한다. 매출은 성공 결제를 더하고 환불 행은 음수로 반영한다.

WITH daily_active AS (
  SELECT DISTINCT timezone('Asia/Seoul', occurred_at)::date AS day, user_id
  FROM game_event
  WHERE event_name = 'play'
),
daily_payment AS (
  SELECT timezone('Asia/Seoul', paid_at)::date AS day, user_id,
         SUM(CASE WHEN status = 'succeeded' THEN amount_krw
                  WHEN status = 'refunded' THEN -amount_krw END) AS revenue,
         BOOL_OR(status = 'succeeded') AS is_payer
  FROM payment
  GROUP BY day, user_id
),
daily_kpi AS (
  SELECT a.day,
         COUNT(*) AS dau,
         COALESCE(SUM(p.revenue), 0) AS active_user_revenue,
         COUNT(*) FILTER (WHERE p.is_payer) AS paying_dau
  FROM daily_active AS a
  LEFT JOIN daily_payment AS p USING (day, user_id)
  GROUP BY a.day
)
SELECT day, dau, active_user_revenue AS revenue_krw, paying_dau,
       ROUND(active_user_revenue::numeric / NULLIF(dau, 0), 2) AS arpdau_krw,
       ROUND(100.0 * paying_dau / NULLIF(dau, 0), 2) AS payment_conversion_pct
FROM daily_kpi
ORDER BY day;

day        | dau | revenue_krw | paying_dau | arpdau_krw | payment_conversion_pct
-----------+-----+-------------+------------+------------+-----------------------
2026-08-01 |   2 |        3500 |          2 |    1750.00 |                 100.00
2026-08-02 |   3 |        2000 |          1 |     666.67 |                  33.33
2026-08-03 |   3 |           0 |          0 |       0.00 |                   0.00
2026-08-08 |   2 |           0 |          0 |       0.00 |                   0.00
2026-08-09 |   1 |           0 |          0 |       0.00 |                   0.00

8월 2일 23:59에 결제한 user 6은 그날 play가 없으므로 이 정의의 active_user_revenue와 paying_dau에서 제외된다. 재무 매출과 ARPDAU 매출을 같게 써야 한다면 이 예외를 먼저 합의해야 한다.

HAVING은 집계된 그룹을 거른다

WHERE는 그룹화 전 행을, HAVING은 GROUP BY 뒤 그룹을 필터링한다. 예를 들어 결제자가 2명 이상인 날짜만 찾을 때 집계값 조건을 HAVING에 둔다.

SELECT timezone('Asia/Seoul', paid_at)::date AS day,
       COUNT(DISTINCT user_id) FILTER (WHERE status = 'succeeded') AS payers
FROM payment
GROUP BY day
HAVING COUNT(DISTINCT user_id) FILTER (WHERE status = 'succeeded') >= 2
ORDER BY day;

day        | payers
-----------+-------
2026-08-01 | 2
2026-08-02 | 2

D1·D7 잔존율은 가입 코호트의 정확한 날짜 복귀율

D_N(c)=\frac{|C_c\cap A_{c+N}|}{|C_c|}\times100\%

여기서 $C_c$ 는 현지 날짜 $c$ 에 가입한 고유 사용자 집합, $A_{c+N}$ 은 정확히 N일 뒤 활성 사용자 집합이다. D7은 가입 후 7일 안에 한 번이라도 온 비율이 아니라 정확히 +7일 활동 비율이다.

분모는 각 가입 코호트 전체다. 분석 종료일이 2026-08-09라면 D7을 완전히 관찰할 수 있는 가입일은 2026-08-02까지이므로 더 최근 코호트는 D7 결과에서 제외한다. 아직 시간이 지나지 않은 사용자를 미복귀자로 세면 잔존율이 인위적으로 낮아진다.

WITH params AS (
  SELECT DATE '2026-08-09' AS analysis_end
),
cohort AS (
  SELECT user_id, timezone('Asia/Seoul', signed_up_at)::date AS cohort_date
  FROM app_user
),
daily_active AS (
  SELECT DISTINCT user_id, timezone('Asia/Seoul', occurred_at)::date AS active_date
  FROM game_event
  WHERE event_name = 'play'
),
flags AS (
  SELECT c.user_id, c.cohort_date,
         BOOL_OR(a.active_date = c.cohort_date + 1) AS retained_d1,
         BOOL_OR(a.active_date = c.cohort_date + 7) AS retained_d7
  FROM cohort AS c
  LEFT JOIN daily_active AS a
    ON a.user_id = c.user_id
   AND a.active_date IN (c.cohort_date + 1, c.cohort_date + 7)
  GROUP BY c.user_id, c.cohort_date
)
SELECT f.cohort_date, COUNT(*) AS cohort_size,
       COUNT(*) FILTER (WHERE f.retained_d1) AS d1_users,
       ROUND(100.0 * COUNT(*) FILTER (WHERE f.retained_d1) / COUNT(*), 2) AS d1_pct,
       CASE WHEN f.cohort_date <= p.analysis_end - 7
            THEN COUNT(*) FILTER (WHERE f.retained_d7) END AS d7_users,
       CASE WHEN f.cohort_date <= p.analysis_end - 7
            THEN ROUND(100.0 * COUNT(*) FILTER (WHERE f.retained_d7) / COUNT(*), 2) END AS d7_pct
FROM flags AS f CROSS JOIN params AS p
GROUP BY f.cohort_date, p.analysis_end
ORDER BY f.cohort_date;

cohort_date | cohort_size | d1_users | d1_pct | d7_users | d7_pct
------------+-------------+----------+--------+----------+-------
2026-08-01  |           2 |        1 |  50.00 |        2 | 100.00
2026-08-02  |           2 |        1 |  50.00 |        1 |  50.00
2026-08-03  |           2 |        0 |   0.00 |          |

예시 잔존율 곡선 읽기

다음 값은 SQL 예제 출력이 아니라 곡선 해석을 위한 가상 코호트 1,000명의 예시다. D0 100%, D1 46%, D3 32%, D7 23%, D14 17%, D30 12%로 감소한다. 잔존율은 항상 단조 감소해야 하는 지표는 아니다. 정확한 N일 복귀율은 이벤트나 주말 효과로 다시 오를 수 있다.

가상 게임 코호트의 날짜별 잔존율 예시

D0 100%

D1 46%

D3 32%

D7 23%

D14 17%

D30 12%

예시 잔존율

주석

틀리기 쉬운 지점과 검증법

결제와 이벤트 원본을 user_id와 날짜로 바로 조인하면 한 사용자의 이벤트 수 × 결제 수만큼 행이 불어나 매출이 중복 합산된다. 양쪽을 사용자·날짜 단위로 먼저 집계한다.
COUNT(*)와 COUNT(column)은 다르다. LEFT JOIN 뒤 COUNT(*)는 미복귀 사용자도 1행으로 세므로 FILTER 조건 또는 nullable 오른쪽 키를 센다.
정수 나눗셈을 피하려고 분자에 100.0을 곱하거나 numeric으로 캐스팅하고, NULLIF로 0 분모를 보호한다.
결제 성공과 환불을 별도 행으로 저장한다면 환불이 어느 원결제를 상쇄하는지, 부분 환불인지 정의한다. 예제의 status만으로는 같은 결제의 상태 변경 이력까지 표현하지 않는다.
현지 날짜 버킷과 UTC 반개구간 필터를 함께 쓸 때 경계를 변환해 인덱스를 활용한다. 출력 날짜만 timezone으로 변환하고 원본 열에 대한 범위 조건도 둔다.

최종 검증은 작은 손계산 데이터와 운영 규모 실행 계획을 모두 사용한다. 예제처럼 중복 플레이, 자정 직전·직후, 비활성 결제, 환불, D7 미성숙 코호트를 테스트 케이스로 고정하면 정의가 바뀔 때 회귀를 발견하기 쉽다.

참고 자료

PostgreSQL 공식 문서 — Aggregate Functions (2026-08-11 확인): COUNT, SUM, BOOL_OR 등 집계 함수의 의미.
PostgreSQL 공식 문서 — SELECT (2026-08-11 확인): GROUP BY, HAVING, FILTER가 적용되는 SELECT 처리 구조.
PostgreSQL 공식 문서 — Date/Time Functions and Operators (2026-08-11 확인): timezone, 날짜 연산과 date_trunc 동작.
PostgreSQL 공식 문서 — Date/Time Types (2026-08-11 확인): timestamp with time zone의 저장·표시와 시간대 처리.

댓글을 불러오는 중…