계정별 실패와 성공 집계

90분 안팎

학습 목표

시간 범위와 계정으로 의심 이벤트를 집계합니다.

개념

횟수보다 집계 계약이 먼저입니다

보관 앱의 실패 로그 다섯 건이 보여도 그 숫자만으로 위험을 비교할 수 없습니다. 어떤 계정의 어떤 시간 범위인지, 동일 사건이 중복 적재됐는지, 실패 이외의 행이 섞였는지 알아야 합니다. 이 레슨은 고정된 5분 창에서 계정별 인증 실패 횟수를 계산하고 마지막 실패 이후의 첫 성공을 찾습니다. 임계값은 교재 정책이며 실제 서비스의 정상 사용 패턴을 검토하지 않은 운영 권장값이 아닙니다.

테이블의 의미를 확인합니다

auth 테이블의 id는 중복 없는 사건 키, account는 합성 계정, ts는 오프셋이 붙은 시각, kind는 failure 또는 success입니다. access_denied는 이번 집계 테이블에 넣지 않습니다. bounds 테이블에는 start와 end가 한 행 있습니다. NULL account와 해석 불가능한 시각은 정상 집계에서 제외하지만 별도 결측 검토 대상으로 남깁니다. 제외한 행이 사라졌다고 데이터가 완전해진 것은 아닙니다.

시작 포함 끝 제외

이번 창은 start 이상 end 미만입니다. 정확히 시작 시각의 실패는 포함하고 종료 시각의 실패는 다음 창에 남깁니다. 양 끝을 포함하면 인접 창에서 종료 사건이 두 번 세어집니다. 두 창 모두 끝을 제외하고 시작도 제외하면 경계 사건이 빠집니다. SQL 조건의 부등호를 쓰기 전에 경계 정책을 문장으로 확정하면 테스트 기대값을 같은 정책에서 도출할 수 있습니다.

문자열 시각을 바로 비교하지 않습니다

오프셋이 다른 ISO 문자열은 사전식 순서와 실제 시간 순서가 다를 수 있습니다. SQLite julianday로 같은 시간 축의 수치로 변환해 비교합니다. 09:02+09:00은 00:02Z로 같은 창에 들어갑니다. 해석할 수 없는 시각은 NULL이 되므로 조건에서 빠집니다. 파서가 제공하는 결과만 믿지 말고 입력 계약을 확인하며 실제 수집 오류 수는 별도 집계로 남깁니다.

실패 행을 먼저 제한합니다

WHERE에서 kind와 시간 범위를 제한한 뒤 GROUP BY account를 적용합니다. 모든 사건을 COUNT하면 성공도 실패 숫자에 포함됩니다. 계정별 행이 아니라 전체 숫자 하나만 구하면 대상 계정을 알 수 없습니다. failures 공통 테이블 식은 COUNT와 MAX 시각을 동시에 만들어 이후 성공을 연결할 기준을 제공합니다. account가 NULL인 행은 별도 품질 문제이며 여러 모르는 계정을 하나로 합치지 않습니다.

마지막 실패 뒤 성공을 선택합니다

성공 조회는 같은 계정이며 마지막 실패보다 늦은 시각이고 같은 창 안에 있어야 합니다. 첫 실패 이후 성공만 찾으면 마지막 실패 이전 성공을 후속 성공처럼 보고할 수 있습니다. 같은 순간의 성공은 엄격히 이후가 아니므로 제외합니다. 이 레슨에서는 MIN으로 조건을 만족하는 첫 성공을 선택합니다. 성공이 없으면 NULL을 출력하고 실패 계정 행 자체는 유지합니다.

집계 없는 계정과 성공 없는 계정

실패가 한 번도 없는 계정은 결과에 나타나지 않습니다. 실패는 있으나 뒤 성공이 없는 계정은 실패 횟수와 NULL을 가진 행으로 남습니다. 이를 구분해야 계정이 안전하다는 결론을 성급하게 내리지 않습니다. 표에 없다는 것은 이 입력 창에 집계 대상 실패가 없다는 뜻입니다. 로그 수집 누락이나 다른 창의 사건은 이 조회만으로 확인하지 못합니다.

출력 순서를 계약에 넣습니다

브라우저 채점 결과는 account 오름차순으로 출력합니다. 정렬을 생략하면 같은 행 집합이더라도 실행 계획에 따라 표시 순서가 달라질 수 있습니다. 성공 시각은 datetime으로 UTC의 날짜와 시각을 표시합니다. NULL은 문자 NULL로 보입니다. 이 포맷은 조회 결과의 표현이며 원본 ts를 덮어쓰는 작업이 아닙니다. 원본 사건의 오프셋과 id는 추후 조사 근거로 보존합니다.

SQL 오류를 해석합니다

no such table: auth는 제공 입력이 없거나 테이블 이름을 틀린 경우입니다. no such column은 별칭 범위 또는 철자를 확인합니다. 숫자가 예상보다 크면 JOIN으로 같은 실패 행이 성공 수만큼 복제됐는지 점검합니다. 먼저 실패 집계를 완성하고 성공을 상관 하위 질의로 찾으면 이번 작은 fixture에서는 행 증식을 피할 수 있습니다. 실제 대량 데이터의 성능은 별도 실행 계획으로 확인합니다.

경계 검사가 정책을 보호합니다

빈 입력에서는 결과가 빈 문자열입니다. 종료 시각의 실패만 있으면 역시 결과가 없습니다. 시작 실패 한 건과 종료 직전 실패는 같은 창에 포함됩니다. 다른 시간대 표기로 된 동일 순간도 같은 판단을 해야 합니다. 테스트는 정상 예제뿐 아니라 이 정책을 깨기 쉬운 경우를 포함합니다. 실행 결과가 다르면 먼저 범위와 변환 함수를 확인하고 정답 출력만 맞추기 위해 fixture를 바꾸지 않습니다.

일정 창과 이동 창의 차이

이번 조회는 미리 정한 창 하나를 계산합니다. 시작 경계 양쪽에 실패가 분산되면 각 창의 횟수는 작을 수 있습니다. 이동 창은 각 사건을 기준으로 최근 구간을 보지만 중복 경보를 줄이는 별도 정책이 필요합니다. 이 과제의 결과를 모든 연속 5분 구간의 탐지 결과라고 설명하지 않습니다. 보고서에는 창 정의와 빠질 수 있는 패턴을 명시하고 추가 분석 필요성을 적습니다.

집계 결과를 조사 입력으로 씁니다

실패 횟수와 이후 성공 시각은 후보를 좁히는 자료입니다. 비밀번호를 기억해 정상 로그인한 사용자도 같은 패턴을 만들 수 있습니다. 자료 접근 거절과 서버 발급 흐름 키를 연결하는 다음 레슨에서 더 구체적인 근거를 찾습니다. 더 읽기의 집계 장은 GROUP BY와 HAVING의 일반 사용을 다루며 여기에서는 계정·시간·성공 순서라는 탐지 계약을 직접 적용합니다.

조회 결과를 검토할 때 계정 두 개가 섞인 작은 테이블을 직접 만들어 봅니다. 한 계정의 성공이 다른 계정 실패의 후속 성공으로 표시되면 상관 조건에 account가 빠진 것입니다. 마지막 실패와 같은 시각의 성공도 넣어 엄격한 이후 조건을 확인합니다. 이런 작은 반례는 긴 운영 로그보다 오류 위치를 쉽게 드러냅니다. 기본 집계가 맞은 뒤 임계값 필터를 덧붙여 후보 목록으로 사용하는 순서를 권합니다.

따라하기

작은 조건을 직접 실행합니다

CREATE TABLE e(account TEXT,kind TEXT);INSERT INTO e VALUES('a','failure'),('a','success'),('a','failure');SELECT account,COUNT(*) FROM e WHERE kind='failure' GROUP BY account;

실행 결과

a 2

입력 테이블을 읽습니다

브라우저 실습의 auth와 bounds 계약을 확인하고 account·kind·julianday 범위를 먼저 제한합니다.

후속 성공을 연결합니다

계정별 COUNT와 MAX 실패 시각을 만든 뒤 같은 계정의 더 늦은 성공 MIN을 조회합니다.

경계를 비교합니다

제공 테스트에서 빈 입력·창 끝·다른 오프셋·성공 뒤 실패 결과를 대조합니다.

확인 문제

실습

auth(id,account,ts,kind)와 bounds(start,end)가 제공됩니다. 시작 포함·끝 제외 창에서 계정별 failure 횟수와 마지막 실패 이후 첫 success의 UTC datetime을 조회합니다. 실패 없는 계정은 제외하고 후속 성공이 없으면 NULL입니다. account 순으로 출력합니다. 잘못된 시각과 NULL account는 집계에서 제외합니다.

실행할 코드

모범 답안

WITH f AS (
 SELECT account,COUNT(*) AS failures,MAX(julianday(ts)) AS last_fail
 FROM auth,bounds WHERE kind='failure' AND account IS NOT NULL
 AND julianday(ts)>=julianday(start) AND julianday(ts)<julianday(end)
 GROUP BY account
)
SELECT account,failures,(SELECT datetime(MIN(julianday(a.ts))) FROM auth a,bounds
 WHERE a.account=f.account AND a.kind='success' AND julianday(a.ts)>f.last_fail
 AND julianday(a.ts)>=julianday(start) AND julianday(a.ts)<julianday(end))
FROM f ORDER BY account;

더 읽기

sql-basics · 06-aggregate-and-group-by

면접 질문

인증 실패 경보를 분석하는 순서를 설명합니다.