-- AI分析の集計を確かめる：編集部作成の架空教材 v1

-- 編集部が作成した架空データをSQLiteで実行して確認した教材です。特定製品やAIの出力・性能を測定した結果ではありません。金額の単位は円、NULLは金額未取得と定義します。

-- 問い：全5注文について、分かっている金額の合計と、未取得の件数を出してください。

-- 新しい空のSQLiteデータベースで実行してください。既存データのある環境では実行しないでください。

CREATE TABLE orders (
  order_id TEXT PRIMARY KEY,
  amount INTEGER
);
CREATE TABLE order_items (
  item_id TEXT PRIMARY KEY,
  order_id TEXT NOT NULL REFERENCES orders(order_id)
);
INSERT INTO orders VALUES
  ('A', 100), ('B', 100), ('C', 50), ('D', 0), ('E', NULL);
INSERT INTO order_items VALUES
  ('A-1', 'A'), ('A-2', 'A'), ('B-1', 'B'), ('D-1', 'D');

-- 結合後の金額を足す：350円。LEFT JOINで明細をつなぐと6行になります。Aの100円が2行に現れるため、注文の部分合計より100円多くなります。
SELECT SUM(o.amount) AS amount
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id;

-- 同じ金額を一つにする：150円。SUM(DISTINCT 金額)は値の重複を除きます。AとBは別の注文でも同じ100円なので一つになり、100円・50円・0円の計150円になります。
SELECT SUM(DISTINCT o.amount) AS amount
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id;

-- 明細がある行だけ足す：300円。INNER JOINではC・Eが対象から落ちます。一方でAの重複は残るため、Aの100円を2回、Bの100円、Dの0円を足して300円になります。
SELECT SUM(o.amount) AS amount
FROM orders o
INNER JOIN order_items i ON i.order_id = o.order_id;

-- 注文の表から一度ずつ足す：250円。この問いには明細の条件がないため、注文の表から集計します。金額のある4件の合計250円と未取得1件を分けます。全体の金額が確定したという意味ではありません。
SELECT SUM(amount) AS known_amount,
       COUNT(*) AS order_count,
       COUNT(amount) AS known_count,
       COUNT(*) - COUNT(amount) AS missing_count
FROM orders;

-- 答えは、金額がある4注文の部分合計250円と、金額未取得1件です。全5注文の総額は未確定です。明細のない注文も対象に含めます。

-- 金額がすべて未取得の場合、SUMはNULLです。ゼロ円に読み替えません。

SELECT SUM(amount) AS amount FROM orders WHERE order_id = 'E';

-- 出典：ガイド掲載のSQLite公式資料とLooker公式資料。各製品の検証結果ではありません。