주문별 합계를 정확하게 유지하기

소개

PAPER & PIER는 시장의 두 판매대에서 소품을 판매합니다. 마감 전에 접수 데스크에서는 각 주문의 상품 금액을 실제로 받은 금액 및 환불 금액과 대조해야 합니다. 한 주문에 여러 상품과 여러 실제 결제가 포함될 수 있습니다. 원본 목록을 부주의하게 조인하면 정확히 결제된 주문도 잘못된 것처럼 보일 수 있습니다.

각 종류의 사실 데이터를 별도로 합산하는 재사용 가능한 SQLite 뷰 세 개를 만듭니다. 기본적인 터미널 탐색과 SQL SELECT/WHERE 지식이 필요합니다. 집계, 조인, 뷰, 누락된 값은 여기서 설명합니다. SQLite와 합성 원장은 준비되어 있습니다. 전체 과정에서 Terminal을 사용하세요.

모든 원래 테이블, ID, 값을 유지하세요. item_totals, payment_totals, refund_totals 뷰만 만들거나 교체하고, 정의를 totals.sql에 저장할 수 있습니다. 금액은 정수 센트 단위입니다. 이는 세금, 결제 서비스 제공업체의 정산, 환전이 포함되지 않은 단순화된 수납 대사 작업입니다.

주문마다 하나의 상품 합계 유지하기

이 단계에서는 정상 주문을 읽고, 원본 데이터 조인이 금액을 중복시키는 방식을 확인하며, 정확한 상품 합계 뷰를 저장합니다. Terminal에서 시작하세요. cd는 디렉터리를 변경합니다. 잘못된 디렉터리에 들어갔을 때도 이 절대 경로로 돌아올 수 있습니다:

cd /home/labex/project/sales-desk

sqlite3는 지정한 데이터베이스를 엽니다. -header는 열 이름을 출력하고, -column은 값을 정렬하며, 따옴표 안의 SQL은 연결이 닫히기 전에 실행됩니다. 따옴표는 SQL의 공백과 세미콜론을 하나의 셸 인수로 묶습니다. 준비된 주문의 식별 정보를 읽어 보세요:

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

주문은 네 건입니다. A1과 A2는 고객 표시 이름이 모두 Alex Kim이지만 서로 다른 주문입니다. 고객 이름이 아니라 반드시 주문 ID로 그룹화하세요. A1의 상품 행과 결제 이벤트를 읽어 보세요:

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

상품 금액은 300 + 500 = 800센트입니다. 결제 P1과 P2는 서로 다른 성공한 400센트 이벤트이며, 이 합계도 800입니다. P3는 실패해 금액에 기여하지 않습니다. succeeded는 이 프로젝트의 계산에 포함되는 이벤트를 뜻하며, pending과 failed는 포함되지 않습니다.

조인은 관련 행들을 결합합니다. AS는 짧은 테이블 별칭을 지정합니다. i는 상품, p는 결제를 뜻합니다. ON은 일치하는 주문 ID를 지정합니다. 합산하기 전에 A1에서 일치하는 모든 쌍을 확인하세요:

sqlite3 -header -column ledger.db "
SELECT i.item_id, i.unit_cents, p.payment_id, p.amount_cents
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded'
ORDER BY i.item_id, p.payment_id;"

행이 네 개입니다. 상품 두 개 각각이 두 결제 모두와 일치합니다. 이는 실제 조인 동작입니다. 상품 네 개나 결제 네 건이 발생했다는 뜻은 아닙니다. SUM은 입력 행 전체에 대해 표현식을 합산합니다. 이렇게 중복된 입력에서는 두 합계 모두 잘못됩니다:

sqlite3 -header -column ledger.db "
SELECT SUM(i.quantity * i.unit_cents) AS joined_due,
       SUM(p.amount_cents) AS joined_paid
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded';"

표시되는 두 합계는 모두 1600으로 실제 금액의 두 배입니다. DISTINCT는 서로 다른 결제 식별 정보가 아니라 서로 다른 값을 유지합니다. 이 문제를 해결할 수 없습니다:

sqlite3 -header -column ledger.db "
SELECT SUM(DISTINCT amount_cents) AS wrong_paid
FROM payments WHERE order_id = 'A1' AND status = 'succeeded';"

결과는 400이며, 적법한 동일 금액 결제 한 건이 빠졌습니다. 대신 각 사실 목록을 따로 합산하세요. GROUP BY order_id는 주문 ID마다 결과 행 하나를 만듭니다. quantity * unit_cents는 합산 전에 각 상품의 금액을 계산합니다. 결과의 집계 단위, 즉 출력 행 하나가 나타내는 대상은 주문 하나입니다.

뷰는 데이터베이스에 저장된 이름 있는 SELECT입니다. 뷰를 읽으면 현재 원본 행에 대해 해당 SELECT를 실행하며, 상품을 복사하거나 교체하지 않습니다. 정의를 totals.sql에 저장하세요. 셸에서 cat은 다음 here-document의 내용을 그대로 파일에 쓰며, >는 기존 내용을 덮어씁니다. 마지막 SQL을 한 줄에 단독으로 붙여 넣으세요. 이 블록은 대화형 SQLite 프롬프트 안이 아니라 셸 프롬프트에서 실행합니다:

cat > totals.sql <<'SQL'
DROP VIEW IF EXISTS item_totals;
CREATE VIEW item_totals AS
SELECT order_id, SUM(quantity * unit_cents) AS due_cents
FROM items
GROUP BY order_id;
SQL

DROP VIEW IF EXISTS는 이 뷰의 이전 정의가 있을 때 그 정의만 제거합니다. 원본 테이블과 데이터는 그대로 둡니다. CREATE VIEW는 새 정의를 저장합니다. <는 저장한 파일을 SQLite의 입력으로 제공합니다:

sqlite3 ledger.db < totals.sql

성공하면 출력이 없는 것이 정상입니다. 실제 뷰를 읽어 보세요:

sqlite3 -header -column ledger.db "SELECT * FROM item_totals ORDER BY order_id;"

A1 = 800, A2 = 250, A3 = 500, A4 = 200센트가 각각 한 행에 나와야 합니다. A3의 수량 2도 반영됩니다. 이제 다른 호환 원장에서도 실행할 수 있는 저장된 뷰 정의라는 유용한 첫 결과물을 얻었습니다.

파일이 없으면 ls로 이 디렉터리의 파일을 나열하세요. 절대 경로 cd와 totals.sql 철자를 확인하세요. SQL 구문 오류는 실패한 문장을 알려 줍니다. 생성 블록 전체를 반복해 잘못 입력한 파일을 덮어쓰고 다시 적용하세요. here-document 입력 후 셸이 계속 기다리면 빠진 마지막 SQL 줄을 입력하세요. 복구하려고 원장을 삭제하지 마세요.

독립적인 금액 합계 결합하기

이 단계에서는 저장한 스크립트에 성공한 결제 및 환불 합계를 추가한 다음, 미결제 주문을 유지하면서 주문당 한 행인 세 종류의 사실 데이터를 결합합니다. Terminal의 /home/labex/project/sales-desk에서 시작하세요.

결제와 환불에도 같은 집계 규칙을 적용합니다. 서로 다른 ID의 동일 금액 이벤트를 포함해 실제 이벤트 각각을 셉니다. 그룹화 전에 각 사실 데이터의 SELECT 안에서 status = 'succeeded'로 필터링하세요. 대기 중인 환불로 수납 금액을 줄이면 안 됩니다. 환불 근거를 읽어 보세요:

sqlite3 -header -column ledger.db "SELECT * FROM refunds ORDER BY refund_id;"

A3에는 성공한 50센트 환불 두 건과 대기 중인 250센트 환불 한 건이 있습니다. 실제 환불 합계는 50이나 350이 아니라 100입니다.

나머지 두 뷰 정의를 덧붙이세요. >>는 상품 뷰를 덮어쓰지 않고 기존 파일 끝에 추가합니다. 실수를 복구하면서 이 추가 작업을 반복한다면, 먼저 앞 단계의 전체 블록으로 totals.sql을 다시 만든 다음 이 블록을 한 번 덧붙이세요:

cat >> totals.sql <<'SQL'
DROP VIEW IF EXISTS payment_totals;
CREATE VIEW payment_totals AS
SELECT order_id, SUM(amount_cents) AS paid_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY order_id;

DROP VIEW IF EXISTS refund_totals;
CREATE VIEW refund_totals AS
SELECT order_id, SUM(amount_cents) AS refunded_cents
FROM refunds
WHERE status = 'succeeded'
GROUP BY order_id;
SQL

sqlite3 ledger.db < totals.sql

저장한 스크립트는 다시 적용할 수 있습니다. 각 정의를 삭제하고 변경되지 않은 원본 사실 데이터로부터 다시 만듭니다. 금액 뷰를 읽어 보세요:

sqlite3 -header -column ledger.db "SELECT * FROM payment_totals ORDER BY order_id; SELECT * FROM refund_totals ORDER BY order_id;"

결제에는 A1 = 800, A3 = 600, A4 = 250이 있습니다. 환불에는 A3 = 100만 있습니다. A2에는 집계 대상 결제나 환불이 없으므로 양쪽 모두에 나타나지 않습니다. 사실 합계에 없다고 해서 주문이 대사 결과에서 빠져서는 안 됩니다.

LEFT JOIN은 오른쪽에 일치하는 행이 없어도 왼쪽의 모든 행을 유지합니다. orders에서 시작해 이미 집계된 각 뷰를 주문 ID로 조인하세요. 일치 항목이 없으면 0이 아니라 값이 없음을 뜻하는 SQL NULL이 주어집니다. COALESCE(value, 0)은 그 합계가 NULL인 경우에만 0으로 바꿉니다. 이벤트 상태 조건은 이미 금액 뷰 안에 있어야 합니다. 일치하지 않은 조인 행에 WHERE 조건을 추가하면 미결제 주문이 빠질 수 있습니다.

sqlite3 -header -column ledger.db "
SELECT o.order_id, o.customer,
       COALESCE(i.due_cents, 0) AS due_cents,
       COALESCE(p.paid_cents, 0) AS paid_cents,
       COALESCE(r.refunded_cents, 0) AS refunded_cents
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
ORDER BY o.order_id;"

각 행을 정상 업무와 대조해 읽어 보세요:

조인 전에 각 사실 뷰에는 주문당 최대 한 행만 있으므로, 한 종류의 사실 데이터가 다른 종류를 증식시킬 수 없습니다. 원래 네 테이블과 모든 레코드를 유지하세요. 추가할 데이터베이스 정의와 파일은 세 뷰와 totals.sql뿐입니다. 다음 실습은 자체 새 원장에서 시작해 이 원리를 완전한 대사 쿼리로 발전시킵니다.

요약

금액 합계에서 생기는 두 가지 함정을 확인하고, 주문당 한 행을 제공하는 재사용 가능한 사실 집계 뷰를 저장한 뒤 LEFT JOIN과 COALESCE로 결합했습니다. 금액이 같은 별개의 이벤트도 실제 이벤트로 유지되었고, 미결제 주문도 계속 표시되었습니다.