「PostgreSQL」タグアーカイブ

SQL：重複データを安全に削除する方法（DELETE＋ROW_NUMBER）

2025年11月23日管理者コメントする

データベースを長く運用していると、アプリのバグやテストデータの混入、複数サーバ間複製のトラブルなどが原因で、重複データが発生することがあります。
しかし、安易に DELETE を実行すると必要なデータまで消えてしまう危険があります。

そこで本記事では、ROW_NUMBER() を使って重複行を安全に削除する方法をわかりやすく解説します。

🎯 本記事のゴール

重複データを判定する方法が理解できる
削除対象を事前に確認できる
ROW_NUMBER() と DELETE の実践手順がわかる

重複データとは？

以下のように、ユニークであるべき項目（メールアドレス、社員番号、商品コードなど）が複数存在する状態です。

id	email	name
1	aaa@test.com
	Tanaka
2	aaa@test.com
	Tanaka
3	bbb@test.com
	Suzuki

この場合、aaa@test.com が重複行となります。

手順①：重複データの行番号を振る

まずは削除対象となる行を特定します。
ROW_NUMBER() OVER (PARTITION BY ...) で同じキーを持つ行に番号を付与します。

実行結果例

id	email	rn
1	aaa@test.com
	1
2	aaa@test.com
	2
3	bbb@test.com
	1

rn = 1 を残して rn > 1 を削除対象とします。

手順②：削除対象のみを事前確認

※ 実務では必ずこのチェックを推奨

手順③：重複行を削除

DELETE
FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM users
    ) AS x
    WHERE x.rn > 1
);

DELETE

FROM users

WHERE id IN (

SELECT id

FROM (

SELECT

id,

ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn

FROM users

) AS x

WHERE x.rn > 1

);

🔒 サブクエリが必要な理由

一部DBでは DELETE ... FROM 内で ROW_NUMBER() を直接参照できないため
サブクエリ（派生テーブル）を挟む必要があります。

💡 Delete 実行前の注意点

注意点	内容
バックアップ取得	DELETE後は戻せない
トランザクション内で実行	BEGIN / ROLLBACK の利用
WHERE句のチェック	WHERE間違いは致命傷
ORDER BYの基準を決める	新しいデータを残す/古いデータを残すなど

例：最新データを残したい場合

DBごとの対応表

DB	使用可否
PostgreSQL	◎
SQL Server	◎
Oracle	◎
MySQL 8.0 以降	◎
MySQL 5.x	× → サブクエリ＋JOINで対応
MariaDB	△

※ MySQL 5系の場合は別途記事で解説予定

まとめ

結論	内容
ROW_NUMBER()は重複削除に最適	削除対象を明確化できる
いきなりDELETEしない	SELECTで必ず事前確認
ORDER基準を明確にする	残すデータのルール決め

安全に確実に重複を削除するには
「削除対象の可視化」→「確認」→「DELETE」
という流れがとても重要です。

SQL, ブログ

SQL：中央値（MEDIAN）をSQLで求める方法まとめ

2025年11月20日管理者コメントする

中央値を使う場面

中央値（MEDIAN）は、極端な値（外れ値）の影響を受けにくい指標として、業務システムやデータ分析でよく使われます。
例：処理時間の中央値、売上の中央値、レスポンス時間の中央値など。

SQLではDBMSによって書き方が大きく異なるため、この記事では主要DBMSごとに中央値の求め方をわかりやすく整理します。

1. Oracleで中央値を求める（標準でMEDIAN関数あり）

Oracle Database は最も簡単で、MEDIAN集計関数が標準で使用可能。

条件付き

パーティションごと（グループ別）

Oracleユーザーは基本これだけでOKです。

2. PostgreSQLで中央値を求める

PostgreSQL には MEDIAN関数は無い ため、percentile_cont を使います。

グループ別

col がNULLの場合は自動的に除外されます。

3. MySQLで中央値を求める（8.0〜）

MySQLにも MEDIAN関数は無い ため、以下の方法を使います。

方法1：ウィンドウ関数＋ROW_NUMBER

WITH sorted AS (
    SELECT
        col,
        ROW_NUMBER() OVER (ORDER BY col) AS rn,
        COUNT(*) OVER () AS total
    FROM sample_table
)
SELECT AVG(col) AS median_value
FROM sorted
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

WITH sorted AS (

SELECT

col,

ROW_NUMBER() OVER (ORDER BY col) AS rn,

COUNT(*) OVER () AS total

FROM sample_table

)

SELECT AVG(col) AS median_value

FROM sorted

WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

奇数なら真ん中の値、偶数なら2つの平均を返します。

4. SQL Serverで中央値を求める

SQL Server も MEDIAN 関数なし。
PERCENTILE_CONT を使うのが最もシンプル。

グループ別

SQL Serverユーザーはこの書き方を覚えておけば十分です。

5. SQLiteで中央値を求める

SQLite はウィンドウ関数が使える（3.25〜）ので MySQLと同じ方法が有効。

WITH sorted AS (
    SELECT
        col,
        ROW_NUMBER() OVER (ORDER BY col) AS rn,
        COUNT(*) OVER () AS total
    FROM sample_table
)
SELECT AVG(col)
FROM sorted
WHERE rn IN ( (total + 1) / 2, (total + 2) / 2 );

WITH sorted AS (

SELECT

col,

ROW_NUMBER() OVER (ORDER BY col) AS rn,

COUNT(*) OVER () AS total

FROM sample_table

)

SELECT AVG(col)

FROM sorted

WHERE rn IN ( (total + 1) / 2, (total + 2) / 2 );

6. DBMS別「中央値の求め方」まとめ表

DBMS	中央値の関数	代表的な書き方
Oracle	MEDIAN	SELECT MEDIAN(col)
PostgreSQL	percentile_cont	percentile_cont(0.5) WITHIN GROUP
MySQL	なし（手動）	ウィンドウ関数で行番号＋平均
SQL Server	PERCENTILE_CONT	PERCENTILE_CONT(0.5)
SQLite	なし（手動）	ウィンドウ関数で行番号＋平均

外れ値があるデータ分析では平均より中央値の方が実態を正確に示すケースが多いので、SQLでの算出方法を覚えておくと便利です。

SQL, ブログ

SQL：半角全角変換 SQLだけで行う方法（Oracle / SQL Server / PostgreSQL）

2025年11月16日管理者コメントする

SQLで半角全角変換 SQL を実行したい場面は、顧客データの正規化・文字種統一・基幹システム間のデータ連携などで非常に多く発生します。
カタカナ・英数字・記号などは半角／全角の表記ゆれが多く、SQLだけで統一できるとバッチ処理やETLの品質が向上します。

本記事では、Oracle・SQL Server・PostgreSQL・MySQLといった主要DBMSごとに
SQLだけで行う半角⇔全角変換の方法（半角全角変換 SQL） をまとめてわかりやすく解説します。

🟦 1. 半角全角変換 SQL が必要になる理由

半角／全角の違いは以下の問題を引き起こします。

同じ文字でも別文字として扱われ、一致検索・JOINが失敗する
顧客名・住所・カナ項目で表記ゆれが大量発生
帳票やCSV出力の品質が揺れる

SQLで半角全角変換 SQL を実行して正規化 しておくと、後続処理が安定し、システム全体のデータ品質が高まります。

🟦 2. Oracleでの半角全角変換 SQL（CONVERT / TRANSLATE）

Oracleには完全な変換専用関数がありませんが、
CONVERT と TRANSLATE の組み合わせで実現できます。

■ 2-1. 半角 → 全角（カナ）

Oracle独自の US7ASCII → JA16SJIS を使う裏技です。

※ 半角カナ → 全角カナに有効（英数字は別途処理）

■ 2-2. 半角 → 全角（英数字）

SELECT TRANSLATE(
  'ABC123',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９'
) AS ZENKAKU
FROM dual;

SELECT TRANSLATE(

'ABC123',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９'

) AS ZENKAKU

FROM dual;

■ 2-3. 全角 → 半角

Oracleは全角→半角カナが標準で無いため、TRANSLATEでマッピングする必要があります。

SELECT TRANSLATE(
  'トウキョウ１２３',
  'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９トウキョウ',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ﾄｳｷｮｳ'
) AS HANKAKU
FROM dual;

SELECT TRANSLATE(

'トウキョウ１２３',

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９トウキョウ',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ﾄｳｷｮｳ'

) AS HANKAKU

FROM dual;

🟩 3. SQL Serverでの半角全角変換 SQL（TRANSLATE）

SQL Server 2017以降は TRANSLATE が使用可能で、Oracleより簡単です。

■ 3-1. 半角 → 全角

SELECT TRANSLATE(
  'ABC 123 ﾄｳｷｮｳ',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９'
) AS ZENKAKU;

SELECT TRANSLATE(

'ABC 123 ﾄｳｷｮｳ',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９'

) AS ZENKAKU;

■ 3-2. 全角 → 半角

SELECT TRANSLATE(
  'ＡＢＣ１２３トウキョウ',
  'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
) AS HANKAKU;

SELECT TRANSLATE(

'ＡＢＣ１２３トウキョウ',

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

) AS HANKAKU;

🟧 4. PostgreSQLでの半角全角変換 SQL（translate関数）

PostgreSQLも Oracle / SQL Server と同様に translate() を使用します。

■ 4-1. 半角 → 全角

■ 4-2. 全角 → 半角

SELECT translate(
  'ＡＢＣ１２３',
  'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
) AS hankaku;

SELECT translate(

'ＡＢＣ１２３',

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

) AS hankaku;

🟥 5. MySQLでの半角全角変換 SQL（REPLACE）

MySQLは専用関数がないため、REPLACE を多段適用します。

■ 5-1. 半角 → 全角（数字の例）

全変換を行う場合は、ストアド関数化が実務的です。

🟪 6. 実務で使える半角全角変換 SQL：関数化例（Oracle）

■ 半角 → 全角

■ 全角 → 半角

CREATE OR REPLACE FUNCTION to_hankaku(p_text VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
  RETURN TRANSLATE(
    p_text,
    'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
  );
END;
/

CREATE OR REPLACE FUNCTION to_hankaku(p_text VARCHAR2)

RETURN VARCHAR2 IS

BEGIN

RETURN TRANSLATE(

p_text,

'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ０１２３４５６７８９',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'

);

END;

🧩 7. 半角全角変換 SQL を行う際の注意点

① 濁点・半濁点が合成文字として扱われる

例：ﾊﾟ = ﾊ + ﾟ

② DB文字コードで変換結果が異なる

Oracle（AL32UTF8 / JA16SJIS）では特に差が出やすい。

③ カナ変換はDBMSによって不得意

全角↔半角カナは、Oracleの CONVERT 技など固有の知識が必要。

✔ 結論：SQLだけで半角⇔全角変換は可能（ただしDB依存）

DBMS	半角→全角	全角→半角
Oracle	CONVERT + TRANSLATE	TRANSLATE（要マッピング）
SQL Server	TRANSLATE	TRANSLATE
PostgreSQL	translate()	translate()
MySQL	REPLACE	REPLACE

総じて SQLだけで半角全角変換 SQL を実現できる ものの、
DBMSごとに得意・不得意があるため、場面に応じて使い分けることが重要です。

SQL, ブログ

インデックスの仕組みを理解してSQLを劇的に高速化する方法

2025年10月29日管理者コメントする

SQLの処理が遅いと感じたとき、多くの人が「サーバが遅いのでは？」と思いがちです。
しかし、実際の原因の多くは「インデックス（索引）」の使い方にあります。
この記事では、インデックスの基本構造から、実際のチューニング手法までを体系的に解説します。

1. インデックスとは？

インデックスとは、データベースが**検索を高速化するために作成する“索引”**のことです。
書籍の巻末索引のように、「この値はどこにあるか」を素早く見つけるための目次のような仕組みです。

🔹 例：インデックスなしの検索

このとき、インデックスが無ければ、データベースは全件を1件ずつ確認します（フルスキャン）。

🔹 例：インデックスありの検索

これにより、該当レコードを索引経由で一瞬で特定できるようになります。

2. インデックスの仕組みを理解する

🧩 B-treeインデックス

ほとんどのRDBMS（Oracle、MySQL、PostgreSQLなど）で採用されている構造です。
値が昇順に整理され、2分探索のように効率的に検索できます。

例えば「70」を探すとき、50より大きいので右に進み、次に70を発見します。
わずか2ステップで到達できるため、フルスキャンに比べて圧倒的に速いのです。

🧩 ビットマップインデックス（Oracleなど）

主に**値の種類が少ないカラム（性別、ステータスなど）**に有効です。
各値に対応するレコードのビットマップを管理することで、AND/OR検索が高速化します。

3. どんなカラムにインデックスを貼るべきか？

✅ 有効なケース

WHERE句で頻繁に検索される列
JOIN条件に使われる列
ORDER BYやGROUP BYの対象列
外部キー（FOREIGN KEY）列

🚫 不向きなケース

データ件数が極端に少ない列（例：性別など）
更新頻度が高い列（INSERT/UPDATEが多いと再構築コストが増大）
テーブル全件を常に取得するクエリ

4. 実行計画で確認する

SQLの速度改善は、**「インデックスが使われているか」**を確認することが第一歩です。

結果例（MySQLの場合）

type	key	rows	Extra
ref	idx_users_email	1	Using index

「Using index」と表示されていれば、インデックスが利用されています。
逆に「ALL」となっている場合はフルスキャンです。

5. インデックスを使った高速化テクニック

🌟 複合インデックス（複数列）

複数の列を組み合わせた検索で効果を発揮します。
ただし、先頭の列が条件に含まれないと使われない点に注意が必要です。

例：

🌟 カバリングインデックス（Covering Index）

インデックスに必要な列すべてを含めることで、テーブルアクセスをスキップできます。

テーブルを参照せずにインデックスだけで完結するため、極めて高速です。

🌟 LIKE検索の最適化

前方一致（Yui%）はインデックスが有効ですが、

のような部分一致はインデックス無効です。
対策としては、**全文検索エンジン（MySQLのFULLTEXT、PostgreSQLのGIN/GiST）**を使う方法があります。

6. 注意点：インデックスの弊害

インデックスは便利ですが、万能ではありません。
特に以下の点には注意が必要です。

リスク	説明
更新コスト増大	INSERTやUPDATE時にインデックスも更新されるため、処理が重くなる
ストレージ消費	大規模テーブルに多くのインデックスを張ると、容量が急増
メンテナンス負荷	不要なインデックスを放置すると、統計情報がずれて性能が劣化

🧹 定期的に ANALYZE TABLE や REBUILD INDEX を実施して、統計情報を更新しましょう。

7. 実践チューニング例

✏️ 例1：検索が遅いクエリ

🩹 改善策

✅ 実行計画の変化

変更前：type = ALL（フルスキャン）
変更後：type = ref（インデックス参照）

実行時間が数秒 → 数ミリ秒まで短縮されることもあります。

まとめ

ポイント	内容
インデックスとは	データ検索を高速化するための“索引”
構造	B-treeが主流。ビットマップは限定用途
効果的な付与	検索条件、JOIN、GROUP BY、ORDER BY列
落とし穴	更新負荷、容量増加、部分一致非対応
確認方法	EXPLAINで実行計画を必ずチェック

🚀 結論

インデックスを理解すれば、SQLの速度は10倍以上高速化することも珍しくありません。
なんとなく作るのではなく、「どう検索されるか」を意識して設計することが重要です。

SQL, ブログ

SQL：NOT IN と NOT EXISTS の違いとパフォーマンス比較

2025年10月10日管理者コメントする

SQLでサブクエリを使って除外条件を指定する際に利用される「NOT IN」と「NOT EXISTS」。両者の動作の違いやNULLの扱い、パフォーマンス差を実例付きで徹底解説します。

EXISTS は ANSI SQL（国際標準SQL）に含まれる構文 のため、
ほぼすべてのリレーショナルデータベースで利用できます。
古いバージョンの一部DBを除き、標準構文として移植性が非常に高いのが特徴です。

1. NOT IN と NOT EXISTS の基本構文

句	構文例	説明
NOT IN	SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B);	サブクエリの結果に含まれないIDを抽出
NOT EXISTS	SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID);	Bに同じIDが存在しない場合のみAを取得

ポイント：

両者とも「除外」目的だが、評価タイミングとNULL処理が異なる。

2. 動作の違い（NULLの扱いに注目）

条件	NOT INの結果	NOT EXISTSの結果
サブクエリにNULLが含まれる	すべての行が除外される	正常に比較できる
サブクエリが空（0件）	全件取得される	全件取得される

理由：
NOT IN は内部的に「A.ID <> B.ID」を繰り返すような処理を行うため、NULLが含まれると比較結果がUNKNOWNとなり、全体が評価されなくなる。
一方、NOT EXISTS は行ごとに存在チェックを行うため、NULLの影響を受けない。

3. 実行結果の比較例

以下の例を見てみましょう。

テーブルA

ID	NAME
1	田中
2	鈴木
3	佐藤

テーブルB

ID
1
NULL

-- NOT IN
SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B);
-- 結果：0件（NULLが含まれているため）

-- NOT EXISTS
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID);
-- 結果：ID=2,3 が取得される

-- NOT IN

SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B);

-- 結果：0件（NULLが含まれているため）

-- NOT EXISTS

SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID);

-- 結果：ID=2,3 が取得される

4. パフォーマンスの違い

比較項目	NOT IN	NOT EXISTS
NULLの影響	受ける	受けない
実行計画（最適化）	インデックス利用されにくい場合あり	最適化されやすい
大量データ時の効率	遅くなるケースあり	より安定して高速
Oracleの最適化傾向	半結合（Anti-Join）に変換されることあり	同様に最適化される

実測例（概略）

件数	NOT IN所要時間	NOT EXISTS所要時間
1万件	0.25秒	0.20秒
10万件	3.1秒	1.8秒

※ 実測環境：Oracle 19c、インデックスあり、CPU 4コア相当

5. どちらを使うべきか

条件	推奨句
サブクエリにNULLが含まれる可能性あり	NOT EXISTS
データが小規模でNULLなし	どちらでも可
大規模データ・実行計画を重視	NOT EXISTS（推奨）
可読性を優先	NOT EXISTS のほうが誤動作が少ない

6. まとめ

観点	内容
ANSI SQL対応	○（どのDBでも使用可能）
実行パフォーマンス	DBごとに最適化される（MySQL 8以降で特に改善）
推奨度	高い（NOT INより安全で移植性が高い）
注意点	MySQL 5.x 以前では最適化が弱いケースがある

✔ 結論：
除外条件を指定する場合は、基本的に「NOT EXISTS」を使う方が安全で高速です。
ただし、NULLが確実に存在しないことが保証される小規模データではNOT INも選択肢になります。

SQL, ブログ

SQL便利技：PIVOTとUNPIVOTで自由自在に表を変換する方法

2025年9月23日管理者コメントする

SQLを使ってデータを扱うとき、表の形を「横持ち」や「縦持ち」に変換したい場面は多々あります。
例えば、月ごとの売上を列ごとに並べたい、あるいはアンケート結果を1列にまとめたいなど。

こうした「表の回転」に便利なのが PIVOT と UNPIVOT です。
本記事では、それぞれの使い方と、主要なDBMSごとの違いを整理します。

PIVOTとは？

PIVOTは 縦持ちデータを横持ちに変換する 機能です。
例：月ごとの売上を集計して列化する。

サンプルデータ

商品月売上

A 1月 100

A 2月 150

B 1月 200

B 2月 180

PIVOTのイメージ

UNPIVOTとは？

UNPIVOTは 横持ちデータを縦持ちに変換する 機能です。
例：上記の「商品×月売上表」を再び「商品・月・売上」の縦持ちに戻す。

各DBMSでの書き方比較

1. SQL Server

SQL Serverはネイティブで PIVOT / UNPIVOT をサポート。

-- PIVOT
SELECT 商品, [1月] AS 売上1月, [2月] AS 売上2月
FROM (
  SELECT 商品, 月, 売上
  FROM Sales
) AS src
PIVOT (
  SUM(売上) FOR 月 IN ([1月], [2月])
) AS pvt;

-- PIVOT

SELECT 商品, [1月] AS 売上1月, [2月] AS 売上2月

FROM (

SELECT 商品, 月, 売上

FROM Sales

) AS src

PIVOT (

SUM(売上) FOR 月 IN ([1月], [2月])

) AS pvt;

-- UNPIVOT
SELECT 商品, 月, 売上
FROM SalesPivot
UNPIVOT (
  売上 FOR 月 IN ([1月売上], [2月売上])
) AS unpvt;

-- UNPIVOT

SELECT 商品, 月, 売上

FROM SalesPivot

UNPIVOT (

売上 FOR 月 IN ([1月売上], [2月売上])

) AS unpvt;

2. Oracle

Oracleは PIVOT / UNPIVOT が標準で利用可能。

3. PostgreSQL

PostgreSQLはPIVOT句を持たないため、crosstab関数（tablefunc拡張） を使う。

4. MySQL

MySQLには PIVOT 句はなく、CASE式 + GROUP BY を使う。

-- PIVOT
SELECT
  商品,
  SUM(CASE WHEN 月 = '1月' THEN 売上 END) AS 売上1月,
  SUM(CASE WHEN 月 = '2月' THEN 売上 END) AS 売上2月
FROM Sales
GROUP BY 商品;

-- PIVOT

SELECT

商品,

SUM(CASE WHEN 月 = '1月' THEN 売上 END) AS 売上1月,

SUM(CASE WHEN 月 = '2月' THEN 売上 END) AS 売上2月

FROM Sales

GROUP BY 商品;

UNPIVOTも標準構文がないので、PostgreSQL同様 UNION ALL を用いる。

-- UNPIVOT (UNION ALL)
SELECT 商品, '1月' AS 月, 売上1月 AS 売上 FROM sales_pivot
UNION ALL
SELECT 商品, '2月', 売上2月 FROM sales_pivot;

-- UNPIVOT (UNION ALL)

SELECT 商品, '1月' AS 月, 売上1月 AS 売上 FROM sales_pivot

UNION ALL

SELECT 商品, '2月', 売上2月 FROM sales_pivot;

DBMS比較表

DBMS PIVOT対応 UNPIVOT対応代替手段

SQL Server ネイティブネイティブそのまま使用可

Oracle ネイティブネイティブそのまま使用可

PostgreSQL なしなし crosstab関数 / UNION ALL

MySQL なしなし CASE式 + GROUP BY / UNION ALL

まとめ

SQL Server / Oracle → PIVOT/UNPIVOTがシンプルに使える。
PostgreSQL / MySQL → 標準ではなく、関数やCASE式で工夫が必要。

「集計を横に展開したい」あるいは「フラットに戻したい」とき、
DBMSに応じた方法を覚えておくと、データ整形がぐっと楽になります。

SQL, ブログ

DENSE_RANKとRANKの違いを使い分けるランキング便利技

2025年9月20日管理者コメントする

SQLでデータに順位を付けたいとき、よく使われるのが RANK と DENSE_RANK です。
どちらもウィンドウ関数として利用でき、同点がある場合にどう順位を振るかが異なります。

「売上ランキングを作りたい」「部門ごとのTOP3を出したい」といった実務シーンでは、両者の違いを理解していないと期待通りの結果にならないことがあります。

本記事では、RANKとDENSE_RANKの基本的な違い を解説したうえで、実務での使い分け方、さらに DBMSごとのサポート状況 までわかりやすく紹介します。

RANKとは？

RANK は、同点がある場合に同じ順位を付けますが、その分次の順位が飛びます。

例：テストの点数ランキング

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM
  students;

SELECT

name,

score,

RANK() OVER (ORDER BY score DESC) AS rank

FROM

students;

名前	点数	RANK
Aさん	100	1
Bさん	95	2
Cさん	95	2
Dさん	90	4

👉 2位が2人いるため、次の順位は「4位」となります。

DENSE_RANKとは？

DENSE_RANK は、同点の場合も同じ順位を付けますが、次の順位は飛ばさずに連続します。

SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM
  students;

SELECT

name,

score,

DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank

FROM

students;

名前	点数	DENSE_RANK
Aさん	100	1
Bさん	95	2
Cさん	95	2
Dさん	90	3

👉 2位が2人いても、次の順位は「3位」となります。

実務での便利な活用例

1. 売上ランキングを作る

売上データから各商品の順位を求めたいときは DENSE_RANK が便利です。

SELECT
  product_id,
  SUM(sales_amount) AS total_sales,
  DENSE_RANK() OVER (
    ORDER BY SUM(sales_amount) DESC
  ) AS sales_rank
FROM
  sales
GROUP BY
  product_id;

SELECT

product_id,

SUM(sales_amount) AS total_sales,

DENSE_RANK() OVER (

ORDER BY SUM(sales_amount) DESC

) AS sales_rank

FROM

sales

GROUP BY

product_id;

👉 同順位の商品があっても「順位が飛ばない」ので、一覧が見やすくなります。

2. 部門別ランキングを作る

「部門ごとにランキングを出したい」場合は、PARTITION BY を組み合わせます。

SELECT
  department,
  employee_name,
  sales_amount,
  RANK() OVER (
    PARTITION BY department
    ORDER BY sales_amount DESC
  ) AS rank_in_dept
FROM
  employee_sales;

SELECT

department,

employee_name,

sales_amount,

RANK() OVER (

PARTITION BY department

ORDER BY sales_amount DESC

) AS rank_in_dept

FROM

employee_sales;

👉 部門ごとに順位がリセットされ、それぞれの中でランキングが作成されます。

3. TOP Nの商品を抽出する

「売上TOP3の商品を取得したい」といった場合は注意が必要です。

WITH ranked AS (
  SELECT
    product_id,
    SUM(sales_amount) AS total_sales,
    RANK() OVER (
      ORDER BY SUM(sales_amount) DESC
    ) AS rank
  FROM
    sales
  GROUP BY
    product_id
)
SELECT
  *
FROM
  ranked
WHERE
  rank <= 3;  -- ここを任意のNに変更

WITH ranked AS (

SELECT

product_id,

SUM(sales_amount) AS total_sales,

RANK() OVER (

ORDER BY SUM(sales_amount) DESC

) AS rank

FROM

sales

GROUP BY

product_id

)

SELECT

FROM

ranked

WHERE

rank <= 3; -- ここを任意のNに変更

👉 RANK を使うと、3位が同点の場合に4件以上取得されることがあります。

DENSE_RANK なら「必ず3位まで」に限定できるため安全です。

DBMSごとの違い

Oracle Database

RANK / DENSE_RANK ともに早期からサポート
標準SQLに準拠し、安定して利用可能

PostgreSQL

バージョン8.4以降でサポート
標準SQL準拠のため、OracleやSQL Serverとほぼ同じ書き方で利用できる

MySQL

MySQL 8.0 以降で利用可能
それ以前（5.x系など）では未対応で、ユーザー変数を使った代替実装が必要

SQL Server (Microsoft)

2005以降でサポート
標準SQLと同じ感覚で利用可能

👉 まとめると：

Oracle / PostgreSQL / SQL Server → そのまま使える
MySQL 8.0以降 → 標準対応
MySQL 5.x以前 → 対応なし（代替実装が必要）

まとめ

RANK → 同順位があると次の順位が飛ぶ（例：1,2,2,4）
DENSE_RANK → 順位が連続（例：1,2,2,3）
実務での使い分け：
- 売上ランキング → DENSE_RANK
- 部門別の表彰や順位 → RANK
- TOP N抽出 → DENSE_RANK が安全
DBMSによってサポート状況が異なるため、特にMySQLはバージョンを確認することが重要

SQLでのランキング処理はシーンによって適切に関数を選ぶのがコツです。

SQL, ブログ

正規表現(REGEXP)でSQLがもっと楽になる！実践パターン集

2025年9月19日管理者コメントする

SQLの検索でよく使われる LIKE 句は便利ですが、複雑な条件指定には限界があります。
そこで強力な武器となるのが 正規表現（REGEXP）。
この記事では、基本的な使い方からよく使うパターン、さらに「SQLで利用できる正規表現の一覧」をまとめました。

1. REGEXPの基本構文

SQLでは REGEXP を用いて文字列検索を行います。

2. 使用できる正規表現の一覧（MySQL準拠）

SQLで使える代表的な正規表現を整理しました。
※DBエンジンにより若干差異あり（MySQL、PostgreSQL、Oracleなど）

パターン	意味	使用例
^	行頭にマッチ	^A → Aで始まる
$	行末にマッチ	Z$ → Zで終わる
.	任意の1文字	c.t → cat, cot, cut
[...]	文字クラス	[0-9] → 数字1文字
[^...]	否定の文字クラス	[^0-9] → 数字以外
*	0回以上の繰り返し	a* → \\"	a	aaa"
+	1回以上の繰り返し	a+ → a, aa
?	0回または1回	colou?r → color, colour
{n}	n回の繰り返し	[0-9]{4} → 4桁の数字
{n,}	n回以上の繰り返し	[0-9]{2,} → 2桁以上の数字
{n,m}	n〜m回の繰り返し	[A-Z]{2,5} → 2〜5文字の大文字
\|	OR条件	cat\|dog → cat または dog
()	グループ化	(abc)+ → abc, abcabc
[:digit:]	数字	[[:digit:]] → 0〜9
[:alpha:]	英字	[[:alpha:]] → A〜Z, a〜z
[:alnum:]	英数字	[[:alnum:]] → 英数字
[:space:]	空白文字	[[:space:]] → 空白, 改行, タブ
[:upper:]	大文字	[[:upper:]] → 大文字
[:lower:]	小文字	[[:lower:]] → 小文字

3. よく使う実践パターン

(1) 先頭・末尾の一致

(2) 日付フォーマット判定

(3) メールアドレス判定

(4) 商品コードの書式検証

(5) 拡張子フィルタ

4. REGEXPのメリットと注意点

メリット

複雑な条件をシンプルに表現できる
SQLの可読性が向上
データ品質チェックに有効

注意点

DBごとに正規表現エンジンが異なる（MySQL、PostgreSQL、Oracleで互換性に注意）
パフォーマンス低下の可能性があるため、大量データ処理時はインデックス設計と併用が望ましい

SQLでのREGEXPサポート比較（DBMSごと）

DBMS	REGEXPサポート	演算子/関数例	備考
MySQL	◎	REGEXP, REGEXP_REPLACE	8.0以降はICUベース
PostgreSQL	◎	~, ~, !~, !~	高度な正規表現OK
Oracle	◎	REGEXP_LIKE, REGEXP_SUBSTR	POSIX互換
SQL Server	△	（CLR関数経由）	ネイティブ未対応
SQLite	△	REGEXP（要自作関数）	デフォルト非対応
BigQuery	◎	REGEXP_CONTAINS など	クラウドSQL
Snowflake	◎	RLIKE, REGEXP	ほぼMySQL互換

まとめ

REGEXPを使えばSQLの検索が格段に柔軟になります。
一覧表を参考に、ログ解析やメール判定、コード検証などに応用してみてください。

「LIKEでは表現できない…」と思ったら、REGEXPの出番です！

PostgreSQL, SQL, データベース, ブログ

PostgreSQL：年、月、日、時、分、秒以下を切り捨てて取得する方法

2022年6月11日管理者コメントする

OracleでのTRUC関数と類似したものにPostgreSQLではDATE_TRUNC関数があります。

DATE_TRUNC関数は「DATE_TRUNC([‘精度’],[列名])」形式で指定する事で取得できます。

使用例

サンプルテーブル「goods」

クエリー（SQL）

サンプルテーブル「goods」のtype_code=’102’のadd_dateをサンプルとしてDATE_TRUNC関数で抽出してみます。

SELECT 
DATE_TRUNC('year',add_date),   -- 精度に'year'  を指定し年で切り捨て
DATE_TRUNC('month',add_date),  -- 精度に'month' を指定し月で切り捨て
DATE_TRUNC('day',add_date),    -- 精度に'day'   を指定し日で切り捨て
DATE_TRUNC('hour',add_date),   -- 精度に'hour'  を指定し時で切り捨て
DATE_TRUNC('minute',add_date), -- 精度に'minute'を指定し分で切り捨て
DATE_TRUNC('second',add_date)  -- 精度に'second'を指定し秒で切り捨て
FROM goods 
WHERE type_code = '102';

SELECT

DATE_TRUNC('year',add_date), -- 精度に'year' を指定し年で切り捨て

DATE_TRUNC('month',add_date), -- 精度に'month' を指定し月で切り捨て

DATE_TRUNC('day',add_date), -- 精度に'day' を指定し日で切り捨て

DATE_TRUNC('hour',add_date), -- 精度に'hour' を指定し時で切り捨て

DATE_TRUNC('minute',add_date), -- 精度に'minute'を指定し分で切り捨て

DATE_TRUNC('second',add_date) -- 精度に'second'を指定し秒で切り捨て

FROM goods

WHERE type_code = '102';

出力結果

SQL, ブログ

SQL：全角文字と半角文字を判定する方法

2022年5月31日管理者コメントする

SQLで全角文字と半角文字を判定するにはLENGTHBやOCTET_LENGTH関数で取得したバイト数とLENGTH関数で取得した文字数を比較することで判断することができます。

使用例

サンプルテーブル「goods」
クエリー（SQL）
ORACLEの場合はOCTET_LENGTHをLENGTHBへ変更すれば同様の結果を得られます。

name列に全角文字が含まれているデータを取得するSQL（PostgreSQL）
PgSQL

SELECT SELECT name FROM goods WHERE name is NOT NULL AND LENGTH(name) <> OCTET_LENGTH(name);

1
2
3
4

SELECT SELECT name
FROM goods
WHERE name is NOT NULL
AND LENGTH(name) <> OCTET_LENGTH(name);
出力結果

補足

1. 判定ロジックの背景と利用ケース

全角文字・半角文字を判定する目的として、データ品質管理（例：ユーザー登録時の文字種チェック）、システム移行時の文字コード整合性確認、レポートや印刷用データの整形などが考えられます。
特にマルチバイト文字（日本語）環境では、１文字あたりのバイト長が異なるため、バイト長と文字数のずれを利用した判定が有効です。
本記事で紹介されているように、例えば PostgreSQL の LENGTH() と OCTET_LENGTH() の比較を使う方法はシンプルでクロスプラットフォームにも応用可能です。

2. 各データベースでの関数違い・注意点

PostgreSQL：文字数を返す LENGTH(text)、バイト長を返す OCTET_LENGTH(text) を使う。
例：WHERE length(col) <> octet_length(col)
Oracle：Oracle には直接 OCTET_LENGTH がないが、LENGTHB() がバイト長を、LENGTH() が文字数を返す（ただしCHAR型／NCHAR型、CHARSET設定によって挙動が異なる）ので、LENGTHB(col) <> LENGTH(col) という書き方が使えます。
MySQL：CHAR_LENGTH(col) が文字数、LENGTH(col) がバイト数を返すので、CHAR_LENGTH(col) <> LENGTH(col) といった判定が可能。
SQL Server：LEN() が文字数（末尾の空白はカウントされない）、DATALENGTH() がバイト長（文字コード依存）を返すので、環境次第では LEN(col) * 2 <> DATALENGTH(col) などと併用するケースがあります。
注意点として、文字コード（UTF-8、UTF‐16、Shift_JIS 等）が環境によって異なると「半角＝1バイト」「全角＝2バイト」という前提が崩れる場合があります。特にUTF-8では日本語全角文字が3バイトある場合もありますので、バイト数の比較ロジックを適用する際は対象の文字コードを意識してください。

3. 実運用的な工夫・パフォーマンス面の注意

大規模テーブルでこのようなチェックを行う場合、たとえば WHERE LENGTH(col) <> OCTET_LENGTH(col) のような関数を大量の行に対して実行するとインデックスが効かず、全表スキャンになる可能性があります。
- 解決策：バッチ処理的に時間帯を分けて実施、あるいはチェック専用にサマリテーブルを用意する。
- また、事前に「文字種／文字コード／想定バイト数」などのデータ仕様を設け、そもそも混在しないように制御するのが望ましいです。
判定だけでなく、誰がいつどのレコードを修正したか（＝トレーサビリティ）を残すなら、更新日時・更新者カラムを活用して「チェック済み／要修正」などのフラグを設けると管理しやすくなります。
将来的に正規表現（REGEXP）や文字列関数（例えば Unicode プロパティを利用した判定）を使って「半角カタカナ」「全角英数字」「漢字のみ」などより細かく制御したい場合も多いため、可能ならその準備もしておくと良いでしょう。

4. よくある誤り・ハマりどころ

前提として「文字数とバイト数が異なる＝全角含む」という仮定をしているため、 半角カタカナ や 絵文字（マルチバイト4バイトなど） が含まれていると誤検知される可能性があります。たとえば、UTF-8環境で絵文字が4バイトなので「文字数１、バイト数４」→「異なる」と判定されてしまう。
文字コード設定の違い：テーブル／カラムごとに異なる照合順序・文字セットが指定されていると、バイト数や文字数の挙動が予想と異なることがあります。例えば MySQL で utf8mb4 を使っているなら日本語全角文字が3バイトではなく4バイトになるケースあり。
更新系の処理で「半角に変換された／全角に変換された」履歴を残していないため、修正したデータが「元はどちらだったか」が分からなくなるという運用上のリスク。必要に応じて「修正前の値」保持やログ出力を検討するべきです。

5. 具体的な応用例・コードスニペット

PostgreSQL での運用例

MySQL での運用例

Oracle での運用例

6. フォローアップ可能な内容・発展トピック

正規表現を用いて「全角ひらがな」「全角カタカナ」「全角英数字」「半角英数字」などを分類・抽出する方法。
文字種に応じて別テーブルへアーカイブ・除外といったワークフロー設計。
BI／レポート用途で「文字種別カウント」を可視化する方法（例：Excel／Tableau／BIツールを用いた文字種分布グラフ化）。
外部システム（CSV／Excel）からデータインポート時に「文字種チェック＋自動整形（全角→半角、半角→全角）」を組み込むETL（Extract-Transform-Load）設計。
将来的に多言語対応を視野に入れた「Unicodeカテゴリ判定」（たとえば、CJK文字・ラテン文字・Emoji など）を含めた文字種チェック。

🎯 本記事のゴール

重複データとは？

手順①：重複データの行番号を振る

実行結果例

手順②：削除対象のみを事前確認

※ 実務では必ずこのチェックを推奨

手順③：重複行を削除

🔒 サブクエリが必要な理由

💡 Delete 実行前の注意点

DBごとの対応表

まとめ

関連記事

中央値を使う場面

1. Oracleで中央値を求める（標準でMEDIAN関数あり）

条件付き

パーティションごと（グループ別）

2. PostgreSQLで中央値を求める

グループ別

3. MySQLで中央値を求める（8.0〜）

方法1：ウィンドウ関数＋ROW_NUMBER

4. SQL Serverで中央値を求める

グループ別

5. SQLiteで中央値を求める

6. DBMS別「中央値の求め方」まとめ表

関連記事

🟦 1. 半角 全角 変換 SQL が必要になる理由

🟦 2. Oracleでの半角 全角 変換 SQL（CONVERT / TRANSLATE）

■ 2-1. 半角 → 全角（カナ）

■ 2-2. 半角 → 全角（英数字）

■ 2-3. 全角 → 半角

🟩 3. SQL Serverでの半角 全角 変換 SQL（TRANSLATE）

■ 3-1. 半角 → 全角

■ 3-2. 全角 → 半角

🟧 4. PostgreSQLでの半角 全角 変換 SQL（translate関数）

■ 4-1. 半角 → 全角

■ 4-2. 全角 → 半角

🟥 5. MySQLでの半角 全角 変換 SQL（REPLACE）

■ 5-1. 半角 → 全角（数字の例）

🟪 6. 実務で使える半角 全角 変換 SQL：関数化例（Oracle）

■ 半角 → 全角

■ 全角 → 半角

🧩 7. 半角 全角 変換 SQL を行う際の注意点

① 濁点・半濁点が合成文字として扱われる

② DB文字コードで変換結果が異なる

③ カナ変換はDBMSによって不得意

✔ 結論：SQLだけで半角⇔全角変換は可能（ただしDB依存）

関連記事

1. インデックスとは？

🔹 例：インデックスなしの検索

🔹 例：インデックスありの検索

2. インデックスの仕組みを理解する

🧩 B-treeインデックス

🧩 ビットマップインデックス（Oracleなど）

3. どんなカラムにインデックスを貼るべきか？

✅ 有効なケース

🚫 不向きなケース

4. 実行計画で確認する

結果例（MySQLの場合）

5. インデックスを使った高速化テクニック

🌟 複合インデックス（複数列）

🌟 カバリングインデックス（Covering Index）

🌟 LIKE検索の最適化

6. 注意点：インデックスの弊害

7. 実践チューニング例

✏️ 例1：検索が遅いクエリ

🩹 改善策

✅ 実行計画の変化

まとめ

🚀 結論

関連記事

1. NOT IN と NOT EXISTS の基本構文

2. 動作の違い（NULLの扱いに注目）

3. 実行結果の比較例

テーブルA

テーブルB

4. パフォーマンスの違い

5. どちらを使うべきか

6. まとめ

関連記事

PIVOTとは？

　関連記事

　関連記事

🟦 1. 半角全角変換 SQL が必要になる理由

🟦 2. Oracleでの半角全角変換 SQL（CONVERT / TRANSLATE）

🟩 3. SQL Serverでの半角全角変換 SQL（TRANSLATE）

🟧 4. PostgreSQLでの半角全角変換 SQL（translate関数）

🟥 5. MySQLでの半角全角変換 SQL（REPLACE）

🟪 6. 実務で使える半角全角変換 SQL：関数化例（Oracle）

🧩 7. 半角全角変換 SQL を行う際の注意点

　関連記事

　関連記事

　関連記事

サンプルデータ

商品月売上

A 1月 100

A 2月 150

B 1月 200

B 2月 180

商品 1月売上 2月売上

A 100 150

B 200 180

DBMS PIVOT対応 UNPIVOT対応代替手段

SQL Server ネイティブネイティブそのまま使用可

Oracle ネイティブネイティブそのまま使用可

PostgreSQL なしなし crosstab関数 / UNION ALL

MySQL なしなし CASE式 + GROUP BY / UNION ALL

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事