ユニークなはずのデータが複数ある場合に、重複データを除いて結果を返すSQL（BigQuery）

# テーブルにユニークなはずのデータが複数あったら．．

Google BigQueryのfooテーブルに以下のデータが入っているとします。
設計上はid列がプライマリーキーという前提ですが．．

| id  | rating | title   | comment |
| :-: | :----: | :-----: | :-: |
| 1   | 5      | 最高です | 感動しました |
| 1   | 5      | 最高です | 感動したぜ |
| 2   | 4      | 良いです | 気に入りました |

idでユニークになるはずなのに、なぜかid==1の行が複数入っています．．
しかもcomment列の内容が微妙に違うぞ．．

このテーブルから、idをユニークにしてデータを取り出したいとします。
こんなSQLを書きました。

# id毎に複数のcommentから1つ採用するSQL例

```sql

WITH
    bar AS (
        SELECT
            id,
            ARRAY_AGG(STRUCT(
                rating,
                title,
                comment
              )
              ORDER BY
                comment
              LIMIT 1
              )[OFFSET(0)] AS data
        FROM
            `hoge.hoge.foo`
        GROUP BY
            id
    )
SELECT
    id,
    data.rating AS rating,
    data.title AS title,
    data.comment AS comment
FROM
    bar
ORDER BY
    id
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/d6d02e35-5725-1937-9148-093506aa5c87.png)

このSQLでは、commentは「感動したぜ」が採用され、「感動しました」は捨てられました。

WITH句のSELECT文で行ったことは、
- id列でGROUP BYして、idをユニークにする
- id以外の全ての列を構造体にまとめて、ARRAY_AGG関数で配列にし、その最初の要素を取り出す

# 配列型の列にcommentを全部入れて返すSQL例

```sql

WITH
    bar AS (
        SELECT
            id,
            ARRAY_AGG(STRUCT(
                rating,
                title
              )
              LIMIT 1
              )[OFFSET(0)] AS data,
            ARRAY_AGG(
                comment
              ) AS comments
        FROM
            `hoge.hoge.foo`
        GROUP BY
            id
    )
SELECT
    id,
    data.rating AS rating,
    data.title AS title,
    comments
FROM
    bar
ORDER BY
    id
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/2c7221cf-4be7-8439-0a0c-add4cd020191.png)

どのcommentも捨てたくない場合のSQLです。
commentを全部配列に入れて、commentsという名前の列で返しています。

# データ作成Pythonコード

今回のfooテーブルを作成するにあたり、PythonでAvroファイルを作りましたので、コードを載せておきます。

```py

import traceback
import fastavro
from pathlib import Path

SCHEMA = {
    "type": "record",
    "name": "MyType",
    "fields": [
        {
            "name": "id",
            "type": "long",
        },
        {
            "name": "rating",
            "type": "long",
        },
        {
            "name": "title",
            "type": "string",
        },
        {
            "name": "comment",
            "type": "string",
        }
    ]
}


DATA = [
    {
        "id": 1,
        "rating": 5,
        "title": "最高です",
        "comment": "感動しました"
    },
    {
        "id": 1,
        "rating": 5,
        "title": "最高です",
        "comment": "感動したぜ"
    },
    {
        "id": 2,
        "rating": 4,
        "title": "良いです",
        "comment": "気に入りました"
    }
]


def main():

    out_dir = Path.cwd()
    out_path = out_dir / "foo.avro"

    with open(out_path, "wb") as fo:
        writer = fastavro.write.Writer(fo, SCHEMA)
        for rec in DATA:
            writer.write(rec)
        writer.flush()


if __name__ == "__main__":
    try:
        main()
    except Exception:
        print(traceback.format_exc())
```

# 参考URL（感謝します）
* [BigQuery で ROW_NUMBER(), RANK() を使うな！](https://zenn.dev/smzst/articles/77b598bbbf7a01)