-- 文具のうち、150円以上のもの
SELECT name, price
FROM products
WHERE category = '文具'
AND price >= 150;
実行結果
name | price
-------+------
ノート | 180
AND は「両方とも」、OR は「どちらか」です。混ぜて使うときは必ずかっこを付けてください。ANDのほうが先に評価されるため、かっこがないと意図しない結果になります。
where_or.sql
-- ✕ 意図とちがう：「文具かつ200円未満」または「飲料すべて」になる
SELECT name FROM products
WHERE category = '文具' AND price < 200 OR category = '飲料';
-- ○ 正しい：「文具か飲料」のうち200円未満
SELECT name, category, price FROM products
WHERE (category = '文具' OR category = '飲料')
AND price < 200;
-- 上のORはINで書くと短くなる
SELECT name, category
FROM products
WHERE category IN ('文具', '飲料');
-- 100円以上300円以下（両端を含む）
SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 300;
※ 除外したいときは NOT IN / NOT BETWEEN です。ただし NOT IN はリストにNULLが混ざると1行も返らなくなるという有名な罠があります（後述）。
あいまい検索 ── LIKE
like.sql
SELECT name FROM products WHERE name LIKE 'ノート'; -- 完全一致と同じ
SELECT name FROM products WHERE name LIKE 'コ%'; -- 「コ」で始まる
SELECT name FROM products WHERE name LIKE '%グ%'; -- 「グ」を含む
SELECT name FROM customers WHERE name LIKE '佐藤%'; -- 姓が佐藤
-- 試しに、都道府県が未登録の顧客を1件足してみる
INSERT INTO customers VALUES (7, '山本 ゆい', NULL, '2025-02-14');
SELECT name, prefecture FROM customers WHERE prefecture = NULL; -- ✕ 0行
SELECT name, prefecture FROM customers WHERE prefecture IS NULL; -- ○ 1行
-- 確認できたら消しておく（このあとの例は顧客6人を前提にしています）
DELETE FROM customers WHERE customer_id = 7;
実行結果（下のSQL）
name | prefecture
-----------+-----------
山本 ゆい | (NULL)
もっと大事な落とし穴WHERE prefecture <> '東京都' と書いても、都道府県がNULLの人は返ってきません。「東京都ではない」かどうかが不明だからです。実務では「東京都以外を全部出したつもりが、住所未登録の顧客が丸ごと抜けていた」という集計ミスが起こります。NULLがありうる列では WHERE prefecture <> '東京都' OR prefecture IS NULL のように明示してください。
-- 高い順に3件だけ
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;
-- 4位から3件（ページ送りに使う）
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3;
NULLはどこに来るか ── 製品によって違います。SQLite・MySQLはNULLが先頭、PostgreSQL・Oracleは末尾に来ます。明示したい場合は ORDER BY prefecture NULLS LAST と書きます（PostgreSQL・Oracle・SQLite 3.30以降）。
日本語の並び順 ── ORDER BY name で日本語を並べても、多くの場合五十音順にはなりません。内部の文字コード順になるためです（漢字は特にばらばらになります）。実務では、ふりがなを入れる name_kana 列を別に用意して、そちらで並べ替えるのが定番の解決策です。
SELECTに書ける列のルール GROUP BYを使ったSELECTには、①GROUP BYに書いた列 ②集約関数 しか書けません。上の例で name を足すと「どの商品名を出せばいいのか」が決まらないため、PostgreSQLやSQL Serverはエラーにします。SQLiteとMySQLは黙って適当な1行を返してしまうので、かえって危険です。迷ったら「その列は、束の中で1つに決まるか？」と自問してください。
もっと実務らしい例
group_order.sql
-- 注文ごとの合計金額（明細を束ねる）
SELECT
order_id AS 注文番号,
COUNT(*) AS 明細数,
SUM(quantity) AS 合計点数,
SUM(quantity * unit_price) AS 合計金額
FROM order_items
GROUP BY order_id
ORDER BY 合計金額 DESC;
-- 顧客ごとの注文回数と、最初・最後の注文日
SELECT
customer_id AS 顧客番号,
COUNT(*) AS 注文回数,
MIN(ordered_at) AS 初回注文日,
MAX(ordered_at) AS 最終注文日
FROM orders
GROUP BY customer_id
ORDER BY 注文回数 DESC, 顧客番号;
※ 顧客5（伊藤さくら）は1度も注文していないため、この結果にまったく現れません。ordersにデータが無いからです。「注文が0回の顧客も0と表示したい」という要求はよくありますが、それにはSTEP 5の LEFT JOIN が必要です。
グループを絞り込む ── HAVING
WHERE は集計する前の行を、HAVING は集計した後のグループを絞ります。「注文が2回以上の顧客」は集計後の条件なのでHAVINGです。
having.sql
SELECT
customer_id AS 顧客番号,
COUNT(*) AS 注文回数
FROM orders
WHERE status <> 'キャンセル' -- 集計する前に、キャンセルを除く
GROUP BY customer_id
HAVING COUNT(*) >= 2 -- 集計した後に、2回以上のグループだけ残す
ORDER BY 注文回数 DESC, 顧客番号;
SELECT
category AS 分類,
COUNT(*) AS 商品数,
ROUND(AVG(price), 1) AS 平均価格,
ROUND(AVG(price) * 1.1) AS 税込平均,
COUNT(DISTINCT price) AS 価格の種類数
FROM products
GROUP BY category
ORDER BY 商品数 DESC, 分類;
SELECT
product_id AS 商品番号,
SUM(quantity) AS 合計数量,
SUM(quantity * unit_price) AS 売上金額
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) >= 5
ORDER BY 売上金額 DESC;
SELECT
o.order_id AS 注文番号,
c.name AS 顧客名,
o.ordered_at AS 注文日,
o.status AS 状態
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.customer_id
ORDER BY o.order_id;
読み方は3つに分けると簡単です。①FROM で軸になる表を決める ②JOIN でつなぐ表を足す ③ON で「どの列とどの列が同じなら同じものとみなすか」を書く。この ON が結合の心臓部です。
AS o / AS c はテーブルの別名です。以降 o.order_id のように短く書けます。両方の表に name のような同名の列があるとき、どちらの表のものか区別するためにも必要です。結合を書くときは必ず別名を付け、すべての列に「表名.」を付けるのが実務の作法です。
結合の種類
書き方
意味
使う場面
INNER JOIN（＝JOIN）
両方にある行だけ
基本。注文と顧客のように必ず相手がいる場合
LEFT JOIN
左の表は全部残し、右が無ければNULL
「0件の人も表示したい」とき
RIGHT JOIN
LEFTの逆
ほぼ使わない（表の順を入れ替えてLEFTで書く）
FULL OUTER JOIN
どちらか一方にある行も全部
2つのデータの突き合わせ。SQLite・MySQLでは使えない版もある
CROSS JOIN
全組み合わせ
カレンダー表と商品の総当たりを作るときなど
自己結合
同じ表を2回使う
社員と上司、親子カテゴリなど
3つ以上の表をつなぐ
JOINは何回でも並べられます。明細に商品名を付けてみましょう。
join_three.sql
SELECT
oi.order_id AS 注文番号,
p.name AS 商品名,
oi.quantity AS 数量,
oi.unit_price AS 単価,
oi.quantity * oi.unit_price AS 金額
FROM order_items AS oi
JOIN products AS p ON oi.product_id = p.product_id
WHERE oi.order_id = 110;
SELECT
p.category AS 分類,
p.name AS 商品名,
SUM(oi.quantity) AS 数量,
SUM(oi.quantity * oi.unit_price) AS 売上
FROM order_items AS oi
JOIN orders AS o ON oi.order_id = o.order_id
JOIN products AS p ON oi.product_id = p.product_id
WHERE o.status = '完了'
GROUP BY p.product_id, p.category, p.name
ORDER BY 売上 DESC;
SELECT
c.customer_id AS 顧客番号,
c.name AS 顧客名,
COUNT(o.order_id) AS 注文回数
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
ここで COUNT(*) と書くと顧客5が「1」になります LEFT JOINで相手が見つからなかった行も、行としては1行存在するからです（中身はNULL）。LEFT JOINして数えるときは、必ず右側の表の列を指定して COUNT(o.order_id) と書く。これはSQLの面接でも定番の確認事項です。
LEFT JOINで条件を書く場所
もうひとつ、間違いが多い点です。LEFT JOINの右側の表に対する条件をWHEREに書くと、LEFT JOINがINNER JOINに戻ってしまいます。条件は ON に書きます。
left_join_where.sql
-- ✕ 顧客5が消える：WHEREはNULLの行も落としてしまう
SELECT c.name, COUNT(o.order_id)
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.status = '完了'
GROUP BY c.customer_id, c.name;
-- ○ 顧客5が0で残る：絞り込みをONの中に入れる
SELECT
c.name AS 顧客名,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS 売上
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id AND o.status = '完了'
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
ORDER BY 売上 DESC;
-- 注文110は1行のはずが、明細3行と結合して3行になる
SELECT o.order_id, o.ordered_at, oi.product_id, oi.quantity
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.order_id = 110;
SELECT
c.prefecture AS 都道府県,
COUNT(DISTINCT o.order_id) AS 注文件数,
SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY c.prefecture
ORDER BY 売上 DESC;
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products) -- 内側が先に計算される
ORDER BY price DESC;
実行結果
name | price
-------------+------
トートバッグ | 1500
マグカップ | 980
内側の SELECT AVG(price) FROM products が先に計算されて 452.5 になり、外側はそれを普通の数値として使います。このように1行1列だけを返すサブクエリをスカラサブクエリと呼び、値が書ける場所ならどこにでも置けます。
IN ── リストとして使う
サブクエリが複数行を返す場合は、IN と組み合わせます。
subquery_in.sql
-- 「完了した注文」に一度でも含まれた商品
SELECT product_id, name, price
FROM products
WHERE product_id IN (
SELECT oi.product_id
FROM order_items AS oi
JOIN orders AS o ON oi.order_id = o.order_id
WHERE o.status = '完了'
)
ORDER BY product_id;
SELECT c.customer_id, c.name, c.prefecture
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 -- 中身は何でもよい。「行があるか」だけを見る
FROM orders AS o
WHERE o.customer_id = c.customer_id -- 外側の値を参照する（相関サブクエリ）
);
NOT IN の有名な罠 同じことを WHERE customer_id NOT IN (SELECT customer_id FROM orders) と書くこともできますが、内側の結果にNULLが1つでも混ざると、結果が必ず0行になります。「不明な値と等しくないか」が判定できないためです。エラーも警告も出ません。「〜でない」を書くときはNOT EXISTSを使うと決めておけば、この事故は起きません。
-- 完了注文1件あたりの平均金額
SELECT
COUNT(*) AS 注文件数,
SUM(t.合計金額) AS 総売上,
ROUND(AVG(t.合計金額), 1) AS 平均注文単価
FROM (
SELECT
o.order_id,
SUM(oi.quantity * oi.unit_price) AS 合計金額
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY o.order_id
) AS t;
SELECT
c.name AS 顧客名,
(SELECT COUNT(*) FROM orders AS o
WHERE o.customer_id = c.customer_id) AS 注文回数,
(SELECT MAX(o.ordered_at) FROM orders AS o
WHERE o.customer_id = c.customer_id) AS 最終注文日
FROM customers AS c
ORDER BY c.customer_id;
LEFT JOIN＋GROUP BYと同じ結果ですが、こちらのほうが読みやすいと感じる人も多いはずです。ただし行数が多い表では遅くなりがちです（外側の1行ごとに内側が動くため）。数百行なら気にせず、数十万行ならJOINに書き換える、と覚えておいてください。
サブクエリとJOIN、どちらを使うか
やりたいこと
おすすめ
理由
相手の表の列も表示したい
JOIN
サブクエリでは相手の列を取り出せない
条件として存在だけ確認したい
EXISTS
行が増えない。重複の心配がない
「〜でない」を書きたい
NOT EXISTS
NOT INのNULL問題を避けられる
集計をさらに集計したい
導出テーブル / CTE
集約関数は入れ子にできない
1つの値と比べたい
スカラサブクエリ
もっとも短く書ける
練習問題
「完了注文の合計金額が2,000円を超えた注文」に含まれる商品名を、重複なく表示してください。
解答を見る
answer.sql
SELECT DISTINCT p.name AS 商品名
FROM order_items AS oi
JOIN products AS p ON oi.product_id = p.product_id
WHERE oi.order_id IN (
SELECT o.order_id
FROM orders AS o
JOIN order_items AS x ON o.order_id = x.order_id
WHERE o.status = '完了'
GROUP BY o.order_id
HAVING SUM(x.quantity * x.unit_price) > 2000
);
SELECT
name AS 商品名,
price AS 価格,
CASE
WHEN price >= 1000 THEN '高価格帯'
WHEN price >= 300 THEN '中価格帯'
ELSE '低価格帯'
END AS 価格帯
FROM products
ORDER BY price DESC;
SELECT
COUNT(*) AS 全注文,
SUM(CASE WHEN status = '完了' THEN 1 ELSE 0 END) AS 完了,
SUM(CASE WHEN status = '発送中' THEN 1 ELSE 0 END) AS 発送中,
SUM(CASE WHEN status = 'キャンセル' THEN 1 ELSE 0 END) AS キャンセル,
ROUND(
SUM(CASE WHEN status = 'キャンセル' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
, 1) AS キャンセル率
FROM orders;
この型は丸ごと覚える価値がありますSUM(CASE WHEN 条件 THEN 1 ELSE 0 END) は「条件に当てはまる行数」、SUM(CASE WHEN 条件 THEN 金額 ELSE 0 END) は「条件に当てはまる行の金額合計」です。GROUP BYと組み合わせれば、行方向にも列方向にも分類した表が1本のSQLで作れます。割合を出すときに 100.0 と小数で書いているのは、整数どうしの割り算で0になるのを防ぐためです（STEP 1）。
pivot_group.sql
-- 都道府県 × 状態 のクロス集計
SELECT
c.prefecture AS 都道府県,
COUNT(*) AS 注文数,
SUM(CASE WHEN o.status = '完了' THEN 1 ELSE 0 END) AS 完了,
SUM(CASE WHEN o.status = 'キャンセル' THEN 1 ELSE 0 END) AS キャンセル
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
GROUP BY c.prefecture
ORDER BY 注文数 DESC, 都道府県;
-- 割る数が0のときにエラーや無限大にならないようにする
SELECT
c.name AS 顧客名,
COUNT(o.order_id) AS 注文回数,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS 売上,
ROUND(
COALESCE(SUM(oi.quantity * oi.unit_price), 0) * 1.0
/ NULLIF(COUNT(DISTINCT o.order_id), 0)
, 1) AS 平均単価
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id AND o.status = '完了'
LEFT JOIN order_items AS oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
ORDER BY 売上 DESC;
-- 月ごとの売上（完了のみ）
SELECT
strftime('%Y-%m', o.ordered_at) AS 年月,
COUNT(DISTINCT o.order_id) AS 注文件数,
SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY strftime('%Y-%m', o.ordered_at)
ORDER BY 年月;
-- 顧客ごとの、初回注文から最終注文までの日数
SELECT
customer_id AS 顧客番号,
MIN(ordered_at) AS 初回,
MAX(ordered_at) AS 最終,
julianday(MAX(ordered_at)) - julianday(MIN(ordered_at)) AS 経過日数
FROM orders
GROUP BY customer_id
ORDER BY 経過日数 DESC;
日付は必ず日付として保存する SQLiteには日付型が無いため文字列で持ちますが、他の製品には DATE / TIMESTAMP 型があります。「20250611」のような数値や、「2025/6/11」のような表記ゆれのある文字列で保存してはいけません。並べ替えも期間指定もできなくなり、後から直すのは大変な作業になります。
文字列を扱う
string_func.sql
SELECT
name AS 商品名,
LENGTH(name) AS 文字数,
UPPER(category) AS 大文字,
SUBSTR(name, 1, 2) AS 先頭2文字,
REPLACE(name, 'ー', '-') AS 記号置換,
TRIM(' 余白あり ') AS 前後の空白除去
FROM products
LIMIT 3;
SELECT
CAST('2025' AS INTEGER) + 1 AS 数値にして計算,
CAST(price AS TEXT) || '円' AS 文字列にして連結,
CAST(price AS REAL) / 3 AS 小数で割る
FROM products
WHERE product_id = 1;
SELECT
category AS 分類,
COUNT(*) AS 商品数,
SUM(CASE WHEN price >= 1000 THEN 1 ELSE 0 END) AS 高額商品数,
ROUND(
SUM(CASE WHEN price >= 1000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)
, 1) AS 割合
FROM products
GROUP BY category
ORDER BY 割合 DESC, 分類;
列名を省いて INSERT INTO products VALUES (…) とも書けますが（STEP 0でそう書きました）、実務では列名を必ず書いてください。表に列が追加された瞬間、列名なしのINSERTは全部壊れるからです。
insert_select.sql
-- SELECTの結果をそのまま別の表に入れる（集計結果の保存やバックアップに使う）
CREATE TABLE products_backup AS SELECT * FROM products WHERE 1 = 0; -- 空の同じ形の表
INSERT INTO products_backup (product_id, name, category, price)
SELECT product_id, name, category, price
FROM products
WHERE category = '文具';
SELECT COUNT(*) AS 退避件数 FROM products_backup;
実行結果
退避件数
--------
5
※ WHERE 1 = 0 は「絶対に真にならない条件」で、列の形だけコピーして中身は空にする定番の書き方です。文具は先ほど追加した2件を含めて5件になっています。
UPDATE ── 値を書き換える
update.sql
-- ① まず、影響する行をSELECTで必ず確認する
SELECT product_id, name, price FROM products WHERE category = '飲料';
-- ② そのうえで、同じWHEREでUPDATEする
UPDATE products
SET price = price + 20
WHERE category = '飲料';
-- ③ 結果を確認する
SELECT product_id, name, price FROM products WHERE category = '飲料';
WHEREを書き忘れたUPDATEは、全行を書き換えますUPDATE products SET price = 0; は、エラーにならずに全商品の価格を0にします。これは実際に何度も起きている事故です。防ぐ習慣は3つ。①WHEREを先に書いてからSETを書く ②必ずトランザクションで囲む ③本番環境では読み取り専用の権限で作業する。
DELETE ── 行を消す
delete.sql
-- 練習で追加した3件を消して、元の状態に戻す
DELETE FROM products WHERE product_id >= 9;
-- 表そのものを消す（中身も定義も消える。取り消せない）
DROP TABLE products_backup;
-- 元に戻ったか確認
SELECT COUNT(*) AS 商品数, SUM(price) AS 価格合計 FROM products;
※ MySQLでは INSERT … ON DUPLICATE KEY UPDATE price = VALUES(price)、SQL Serverでは MERGE 文を使います。日次でデータを取り込むバッチ処理では必須の書き方です。確認できたら UPDATE products SET price = 180 WHERE product_id = 1; で元に戻しておいてください。
練習問題
注文108（発送中）を「完了」に変えたいとします。安全な手順でSQLを書いてください。
解答を見る
answer.sql
-- ① 対象を確認（1件だけか？）
SELECT order_id, status FROM orders WHERE order_id = 108;
-- ② トランザクションを開始してから更新
BEGIN;
UPDATE orders SET status = '完了' WHERE order_id = 108;
-- ③ 結果を確認
SELECT order_id, status FROM orders WHERE order_id = 108;
-- ④ 問題なければ確定。おかしければ ROLLBACK;
ROLLBACK; -- ← このページの以降の例は「発送中」のままを前提にしているので戻す
-- よく絞り込みに使う列、よく結合に使う列に付ける
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_ordered_at ON orders(ordered_at);
CREATE INDEX idx_items_order ON order_items(order_id);
-- 複数列のインデックス（左から順に効く）
CREATE INDEX idx_orders_status_date ON orders(status, ordered_at);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL
);
CREATE TABLE equipments (
equipment_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
asset_no TEXT NOT NULL UNIQUE -- 資産番号は重複しない
);
CREATE TABLE loans (
loan_id INTEGER PRIMARY KEY,
employee_id INTEGER NOT NULL,
equipment_id INTEGER NOT NULL,
lent_on TEXT NOT NULL,
returned_on TEXT, -- 返却前はNULL
CHECK (returned_on IS NULL OR returned_on >= lent_on),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (equipment_id) REFERENCES equipments(equipment_id)
);
-- 貸出中の一覧
SELECT e.name AS 社員, q.name AS 備品, l.lent_on AS 貸出日
FROM loans AS l
JOIN employees AS e ON l.employee_id = e.employee_id
JOIN equipments AS q ON l.equipment_id = q.equipment_id
WHERE l.returned_on IS NULL;
GROUP BYは、集計すると元の行が消えます。商品ごとの売上を出せば、8行の結果になり、個々の明細は見えません。一方ウィンドウ関数は、元の行を残したまま、その行の隣に集計値を並べます。「明細を見せつつ、同時に全体に占める割合も出したい」という要求に応えられるのはこちらだけです。
window_ratio.sql
SELECT
p.name AS 商品名,
SUM(oi.quantity * oi.unit_price) AS 売上,
SUM(SUM(oi.quantity * oi.unit_price)) OVER () AS 全体売上,
ROUND(
SUM(oi.quantity * oi.unit_price) * 100.0
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER ()
, 1) AS 構成比
FROM order_items AS oi
JOIN orders AS o ON oi.order_id = o.order_id
JOIN products AS p ON oi.product_id = p.product_id
WHERE o.status = '完了'
GROUP BY p.product_id, p.name
ORDER BY 売上 DESC;
OVER () が付くと「集計はするが、行はつぶさない」という意味になります。かっこの中が空なら結果全体が集計の範囲（＝ウィンドウ）です。構成比の計算は、これまでなら2本のSQLか導出テーブルが必要でした。
書き方の型
window_syntax.sql
関数(引数) OVER (
PARTITION BY 区切りたい列 -- どの単位で区切るか（省略すると全体）
ORDER BY 並べたい列 -- 順位・累計・前後の参照に使う
ROWS BETWEEN … AND … -- さらに細かい範囲指定（省略可）
)
関数
意味
典型的な用途
ROW_NUMBER()
1,2,3… 同点でも別番号
グループごとの最新1件、重複排除
RANK()
同点は同順位、次は飛ぶ（1,2,2,4）
売上順位
DENSE_RANK()
同点は同順位、次は飛ばない（1,2,2,3）
等級・ランク分け
SUM() OVER(ORDER BY …)
累計
累計売上、在庫推移
LAG() / LEAD()
前の行 / 次の行の値
前月比、前回来店からの間隔
AVG() OVER(…)
移動平均など
7日移動平均
NTILE(n)
n等分したときの区分
上位20%の顧客抽出
順位を付ける ── 3つの違いを見る
window_rank.sql
SELECT
product_id AS 商品番号,
SUM(quantity) AS 数量,
ROW_NUMBER() OVER (ORDER BY SUM(quantity) DESC, product_id) AS 行番号,
RANK() OVER (ORDER BY SUM(quantity) DESC) AS 順位,
DENSE_RANK() OVER (ORDER BY SUM(quantity) DESC) AS 密順位
FROM order_items
GROUP BY product_id
ORDER BY 数量 DESC, 商品番号;
SELECT 顧客番号, 顧客名, 注文番号, 注文日
FROM (
SELECT
c.customer_id AS 顧客番号,
c.name AS 顧客名,
o.order_id AS 注文番号,
o.ordered_at AS 注文日,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id -- 顧客ごとに区切って
ORDER BY o.ordered_at DESC -- 新しい順に番号を振る
) AS rn
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
)
WHERE rn = 1 -- 各顧客の1番目＝最新だけ残す
ORDER BY 顧客番号;
なぜ入れ子にする必要があるのか ウィンドウ関数はWHEREの後に計算されるため、WHERE ROW_NUMBER() OVER (…) = 1 とは書けません（STEP 3の実行順序）。いったんサブクエリやCTEで番号を振り、外側で絞る。この「番号を振ってから絞る」型は丸暗記してよいほど頻出します。
累計と前月比
window_cumulative.sql
SELECT
年月,
売上,
SUM(売上) OVER (ORDER BY 年月) AS 累計売上,
LAG(売上) OVER (ORDER BY 年月) AS 前月売上,
売上 - LAG(売上) OVER (ORDER BY 年月) AS 前月差
FROM (
SELECT
strftime('%Y-%m', o.ordered_at) AS 年月,
SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY strftime('%Y-%m', o.ordered_at)
)
ORDER BY 年月;
SELECT
p.category AS 分類,
SUM(oi.quantity * oi.unit_price) AS 売上,
RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS 順位,
ROUND(
SUM(oi.quantity * oi.unit_price) * 100.0
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER ()
, 1) AS 構成比
FROM order_items AS oi
JOIN orders AS o ON oi.order_id = o.order_id
JOIN products AS p ON oi.product_id = p.product_id
WHERE o.status = '完了'
GROUP BY p.category
ORDER BY 売上 DESC;
WITH order_totals AS (
-- ① 注文ごとの金額を出す
SELECT
o.order_id,
o.customer_id,
SUM(oi.quantity * oi.unit_price) AS 合計金額
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY o.order_id, o.customer_id
)
-- ② ①の結果を、普通の表のように使う
SELECT
COUNT(*) AS 注文件数,
SUM(合計金額) AS 総売上,
ROUND(AVG(合計金額), 1) AS 平均単価,
MAX(合計金額) AS 最高額
FROM order_totals;
WITH order_totals AS (
-- ① 完了注文ごとの金額
SELECT o.order_id, o.customer_id,
SUM(oi.quantity * oi.unit_price) AS 金額
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY o.order_id, o.customer_id
),
customer_summary AS (
-- ② 顧客ごとにまとめる（①を使う）
SELECT c.customer_id, c.name, c.prefecture,
COUNT(t.order_id) AS 注文回数,
COALESCE(SUM(t.金額), 0) AS 売上,
MAX(o.ordered_at) AS 最終注文日
FROM customers AS c
LEFT JOIN order_totals AS t ON c.customer_id = t.customer_id
LEFT JOIN orders AS o ON t.order_id = o.order_id
GROUP BY c.customer_id, c.name, c.prefecture
)
-- ③ 最後に、ランク分けして表示する（②を使う）
SELECT
name AS 顧客名,
prefecture AS 都道府県,
注文回数,
売上,
最終注文日,
CASE
WHEN 売上 >= 2000 THEN '優良'
WHEN 売上 >= 1000 THEN '一般'
WHEN 売上 > 0 THEN 'ライト'
ELSE '未購入'
END AS 区分
FROM customer_summary
ORDER BY 売上 DESC;
WITH RECURSIVE months(年月) AS (
SELECT '2025-01' -- 開始点
UNION ALL
SELECT strftime('%Y-%m', date(年月 || '-01', '+1 month')) -- 1か月ずつ進める
FROM months
WHERE 年月 < '2025-06' -- 終了条件（必須）
),
monthly_sales AS (
SELECT strftime('%Y-%m', o.ordered_at) AS 年月,
SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY strftime('%Y-%m', o.ordered_at)
)
SELECT
m.年月,
COALESCE(s.売上, 0) AS 売上
FROM months AS m
LEFT JOIN monthly_sales AS s ON m.年月 = s.年月
ORDER BY m.年月;
WITH order_totals AS (
SELECT o.order_id, o.customer_id,
SUM(oi.quantity * oi.unit_price) AS 金額
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY o.order_id, o.customer_id
),
per_customer AS (
SELECT c.name AS 顧客名,
ROUND(AVG(t.金額), 1) AS 平均購入額
FROM order_totals AS t
JOIN customers AS c ON t.customer_id = c.customer_id
GROUP BY t.customer_id, c.name
)
SELECT *
FROM per_customer
WHERE 平均購入額 > (SELECT AVG(金額) FROM order_totals)
ORDER BY 平均購入額 DESC;
-- ① インデックスが無い状態
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;
実行結果（例）
QUERY PLAN
`--SCAN orders
explain2.sql
-- ② インデックスを作ってから、もう一度
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;
実行結果（例）
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
SCAN は「全部読んだ」、SEARCH … USING INDEX は「索引で直行した」という意味です。この2語を見分けられるだけで、遅い原因のかなりの部分が特定できます。PostgreSQLでは Seq Scan（全表走査）と Index Scan、MySQLでは type: ALL と type: ref が同じ役割の言葉です。
-- △ 全部結合してから絞る（大きな表では無駄が大きい）
SELECT c.name, SUM(oi.quantity * oi.unit_price) AS 売上
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.ordered_at >= '2025-06-01'
GROUP BY c.customer_id, c.name;
-- ○ 先に絞って小さくしてから結合する
WITH target_orders AS (
SELECT order_id, customer_id
FROM orders
WHERE ordered_at >= '2025-06-01' AND status = '完了'
),
totals AS (
SELECT t.customer_id, SUM(oi.quantity * oi.unit_price) AS 売上
FROM target_orders AS t
JOIN order_items AS oi ON t.order_id = oi.order_id
GROUP BY t.customer_id
)
SELECT c.name AS 顧客名, t.売上
FROM totals AS t
JOIN customers AS c ON t.customer_id = c.customer_id
ORDER BY t.売上 DESC;
SELECT order_id, customer_id, ordered_at, status
FROM orders
WHERE ordered_at >= '2025-06-01'
AND ordered_at < '2025-07-01'; -- 翌月の1日「未満」にすると月末の時刻まで漏れなく入る
※ BETWEEN '2025-06-01' AND '2025-06-30' と書くと、日時型の場合に「6月30日 12時」のデータが漏れます。期間は「以上・未満」で書くのが実務の鉄則です。SELECT * をやめている点も、わずかですが効きます。
この5つは、例外なく守ってください
① 本番環境では、まず読み取り専用の権限で作業する（そもそも壊せない状態にする）
② UPDATE・DELETEは必ず同じWHEREでSELECTしてから、件数を確認して実行する
③ 変更はトランザクションで囲む。確認してからCOMMITする
④ 大量更新の前にバックアップを取る。取ったことを確認してから始める
⑤ 業務時間中に重いクエリを流さない。全件集計は夜間か、分析用の複製データベースで行う
-- 目的：月次の売上レポート（経理部・毎月5日提出）
-- 定義：status='完了' のみ対象。金額は明細の 数量×単価 の合計。
-- 作成：2025-06-15 うえき / 変更：2025-07-01 消費税率の記載を削除
WITH monthly AS (
SELECT
strftime('%Y-%m', o.ordered_at) AS 年月
, SUM(oi.quantity * oi.unit_price) AS 売上
FROM orders AS o
JOIN order_items AS oi ON o.order_id = oi.order_id
WHERE o.status = '完了'
GROUP BY strftime('%Y-%m', o.ordered_at)
)
SELECT
年月
, 売上
, SUM(売上) OVER (ORDER BY 年月) AS 累計
FROM monthly
ORDER BY 年月;
続けるためのコツ ① 自分の仕事のデータで試すのがいちばん続きます。売上でも、勤怠でも、部活の記録でもかまいません ② 分からない場面に出会ったら、まず10行くらいの小さな表を自分で作って試す。大きなデータで悩むより百倍速く分かります ③ エラーメッセージは英語でもそのまま検索する。だいたい同じところでつまずいた人がいます