ROW_NUMBERとPARTITION BYで重複データから代表行を抽出する方法

前提

今回扱っているデータベースはPosgreSQLです。

やりたかったこと

今回、TODO画面に表示するカードを取得する処理を改善する対応を行っていました。
カードのデータはviewのデータを条件で絞って取得してくるようになっています。

登場人物は以下のとおりです。

• 担当者（案件を担当している人）
• 申請者（案件内で帳票を承認申請をする人）
• 承認者（案件内で承認依頼を出された人）

申請者は更新権限があれば対象帳票の申請用カードを表示することができます。
承認者は承認権限があれば対象帳票の承認用カードのを表示することができます。
担当者は上記二人に権限がない場合、もしくは上記２人の役割を兼任しているとき、それぞれのカードを表示することができます。

対象viewの構造

viewは作成者（送付側申請者）、送付側承認者、受領側申請者、受領側承認者の4つのviewをUNIOU ALLでくっつけたものになります。

このviewのデータを取得する際は、同じIDの帳票のデータは１つだけ取得されることを期待しています。
そのため、ログインユーザーの今の立場に応じて、どのデータを取得するかを制御して、データを取得します。

発生した問題

本来の申請者から権限が消え、担当者に申請者としてのカードが表示されるようになったとき、
一度に表示される同じIDの帳票カードは1つだけなので、担当者が同時に承認者（今承認の順番が回ってきている状態）の場合は承認者としてのカードのみ表示し、他は全て非表示にしたいです。

しかし、普通にデータを取ってこようとすると、作成者として取ってくるデータと、承認者として取ってくるデータで同じIDのデータが取得できてしまうことがあり、同じidの複数データを取得してきてしまう状況が発生しました。

解決策

今回は取得されたデータに対して、以下の方法で対応しました。

1. 同じ帳票IDを持つものでまとめる（PARTITION BY）
2. グループ内で順位をつける（ROW_NUMBER()）
3. 帳票のデータが承認用だった場合、そのデータを優先して並べ替える（ORDER BY）
4. レコードの中で先頭の１件を取得する

SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER(
                PARTITION BY id,
                user_id
                ORDER BY
                    CASE
                        WHEN is_approval = TRUE THEN 1
                        ELSE 2
                    END
            ) as rn
        FROM
            updaters
    ) AS todo
WHERE
    rn = 1;

このようにすることで、帳票IDが重複していた場合に承認用のデータを優先したデータを１件だけ取得することができます。

ROW_NUMBERとPARTITION BYでグループ分けをする方法

ROW_NUMBER()とは？

ROW_NUMBER()は、指定した条件に基づいて行番号を付与する関数です。
通常はデータの並び順に関する番号が振られます。（番号は1からスタート）
今回のケースではここがウィンドウ関数です。

PARTITION BYとは？

PARTITION BYはOVER句の中で使用されるオプションの一つです。
PARTITION BYへ渡した値ごとにグループを分けることができます。
同じidを持つものをまとめたいときに便利です。

これら２つを用いることで、重複している帳票ID単位でデータをグループ化することができ、その中で順序をつけることができます。

ORDER BY CASEを使って優先度をつける方法

ROW_NUMBERとPARTITION BYを使ってグループ分けをすることはできましたが、
このままだと承認用ではないデータが優先されて取得されてしまう可能性があります。

そこで使うのがORDER BYです。

ORDER BYとは？

このORDER BYは先ほど出てきたOVER句の中で使用されるオプションの一つです。
これを使うことにより、先ほどグループ化したデータに対して順序を指定することができます。

今回はここでCASE文を使い、承認用であることを示すフラグ(is_approval)がtrueなら1を、falseなら2を返すようにしています。

CASE
    WHEN is_approval = TRUE THEN 1
    ELSE 2
END

これにより、重複する帳票データがあった場合に、もし承認用のデータが含まれていれば、そのデータが優先して先頭に返されます。

その後、外側のSELECT文で取得する際にrn=1を指定することで先頭のデータ１件のみを取得することができます。

このように、ORDER BYでCASE WHENを使うと重複したデータの中で優先度を決めてデータを取得することができます。

id がユニークな場合の意味のなさ、冗長なSQLになってしまう例の紹介

今回の対応は、同じidのものが複数取得されるケースについて取り扱いました。
同じようなケースであれば今回の対応は役に立ちますが、idがユニークキーで重複する可能性のないコードではグループ化したとしてもすでに１件になるため、冗長な処理になる可能性があります。
ですので、今回の対応は重複を排除する際に使うのがおすすめです。

さいごに

元々SQLには明るくない自覚があったのですが、今回の対応を経て、わかっていると思っている書き方や読み方の部分でもあいまいな理解になっていたんだなぁと認識する機会がとても多くありました。

今後は改めてSQLのことを学び直すことで理解を深めるとともに、SQLの修正等に携われる機会があれば積極的に携わって理解を深めていきたいです。

参考