SQLite로 streak를 영리하게 — substr DATE와 cursor 역순

음성 챗봇의 게이미피케이션 시스템에서 가장 영향력 있는 동기 메커니즘이 streak(연속일)입니다. 매일 한 번 이상 챗봇을 쓰면 streak가 +1, 하루라도 빠지면 0으로 리셋. "오늘 streak 끊지 않기"는 강력한 행동 동기로 작동합니다.

그런데 streak를 SQL로 계산하는 게 의외로 까다롭습니다. 단순히 SELECT 한 줄로는 안 풀리고, 날짜의 연속성을 추적하는 로직이 필요합니다. 가정용 챗봇 정도의 규모에선 단일 프로세스 가정과 substr DATE 부분 매칭으로 단순하게 풀 수 있는데, 그 디자인을 정리해봅니다.

"streak"의 정의를 먼저 정해야 한다

streak를 SQL로 계산하기 전에, 도메인 룰부터 분명히 해야 합니다. 의외로 가지각색입니다.

• 오늘 사용 안 했으면 streak는 어떻게 되나? 어제까지 7일이었는데 오늘 안 쓰면, 지금 7인가 0인가?
• 같은 날 여러 번 쓰면? 같은 날짜에 여러 번 사용해도 streak는 +1만 인정.
• 시간대 기준은? 자정 직전과 직후를 어떻게 다룰까. 시스템 로컬 시간 기준?

제가 정한 규칙은 단순합니다.

1. 오늘 한 번이라도 쓰면 streak는 1 이상.
2. 오늘 안 썼으면 streak는 0(어제까지의 누적이 보존되는 게 아니라).
3. "같은 날"의 기준은 시스템 로컬 시간의 자정.

두 번째 규칙이 핵심입니다. 어제까지 7일이었는데 오늘 안 쓰면 streak는 0으로 떨어집니다. 이게 동기 측면에서 의도한 효과입니다 — "오늘 streak 깨지면 안 돼"라는 작은 위기감이 매일의 첫 질문을 만들어냅니다. 어제까지의 누적이 그대로 보존되면 위기감이 약해지죠.

데이터 모델 — 이벤트 테이블 하나면 충분하다

streak를 별도 컬럼으로 저장하지 않고 매번 이벤트 로그에서 계산합니다. 이벤트 테이블만 있으면 됩니다.

CREATE TABLE IF NOT EXISTS score_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user TEXT NOT NULL,
    kind TEXT NOT NULL,           -- 'question' | 'answer_listened' | 'greeting'
    points INTEGER NOT NULL,
    metadata TEXT,
    created_at TEXT NOT NULL      -- ISO8601 (예: '2026-05-01T19:23:45')
);

CREATE INDEX IF NOT EXISTS ix_events_user_date
    ON score_events (user, substr(created_at, 1, 10));

코드 설명 — created_at은 ISO8601 형식의 텍스트로 저장합니다. SQLite는 timestamp 전용 타입이 없고, ISO8601 문자열이 lexicographic으로 정렬해도 시간순이라는 좋은 성질이 있어서 텍스트 그대로 다룹니다. 인덱스가 흥미로운데, 함수 인덱스로 (user, substr(created_at, 1, 10))을 직접 지정합니다. substr(... , 1, 10)은 ISO 문자열의 앞 10자, 즉 날짜 부분("2026-05-01")만 추출합니다. 이렇게 두면 "어제 사용했나" 같은 날짜 단위 쿼리가 빠르게 동작합니다.

streak 계산 — cursor 역순 순회

실제 streak 계산은 두 단계입니다.

1. 이 사용자가 사용한 모든 날짜의 distinct 집합을 구한다.
2. 오늘부터 시작해서 하루씩 거꾸로 가면서, 그 날짜가 집합에 있는지 확인. 있으면 streak +=1, 없으면 멈춤.

그림 설명 — 위쪽 줄은 사용자가 실제로 사용한 날짜의 distinct 집합입니다. 04-27이 빠져 있는 게 핵심입니다. 아래쪽 줄은 cursor 역순 순회: 오늘(05-01)에서 시작해 거꾸로 가며 집합에 있으면 streak +=1, 다음 날짜로 이동(어제). 04-30, 04-29, 04-28까지 연속이라 streak = 4까지 늘어납니다. 04-27이 집합에 없어서 거기서 멈춥니다(04-26은 집합에 있지만 도달 못 함). 결과 streak = 4.

코드 — 두 함수면 끝

def get_streak(self, user: str, today: dt.date) -> int:
    """오늘부터 시작해서 연속으로 활동한 일수.

    오늘 활동이 없으면 streak는 0이다 (어제 streak가 아무리
    길어도 오늘 빠지면 0). 이 정의가 사용자에게 "오늘 첫 질문을
    하지 않으면 streak가 끊긴다"는 동기를 만든다.
    """
    # ── 1단계: 이 사용자가 활동한 모든 "날짜"를 한 번에 긁어온다 ──
    # created_at은 '2026-05-01T19:23:45' 형태의 ISO8601 문자열.
    # substr(created_at, 1, 10)은 앞 10자(즉, '2026-05-01')만 잘라내
    # 시각 정보를 버리고 날짜만 남긴다. DISTINCT로 같은 날 중복 제거.
    # 함수 인덱스 ix_events_user_date(user, substr(...))가 걸려 있어
    # 이 추출 자체가 인덱스 스캔으로 끝나 빠르다.
    with self._conn() as c:
        rows = c.execute(
            "SELECT DISTINCT substr(created_at, 1, 10) AS d"
            "  FROM score_events"
            " WHERE user = ?"
            " ORDER BY d DESC",          # 알고리즘상 정렬은 불필요(곧 set로 바꿈).
                                          # 디버깅/로그 가독성용.
            (user,),
        ).fetchall()

    # ── 2단계: 문자열 날짜를 date 객체 set으로 변환 ──
    # 다음 단계에서 "이 날짜 활동했나?"를 수백 번 물어봐야 하는데,
    # set은 O(1) 멤버십 테스트가 가능해서 list 대비 압도적으로 빠르다.
    # date 객체로 바꿔두면 dt.timedelta(days=1) 산술이 자연스럽게 된다.
    days = {dt.date.fromisoformat(r["d"]) for r in rows}

    # ── streak 정의의 핵심 가드 ──
    # 어제까지 streak가 100이었더라도 "오늘" 활동이 없으면 즉시 0.
    # 이 한 줄이 매일 첫 질문을 만드는 동기 메커니즘의 전부다.
    if today not in days:
        return 0

    # ── 3단계: cursor 역순 순회 ──
    # 오늘부터 시작해 하루씩 뒤로 가며, 그 날짜가 활동 집합에 있으면
    # streak를 +1 하고 또 하루 뒤로. 집합에 없는 첫 날을 만나는 순간
    # while 조건이 깨지고 루프 종료 → 그 시점의 streak가 답.
    #
    # 예: 활동일 = {04-26, 04-28, 04-29, 04-30, 05-01}, today=05-01
    #   cursor=05-01 ✓ → streak=1, cursor=04-30
    #   cursor=04-30 ✓ → streak=2, cursor=04-29
    #   cursor=04-29 ✓ → streak=3, cursor=04-28
    #   cursor=04-28 ✓ → streak=4, cursor=04-27
    #   cursor=04-27 ✗ → 루프 종료. 04-26은 집합에 있어도 도달 못 함.
    #   결과: streak = 4
    streak = 0
    cursor = today
    while cursor in days:
        streak += 1
        cursor -= dt.timedelta(days=1)   # 하루 전으로 한 칸 이동
    return streak

코드 설명 — SQL은 단 한 줄: SELECT DISTINCT substr(created_at, 1, 10) AS d ... ORDER BY d DESC. substr로 날짜 부분만 잘라내고 distinct로 중복 제거. 함수 인덱스 ix_events_user_date가 이 쿼리를 빠르게 만들어줍니다. 결과를 Python set으로 받아 cursor 역순 순회합니다. while cursor in days 루프가 streak의 본질을 가장 직관적으로 표현합니다 — 오늘부터 거꾸로 가며 집합에 있는 동안 카운트, 없으면 멈춤.

이 구현은 O(N)입니다. N은 사용자가 활동한 distinct 일 수. 한 사용자가 1년 동안 매일 사용했다면 N = 365이고, set lookup O(1) × 365회면 1ms 미만입니다. 가정용 챗봇 규모에선 충분합니다. 더 큰 규모라면 cursor가 며칠 만에 멈출 수 있어서 평균적으로 N보다 훨씬 빨리 끝납니다.

"왜 streak를 컬럼으로 안 두고 매번 계산하나"

streak를 별도 컬럼으로 저장하는 옵션도 있었습니다. users 테이블에 streak INT를 두고, 매 질문 이벤트 시 어제 컬럼 값을 보고 +1 또는 0으로 갱신. 그러나 이렇게 하면 두 가지 복잡도가 추가됩니다.

첫째, 오늘 활동이 없는 경우의 자동 리셋이 까다롭습니다. 어제까지 streak 7이었던 사용자가 오늘 사용 안 하면, 누군가가 자정에 컬럼을 0으로 만들어줘야 합니다. cron 작업 또는 lazy 평가가 필요한데, 두 방식 다 추가 코드가 됩니다.

둘째, 이벤트 데이터와의 정합성이 깨질 수 있습니다. 컬럼 갱신 로직에 버그가 있으면 이벤트는 제대로 들어가는데 streak 컬럼이 틀어집니다. 이걸 어떻게 자동 검증할까요.

매번 계산은 위 두 복잡도를 한 번에 없앱니다. 이벤트 데이터가 진실의 원천이고, streak는 그것의 derived view일 뿐입니다. 데이터 정합성 걱정이 0이 되고, "오늘 사용 안 했으면 0"이라는 룰이 자동으로 표현됩니다. 성능은 위에서 본 대로 충분합니다.

왜 동시성 걱정을 안 했는가

이 구현이 단순한 한 가지 결정적 이유가 있습니다. aiohttp 단일 프로세스 가정입니다. 챗봇 서버는 한 머신에서 한 프로세스로 돕니다. 동시 사용자 수도 한 명(아이)뿐이고, 한 사용자가 같은 시점에 두 요청을 보낼 일이 거의 없습니다(음성 발화는 직렬).

이 가정 아래에서는 SQLite의 기본 보호(파일 잠금)만으로 충분합니다. INSERT가 여러 곳에서 동시에 들어와도 SQLite가 직렬화해주고, SELECT는 read 잠금이라 INSERT를 잠시 막을 뿐 데이터가 깨지지 않습니다. 별도 트랜잭션 격리·재시도 로직 없이 자연스럽게 동작합니다.

만약 동시 사용자가 100명이고 distributed setup이 필요하다면 이야기가 달라집니다. 그러나 그런 환경에서 streak 컬럼을 두는 게 옳은지도 다시 생각해볼 문제입니다(Redis 같은 캐시 + 이벤트 스토어가 더 나을 수도). 가정용 챗봇 규모에선 SQLite 단일 파일이 가장 단순하고 강건한 답이었습니다.

마치며 — "충분히 단순한 답"의 가치

streak 계산은 처음 봤을 때 "윈도우 함수 써야 하나?", "lag join으로 어제 행과 비교?" 같은 복잡한 SQL 기법이 떠오르는 문제입니다. 그러나 위 코드처럼 한 줄짜리 SELECT + Python에서 O(N) 순회로 풀면 모든 게 단순해집니다.

여기서 단순함을 가능하게 한 결정 두 가지가 있었습니다.

1. streak를 컬럼이 아닌 derived view로 둔다 — 데이터 정합성 걱정을 없앤다.
2. 동시성을 단순화한다(단일 aiohttp 프로세스) — 트랜잭션 보호의 무게를 SQLite에 맡긴다.

이 두 결정이 코드 30줄과 SQL 한 줄로 streak 시스템 전체를 표현하게 했습니다. 가정용 규모를 살짝 넘는 시스템에선 같은 패턴이 잘 동작합니다. 그 너머 규모에선 다른 디자인이 필요하지만, "정답이 하나"가 아니라 "사용 규모에 맞는 정답"을 고르는 사고가 더 중요합니다.

---

이 글은 생성형 AI의 도움을 받아 작성되었습니다. 원본 자료를 기반으로 AI가 초안을 생성하고, 작성자가 검토·편집하였습니다.