跳到正文
DEV Community· saravana kumar·· 9 天前AI 評分49

SQL Join Fanout:為何 SUM(DISTINCT amount) 並非解決方法

SQL Join Fanout: Why SUM(DISTINCT amount) Is Not the Fix

AI 導讀

將兩張明細表按 order_id 連接,兩筆各為 50 的付款會展開成四行,令總額由 100 膨脹至 200;SUM(DISTINCT amount) 則只得 50,因為它去除重複數值,而非重複付款。正確做法是在預期的資料粒度先分別彙總,再連接結果,並核實來源事件是否重複及貨幣是否一致。

正文

saravana kumar

A report can have valid SQL and still double revenue. A common cause is joining two detail tables before deciding the output grain.

A tiny counterexample

Imagine order 1 has two items and two payments. Each payment is 50. Joining order items to payments using only order_id produces four rows. The payment sum becomes 200, although the payments total 100.

SUM(DISTINCT amount) looks tempting. It returns 50 because both legitimate payments have the same amount. It deduplicates values, not payment identities.

Aggregate each side to the intended grain

This PostgreSQL example uses CTEs and inline data. It is an illustrative query with expected results reasoned by inspection; it has not been run against a database for this post.

WITH items(order_id, item_id) AS (
  VALUES (1, 11), (1, 12)
), payments(order_id, payment_id, amount) AS (
  VALUES (1, 101, 50), (1, 102, 50)
), item_totals AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM items
  GROUP BY order_id
), payment_totals AS (
  SELECT order_id, SUM(amount) AS paid_amount
  FROM payments
  GROUP BY order_id
)
SELECT i.order_id, i.item_count, p.paid_amount
FROM item_totals AS i
JOIN payment_totals AS p USING (order_id);

Expected row: order_id = 1, item_count = 2, paid_amount = 100. Each CTE contributes at most one row per order, so this join preserves that grain.

Three decisions the query cannot make for you

  1. Which orders belong in the report? An inner join omits orders without either items or payments. To include unpaid orders, begin with an authoritative orders table and left-join the aggregates.
  2. What does missing mean? A missing payment aggregate might mean no payments, a delayed source or a broken join key. Use COALESCE(..., 0) only when zero is the intended business interpretation.
  3. Are source events unique? Pre-aggregation does not remove delivery retries. Validate payment IDs and resolve duplicated source events before summing money.

If currencies differ, group by currency too or apply a documented conversion policy. A sum of 50 USD and 50 INR is not a meaningful amount of 100.

A useful debugging habit

Before and after every join, inspect row counts per business key. Then reconcile the amount with its source at the same cutoff and currency. Count inflation is a clue, but a matching count alone does not establish correctness.

The practical rule is simple: write down what one output row represents, and make every input meet that grain before combining independent one-to-many relationships.

Reference: PostgreSQL SELECT and grouping.


Saravana Kumar | TechX

Personal website · Instagram

AI disclosure: This original tutorial was generated by an AI assistant without human editing. Example results are expectations, not a claim of database execution.

來源:DEV Community · dev.to