1. やりたいこと
複数のカラムを条件に重複しているレコードを取得するSQLを書きたい。
例として店舗を管理するstoresテーブルがあってregion, prefecture, cityが重複しているレコードを探したいとする。
例えば、以下のようなレコードを抽出したいのであって、
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: ABC商店
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: DEFストア
以下のようなレコードは抽出の対象外。
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: ABC商店
region: 九州, prefecture: 福岡県, city: 北九州市, store_name: HIJマート
いくつか記事のアイディアを試したのだけど、うまくいかなくて最後は自力で紐解いたのでメモ。
巷の記事は「これでうまくいく」だけで解説とかなくて、僕も備忘録と記事数稼ぐだけならそれで終わりたいけど、中身をきちんと理解して自分の血肉にしたいので、説明を付けるように努める。
2. 実装！
結論としてはこのように書けば得たいものは得られました。
SELECT
region,
prefecture,
city,
store_name
FROM
stores
WHERE
(region, prefecture, city) in(
SELECT
region, prefecture, city FROM stores
WHERE
region = stores.region
AND prefecture = stores.prefecturew
AND city = stores.city
GROUP BY
region, prefecture, city,
HAVING
count(*) > 1)
ORDER BY
region,
prefecture,
city,
store_name
ざっくり日本語訳
storesテーブルから今評価しているregionと同じregionの値を持つもの（prefecture, cityも同様に）という条件で、region, prefecture, cityの組み合わせが同じものでまとめてね。その中でデータの個数が1より大きいものを抽出してね。その抽出されたものからregion, prefecture, cityのカラムを取得してね。サブクエリで抽出できたデータとregion, prefecture, cityが同じ組み合わせという条件で抽出してね。その抽出したデータのregion, prefecture, city, store_nameを取得してね。並び順は、まずregionで、それからprefecture, city, store_nameの順で昇順にしてね。
3. 解説
解説というか自分の理解があやふやなところの整理。
3-1. サブクエリ
WHERE句の中のin()はサブクエリ。
サブクエリとは？
通常、WHERE句に使う条件としては、数値や文字列といった何らかの値を利用しますが、サブクエリを利用することにより、SELECT文の結果を条件として利用できます。
「さらっと覚えるSQL＆T-SQL入門（7）SELECT文の総仕上げはサブクエリ」
https://atmarkit.itmedia.co.jp/ait/articles/0708/29/news118.html
なんか見た目は派手ですが、よーくやるWHERE region = "九州"というやつと構造的には全く同じぽい。WHERE カラム名 演算子 条件値という構造。
WHERE カラム名 演算子 条件値
WHERE region = "九州" -- よくやるやつ
WHERE region IN ( SELECT region from stores ...) -- subquery（サブクエリ）
INについて
これは演算子ですね。サブクエリじゃなくても使うやつと同じかな？WHERE region IN ('九州', '関東')みたいなやつと。
一応サブクエリの章内で、ドキュメントがありました。
expression IN (subquery)
右辺は括弧で括られた副問い合わせで、正確に１列を返すものでなければなりません。 左辺式は評価され、副問い合わせの結果行と比較されます。 副問い合わせの行のどれかと等しい場合、INの結果は"true（真）"です。 （副問い合わせが行を返さない場合を含め）等しい行が見つからない場合、結果は"false（偽）"です。
「PostgreSQL 9.4.5文書」
第 9章関数と演算子
9.22. 副問い合わせ式
9.22.2. IN
https://www.postgresql.jp/document/9.4/html/functions-subquery.html
今回のSQLでやっていることはregion, prefecture, cityの組み合わせでグルーピングした時に、count(*)の結果が１より大きいもの、つまり重複しているものの組み合わせの一覧をくれる。ここでは重複しているレコードを全て取得できるわけではない。あくまで組み合わせの一覧。何を言っているかというと、つまり、aのようなデータが取得できるのではなく、
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: ABC商店
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: DEFストア
bのようなデータが取得できる
region: 九州, prefecture: 福岡県, city: 福岡市
3-2. サブクエリの中のWHEREのregion = stores.regionってやつ
これは左辺が今評価しているレコードのカラムregion、右辺がstoresの全レコードのregionだと思う。なので今評価しているレコードのregionとその全レコードのregionを比較してTRUE, FALSEを返してくれているのだと思う。
3-3. GROUP BYに渡すカラムを複数にするとどうなるのか
渡されたカラム全ての組み合わせでグループを作ってくれる。
SQLの実行順序としては以下のはずなので、
FROM
ON, JOIN
WHERE
GROUP BY
COUNT, SUM, AVG, MIN
HAVING
SELECT, DISTINCT
ORDER BY
LIMIT
3-2で解説したWHERE句でregionの中、prefectureの中、cityの中、それぞれの中で重複しているものが選択されて、そのレコードに対してGROUP BYが発動し、region, prefecture, cityの組み合わせが同じもので統一されるのだと思う。
3-4. HAVINGを理解する
上のGROUP BYで得られた結果に対して条件を与えて抽出を行う。
WHEREと似ているが、上のSQLの実行順序（WHERE→GROUP BY→HAVINGの順）を見てもわかる通りで、HAVINGはGROUP BYでグルーピングされてから条件を与えて抽出を行う。なので、GROUP BYがない場合、結果はWHEREと同じになるらしい。（参考： [SQL] Where句とHaving句の違い）
だから今回の場合は、region: 九州, prefecture: 福岡県, city: 福岡市の組み合わせのデータの個数を数えて1より大きければ抽出するという形になっている。
最後に
ツッコミなどあればぜひお願いします。