グループごとの最新レコードを取得するSQL

SQL

仕事でグループごとの最新レコードを取得するSQLを書く機会がありました。
グループごとの最新レコードを取得する方法は色々ありますが、今回は分析関数(ROW_NUMBER)を使用してみました。

グループごとの最新レコードを取得する方法

調べてみるとグループごとの最新レコードを取得する方法は色々あるようです。
特に参考になったのは以下です。

集約関数(MAX)やNOT EXISTSを使う方法
同一グループの中で最大のレコードを取得する SQL を書く - TIM Labs

GROUP BYや分析関数(ROW_NUMBER)を使う方法
SQLでグループごとにある最大値の行を取得する - Qiita

分析関数とは

分析関数を使う方法がありましたが、そもそも分析関数に馴染みがなかったので調べました。

分析関数(ROW_NUMBER・RANK・DENSE_RANK)は連番や順位を返すSQL関数です。
JOIN・WHERE・GROUP BY・HAVING句が実行された結果に対して分析関数が実行されるので、分析関数が使える箇所はSELECT・ORDER BY句になります。
分析関数によって取得レコードが集約されることはありません。

分析関数の構文は以下になります。

分析関数 OVER (PARTITION BY 集計単位 ORDER BY 表示順) 

ROW_NUMBER()

1から始まる連番を返します。
同値の場合の順番は不定です。
順番を一定にしたい場合はソート条件にユニークキーを追加する必要があります。

RANK()

1から始まる順位を返します。
同値の場合は同順位で、次の順位は飛ばされます。

DENSE_RANK()

1から始まる順位を返します。
同値の場合は同順位で、次の順位は飛ばされません。

OVER句

分析関数や集約関数で使用できます。
PARTITION BY句で集計単位を指定します。
ORDER BY句でソート順を指定します。
PARTITION BY・ORDER BY句はそれぞれ省略可能で、OVER句自体も省略可能です。

分析関数でSQLを作成してみた

今回作成したSQLの仕様は以下です。

  • グループごとで更新日時が最新のレコードを抽出する
  • グルーピングするキーは２つある
  • 更新日時はユニークではない
  • 更新日時が同値のレコードが複数存在する場合、その中で任意のレコードを抽出する

グループキーが複数あり、最新レコードも複数存在しうるケースだったため、分析関数を使うのが簡単かと思いました。
とういうか、グループごとの最新レコードを取得したい場合は分析関数を使うのが一番楽な気がします。

上記の仕様を実装したSQLのサンプルが以下になります。
データベースはOracleを使用しています。

SELECT
  順位付けしたテーブル.取得したいカラム１
  , 順位付けしたテーブル.取得したいカラム２
  , 順位付けしたテーブル.取得したいカラム３
FROM
  (
    SELECT
      取得したいカラム１
      , 取得したいカラム２
      , 取得したいカラム３
      , ROW_NUMBER() OVER ( PARTITION BY 集計したいカラム１ , 集計したいカラム２ ORDER BY 更新日時 DESC ) AS 順位
    FROM
      対象テーブル
    WHERE
      検索したいカラム１ = '検索条件１'
      AND 検索したいカラム２ = '検索条件２' 
      AND 検索したいカラム３ = '検索条件３' 
  ) 順位付けしたテーブル
WHERE
  順位付けしたテーブル.順位 = 1

参考
ROW_NUMBER,RANK,DENSE_RANK 行番号や順位を返すSQL関数

分析関数 ROW_NUMBER の使用例 - オラクル・Oracleをマスターするための基本と仕組み

SQL PARTITION BYの基本と効率的に集計する便利な方法

SQL - グルーピング, 集約関数
https://creasys.org/rambo/articles/9a3caec10b8c21e45671