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のようなデータが取得できるのではなく、

a

region: 九州, prefecture: 福岡県, city: 福岡市, store_name: ABC商店 
region: 九州, prefecture: 福岡県, city: 福岡市, store_name: DEFストア

bのようなデータが取得できる

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より大きければ抽出するという形になっている。

最後に

ツッコミなどあればぜひお願いします。