매출 대사 자료 인계하기

소개

PAPER & PIER에는 주문당 CSV 한 행과 판매대별 요약으로 구성된 마감 인계 자료가 필요합니다. 일부 개별 주문이 미납 또는 초과 결제 상태여도 두 판매대 모두 전체 차이는 0일 수 있습니다. 인계 자료는 총합 하나로 성공을 선언하지 않고 이러한 예외를 유지해야 합니다.

재사용 가능한 SQL 쿼리를 저장하고 제공된 내보내기 도구로 실제 결과에서 실제 CSV 파일을 만듭니다. 먼저 「주문별 합계를 정확하게 유지하기」를 완료하거나 SUM/GROUP BY, LEFT JOIN, COALESCE, 조인 중복 문제를 알고 있어야 합니다. 공통 테이블 표현식과 상태 분류는 여기서 설명합니다. 전체 과정에서 Terminal을 사용하세요. 이 단원에는 독립적인 주문 여섯 건의 원장이 있습니다.

원본 테이블 네 개와 제공된 도구를 모두 변경하지 마세요. query.sql과 reports/ 아래 파일만 변경하고 데이터베이스 객체는 추가하지 마세요. 모든 금액은 정수 센트 단위입니다. 이는 세금이나 결제 서비스 제공업체의 정산을 포함하지 않는 단순화된 수납 대사 작업입니다. 내보내기 도구가 CSV 인코딩과 요약 계산을 처리하므로 Python을 작성할 필요는 없습니다.

재사용 가능한 주문별 쿼리 저장하기

이 단계에서는 자체 완결적인 SQL 문 하나로 각 주문의 순수납액, 차이, 상태를 계산합니다. Terminal에서 시작하세요:

cd /home/labex/project/sales-desk

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

이 독립 원장에는 A1부터 A6까지 있으며, Alex Kim으로 표시된 주문 두 건과 쉼표 또는 따옴표 문자가 있는 표시 이름도 포함됩니다. ID로 주문을 구분하며, 표시 이름은 저장된 그대로 정확히 유지하세요. 잔액을 계산하기 전에 모든 사실 목록을 검토하세요:

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

앞서와 마찬가지로 succeeded 결제와 환불만 포함합니다. 금액이 같은 성공 이벤트 두 건도 모두 셉니다. 집계 대상 사실 데이터가 없으면 0으로 계산합니다. 계산식은 다음과 같습니다:

공통 테이블 표현식(CTE)은 한 문장 안에서만 사용할 수 있는 이름 붙은 중간 SELECT입니다. WITH name AS (...)로 시작하며, 여러 CTE는 쉼표로 구분합니다. 데이터베이스 객체를 만들거나 다른 실습의 파일에 의존하지 않으면서 앞서 뷰에서 사용한 종류별 독립 집계 원리를 적용할 수 있습니다.

먼저 CTE 세 개가 각 사실 데이터를 독립적으로 집계합니다. 이어서 totals는 주문에서 시작해 누락된 합계를 0으로 채웁니다. balances는 이름 붙은 합계에서 순수납액과 차이를 계산합니다. 마지막 SELECT는 CASE WHEN ... THEN ... ELSE ... END로 차이의 부호에 따라 상태를 선택합니다. 이 계층 구조를 사용하면 같은 SELECT 목록의 다른 표현식 별칭에 의존하지 않고 다음 SELECT에서 별칭을 사용할 수 있습니다. 중간 잔액 계층의 SELECT *는 합계를 이어 전달하며, 최종 SELECT는 필요한 순서대로 정확히 아홉 열을 명시합니다.

문장 전체를 저장하세요. cat >는 파일을 덮어씁니다. 따옴표로 지정한 here-document는 SQL을 그대로 보존합니다. 마지막 SQL 줄도 포함하세요:

cat > query.sql <<'SQL'
WITH
item_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS due_cents
  FROM items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'succeeded' GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount_cents) AS refunded_cents
  FROM refunds WHERE status = 'succeeded' GROUP BY order_id
),
totals AS (
  SELECT o.order_id, o.customer, o.stall,
         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
),
balances AS (
  SELECT *, paid_cents - refunded_cents AS net_cents,
         paid_cents - refunded_cents - due_cents AS difference_cents
  FROM totals
)
SELECT order_id, customer, stall, due_cents, paid_cents,
       refunded_cents, net_cents, difference_cents,
       CASE WHEN difference_cents = 0 THEN 'settled'
            WHEN difference_cents < 0 THEN 'underpaid'
            ELSE 'overpaid' END AS status
FROM balances
ORDER BY order_id;
SQL

query.sql에 SQLite 점 명령이나 셸 명령을 넣지 마세요. 내보내기 도구는 일반 SELECT/WITH 문 하나를 실행합니다. 쿼리는 주문 ID와 금액이 다르더라도 같은 스키마를 가진 다른 원장의 원본 테이블 네 개에서 동작해야 합니다. 결과 행을 고정해 작성하거나 데이터베이스 뷰에 의존하면 재사용 가능한 쿼리가 되지 않습니다.

<는 이 파일을 SQLite CLI로 보내 읽기 쉬운 첫 실행 결과를 만듭니다:

sqlite3 -header -column ledger.db < query.sql

총합만 보지 말고 실제 행을 읽어 보세요. 예상 잔액은 다음과 같습니다:

A2는 결제가 없어도 계속 표시됩니다. A3에서는 성공한 50센트 환불 두 건이 모두 유지됩니다. A6의 실패한 환불은 순수납액을 줄이지 않습니다. A1과 A2는 고객 표시 이름이 같아도 별개로 유지됩니다. 음수 차이를 포함해 다섯 센트 열 모두 실제 정수입니다. 서식이 적용된 통화 문자열로 바꾸지 마세요.

구문 오류가 나면 보고된 위치를 읽고 query.sql을 수정한 전체 블록으로 덮어쓴 다음 다시 실행하세요. 경로가 틀렸다면 위의 절대 경로 cd로 돌아가 ls로 query.sql을 찾으세요. 원장을 비우거나, 잔액을 억지로 맞추려고 금액을 변경하거나, 저장된 결과 테이블을 추가하지 마세요.

실제 CSV 파일 인계하고 읽기

이 단계에서는 저장한 쿼리를 내보내고, CSV 따옴표 처리가 필요한 표시 이름을 포함한 실제 인계 파일을 읽습니다. Terminal의 /home/labex/project/sales-desk에서 시작하세요.

./export는 현재 디렉터리에 제공된 실행 파일입니다. query.sql을 읽고 읽기 전용 연결로 ledger.db에서 그 문장을 변경 없이 실행하며, 실제 반환 행을 reports/orders.csv에 씁니다. reports/summary.csv는 그 반환 행만으로 도출합니다. 잘못된 쿼리를 고치거나 누락된 주문을 추가하지 않습니다. 준비된 reports 디렉터리가 두 파일의 저장 위치입니다:

./export

메시지는 쿼리 행 여섯 개와 두 출력 파일 이름을 알려 줍니다. 아래의 읽기 도구는 판매대 요약 두 개를 보여 줍니다. 내보내기 성공은 파일이 작성되었다는 뜻이며 계산이 정확하다는 뜻은 아닙니다. 쿼리가 없거나 잘못되면 내보내기 도구가 실패를 보고합니다. 이전에 성공한 내보내기의 오래된 파일이 남아 있을 수 있습니다. 저장된 쿼리를 수정하고 ./export를 다시 실행한 뒤에야 파일을 최신 결과로 취급하세요.

./review는 제공된 CSV 읽기 도구입니다. 데이터베이스를 참조하지 않고 저장된 파일을 읽으며, 해석한 각 행을 이름표가 붙은 레코드로 표시합니다. 출력이 없거나 변경되었다면 그 상태가 그대로 유지됩니다:

./review

주문 레코드 여섯 개는 앞 단계의 표와 일치하며 원래 고객 및 판매대 표시 이름도 정확해야 합니다. 요약 열은 다음과 같습니다:

North, Pier에는 주문 세 건이 있습니다. 청구액 1650, 순수납액 1650, 차이 0이며 각 상태가 한 건씩입니다. South에도 세 건이 있습니다. 청구액 1100, 순수납액 1100, 차이 0이며 각 상태가 한 건씩입니다. 전체 차이가 0이어도 두 판매대를 합쳐 결제가 맞지 않는 주문은 네 건입니다. 초과 결제와 미납은 총합에서 상쇄되지만, 다른 사람의 주문을 결제 완료로 만들지는 않습니다.

CSV는 쉼표로 구분된 값을 뜻하지만 표시 이름 안의 쉼표는 하나의 필드에 속합니다. 표준 CSV는 이런 필드를 큰따옴표로 감싸고, 필드 안의 따옴표는 따옴표 문자 두 개로 나타냅니다. cat은 원시 파일 바이트를 텍스트로 표시합니다. 이 인코딩을 ./review에서 올바르게 해석한 표시 이름과 비교하세요:

cat reports/orders.csv

cat reports/summary.csv

주문 파일에서 고객 표시 이름 Paper, "Pine"은 "Paper, ""Pine"""로 인코딩되고, North, Pier는 "North, Pier"로 인코딩됩니다. 추가된 따옴표 문자는 CSV 문법이며 표시 이름이 바뀐 것이 아닙니다. 각 줄을 모든 쉼표에서 나누면 이 레코드가 손상됩니다. 제공된 쓰기 및 읽기 도구가 형식을 처리합니다.

CSV가 없으면 query.sql을 수정한 다음 ./export를 다시 실행하고 두 파일을 다시 읽으세요. 누락된 주문을 숨기려고 요약을 직접 편집하거나, 내보내기/읽기 도구를 교체하거나, 원본 레코드를 변경하지 마세요. 인계 자료는 재사용 가능한 query.sql과 실제 CSV 파일 두 개로 구성됩니다. 파일에는 모든 원본 주문에 대해 한 행씩, 정확한 아홉 주문 열, 위에 표시한 여덟 요약 열이 있어야 합니다. 행 순서 자체가 업무 결과는 아닙니다. 식별 정보, 값, 표시 이름, 그리고 이들의 관계가 중요합니다.

요약

재사용 가능한 CTE 쿼리를 만들고, 순수납액에서 상품 금액을 뺀 차이로 각 주문을 분류하며, 실제 CSV 파일 두 개를 인계했습니다. 올바르게 따옴표 처리된 표시 이름을 읽고, 판매대 합계의 균형과 개별 주문의 결제 완료를 구분했습니다.