SQL tips 3: 連番での重複削除

特定の観察単位における重複を取り除いた結果を取り出すクエリのご紹介です。
例えば、個人ごとに年に一度だけ発生するイベントのデータを作りたいが、データ上にはエラーや特殊なケースで年に二度以上、発生しうる場合を対処したいときです。

■処理イメージ

■クエリ

WITH event_prep AS(
    SELECT 
        ID
        , date
        , X1
        , ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date ASC) AS num
    FROM event)

SELECT ID, date, X1
    FROM event_prep
    WHERE num = 1

やっていること
・ROW_NUMBER関数で個人ごとに連番（num）を作成する。
・その際、ORDER BY で date が古いものが一番上に来るようにしている（これは場面によって、何をどのように並び変えるかが変わります。）
・WITH句で作ったものから、連番が 1の値を取るデータを取り出す。

POSデータを使って個人ごとに最も大きい購買を集めたデータとかも同様の方法で作れると思います。