複数のカラムの組み合わせが重複しているレコードを取得するSQL(PostgreSQL)

## 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. 実装！

結論としてはこのように書けば得たいものは得られました。

```sql
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 カラム名 演算子 条件値`という構造。

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

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

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

```plaintext: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句の違い](https://dev.classmethod.jp/articles/difference-where-and-having/)）

だから今回の場合は、`region: 九州, prefecture: 福岡県, city: 福岡市`の組み合わせのデータの個数を数えて1より大きければ抽出するという形になっている。

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