この講義では、まさにクエリの魔法をやってみるよ！複数のCTEを使った例をいくつか作って、どうやってそれらを組み合わせて複雑で多段階なクエリを作れるかを見せるね。こういう例は、特に難しい分析タスクでリアルに役立つよ。
例1: 学生の成績分析
例えば、大学のデータベースがあって、3つのテーブルがあるとしよう：
studentsテーブル：
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name TEXT NOT NULL
);
gradesテーブル：
CREATE TABLE grades (
grade_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_id INT NOT NULL,
grade NUMERIC(3, 1) NOT NULL
);
coursesテーブル：
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name TEXT NOT NULL
);
課題: 平均点が85より高い学生のリストを、彼らの平均点と受講しているコース名と一緒に取得しよう。
クエリ:
WITH high_achievers AS (
-- 平均点が高い学生を探す
SELECT
student_id,
AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 85
),
student_courses AS (
-- 各学生が受講しているコースを探す
SELECT
s.student_id,
c.course_name
FROM grades g
JOIN courses c ON g.course_id = c.course_id
JOIN students s ON g.student_id = s.student_id
)
-- 結果を結合する
SELECT
s.student_name,
ha.avg_grade,
sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;
解説:
- 最初のCTE（
high_achievers）では、各学生の平均点を計算して、85点より高い人だけを選ぶよ。 - 2つ目のCTE（
student_courses）では、学生とそのコースを紐付けてる。 - メインクエリで両方のCTEのデータを結合して、学生名・平均点・受講コースを一覧にしてるよ。
例2: ネットショップの売上レポート
ネットショップを運営しているとして、次のテーブルがあるとしよう：
orders（注文）テーブル：
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
customers（顧客）テーブル：
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL
);
order_items（注文内の商品）テーブル：
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
課題: 各顧客について次の情報を表示するレポートを作ろう：
- 注文の総数
- 直近1ヶ月の全注文の合計金額
- その人が買った全ユニーク商品のリスト
クエリ:
WITH recent_orders AS (
-- 直近1ヶ月の注文を選ぶ
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
-- 各顧客の注文数と合計金額を計算
SELECT
ro.customer_id,
COUNT(ro.order_id) AS total_orders,
SUM(ro.total_amount) AS total_spent
FROM recent_orders ro
GROUP BY ro.customer_id
),
customer_products AS (
-- 各顧客が買ったユニーク商品を選ぶ
SELECT DISTINCT
ro.customer_id,
oi.product_id
FROM recent_orders ro
JOIN order_items oi ON ro.order_id = oi.order_id
)
-- 結果を結合する
SELECT
c.customer_name,
cs.total_orders,
cs.total_spent,
ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;
解説:
- 最初のCTE（
recent_orders）で直近1ヶ月の注文を選ぶ。 - 2つ目のCTE（
customer_summary）で各顧客の注文数と合計金額を計算。 - 3つ目のCTE（
customer_products）で各顧客が買ったユニーク商品を取得。 - 最後のクエリで全部まとめて、
ARRAY_AGG()でユニーク商品のリストを作ってるよ。
例3: 社員の階層分析
社員のテーブルがあるよ：
employeesテーブル：
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
manager_id INT NULL
);
課題: 社長から始まる社員の階層を作って、各社員の階層レベルを表示しよう。
クエリ:
WITH RECURSIVE employee_hierarchy AS (
-- マネージャーがいない社員（社長）から始める
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 現在のレベルの部下を追加
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- 階層を表示
SELECT
employee_id,
employee_name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, employee_id;
- 再帰クエリで、まず社長（マネージャーがいない社員）から始める。
- 各ステップで、現在のレベルの部下を追加して、
levelを1増やす。 - メインクエリで全階層をレベルと社員IDでソートして表示するよ。
便利なコツとよくあるミス
- CTEの使いすぎ: サブクエリで済むならCTEを使いすぎないこと。CTEはデータを一時的に保存するから、パフォーマンスが落ちることもあるよ。
- CTEの名前: クエリが読みやすいように、CTEには分かりやすくて短い名前をつけよう。
- 実行順序: CTEは宣言した順番で実行されるって覚えておこう。
- データのグループ化:
GROUP BYは本当に必要なときだけ使って、無駄な処理を避けよう。
これらの例から分かるように、CTEを使うと複雑な課題を段階的に分けて、クエリの読みやすさやメンテナンス性をアップできるよ。これでPostgreSQLで難しい分析タスクもバッチリ解決できるね！
GO TO FULL VERSION