← レッスン一覧に戻る

解答 02.3 — 集計とJOIN（TopCustomersBySpend）

このノートブックは 02.3-sql-aggregation-joins.ipynb の練習問題の解答です。 まず問題の前提コード（スキーマ・シード）を再掲し、次に解答、最後にチェックを実行します。

先に自分の力で解いてから、答え合わせに使ってください。

前提コード（問題ノートブックと同じ定義・同じシード）

import (
	"database/sql"
	"fmt"
	"reflect"

	_ "modernc.org/sqlite"
)

func openCourseDB(path string) *sql.DB {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		panic(err)
	}
	db.SetMaxOpenConns(1)
	return db
}

type CustomerSpend struct {
	CustomerID int
	Name       string
	Total      int
	OrderCount int
}

func mustEqual(got, want any, name string) {
	if reflect.DeepEqual(got, want) {
		fmt.Printf("✅ Passed: %s\n", name)
		return
	}
	panic(fmt.Sprintf("❌ %s\n  got  = %v (%T)\n  want = %v (%T)", name, got, got, want, want))
}

%%
db := openCourseDB("_02_3_sql_aggregation_joins_solutions.db")
defer db.Close()

mustExec := func(query string, args ...any) {
	if _, err := db.Exec(query, args...); err != nil {
		panic(err)
	}
}

mustExec(`DROP TABLE IF EXISTS orders`)
mustExec(`DROP TABLE IF EXISTS customers`)
mustExec(`DROP TABLE IF EXISTS products`)

mustExec(`CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)`)
mustExec(`CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)`)
mustExec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, product_id INTEGER, quantity INTEGER)`)

mustExec(`INSERT INTO customers (id, name) VALUES (1,'tanaka'), (2,'suzuki'), (3,'sato'), (4,'sato')`)
mustExec(`INSERT INTO products (id, name, price) VALUES (1,'keyboard',3000), (2,'mouse',1500), (3,'monitor',20000), (4,'mouse',1800)`)
mustExec(`INSERT INTO orders (id, customer_id, product_id, quantity) VALUES
	(1,1,1,1),
	(2,1,2,2),
	(3,2,3,1),
	(4,3,2,1),
	(5,2,1,3),
	(6,99,3,1)`)

fmt.Println("✅ シード完了")

✅ シード完了

解答: TopCustomersBySpend

orders × products × customers をid で正しく結合し、GROUP BY + HAVING で 売上合計が minTotal 以上の顧客だけを、売上の降順で返します。

func TopCustomersBySpend(db *sql.DB, minTotal int) ([]CustomerSpend, error) {
	rows, err := db.Query(`
		SELECT c.id, c.name, SUM(p.price * o.quantity) AS total, COUNT(*) AS cnt
		FROM orders o
		JOIN products p ON p.id = o.product_id
		JOIN customers c ON c.id = o.customer_id
		GROUP BY c.id, c.name
		HAVING SUM(p.price * o.quantity) >= ?
		ORDER BY total DESC
	`, minTotal)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []CustomerSpend
	for rows.Next() {
		var s CustomerSpend
		if err := rows.Scan(&s.CustomerID, &s.Name, &s.Total, &s.OrderCount); err != nil {
			return nil, err
		}
		result = append(result, s)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return result, nil
}

チェック（問題ノートブックと同じ期待値）

%%
db := openCourseDB("_02_3_sql_aggregation_joins_solutions.db")
defer db.Close()

result, err := TopCustomersBySpend(db, 5000)
if err != nil {
	panic(err)
}

mustEqual(len(result), 2, "5000円以上の顧客は2人")
mustEqual(result[0], CustomerSpend{CustomerID: 2, Name: "suzuki", Total: 29000, OrderCount: 2}, "1位はsuzuki（29000円）")
mustEqual(result[1], CustomerSpend{CustomerID: 1, Name: "tanaka", Total: 6000, OrderCount: 2}, "2位はtanaka（6000円）")

fmt.Println("🎉 すべてのチェックが通りました")

✅ Passed: 5000円以上の顧客は2人
✅ Passed: 1位はsuzuki（29000円）
✅ Passed: 2位はtanaka（6000円）
🎉 すべてのチェックが通りました

解説

1. id同士のJOIN: p.id = o.product_id / c.id = o.customer_id のどちらもユニークキー同士の結合です。 名前のような重複しうる列を使わないので、行が膨らむ心配がありません
2. SUM(p.price * o.quantity): 1商品あたりの金額（単価×数量）を注文単位で計算してから、 顧客単位で合計しています
3. HAVING: SUM(...) という集計結果に条件を掛けるので WHERE ではなく HAVING を使います
4. ORDER BY total DESC: SQL 側で並べ替えてから返しているので、Go 側で追加のソートは不要です

存在しない顧客id=99を参照する注文（id=6）は、customers と INNER JOIN した時点で 自動的に除外されます。これは 02.3 の「罠2」で見た「INNER JOINは参照先が無いと消える」性質を そのまま活かした形です（このレッスンでは「消えてほしい」ケースなので好都合です）。