SQL コピペで使える実践 Tips 集【中級者向け】

約 19 分で読めます

SQL コピペでそのまま動くクエリを集めました。実務でよく書くパターンを中心に、Window 関数・CTE・JSON 操作・実行計画の読み方まで PostgreSQL を主軸に解説します。

よく書くクエリパターン

UPSERT（INSERT ON CONFLICT）

行が存在すれば UPDATE、なければ INSERT する定番パターンです。

INSERT INTO users (id, name, email, updated_at)
VALUES (1, 'Alice', '[email protected]', NOW())
ON CONFLICT (id)
DO UPDATE SET
  name       = EXCLUDED.name,
  email      = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at;

EXCLUDED は INSERT しようとした値を参照します。競合時に何もしたくない場合は DO NOTHING を使います。

INSERT INTO tags (name)
VALUES ('postgresql')
ON CONFLICT (name) DO NOTHING;

MySQL の場合: INSERT ... ON DUPLICATE KEY UPDATE を使います。EXCLUDED の代わりに VALUES(col) で参照します。

-- MySQL
INSERT INTO users (id, name, email, updated_at)
VALUES (1, 'Alice', '[email protected]', NOW())
ON DUPLICATE KEY UPDATE
  name       = VALUES(name),
  email      = VALUES(email),
  updated_at = VALUES(updated_at);

バルクインサート

1 行ずつ INSERT するより VALUES を並べる方が大幅に速くなります。

INSERT INTO products (name, price, category)
VALUES
  ('Widget A', 1200, 'gadget'),
  ('Widget B', 3500, 'gadget'),
  ('Widget C',  980, 'supply'),
  ('Widget D', 7200, 'gadget');

大量データを CSV から流し込む場合は COPY が最速です。

COPY products (name, price, category)
FROM '/tmp/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

クライアント側ファイルを使う場合は \copy（psql メタコマンド）を使います。

\copy products (name, price, category)
FROM 'products.csv'
WITH (FORMAT csv, HEADER true);

Window 関数で集計を柔軟にする

ROW_NUMBER・RANK・DENSE_RANK

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

RANK は同順位が出ると次の番号を飛ばします。DENSE_RANK は飛ばしません。ROW_NUMBER は同じ値でも必ず異なる番号を振ります。

グループ内の最新レコード 1 件だけ取り出すイディオムです。

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) sub
WHERE rn = 1;

LAG / LEAD で前後行の値を参照する

SELECT
  date,
  sales,
  LAG(sales)  OVER (ORDER BY date) AS prev_sales,
  LEAD(sales) OVER (ORDER BY date) AS next_sales,
  sales - LAG(sales) OVER (ORDER BY date) AS diff
FROM daily_sales
ORDER BY date;

LAG(col, n, default) の第 2 引数でオフセット（デフォルト 1）、第 3 引数で前行がない場合のデフォルト値を指定できます。

累積合計・移動平均

SELECT
  date,
  sales,
  SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
  AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)          AS moving_avg_7d
FROM daily_sales
ORDER BY date;

CTE（WITH 句）で可読性を上げる

サブクエリを WITH 句に切り出すと、処理の流れを上から下へ読めるようになります。

WITH
active_users AS (
  SELECT id, name, created_at
  FROM users
  WHERE deleted_at IS NULL
    AND last_login_at >= NOW() - INTERVAL '30 days'
),
user_order_counts AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
),
ranked AS (
  SELECT
    u.id,
    u.name,
    COALESCE(oc.order_count, 0) AS order_count,
    RANK() OVER (ORDER BY COALESCE(oc.order_count, 0) DESC) AS rank
  FROM active_users u
  LEFT JOIN user_order_counts oc ON oc.user_id = u.id
)
SELECT *
FROM ranked
WHERE rank <= 10;

再帰 CTE で階層データを展開する

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT
  REPEAT('  ', depth) || name AS indented_name,
  depth
FROM category_tree
ORDER BY id;

JSON 操作

PostgreSQL の JSON 操作

-- JSON カラムから値を取り出す
SELECT
  data->>'name'          AS name,
  data->'address'->>'city' AS city,
  (data->>'age')::int    AS age
FROM profiles;

-- JSON 配列を行に展開する
SELECT
  id,
  jsonb_array_elements_text(tags) AS tag
FROM articles;

-- JSON オブジェクトを更新する（jsonb_set）
UPDATE profiles
SET data = jsonb_set(data, '{address, city}', '"Osaka"')
WHERE id = 42;

-- キーの存在チェック
SELECT * FROM profiles WHERE data ? 'email';

-- JSON を集約して配列にする
SELECT
  department,
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS members
FROM employees
GROUP BY department;

MySQL の JSON 操作

-- 値を取り出す（->/->>）
SELECT
  data->>'$.name'           AS name,
  data->>'$.address.city'   AS city
FROM profiles;

-- JSON 配列を行に展開する
SELECT id, jt.tag
FROM articles,
     JSON_TABLE(tags, '$[*]' COLUMNS (tag VARCHAR(100) PATH '$')) AS jt;

-- JSON を更新する
UPDATE profiles
SET data = JSON_SET(data, '$.address.city', 'Osaka')
WHERE id = 42;

インデックスの貼り方・確認方法

基本的なインデックス作成

-- 単一カラム
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 複合インデックス（選択性の高い列を先に）
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 部分インデックス（WHERE 句付き）
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 式インデックス（lower() で大文字小文字を無視した検索に使う）
CREATE INDEX idx_users_email_lower ON users (lower(email));

インデックスを張っても使われない代表例は WHERE lower(email) = '[email protected]' に対して email カラムのインデックスしかない場合です。式インデックスで対処します。

現在のインデックス一覧を確認する

-- テーブルのインデックス一覧
SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;

-- 使用統計（idx_scan が 0 なら不要なインデックス候補）
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan;

EXPLAIN ANALYZE で実行計画を読む

基本的な使い方

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

出力のポイントを押さえます。

Limit  (cost=1234.56..1234.61 rows=20 width=40) (actual time=45.2..45.3 rows=20 loops=1)
  ->  Sort  (cost=1234.56..1239.56 rows=2000 width=40) (actual time=45.2..45.2 rows=20 loops=1)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Hash Left Join  (cost=...)  (actual time=... rows=15000 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=...) (actual time=... rows=200000 loops=1)
              ->  Hash  (cost=...) (actual time=... rows=15000 loops=1)
                    ->  Index Scan using idx_users_created_at on users u

確認すべき点は以下の通りです。

  • Seq Scan が大きなテーブルに出ていればインデックス追加を検討
  • cost=見積もり と actual time=実績 の乖離が大きければ統計情報が古い（ANALYZE を実行）
  • loops=N が大きい Nested Loop はインデックスの有無を確認
  • Sort Method: external merge が出たら work_mem を増やすと改善することがある

JSON 形式で取得してツールに渡す

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE status = 'pending';

explain.depesz.com や pgMustard にペーストするとビジュアルで確認できます。

まとめ

カテゴリTipsポイント
UPSERTON CONFLICT DO UPDATEEXCLUDED で挿入しようとした値を参照
バルクインサートVALUES 複数行 / COPY大量ロードは COPY が最速
Window 関数ROW_NUMBER / RANK / LAG / LEADPARTITION BY でグループ内集計
累積・移動集計ROWS BETWEEN ... AND ...フレーム句でウィンドウ幅を制御
CTEWITH 句 / 再帰 CTE可読性向上・階層データ展開に有効
JSON-> / ->> / jsonb_setjsonb 型は演算子・GIN インデックスが豊富
インデックス複合・部分・式インデックスpg_stat_user_indexes で使用状況を監視
実行計画EXPLAIN ANALYZESeq Scan と cost vs actual の乖離に注目

どのクエリもそのままコピーして動かせます。実運用では EXPLAIN ANALYZE で実行計画を確認しながら必要に応じてインデックスを追加してください。

関連記事