PostgreSQL 요금 조회 우선순위 쿼리: ALL 폴백·기준일자 이력 설계
노선 요금을 조회할 때 항상 출발 정류장과 도착 정류장이 정확히 일치하는 데이터만 존재하는 것은 아니다. 특정 구간의 요금이 없으면 출발지 기준 요금, 도착지 기준 요금, 노선 전체 기본요금 순서로 대체해야 할 수 있다. 여기에 과거 요금과 미래 적용 예정 요금까지 관리하려면 단순한 동등 비교만으로는 부족하다.
이 글에서는 다음 요구사항을 하나의 구조로 정리한다.
- 출발·도착 정류장이 모두 일치하는 요금을 가장 먼저 조회한다.
- 정확한 요금이 없으면
S또는E기준에 따라 한쪽 정류장만 일치하는 규칙을 선택한다. - 그래도 없으면 노선 전체에 적용되는 기본요금을 사용한다.
- 조회 기준일보다 미래인 요금은 선택하지 않는다.
- 같은 규칙의 이력이 여러 건이면 기준일 이전의 가장 최신 요금을 선택한다.
- 관리자 화면에서는 현재 적용일과 미래 적용 예정일을 함께 조회한다.
조회 우선순위부터 명확히 정하자
예제에서는 정류장 코드 대신 사용할 수 있는 예약 문자열로 'ALL'을 사용한다. 여기서 'ALL'은 PostgreSQL의 SQL 연산자가 아니라 테이블에 저장하는 문자열 값이다.
출발 정류장 ST001, 도착 정류장 ST009를 조회한다고 가정하면 후보는 네 가지다.
ST001 → ST009: 출발과 도착이 모두 정확히 일치ST001 → ALL: 출발만 일치ALL → ST009: 도착만 일치ALL → ALL: 노선 전체 기본값
우선순위 기준값이 S이면 출발 일치 규칙을 먼저 선택한다.
- 1순위:
ST001 → ST009 - 2순위:
ST001 → ALL - 3순위:
ALL → ST009 - 4순위:
ALL → ALL
기준값이 E이면 2순위와 3순위만 바뀐다.
- 1순위:
ST001 → ST009 - 2순위:
ALL → ST009 - 3순위:
ST001 → ALL - 4순위:
ALL → ALL
이 순서를 먼저 문서로 고정해야 SQL의 정렬 순서가 비즈니스 규칙과 어긋나지 않는다.
기준일자 하나로 요금 이력 관리하기
시작일과 종료일을 모두 저장하면 유효기간을 명확하게 표현할 수 있다. 하지만 과거 구간 사이에 새 요금을 삽입할 때 앞뒤 행의 종료일을 계속 수정해야 한다.
관리자가 과거와 미래의 기준일을 자유롭게 추가해야 한다면 effective_date 하나만 저장하는 방식이 단순하다. 새 요금은 INSERT만 하고, 조회할 때 기준일 이전의 최신 행을 찾는다.
CREATE TABLE route_fare (
fare_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
route_code varchar(30) NOT NULL,
start_stop_code varchar(30) NOT NULL,
end_stop_code varchar(30) NOT NULL,
effective_date date NOT NULL,
fare_amount integer NOT NULL CHECK (fare_amount >= 0),
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uq_route_fare_rule_date
UNIQUE (
route_code,
start_stop_code,
end_stop_code,
effective_date
)
);
유니크 제약은 같은 노선·같은 규칙·같은 기준일의 요금이 중복 등록되는 것을 막는다. 금액을 원 단위로만 관리한다면 정수형이 다루기 편하다. 소수 단위 통화를 지원해야 한다면 부동소수점형 대신 필요한 자릿수를 지정한 numeric을 사용한다.
ALL 문자열을 사용할 때의 주의점
ALL은 실제 정류장 코드로 절대 사용되지 않는 예약값이어야 한다. 정류장 코드에 외래 키를 적용해야 하거나 규칙이 더 복잡해진다면 가짜 ALL 정류장을 만들기보다 다음 구조를 검토하는 편이 안전하다.
scope_type을PAIR,START,END,ROUTE처럼 분리한다.- 적용되지 않는 정류장 ID는
NULL로 저장한다. CHECK제약으로 범위 유형과 정류장 값의 조합을 제한한다.- PostgreSQL 15 이상에서
NULL와일드카드를 사용한다면UNIQUE NULLS NOT DISTINCT로 중복을 막는다.
이 글에서는 우선순위 쿼리를 읽기 쉽게 보여주기 위해 'ALL' 방식을 유지한다.
재현 가능한 예제 데이터
INSERT INTO route_fare (
route_code,
start_stop_code,
end_stop_code,
effective_date,
fare_amount
)
VALUES
('R100', 'ST001', 'ST009', DATE '2026-05-10', 4800),
('R100', 'ST001', 'ST009', DATE '2026-06-01', 5000),
('R100', 'ST001', 'ALL', DATE '2026-06-01', 4700),
('R100', 'ALL', 'ST009', DATE '2026-06-01', 4500),
('R100', 'ALL', 'ALL', DATE '2026-05-10', 3800),
('R100', 'ALL', 'ALL', DATE '2026-06-01', 4000),
('R100', 'ALL', 'ALL', DATE '2026-07-10', 4200),
('R100', 'ALL', 'ALL', DATE '2026-07-30', 4500);
조회 기준일을 2026-07-01로 지정하면 7월 10일과 7월 30일 데이터는 아직 적용 전이므로 단건 요금 후보에서 제외되어야 한다.
ORDER BY CASE로 최우선 요금 한 건 조회하기
아래의 :route_code, :start_stop_code, :end_stop_code, :priority_basis, :target_date는 애플리케이션에서 바인딩하는 파라미터다.
SELECT
f.fare_id,
f.fare_amount,
f.effective_date,
f.start_stop_code,
f.end_stop_code
FROM route_fare AS f
WHERE f.route_code = :route_code
AND :priority_basis IN ('S', 'E')
AND f.effective_date <= :target_date
AND f.start_stop_code IN (:start_stop_code, 'ALL')
AND f.end_stop_code IN (:end_stop_code, 'ALL')
ORDER BY
CASE
WHEN f.start_stop_code = :start_stop_code
AND f.end_stop_code = :end_stop_code
THEN 1
WHEN (:priority_basis = 'S'
AND f.start_stop_code = :start_stop_code
AND f.end_stop_code = 'ALL')
OR (:priority_basis = 'E'
AND f.start_stop_code = 'ALL'
AND f.end_stop_code = :end_stop_code)
THEN 2
WHEN (:priority_basis = 'S'
AND f.start_stop_code = 'ALL'
AND f.end_stop_code = :end_stop_code)
OR (:priority_basis = 'E'
AND f.start_stop_code = :start_stop_code
AND f.end_stop_code = 'ALL')
THEN 3
WHEN f.start_stop_code = 'ALL'
AND f.end_stop_code = 'ALL'
THEN 4
ELSE 99
END ASC,
f.effective_date DESC,
f.fare_id DESC
LIMIT 1;
쿼리는 다음 순서로 동작한다.
WHERE절에서 조회 노선과 관련 없는 행, 미래 이력, 네 가지 후보에 포함되지 않는 행을 제거한다.CASE가 정확 일치와 폴백 규칙에 1~4의 우선순위를 부여한다.- 같은 우선순위 안에서는
effective_date DESC로 기준일 이전의 최신 이력을 선택한다. - 마지막으로
fare_id DESC를 사용해 정렬 결과를 결정적으로 만든다. LIMIT 1로 가장 앞선 요금 한 건만 반환한다.
예제 데이터에서 R100, ST001, ST009, 2026-07-01을 조회하면 정확히 일치하는 2026년 6월 1일 요금 5,000원이 선택된다. 정확 일치 행이 없다면 S는 4,700원, E는 4,500원을 선택한다.
모든 후보가 미래 데이터뿐이라면 결과는 0건이어야 한다. 아직 적용되지 않은 요금을 현재 요금으로 자동 선택하면 기준일 조회의 의미가 깨지기 때문이다. 미래 요금 미리보기 기능은 별도의 조회 모드로 분리하는 편이 안전하다.
우선순위와 최신 날짜의 순서를 바꾸면 의미도 바뀐다
이 글의 정렬 기준은 규칙 우선순위 → 같은 우선순위의 최신 기준일이다. 따라서 오래된 정확 일치 규칙과 최근의 ALL → ALL 규칙이 동시에 유효하더라도 정확 일치 규칙이 먼저 선택된다.
반대로 effective_date DESC를 먼저 정렬하면 최근에 등록된 전체 기본값이 과거의 정확 일치 규칙을 덮을 수 있다. 두 방식은 성능 최적화의 차이가 아니라 전혀 다른 업무 정책이다.
요금표 전체가 특정 날짜에 한꺼번에 새 버전으로 바뀌는 구조라면 정렬 순서만 뒤집기보다 fare_version 또는 요금표 버전 테이블을 별도로 두고, 먼저 적용 버전을 결정한 뒤 그 버전 안에서 우선순위를 계산하는 설계가 명확하다.
현재 적용일과 미래 적용일을 함께 조회하기
관리자 화면의 기준일 선택 목록에는 과거 이력을 전부 보여줄 필요가 없을 수 있다. 현재 적용 중인 날짜 한 건과 미래에 적용될 날짜만 보여주려면 먼저 기준일 이하의 가장 최근 날짜를 찾는다.
WITH anchor AS (
SELECT MAX(effective_date) AS current_effective_date
FROM route_fare
WHERE route_code = :route_code
AND effective_date <= :target_date
)
SELECT DISTINCT
f.effective_date
FROM route_fare AS f
CROSS JOIN anchor AS a
WHERE f.route_code = :route_code
AND (
a.current_effective_date IS NULL
OR f.effective_date >= a.current_effective_date
)
ORDER BY f.effective_date ASC;
기준일이 2026-07-01이고 등록된 날짜가 5월 10일, 6월 1일, 7월 10일, 7월 30일이라면 결과는 다음 세 건이다.
2026-06-01: 현재 적용 중인 기준일2026-07-10: 미래 적용 예정일2026-07-30: 미래 적용 예정일
과거 기준일이 한 건도 없다면 anchor의 값은 NULL이 되고 미래 날짜 전체가 반환된다. 노선 전체가 아니라 특정 요금 규칙의 날짜만 필요하다면 CTE와 외부 쿼리에 동일한 출발·도착 범위 조건을 추가해야 한다.
CURRENT_DATE, now()와 타임존을 정확히 이해하기
조회 기준을 항상 오늘로 고정할 수 있다면 :target_date 대신 CURRENT_DATE를 사용할 수 있다. 다만 PostgreSQL의 현재 날짜·시간 함수는 현재 트랜잭션의 시작 시점을 기준으로 하며, 달력 날짜는 세션의 TimeZone 설정에 영향을 받는다.
먼저 현재 세션 설정을 확인한다.
SHOW TimeZone;
SELECT
CURRENT_DATE,
CURRENT_TIMESTAMP;
모든 연결에서 한국 시간을 사용하도록 세션 타임존을 보장했다면 다음처럼 간단히 작성할 수 있다.
SET TIME ZONE 'Asia/Seoul';
SELECT CURRENT_DATE;
세션 설정과 무관하게 서울의 달력 날짜를 명시하려면 타임존 정보가 있는 타임스탬프를 먼저 서울 시간으로 변환한 뒤 날짜로 바꾼다.
SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Seoul')::date;
CURRENT_DATE AT TIME ZONE 'Asia/Seoul'은 서울 기준의 오늘을 구하는 표현으로 적절하지 않다. CURRENT_DATE에는 시각 정보가 없기 때문에 날짜를 자정의 타임스탬프로 해석하는 별도의 변환이 일어나며, 의도한 업무 날짜와 다른 의미가 된다.
또한 now()는 호출 순간마다 계속 변하는 실시간 시계가 아니다. PostgreSQL에서 now(), CURRENT_TIMESTAMP, transaction_timestamp()는 현재 트랜잭션의 시작 시각을 반환한다. 매우 긴 트랜잭션이 자정을 넘는 작업이라면 이 특성도 고려해야 한다.
Spring Boot에서는 날짜를 파라미터로 넘기는 편이 좋다
결제·정산·예약 같은 핵심 로직은 SQL 안에서 오늘을 직접 계산하는 것보다 애플리케이션이 LocalDate를 전달하는 방식이 테스트하기 쉽다. 운영에서는 서울 시간의 Clock을 사용하고 테스트에서는 고정된 Clock으로 교체할 수 있다.
@Bean
Clock businessClock() {
return Clock.system(ZoneId.of("Asia/Seoul"));
}
LocalDate targetDate = LocalDate.now(businessClock);
fareMapper.findFare(
routeCode,
startStopCode,
endStopCode,
priorityBasis,
targetDate
);
MyBatis에서 LocalDate를 DATE로 바인딩하면 컬럼이나 파라미터에 불필요한 문자열 변환을 적용할 필요가 없다.
AND f.effective_date <= #{targetDate}
과거·미래 시점 테스트에서도 원하는 날짜를 직접 전달할 수 있어 DB 서버 시간을 바꾸지 않고 동일한 쿼리를 검증할 수 있다.
유니크 제약과 인덱스는 역할이 다르다
앞서 정의한 유니크 제약은 PostgreSQL이 자동으로 생성하는 B-tree 유니크 인덱스를 통해 중복을 막는다. 관리자 화면에서 노선별 기준일 목록을 자주 조회한다면 다음 인덱스를 추가로 검토할 수 있다.
CREATE INDEX idx_route_fare_route_date
ON route_fare (route_code, effective_date);
다만 인덱스가 있다고 해서 ORDER BY CASE 정렬까지 항상 사라지는 것은 아니다. PostgreSQL 플래너는 테이블 크기와 통계, 조건의 선택도에 따라 인덱스 스캔이나 순차 스캔을 선택한다. 특히 CASE로 계산한 우선순위는 일반 복합 인덱스의 물리적 정렬과 그대로 일치하지 않을 수 있다.
따라서 “인덱스를 만들면 반드시 몇 ms 안에 끝난다”라고 단정하기보다 실제 데이터 분포에서 실행 계획을 확인해야 한다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
노선별 요금 규칙이 몇 건 되지 않는다면 소량의 후보를 정렬하는 비용은 매우 작을 수 있다. 반대로 규칙 수와 이력이 크게 늘면 범위 유형별 부분 인덱스나 네 개의 분기 쿼리를 UNION ALL로 결합하는 방식이 더 예측 가능한 실행 계획을 만들 수 있다. 최종 선택은 추측보다 실행 계획과 부하 테스트를 기준으로 해야 한다.
기간 이력이 더 적합한 경우
기준일자 하나만 저장하는 방식은 중간 날짜 삽입이 간단하지만, 각 행의 명시적인 종료일을 바로 확인하기는 어렵다. 결제 감사나 정책 승인처럼 유효기간 자체를 엄격하게 관리해야 한다면 시작일과 종료일 또는 PostgreSQL의 daterange를 고려할 수 있다.
기간은 양쪽 끝을 모두 포함하는 방식보다 [시작일, 종료일) 반개구간으로 관리하는 것이 안전하다. 이전 요금의 종료일과 다음 요금의 시작일이 같아도 두 행이 동시에 유효해지지 않기 때문이다.
WHERE valid_from <= :target_date
AND (
valid_to IS NULL
OR :target_date < valid_to
)
PostgreSQL의 범위 타입과 GiST 배제 제약을 사용하면 같은 노선과 같은 규칙에서 기간이 겹치는 데이터의 저장 자체를 차단할 수도 있다.
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE route_fare_period
ADD CONSTRAINT ex_route_fare_no_overlap
EXCLUDE USING gist (
route_code WITH =,
start_stop_code WITH =,
end_stop_code WITH =,
valid_period WITH &&
);
기준일자 방식과 기간 방식 중 어느 하나가 항상 정답인 것은 아니다.
- 과거·미래 날짜를 자유롭게 추가하고 기존 행 수정을 최소화하려면 기준일자 하나가 단순하다.
- 명시적인 유효기간, 겹침 방지, 승인·감사 요구가 중요하면 기간 방식이 적합하다.
- 요금표 전체가 버전 단위로 바뀐다면 개별 규칙 이력보다 요금표 버전 모델이 명확하다.
반드시 확인할 경계 조건
운영 반영 전에는 최소한 다음 상황을 테스트해야 한다.
- 네 가지 규칙이 모두 존재할 때 정확 일치가 선택되는가?
- 정확 일치가 없을 때
S와E의 2·3순위가 올바르게 바뀌는가? - 같은 규칙에 과거 이력이 여러 건이면 기준일 이전의 최신 행이 선택되는가?
- 최신 등록 행이 미래여도 그 이전의 유효한 과거 행을 찾는가?
- 정확 일치 규칙은 미래에만 있고 폴백 규칙은 과거에 있을 때 폴백을 선택하는가?
- 모든 후보가 미래뿐이거나 데이터가 없을 때 0건을 반환하는가?
- 같은 규칙과 기준일의 동시 등록을 유니크 제약이 차단하는가?
priority_basis가S또는E가 아닐 때 잘못된 요금을 반환하지 않는가?- 기준일과 적용일이 같은 날에도 해당 요금이 포함되는가?
- 한국 시간 자정 전후의 업무 날짜가 의도대로 계산되는가?
마무리
복잡해 보이는 요금 조회도 역할을 나누면 구조가 선명해진다. WHERE 절은 관련 후보와 유효한 이력만 남기고, ORDER BY CASE는 폴백 우선순위를 정하며, effective_date DESC는 같은 규칙의 최신 이력을 선택한다.
가장 중요한 것은 SQL을 작성하기 전에 우선순위와 이력의 의미를 먼저 고정하는 것이다. “구체적인 규칙이 항상 우선인가?”, “최근의 전체 요금표가 과거의 예외 규칙까지 덮는가?”를 명확히 정해야 테이블 구조와 정렬 순서가 흔들리지 않는다.
그다음 유니크 제약으로 중복을 차단하고, 날짜를 파라미터로 전달해 재현 가능한 테스트를 만들며, 실제 데이터로 실행 계획을 확인하면 유지보수하기 좋은 요금 조회 구조를 만들 수 있다.
