「SQLServer」タグアーカイブ

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, ブログ

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

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

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

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

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

RANKとは？

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

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

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

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

DENSE_RANKとは？

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

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

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

実務での便利な活用例

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

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

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

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

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

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

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でのランキング処理はシーンによって適切に関数を選ぶのがコツです。

🎯 本記事のゴール

重複データとは？

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

実行結果例

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

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

手順③：重複行を削除

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

💡 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依存）

関連記事

RANKとは？

DENSE_RANKとは？

実務での便利な活用例

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

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

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

DBMSごとの違い

Oracle Database

PostgreSQL

MySQL

SQL Server (Microsoft)

まとめ

関連記事

「駑馬十駕」を信念に IT系情報を中心に調べた事をコツコツ綴っています。

　関連記事

　関連記事

🟦 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 を行う際の注意点

　関連記事

　関連記事