ROW_NUMBER() OVER()で重複データの排除

結構おもしろい方法だったので、備忘のため記しておきます。
使用しているDBはOracleです。

前提

たとえば、ITEM_LISTというテーブルからデータを持ってきたいとします。
こんなデータです。

ITEM_CD	ITEM_NAME	ITEM_CATEGORY	CREATE_DATE
01	たぬき	どうぶつ	12/12
02	卵焼き	食べ物	10/01
01	たぬき	どうぶつ	08/13
03	モルモット	どうぶつ	08/13

ITEM_CDとITEM_CATEGORYで、「どうぶつ」カテゴリの「たぬき」が重複しています。
重複データは必要ない。さらにITEM_LISTテーブルにはユニークキーたるIDカラムがないので、IDを振りたい。
そんな用事があるときにROW_NUMBER()とOVER()を使います。

サンプル

SELECT
  ROWNUM ID, -- 行番号はIDと名付ける
  TBL.* -- TBLクエリの全てを取る
FROM
(
    SELECT
        LIST.*, -- LISTテーブルの全データ取得
        ROW_NUMBER() OVER(
            PARTITION BY
                ITEM_CD, --アイテムコード……
                ITEM_CATEGORY -- と、アイテムカテゴリーの重複を許さない
            ORDER BY
                ITEM_CD -- アイテムコード昇順で並べる
        ) RW_NUM
    FROM
        ITEM_LIST LIST
) TBL
WHERE
    TBL.RW_NUM = 1 -- TBLクエリのうち、ROW_NUMBERが1のものだけ抽出。すなわち、重複のうち1番目のものだけ抽出

上のサンプルのOVER()の中では以下のことを指定しています。
ITEM_LISTテーブルを「ITEM_CDとITEM_CATEGORY」の塊で括り、その中でITEM_CDが若い順に並べる。

このルールで作った順位データはRW_NUMと名付けます。
そして、その順位とアイテムデータが組み合わさったサブクエリはTBLと呼びます。

WHERE句にて、重複データを除くためTBLのRW_NUMが1以外のデータはすべて捨てます。
なぜなら2番目以後はすべて重複データだからです。
ORDER BYに入れたいカラムが特にないときはパーティションと同じカラムでも大丈夫です。

最後に、メインとなるクエリでは、TBLクエリのデータを丸ごと取得し、その一行一行にROWNUMを利用してIDを振ります。

結果

先のSQLで取得した結果、このようになるはずです。

ID	ITEM_CD	ITEM_NAME	ITEM_CATEGORY	CREATE_DATE
1	01	たぬき	どうぶつ	12/12
2	02	卵焼き	食べ物	10/01
3	03	モルモット	どうぶつ	08/13

08/13に登録された「たぬき」は重複とみなされて消えています。