주문·항목·결제 JOIN, 합계가 부풀 때 사전 집계하는 법

주문 항목 금액과 결제 금액을 한 쿼리에서 구하려고 두 자식 표를 주문에 붙이면 합계가 부풀 수 있다. 결합된 행 한 개가 무엇을 뜻하는지 확인하고, 주문별 합계가 목적이라면 각 자식 표를 주문 단위로 먼저 집계한 뒤 결합하는 방법을 검토한다. SUM(DISTINCT amount)로 숫자의 중복만 없애면 같은 금액의 서로 다른 거래까지 사라질 수 있다.

이 글의 주문·항목·결제와 금액은 모두 합성 자료다. SQLite 3.53.1의 메모리 DB에서 입력·결합행·합계를 실제 대조했다. 실제 주문 서비스·회계 자료·결제 요청이나 운영 DB를 시험한 결과가 아니며, 가상 금액의 단위는 예시 전체에서 같다.

항목 두 건과 결제 세 건을 붙이면 여섯 조합이 된다

주문 O1에 항목 i1=100, i2=200이 있고 결제는 p1=100, p2=100, p3=100이라고 하자. 항목 합계는300, 결제 합계도300이다. 항목 한 행과 결제 한 행은 다른 단위의 사실이고, 이번에는 항목별로 어떤 결제를 배분했는지 연결 정보가 없다.

두 자식 표를 모두 order_id로만 주문에 붙이면 i1은 p1·p2·p3과, i2도 p1·p2·p3과 연결된다. 각 항목 금액은 세 번, 각 결제 금액은 두 번 나타난다. SQLite SELECT의 JOIN 처리 설명처럼 조건에 맞는 행 조합이 생긴 뒤 그 결과에 집계가 적용되기 때문이다.

O1의 계산 경로	항목 합계	결제 합계
각 원본 자식 표에서 합산	300	300
여섯 결합행에서 SUM	900	600
주문별 사전 집계 후 결합	300	300

이 차이는 결제가 실제로 늘었다는 뜻이 아니다. 원래 금액을 반복해서 더한 결과다. 행 수를 설명하는 키의 관계와 결합 행 증가에서 한 단계 더 나아가, 각 금액이 결과에 몇 번 포함됐는지 추적해야 한다.

주문 단위 합계라면 자식별로 한 행을 만든다

이번 목적은 주문마다 항목 합계와 결제 합계를 각각 보여 주는 것이다. 다음 쿼리는 각 자식에서 order_id로 먼저 집계하고 그 결과를 주문에 결합한다. 로컬에서 O1은300/300, 자식이 없는 O2는NULL/NULL로 반환됐다.

WITH item_totals AS (
    SELECT order_id, SUM(amount) AS item_total
    FROM items GROUP BY order_id
), payment_totals AS (
    SELECT order_id, SUM(amount) AS payment_total
    FROM payments GROUP BY order_id
)
SELECT o.order_id, i.item_total, p.payment_total
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN payment_totals p ON p.order_id = o.order_id;

부모 표의 order_id가 유일하고, 각 집계 결과도 같은 완전한 키에 대해 최대 한 행인지 확인해야 한다. 월·통화·지점이 다른 금액을 함께 더할 수 없는 업무라면 필요한 키·기간·환산 규칙을 먼저 정한다. 이번의 같은 단위 작은 예가 그 판단까지 대신하는 것은 아니다.

집계 결과를 다시 여러 명세 행에 붙이고 합산하면 주문 합계가 명세 수만큼 반복될 수 있다. 상품별 결제액을 보고 싶을 때에도 주문별 합계만으로는 배분 방법이 부족하다. 항목과 결제 중 어느 단위를 보고하는지, 실제 대응 정보나 배분 규칙이 있는지 확인해야 한다.

같은 금액의 다른 거래를 SUM(DISTINCT)로 지우지 않는다

SQLite의 집계 함수 설명에서 DISTINCT는 집계에 넘길 중복 값을 제외한다. 거래 ID의 중복을 판정하는 기능은 아니다. 이번 p1·p2·p3은 서로 다른 결제지만 금액은 모두100이므로, SUM(DISTINCT p.amount)는100이 됐다. 원래 합계300으로 돌아오지 않는다.

반대로 COUNT(DISTINCT payment_id)가3건을 반환해도 금액 합계를 정확히 계산했다는 증거가 되지는 않는다. 제외하려는 중복이 거래인지 값인지 나누고, 같은 금액의 서로 다른 ID를 검증 자료에 넣어 본다. 이는 작성자의 확인 제안이며 이번 SQL 관측 이외의 제품 동작을 보장하지 않는다.

자식이 없는 주문과 확인하지 못한 금액을 구별한다

이번 O2에는 자식 행이 없다. LEFT JOIN 후 COUNT(*)는1이었지만 부모를 남기기 위한 행이지, 실제 항목이나 결제가 한 건 있었다는 뜻은 아니다. SQLite SUM은 NULL이 아닌 입력이 없으면 NULL을 반환한다. 필요한 건수를 셀 때에도 NULL이 아닌 자식 ID를 세는 등 무엇을 세는지 명확히 한다.

NULL을0으로 바꿀 때도 자식 행 없음이 확인된0을 뜻하는 업무인지 먼저 정한다. 아직 확보하지 못한 결제 자료까지0으로 단정하지 않는다. 키 자체가 비었다면 SQL과 pandas의 결측 키 매칭도 따로 점검한다.

출처가 지지하는 계산과 결론을 구별하는 방법에는 연인원과 고유 인원을 혼동하지 않는 예가 있다. 이 글에서 추가한 판단은 두 자식 표의 단위가 교차해 같은 금액이 반복되는 구조와, 자식별 집계로 숫자를 보존할 조건이다. 처음부터 원표의 행 관계가 다르면 OCR 표의 값과 행·열 대응으로 돌아간다.

자주 묻는 질문

GROUP BY order_id만 붙이면 고쳐지나요?

결합 후 반복된 행을 주문별로 묶어도 SUM에 들어가는 값의 횟수는 그대로다. 어떤 입력 행을 몇 번 더하는지 보고, 목적에 맞으면 각 자식의 집계를 먼저 수행한다.

사전 집계는 언제나 맞는 방법인가요?

보고할 단위와 집계 키가 같고 기간·금액 단위·부모 키 유일성이 확인돼야 한다. 상품별 결제 배분이나 명세 사이의 대응이 필요한 업무를 주문별 합계만으로 해결했다고 하지 않는다.

합계가 우연히 같으면 확인을 끝내도 되나요?

같은 금액의 별도 거래, 여러 자식 행, 자식이 없는 부모를 포함해 각 거래 ID와 금액이 의도한 횟수만큼 계산됐는지 본다. 총액의 일치만으로는 상쇄나 잘못된 연결을 놓칠 수 있다.