장부에 없는 숫자
퀴즈 하나. 재고 관리 시스템에서 '남은 수량'은 어디에 저장돼 있을까. 대부분 stock이나 remain_qty 같은 컬럼을 떠올릴 것이다. 나도 그랬다.
그런데 내가 운영에 참여하는 시스템(농산물 생산 이력 관리, Spring Boot + JPA/Hibernate + MyBatis + PostgreSQL)에서 가장 중요한 숫자인 '건조물 잔량'은 어느 테이블에도 저장돼 있지 않다. 화면에는 매번 잘만 표시되는 그 숫자가, 데이터베이스 어디를 뒤져도 없다. MyBatis SELECT 245건을 하나씩 읽어나가던 중에 그 비밀을 푸는 쿼리를 만났다 — 잔량은 저장되는 값이 아니라, 조회할 때마다 SQL이 새로 계산하는 값이었다.
창고 비유로 말하면 이 시스템은 재고를 '장부에 적어두지 않는다'. 누가 물어볼 때마다 창고에 들어가 저울을 들고 실사(實査)를 한다. 이 글은 그 실사 쿼리 한 개를 처음부터 끝까지 해부한 기록이다.
잔량의 정체: 컬럼이 아니라 계산식
이 시스템에서 건조물(수확 후 건조를 마친 농산물)의 잔량은 이렇게 정의된다.
실제 잔량 = (건조 후 무게 × 995/1000) ← 0.5% 자연 손실 보정
- 포장으로 나간 양 (packaging 합계)
- 폐기 대상으로 잡힌 양 (dispose_target 합계)
- 품질검사 시료로 쓴 양 (drying_exam 합계)
첫 줄부터 도메인의 물리학이 들어 있다. 농산물은 건조 후에도 수분이 날아가면서 무게가 조금씩 준다. 그래서 계산의 출발점부터 995/1000, 즉 0.5%를 자연 손실로 깎고 시작한다. 나머지는 뺄셈이다 — 포장돼서 나간 것, 폐기로 잡힌 것, 검사 시료로 쓴 것을 차감하면 지금 창고에 남아 있어야 할 양이 나온다.
흥미로운 건 이 규칙이 사는 곳이다. 자바 코드 어디에도 이 수식은 없다. 서비스 계층에도, 엔티티에도 없다. 이 시스템의 핵심 비즈니스 규칙 하나가 'MyBatis XML의 SQL 안에만' 존재한다. SQL을 읽지 못하면 이 시스템의 잔량이 어떻게 정해지는지 영영 알 수 없다는 뜻이다.
쿼리 해부: 소계 두 개를 만들어 조인한다
실물 쿼리의 골격이다 (테이블·컬럼 구조는 원본 그대로, 식별 정보만 정리했다).
select coalesce(sum(
cast(x.after_weight as numeric)
- x.use_packing - x.use_exam - x.use_disp_target), 0)
from (
select d.after_weight,
coalesce(sum(cast(p.weight as numeric)), 0) as use_packing,
coalesce(t_disp.volume, 0) as use_disp_target,
coalesce(t_exam.use_exam, 0) as use_exam
from drying d
left join packaging p on p.drying_id = d.id
left join ( -- 건조 건별 폐기량 소계
select d.id, sum(cast(dt.volume as numeric)) as volume
from drying d left join dispose_target dt on dt.drying_id = d.id
group by d.id
) t_disp on t_disp.id = d.id
left join ( -- 건조 건별 검사 시료량 소계
select dqe.drying_id, coalesce(sum(cast(de.volume as numeric)), 0) as use_exam
from drying_exam de
inner join drying_qa_exam dqe on de.id = dqe.exam_id
group by dqe.drying_id
) t_exam on t_exam.drying_id = d.id
group by d.id, d.after_weight, t_disp.volume, t_exam.use_exam
) x
where coalesce(x.after_weight, 0) = 0
or ((x.after_weight * 995/1000) - x.use_packing - x.use_disp_target - x.use_exam > 0);
처음 봤을 때는 벽처럼 보였다. 그런데 층을 나눠 읽으면 구조는 단순하다. 안쪽에서 바깥쪽으로 세 층이다.
'1층 — 소계 서브쿼리 두 개.' t_disp와 t_exam이라는 괄호 안 SELECT들은 '건조 건별 폐기량 합계'와 '건조 건별 시료량 합계'를 미리 만들어두는 임시 표다. 이렇게 FROM 절 안에 들어간 SELECT를 인라인 뷰(inline view)라고 부른다. 복잡한 집계는 한 방에 하려 들지 말고, 소계를 먼저 만들어 표처럼 조인하라 — 이 쿼리가 보여주는 첫 번째 기술이다.
'2층 — LEFT JOIN으로 붙이기.' 왜 전부 LEFT JOIN일까. 포장을 한 번도 안 한 건조 건, 폐기가 없는 건조 건도 잔량은 계산돼야 하기 때문이다. INNER JOIN을 썼다면 포장 기록이 없는 건조 건은 결과에서 통째로 사라진다 — 재고가 가장 많이 남은 건들이 목록에서 증발하는, 꽤 무서운 버그가 된다.
'3층 — COALESCE와 CAST.' 이 쿼리에 coalesce가 여섯 번 나오는 데는 이유가 있다. LEFT JOIN으로 짝이 없는 행에는 NULL이 들어오는데, SQL에서 NULL은 산수를 오염시킨다 — 100 - NULL은 0도 100도 아닌 NULL이다. coalesce(값, 0)은 “NULL이면 0으로 쳐라”는 안전장치다. LEFT JOIN과 SUM을 쓰면서 COALESCE가 없는 쿼리는 대부분 시한폭탄이다. cast(... as numeric)은 타입이 제각각인 값들 — 문자형으로 저장된 포장 무게와 용량들, 부동소수(double)로 저장된 건조 무게 — 을 정확한 십진 계산이 가능한 numeric 하나로 통일하는 장치다. 컬럼 타입이 이렇게 균일하지 않은 것 역시 연식이 있는 시스템에서 드물지 않게 만나는 흔적이다.
마지막 WHERE의 > 0 조건까지 읽으면 이 쿼리의 목적이 완성된다. '계산해봤더니 아직 잔량이 남아 있는 건조 건만' 골라내는 것이다.
이 쿼리는 재현 스크립트로 직접 돌려볼 수 있다. 샘플 건조 3건(잔량이 남은 건, 딱 떨어져 제외되는 건, 무게 미입력 건)과 손으로 계산한 기대값을 주석으로 달아뒀고, PostgreSQL에서 실행하면 주석의 계산과 같은 값이 나오는 것을 확인할 수 있다.
건너편 JPA 엔티티에는 계산이 없다
같은 데이터를 바라보는 JPA 쪽 엔티티는 어떻게 생겼을까.
@Entity
public class Drying {
private double beforeWeight = 0.0; // 건조 전 중량
private Double afterWeight = 0.0; // 건조 후 중량
@OneToMany(mappedBy = "drying") private List<DisposeTarget> disposeTargets;
@OneToMany(mappedBy = "drying") private List<Packaging> packagings;
@OneToMany(mappedBy = "drying") private List<DryingQaExam> qaExams;
}
무게 두 개와 컬렉션 세 개. 잔량은 흔적도 없다. 원재료(연관관계)는 다 갖고 있지만 계산은 하지 않는다 — 계산은 전부 SQL의 몫이다.
이 대비는 첫 글에서 발견한 분업 규칙의 실물 사례이기도 하다. 다중 조인과 집계가 필요한 조회는 MyBatis가 맡는다고 했는데, 그 대표 선수가 바로 이 쿼리다. 참고로 이런 다층 인라인 뷰와 조건부 집계는 JPQL로 옮기기 어렵고, 옮길 수 있어도 옮길 이유가 없다 — 엔티티 그래프로 표현할 대상이 아니라 순수한 집합 연산이기 때문이다.
저장할 것인가, 계산할 것인가
여기서 설계 질문이 나온다. 잔량을 매번 계산하는 게 최선일까? 잔량 컬럼을 하나 두고 포장·폐기 때마다 갱신하면 조회가 훨씬 싸지 않을까? 실제 설계 문서에 기록된 선택지는 세 가지였다.
| 선택지 | 장점 | 대가 |
|---|---|---|
| ① 조회 시 동적 집계 (현재) | 항상 정확 — 원본 기록에서 매번 계산하므로 어긋날 수가 없다 | 조회 비용. 호출될 때마다 집계가 돈다 |
| ② 잔량 컬럼 + 애플리케이션 갱신 | 조회가 빠르다 | 포장·폐기·검사 코드 어느 한 곳이라도 갱신을 빼먹으면 장부와 실물이 어긋난다. 그리고 그 버그는 조용히 쌓인다 |
| ③ 트리거/스케줄 갱신 | 중간 지점 | DB 트리거·배치라는 운영 복잡도가 새로 생긴다 |
창고 비유로 옮기면 ①은 매번 실사, ②는 장부 관리, ③은 정기 재고조사다. 어느 쪽이 정답이라는 게 아니라 — 무엇을 지불할지의 선택이다. 이 시스템은 규제 산업이라 '숫자가 틀리는 것'의 비용이 '조회가 느린 것'의 비용보다 압도적으로 크고, 그래서 ①을 유지하고 있다. 반대로 초당 수백 번 조회되는 커머스 재고라면 같은 논리로 ②나 ③에 기울었을 것이다.
'재고 수량 같은 건 당연히 컬럼에 있겠지'라는 처음의 직감으로 돌아가면 — 파생 값(다른 값들로부터 계산되는 값)을 저장할지 계산할지는 당연한 게 아니라 설계 결정이고, 각 선택에는 가격표가 붙어 있다.
실무 조언: 파생 값을 만나면 물어볼 것들
- 이 값의 '진실의 원천'은 어디인가. 저장된 컬럼인가, 계산식인가, 둘 다인가. 둘 다라면 어긋났을 때 누구를 믿는지가 정해져 있는가.
- LEFT JOIN + 집계 조합에서 COALESCE를 확인하라. 짝 없는 행의 NULL이 합계를 통째로 NULL로 만드는 사고는 데이터가 쌓인 뒤에야 드러난다.
- 비즈니스 규칙이 SQL에만 사는 시스템이라면, 최소한 그 수식을 문서 어딘가에 옮겨 적어두자. 이 시스템의 0.5% 보정 같은 규칙은 SQL을 읽을 수 있는 사람에게만 보이는 지식이었다.
마치며: 숫자의 무게
잔량 쿼리 하나를 해부하며 배운 것은 문법보다 태도에 가깝다. 인라인 뷰도 COALESCE도 결국 도구이고, 진짜 질문은 '이 숫자는 어디서 오는가'였다. 시스템에서 제일 중요한 숫자가 컬럼이 아니라 계산식이라는 걸 알고 나면, 화면의 숫자 하나가 다르게 보인다.
그런데 이 숫자들의 일은 여기서 끝나지 않는다. 월말이 되면 이 잔량과 폐기량들이 업체별 소계와 총계, 1월부터 12월까지 늘어선 컬럼을 가진 보고서로 불려 나간다. 그 보고서의 세계는, JPA가 완전히 손을 들고 SQL이 홈그라운드가 되는 곳이다.