rank()王国

おはようございます、ねすたです、よろしくどうぞお願いします。

今回はPrestoSQLの話題として、RANK関数をピックアップしたいと思います。

GROUP BY の限界

そもそもの課題としては、データ抽出依頼を着手していた際に

『ID1＋ID2単位で重複しているレコードの中で、登録日付が一番古いレコードを取得したい』

という処理をクエリで実現しようとしていました。

＜データイメージ①＞

※1番上のレコードのみを取得したい。

上記を満たすため、当初はこういうことをやろうとしていました。

SELECT MIN(regist_date), id_1, id_2 FROM table GROUP BY id_1, id_2;

カラムが上記だけであればこれでもOKなのですが、今回の問題点としては 集計上他にもカラムが存在し、そのカラムは重複しない ということです。

＜データイメージ②＞

上記追加カラムも含めてSQLを組んでみます。

SELECT MIN(regist_date), id_1, id_2, col_1, col_2
FROM table
GROUP BY id_1, id_2;

上記だとグループ化されているカラム数と集計関数をかませていないcol_1, col2に齟齬がありエラーになります。集計関数を使うのは登録日時だけでよいので、グループ化にカラムを追加します。

SELECT MIN(regist_date), id_1, id_2, col_1, col_2
FROM table
GROUP BY id_1, id_2, col_1, col_2;

上記でクエリ上は問題ありません。

ただ、取得結果に問題があり、id_1, id_2, col_1, col_2の4つのカラムに対してグループ化しているので、データイメージ②の状態のままデータが返されてしまいます。

これではやりたいことが実現できないし、本来グループ化はID1＋ID2だけでなければならないので、単純なGROUP BYはやめたほうがいいな、と思いました。

救世主rank()

なんか簡単にできる方法ないかなー、と思いググっていると、RANK関数に出会いました。早速使ってみます。

構文はこちら。SELECT句に入れるだけです。

rank() OVER (PARTITION BY {グループ化カラム（複数可）} ORDER BY {ランク付け対象のカラム} ASC/DESC)

rank()を使う上でのキーポイントは2つ。

■PARTITION BY：どの項目（カラム）を基準にランク付けを行うのかを設定するオプション。複数カラムの場合は「,」区切り。

■ORDER BY：ランク付け対象のカラムの順位ソート。昇順は省略可。

上記を踏まえてクエリを作成してみます。

SELECT regist_date,
       rank() OVER (PARTITION BY id_1, id_2 ORDER BY regist_date) AS rnk,
       id_1,
       id_2,
       col_1,
       col_2
FROM table
;

こうすることで、「ID1 + ID2単位で括った場合での登録日時昇順」でのランク付けが行われます。

＜出力イメージ＞

本来の目的としては登録日付が一番古いレコードを取得することなので、こうします。

SELECT regist_date,
       id_1,
       id_2,
       col_1,
       col_2
FROM
(
  SELECT regist_date,
         rank() OVER (PARTITION BY id_1, id_2 ORDER BY regist_date) AS rnk,
         id_1,
         id_2,
         col_1,
         col_2
  FROM table
)
WHERE rnk = 1
;

これで結果は1レコードのみとなります。

＜出力イメージ＞

やりたかったことが実現できました！！

ちょっと応用

実際では日々大量のデータが登録されているはずですので、集計の際は日別だったり月別であったりと、時間軸を調整しないとダメですよね。

という訳で、上記の応用として、

「対象期間内において2つのIDが重複しているデータの中で、日別で一番古いレコードを取得する」をやってみます。

といってもやり方は一緒で、PARTITION BY オプションに日付単位という考え方を含ませるだけで勝手に実現してくれます。

SELECT SUBSTR(regist_date, 1, 10),
       id_1,
       id_2,
       col_1,
       col_2
FROM
(
  SELECT regist_date,
         rank() OVER (PARTITION BY SUBSTR(regist_date, 1, 10), id_1, id_2 ORDER BY regist_date) AS rnk,
         id_1,
         id_2,
         col_1,
         col_2
  FROM table
)
WHERE rnk = 1
GROUP BY SUBSTR(regist_date, 1, 10)
;

※後付けで申し訳ありませんが、登録日付は文字列型としているためSUBSTRでやっています。DATE型の場合は日付関数を使ってください。

上記を実行すると、その日の一番古いレコードを取得して日別に並べてくれる（日ごとにランク付けしなおしてくれる）のでrank()はめっちゃ有能です。

こちらからは以上です。