BigQueryでデータの重複チェック！

BigQueryデータマートを作成する際やデータの数値検証をする際によく使うSQLのメモ。
BigQUeryでなくてもいけると思う。

たとえば、下記のようなテーブル「sample」があるとする。カラムはanimalとfavoriteである。

animal	favorite
dog	meat
cat	vegetable
dog	fish
fish	vegetable

様々な処理を施しanimalユニークなテーブルだと想定していたが、上記の表のとおり、animal='dog'のデータが2つある。
このような重複が発生しているデータを取得するSQLは下記である。

WITH
TMP AS
(
    SELECT
        animal
        ,COUNT(*) AS CNT
    FROM
        sample
    GROUP BY
        animal
)

SELECT
    *
FROM
    animal A
WHERE
    EXISTS
        (
            SELECT
                *
            FROM
                TMP B
            WHERE
                A.animal = B.animal
                AND B.CNT >= 2
        )
ORDER BY
    animal
    ,favorite

最後のORDER BYは状況に応じて実装する。
今回でいえば、下記のような結果が出力される。

animal	favorite
dog	fish
dog	meat

ちなみに、複合主キーの場合についても、同様に実装できる。

じゃあ例えば、今回のsampleテーブル（下記、再掲）で、(animal, favorite)の組み合わせが複合主キーだとする。

animal	favorite
dog	meat
cat	vegetable
dog	fish
fish	vegetable

この場合は下記で重複チェックができる。

WITH
TMP AS
(
    SELECT
        animal
        ,favorite
        ,COUNT(*) AS CNT
    FROM
        sample
    GROUP BY
        animal
        ,favorite
)

SELECT
    *
FROM
    animal A
WHERE
    EXISTS
        (
            SELECT
                *
            FROM
                TMP B
            WHERE
                A.animal = B.animal
                AND A.favorite = B.favorite
                AND B.CNT >= 2
        )
ORDER BY
    animal
    ,favorite

この場合は、重複が存在しないので、BigQueryであれば「表示できるデータがありません」のような表示が出てくる。

お終い。