SQL文の書き方の一覧
| 構文 | すること |
|---|---|
SELECT 列 FROM テーブル |
列を選んで行を返す |
WHERE 条件 |
行を絞り込む |
GROUP BY 列 |
列の値ごとに1行にまとめる |
HAVING 条件 |
まとめたグループを絞り込む |
ORDER BY 列 DESC |
結果を並べる(DESCは降順) |
JOIN テーブル ON 条件 |
別のテーブルの行を組み合わせる |
関数() OVER (PARTITION BY ...) |
行を残したまま計算を足す |
EXPLAIN クエリ |
実行計画を見る |
SQL文は、書く順番(SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY)と、データベースが処理する順番が違います。行の絞り込み、グループ、グループの絞り込み、並べ替えの順に考えると、結果を読み違えにくくなります。
SELECT、WHERE、GROUP BY
次の架空の注文は、2人の顧客のものです。WHEREは1件ずつの注文を絞り込み、GROUP BYは顧客ごとに1行を作り、HAVINGはそのグループを絞り込みます。
WITH orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE total >= 30
GROUP BY customer_id
HAVING SUM(total) >= 50
ORDER BY revenue DESC;
結果は顧客1で、注文2件、売上100です。顧客2は行の絞り込みは通りますが、グループの絞り込みで外れます。出力の順番はORDER BYで決まります。数字は練習用のデータです。
JOINと一致しない行
注文のない顧客もレポートに残すなら、LEFT JOINを使います。COUNT(*)は顧客の一致しない行も数えてしまうので、一致した注文のIDを数えてください。
WITH customers(customer_id, name) AS (
VALUES (1, 'Mina'), (2, 'Nuri'), (3, 'Teo')
), orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total) AS revenue
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
Minaは注文2件で売上100、Nuriは1件で30、Teoは0件で売上はNULLです。レポートでこの合計を0とするなら、COALESCE(SUM(o.total), 0)を使ってください。いくつもの明細のテーブルを結合する前に、INNER JOIN:一致する行を組み合わせるで練習できます。
ウィンドウ関数
ウィンドウ関数は、各行を残したまま計算を足します。このクエリは顧客ごとに注文に番号を振り、合計が同じときはorder_idで順番を決めます。
WITH orders(order_id, customer_id, total) AS (
VALUES (101, 1, 40), (102, 1, 60), (103, 2, 30)
)
SELECT order_id, customer_id, total,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY total DESC, order_id
) AS rn
FROM orders
ORDER BY customer_id, rn;
顧客1の注文102がrn = 1、注文101が2です。顧客2の注文103は1です。rn = 1だけを残すには、このクエリを別のCTEに入れ、その外で番号の付いた行を絞り込みます。順位や累計はウィンドウ関数:ROW_NUMBER、RANK、DENSE_RANKで扱っています。
実行計画と安全な変更
クエリの前にEXPLAINを付けると、実行計画を確かめられます。見積もり、測った根拠、正しさの確認はSQLクエリの最適化:実行計画の根拠を読んでください。インデックスの動きは、データベースのエンジンによって変わります。
このチートシートはデータを読む構文が中心です。更新とトランザクションはSQLのコースで練習し、データベースの管理はPostgreSQLの管理のドキュメントを参照してください。時間を計る選考のテストの練習には、SQLの面接ハブを使えます。