
SOFTMOA TECHNOLOGY
SQLite LAST_VALUE가 최신 상태를 반환하지 않을 때 프레임 확인 방법
소프트모아가 정리한 기술 기록입니다.
Contents
SQLite에서 출고 이력의 최신 상태를 구하려고 LAST_VALUE()를 썼는데 행마다 다른 상태가 나오면 OVER 안에서 프레임이 어디서 끝나는지 확인합니다. 이력은 그대로 두고 출고 건마다 최신 상태를 붙이려면 끝을 UNBOUNDED FOLLOWING으로 지정합니다. 출고 건별로 최신 이력 한 줄만 필요하다면 ROW_NUMBER()로 그 행을 고릅니다.
같은 출고 건에서 두 프레임 비교하기
아래는 가상의 출고 이력 5행입니다. event_minute는 작업 시작 뒤 경과한 분이며, 같은 분에 여러 이벤트가 있으면 이 예제에서는 event_id가 큰 쪽을 나중 기록으로 간주합니다. 파일을 열 필요 없이 sqlite3 -batch -bail -header -column :memory:를 시작한 뒤 SQL을 붙여 넣으면 됩니다. SELECT sqlite_version();으로 실제 실행 버전을 확인합니다. SQLite는 3.25.0부터 윈도우 함수를 지원하며[2], 아래 결과는 3.54.0에서 확인했습니다.
WITH events(shipment_id, event_minute, event_id, status) AS (
VALUES ('S17', 8, 101, 'queued'),
('S17', 12, 102, 'picking'),
('S17', 20, 103, 'packed'),
('S17', 20, 104, 'shipped'),
('S23', 5, 201, 'queued')
)
SELECT shipment_id, event_minute, event_id, status,
LAST_VALUE(status) OVER (
PARTITION BY shipment_id ORDER BY event_minute, event_id
) AS default_last,
LAST_VALUE(status) OVER (
PARTITION BY shipment_id ORDER BY event_minute, event_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS shipment_last
FROM events
ORDER BY shipment_id, event_minute, event_id;
S17의 첫 이력인 101번을 보세요. 원래 상태와 default_last는 queued지만 shipment_last는 shipped입니다. 20분에 기록한 103번에서도 두 열은 packed와 shipped로 갈립니다. 그런데 같은 20분의 104번에서는 둘 다 shipped입니다. 이력이 하나뿐인 S23도 두 열이 queued로 같아서, 이런 행만 보면 기존 조회가 맞는 것처럼 보입니다.
PARTITION BY는 어느 출고 건의 이력을 볼지 정합니다. 프레임은 그 이력 중 지금 계산에 쓸 행의 범위입니다. SQLite에서 프레임을 생략하면 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 적용됩니다. LAST_VALUE()는 이 범위의 마지막 행에서 값을 읽습니다.[1] 기본 범위에는 ORDER BY에 적힌 값이 모두 현재 행과 같은 행도 들어갑니다. 예제에서는 event_id로 103번과 104번의 순서를 정했습니다. 분만 정렬하면 두 행 중 어느 쪽이 마지막인지 보장할 수 없습니다. 프레임을 넓혀도 순서는 정해지지 않습니다.[1][3]
출고 건마다 한 줄만 남기려면
이력 화면은 5행을 유지하고 최신 상태 열만 덧붙이면 됩니다. 출고 현황 화면에 2건만 표시한다면 시간과 이벤트 번호를 내림차순으로 정렬한 뒤 건별 1번 행을 고릅니다. 상태를 기록한 시각과 이벤트 번호도 같은 행에서 읽습니다.
WITH events(shipment_id, event_minute, event_id, status) AS (
VALUES ('S17', 8, 101, 'queued'),
('S17', 12, 102, 'picking'),
('S17', 20, 103, 'packed'),
('S17', 20, 104, 'shipped'),
('S23', 5, 201, 'queued')
), ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY shipment_id ORDER BY event_minute DESC, event_id DESC
) AS rn
FROM events
)
SELECT shipment_id, event_minute, event_id, status
FROM ranked
WHERE rn = 1
ORDER BY shipment_id;
결과는 S17 / 20 / 104 / shipped와 S23 / 5 / 201 / queued입니다. queued 상태인 출고만 보고 싶다면, status = 'queued'를 바깥의 WHERE rn = 1 뒤에 AND로 붙입니다. 그러면 S23만 남습니다. 같은 조건을 ranked 안의 FROM events 뒤에 WHERE로 넣으면 순위를 매기기 전에 shipped 행을 빼므로 S17의 101번도 결과에 섞입니다.[1][3] 두 쿼리 모두 오류 없이 실행되지만 첫 쿼리만 최신 상태가 queued인 건을 찾습니다.
- 운영 쿼리에 옮기기 전, 시간 값이 같을 때 실제 처리 순서를 어떤 열이 나타내는지 확인합니다. 번호가 단순 저장 순서라면 지연 수신된 이벤트가 본래 순서와 다르게 보일 수 있습니다.
- 조회 기간을 먼저 잘라 내면 그 기간 안의 마지막 이력만 찾습니다. 기간 밖 기록까지 포함한 현재 상태가 필요하면 순위를 계산할 입력의 범위부터 확인합니다.
문법 확인: [1] SQLite 윈도우 함수, [2] SQLite 3.25.0 릴리스 노트, [3] SQLite SELECT. 서비스 안내는 softmoa.com에서 확인할 수 있습니다.
소프트모아는 해당 시스템을 구축합니다. 문의하기
