演習のデータ
架空の修理の会社が、2つの支店の作業を記録しています。hoursがNULLなら作業時間がまだ記録されていないこと、0なら記録した時間がないことを表します。2つは別の問いに答えます。PostgreSQLの使い捨てのデータベースで一度だけ準備のSQLを実行するか、専用のデータで採点するブラウザーのコースを開いてください。
CREATE TABLE repair_jobs (
job_id integer PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL,
hours numeric(5,2) CHECK (hours >= 0),
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO repair_jobs VALUES
(101, 'North', 'open', 2.5, '{"priority":"high"}'),
(102, 'North', 'done', 1.0, '{"priority":"low"}'),
(103, 'South', 'open', NULL, '{}'),
(104, 'South', 'open', 0, '{"priority":"high"}');
問題
- openの作業のIDを、IDの順に返してください。答えは101、103、104です。
ORDER BYのないクエリは、この順番を約束しません。 - 支店ごとに、すべての作業の件数、作業時間が記録された件数、作業時間の合計を出してください。Northは作業2件、記録2件、3.5時間です。Southは作業2件、記録1件、合計0時間です。
detailsのpriorityがhighのIDを返してください。答えは101と104です。キーがないものを、highとして扱ってはいけません。- トランザクションの中で作業101を3時間に変え、ロールバックしてください。もう一度行を読むと、2.5時間のままのはずです。
答えを見比べる前に、別のデータでも試してください。作業102をopenに、作業103を1.25時間に変え、作業104からpriorityのキーを消します。どの結果が変わるはずかを先に予想してください。こうすると、IDの一覧を覚えただけの答えが通らなくなります。
解答例
SELECT job_id FROM repair_jobs
WHERE status = 'open' ORDER BY job_id;
SELECT branch, COUNT(*) AS jobs,
COUNT(hours) AS recorded_hours,
SUM(hours) AS total_hours
FROM repair_jobs GROUP BY branch ORDER BY branch;
SELECT job_id FROM repair_jobs
WHERE details ->> 'priority' = 'high' ORDER BY job_id;
BEGIN;
UPDATE repair_jobs SET hours = 3 WHERE job_id = 101;
ROLLBACK;
SELECT hours FROM repair_jobs WHERE job_id = 101;
PostgreSQLのNULLの扱いがポイントです。COUNT(*)はすべての行を数え、COUNT(hours)はNULLを無視します。SUMもNULLの入力を無視し、NULLでない入力がなければNULLを返します。SouthにNULLの行しかなければ合計はNULLになるので、COALESCEは業務のルールが0を求めるときだけ使ってください。
JSONBの文字列の演算子->>は文字列を返します。オブジェクトのキーがなければここではSQLのNULLが返るので、等しいかどうかの条件で作業103は除かれます。トランザクションの例はデータを変えてからROLLBACKで戻します。ただのSELECTでは、この動きを示せません。
実行計画と次の練習
絞り込みのクエリにEXPLAIN (ANALYZE, FORMAT JSON)を実行してください。4行しかなければ、インデックスがあってもシーケンシャルスキャンが妥当なことがあります。期待したノード名を出すためだけに、インデックスを使わせないでください。
続けてNULLと空文字、JSONBの値の取り出しと存在しないキー、トランザクションとセーブポイントに進んでください。PostgreSQLのコマンド一覧は構文の参照です。コースでは、変えたデータで答えを採点します。