この機能はCTE（Common Table Expressions）で、WITH文と呼ぶこともあります。データベースのビューと同様、この機能の主な利点は、現在のトランザクションで一時テーブルを作成できることです。CTEを使用することで、明確な考えでモジュールを構築することができ、他の人があなたのやっていることを理解しやすくなるので、たくさん使用することができます。
簡単な例を挙げましょう。
WITH users_tasks AS (
SELECT
users.email,
array_agg(tasks.name) as task_list,
projects.title
FROM
users,
tasks,
project
WHERE
users.id = tasks.user_id
projects.title = tasks.project_id
GROUP BY
users.email,
projects.title
)
このように一時テーブルusers_tasksを定義することで、後でusers_tasksに対する基本的なクエリ文を追加することができます：
SELECT *
FROM users_tasks;
面白いのは、それらを結びつけることができることです。各ユーザーに割り当てられたタスクの量がわかったとき、あるタスクでボトルネックになっているのは誰なのか、それは結果的にそのタスクの50％以上を担当しているからなのかもしれません。単純化するために、一人当たりのタスクの総数を計算し、次に各タスクに対して各人が担当している人数の合計を計算する様々な方法を使うことができます。
total_tasks_per_project AS (
SELECT
project_id,
count(*) as task_count
FROM tasks
GROUP BY project_id
),
tasks_per_project_per_user AS (
SELECT
user_id,
project_id,
count(*) as task_count
FROM tasks
GROUP BY user_id, project_id
),
今、それは結合され、その後、ユーザーの50％以上が発見されます。
overloaded_users AS (
SELECT tasks_per_project_per_user.user_id,
FROM tasks_per_project_per_user,
total_tasks_per_project
WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
)
最終的な目標は、この作業をオーバーロードしたユーザーとタスクのリストをカンマ区切りで取得することです。単純に、カンマ区切りのoverloaded_usersリストと users_tasksSQL文は常に結果を持つ。あなたはそれについて疑う余地はない。SQL文を推論しやすいものにすることが、正しさを保証する鍵である。少し長いかもしれないが、読みやすいように。おまけとして、各レベルにコメントをつけた。
--- Created by Craig Kerstiens 11/18/2013
--- Query highlights users that have over 50% of tasks on a given project
--- Gives comma separated list of their tasks and the project
--- Initial query to grab project title and tasks per user
WITH users_tasks AS (
SELECT
users.id as user_id,
users.email,
array_agg(tasks.name) as task_list,
projects.title
FROM
users,
tasks,
project
WHERE
users.id = tasks.user_id
projects.title = tasks.project_id
GROUP BY
users.email,
projects.title
),
--- Calculates the total tasks per each project
total_tasks_per_project AS (
SELECT
project_id,
count(*) as task_count
FROM tasks
GROUP BY project_id
),
--- Calculates the projects per each user
tasks_per_project_per_user AS (
SELECT
user_id,
project_id,
count(*) as task_count
FROM tasks
GROUP BY user_id, project_id
),
--- Gets user ids that have over 50% of tasks assigned
overloaded_users AS (
SELECT tasks_per_project_per_user.user_id,
FROM tasks_per_project_per_user,
total_tasks_per_project
WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
)
SELECT
email,
task_list,
title
FROM
users_tasks,
overloaded_users
WHERE
users_tasks.user_id = overloaded_users.user_id
CTEは通常、間引かれて最適化されたSQL文ほど高性能ではありません。ほとんどの違いは2分の1以下です。Postgresのオプティマイザは、将来的にもっと良くなっていくでしょう。
余談ですが、同じことを10～15行程度の短いSQLで行うことはできますが、あなたはそれをすぐに理解できないかもしれません。読みやすさの利点は、SQLが正しい処理をしているかどうかを確認する必要がある場合に現れます。SQL文には必ず結果があり、それは間違いありません。SQL文が推論しやすいことを保証することが、正しさの鍵です。
