SQLのパターン集

SQL

SQLで参考になったパターンを集めてみました。

重複レコードの削除

テーブルにレコードが重複していた際に除去するクエリ
自己結合を使って、（論理的に）2つのテーブルで同じカラム値を持つレコードを特定する。
ポイントは、主キーであるidを不等号にすることで別々のレコードを指させておいて、その他のカラムは同じ値をとるという条件にすること。

-- EXISTS句
-- idのみが違い、他のカラムはすべて同じレコードがあったら削除
DELETE FROM tbl AS tbl1 WHERE EXISTS ( SELECT * FROM tbl AS tbl2 WHERE tbl1.id < tbl2.id AND tbl_1.col1 = tbl2.col_1 AND ... AND tbl1.col_n = tbl2.col_n );
-- 極値関数
-- 自分のidと同じカラムで最大のidより小さかったら削除
DELETE FROM tbl AS tbl1 WHERE tbl1.id < (SELECT MAX(tbl2.id) FROM tbl AS tbl2 WHERE tbl1.col_1 = tbl2.col_1 AND ... AND tbl1.col_n = tbl2.col_N );
ランキングの求め方

値段の高いもの順にランキングをつくるクエリ
ランキングの種類には下記がある。

-- 同じ値でも異なるランクとして表示 （ランクの非重複・連番が保証）
SELECT name, price, 
       RANK() OVER (ORDER BY price DESC) AS rank
FROM tbl;

-- 同じ値は同じランク かつ 飛び石しない（ランクの重複・連番が保証）
SELECT name, price, 
       DENSE_RANK() OVER (ORDER BY price DESC) AS rank
FROM tbl;

-- 同じ値は同じランク かつ 飛び石しない（自己非等値結合）
SELECT name, price, 
       (SELECT DISTINCT(COUNT(P2.price)) FROM tbl AS P2 WHERE P2.price > P1.price) +1 AS rank -- P1よりも値段が高いものの個数
FROM tbl AS P1;

-- 同じ値は同じランク かつ 飛び石する（自己非等値結合）
SELECT name, price, 
       (SELECT COUNT(P2.price) FROM tbl AS P2 WHERE P2.price > P1.price) +1 AS rank
FROM tbl AS P1;

-- 同じ値は異なるランク　かつ　飛び石する（これはありえない）
col_1...col_nがすべてNULLのレコードを返す

COALESCE(col1, .., col_n) IS NULLを用いる。
COALESCE(col1, ..., col_n) 述語はcol_1, ..., col_nそれぞれに対して述語を繋げる関数です。

-- COALESCE()はNULLでない最初の列を返すので、全てNULLだったらNULL IS NULLでtrueとなりレコードが取得できる
SELECT * FROM tbl WHERE COALESCE(col1, .., col_n) IS NULL;

-- ∀カラム, カラム IS NULL ⇔ ¬(∃カラム, カラム IS NOT NULL)
-- NOT EXISTSで書き換えられる
SELECT * FROM tbl T1 WHERE NOT EXISTS (SELECT * FROM tbl T2 WHERE T2.id=T1.id AND T2.col_1 IS NOT NULL OR ... OR T2.col_n IS NOT NULL);
col_1...col_nのいずれかがNULLのレコードを返す
-- やりたいこと
SELECT * FROM tbl WHERE col_1 IS NULL OR col_2 IS NULL OR ... OR col_n IS NULL;

-- EXISTSで書く
SELECT * FROM tbl T1 WHERE EXISTS (SELECT * FROM tbl T2 WHERE T2.id=T1.id AND T2.col_1 IS  NULL OR .. OR T2.col_n IS NULL);

-- 二重否定する(ANDで結ぶと一つfalseがあっただけで検索が打ち切られるので早くなる)
SELECT * FROM tbl T1 WHERE NOT EXISTS (SELECT * FROM tbl T2 WHERE T2.id=T1.id AND T2.col_1 IS NOT NULL AND ... AND T2.col_n IS NOT NULL);
連番で欠けがあるかを確認する

MAX()=COUNT()となるか？

-- idは異なる連番?を表す
-- table全体を集合と見なして、集合に対する条件のHAVINGを用いる
SELECT '欠けあり' AS flag  FROM tbl HAVING COUNT(*) <> MAX(id);
連番のうち一番最初の欠損値

1インクリメントしたIDがもとのテーブルに含まれていなかったら、そのIDは欠番
欠番IDの中で最小値を求める。

SELECT MIN(id+1) FROM tbl WHERE id+1 NOT IN (SELECT id FROM tbl);
NULL同士も考慮した同値判定

通常NULLにθ演算子を適用するとUNKNOWNが返る。
「両方がNULL または 両方がNULLではなく等しい」演算を実現するにはIS NOT DISTINCT FROM演算子を使う。
MySQLの場合「<=>」を使う。テーブル同士の比較や相関サブクエリで使える。

<expression 1> IS NOT DISTINCT FROM <expression2>
⇔ (<expression 1> IS NOT NULL AND <expression 2> IS NOT NULL AND <expression 1>= <expression 2>) OR (<expression 1>IS NULL AND <expression 2> IS NULL)

SQLトリックを使って次のようにも書ける。

COALESCE(x, <x,y以外の適当な非NULL値>) = COALESCE(y, <x,y以外の適当な非NULL値>)

x, yが取り得ない任意の非NULL値を使う。例えばx, yが正の整数であったら、-1を使う。
この時、x, yがNULLだったらCOALESCE(NULL, -1)=-1となる。

x y 1 2 NULL
1 1=1⇔TRUE 1=2⇔FALSE 1=-1⇔FALSE
2 2=1⇔FALSE 2=2⇔TRUE 2=-1⇔FALSE
NULL -1=1⇔FALSE -1=2⇔FALSE -1=-1⇔TRUE
欠番である最小値をを求める

値はすべて正の整数であるとする。1から始まる場合と1から始まらない場合があることに注意。
自分のIDに1を加算した値が、存在していない（ID+1）の中で最も小さいものが欠番の最初。

SELECT CASE WHEN(*)=0 OR MIN(id)>1 THEN 1-- 下限が1でない場合は1を返す、レコードが無い場合は1
       ELSE (SELECT MIN(id+1) FROM tbl T1 WHERE NOT EXISTS (SELECT * FROM tbl T2 WHERE T1.id+1=T2.id)) END
FROM tbl T2;
最頻値を求める

GROUP BYでCOUNT()したものが、他のすべてのCOUNT()よりも値が大きければよい。

SELECT prefecture, COUNT(*) AS cnt FROM FromPrefecture GROUP BY prefecture
HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM FromPrefecture GROUP BY prefecture);

「他のすべての値よりも大きい」=「他のすべての値の最大値よりも大きい」という訳でALL()は極値関数で置き換えられる。

SELECT prefecture, COUNT(*) AS cnt FROM FromPrefecture GROUP BY prefecture
HAVING COUNT(*) >= (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM FromPrefecture GROUP BY prefecture) T);
NULLを含まない列を探す

id, prefecture, phone_numberのテーブルを想定する。idとprefectureは非NULLでphone_numberはNULLを含んでいるとする。

SELECT prefecture FROM tbl GROUP BY prefecture HAVING COUNT(*)=COUNT(phone_number);

NULLでない行を1、含む行を0と変換する特性関数を使った解き方

SELECT prefecture FROM tbl GROUP BY prefecture
HAVING COUNT(*)=SUM(CASE phone_number IS NOT NULL THEN 1 ELSE 0 END);
重複があるグループを探す

重複を排除したときの個数と通常の個数が同じか？という条件をつける。
すなわち、HAVING COUNT(col) <> COUNT(DISTINCT col)

SELECT prefecture FROM tbl GROUP BY prefecture HAVING COUNT(phone_number)<>COUNT(DISTINCT phone_number);
差集合の計算
  • NOT EXISTS
  • EXCEPT
  • 外部結合してNULLがないレコードのみを選ぶ