重複データからユニークな行を取り出すSQL

開発

昨日のエントリで N対M になるようなテーブルを結合するときは、N対1 にする必要がある旨書いたが、具体的にどうするかまでは書かなかった。

例えば、全銀CSVをそのまま取り込んだ銀行マスタ（銀行コード、銀行名称、銀行支店コード、銀行支店名称）から銀行コードと銀行名称を取るSQLを考える。

この場合、銀行コード、銀行名称は常識的に考えて同じ物が入っていることは明らかであるので、次のように書ける。

SELECT
    GM.銀行コード,
    MIN(GM.銀行名称) AS 銀行名称
FROM
    銀行マスタ GM
GROUP BY
    GM.銀行コード

昨日のエントリで注意したように、銀行名称を GROUP BY に加えてはならない。もし何らかの不具合により同じ銀行コードに対し異なる銀行名称が混在してしまった場合に、トランザクションが重複してしまう。不適切なマスタ名が表示されることとトランザクションが重複することでは後者の方が重大な不具合あるから、絶対に避ける必要がある。

問題は、名称が同じコードに対しある条件で混在する仕様である場合である。例えば、バージョン番号が用意されており、同じコードに対し複数の名称が存在する場合は最新バージョンを表示すること、という仕様の場合は次のように書く必要がある。

SELECT
    GM.銀行コード,
    MIN(GM.銀行名称) AS 銀行名称
FROM
    銀行マスタ GM
WHERE
    GM.バージョン番号 = (
        SELECT
            MAX(GM2.バージョン番号)
        FROM
            銀行マスタ GM2
        WHERE
            GM2.銀行コード = GM.銀行コード
    )
GROUP BY
    GM.銀行コード

ただ、サブクエリーを含むこともあり決して見通しの良いSQLではない。もし Oracle、DB2、SQL Server と言った商用データベースを利用しているのであれば、ウィンドウ関数を使う方がより柔軟で直感的だと思う（ここで使用した ROW_NUMBER 関数はとても有用なので覚えておいた方がよい）。

SELECT
    GM.銀行コード,
    GM.銀行名称
FROM
    (
        SELECT
            GM.銀行コード,
            GM.銀行名称,
            ROW_NUMBER () OVER (
                PARTITION BY
                  GM.銀行コード
                ORDER BY
                  GM.バージョン番号 DESC,
                  GM.銀行支店コード
            ) AS 行番号
        FROM
            銀行マスタ GM
    ) GM
WHERE
    GM.行番号 = 1

ROW_NUMBER関数使用時の注意点は、PARTITION BY と ORDER BY の項目に行を特定するユニークな項目の組み合わせを入れる必要があるということだ。項目が欠けると実行する度に異なる順序が返ってくることになる。