MAX_BYについて

MAX_BYを使うようなケースについて

MAX_BYとは

MAX_BY(カラムx, カラムy)のような関数で、第二引数のカラムが最大のレコードの、第一引数のカラムの値を結果として返すもの

利用背景

GROUP BYで指定したカラム以外は、基本的にどのSQLでもSELECTでそのまま使うことはできない

• GROUP BYしたレコード群の内、どのレコードの値を指定すれば良いか判断できないため
• GROUP BY指定以外のカラムをSELECTに指定したい時に有効

構文

SELECT MAX_BY(cloumn1, cloumn2)

例

下記のような状況があったする

• 3つのカラムの組み合わせが同値である場合、そのレコードを一意とするような方針になった
• 一意のレコードだけが残るテーブルを新たに作るために該当レコード全カラムの値を抽出したい
• 一意とするカラム以外の値はレコードの更新日でGROUP BYした内、値の最も大きいものを採用する

  SELECT MAX_BY(cloumn1, updated_at)
       , MAX_BY(cloumn2, updated_at)
       , MAX_BY(cloumn3, updated_at)
       , cloumn4
       , cloumn5
       , cloumn6
       , MAX_BY(created_at, updated_at)
       , MAX(updated_at)
    FROM not_unique_table
GROUP BY cloumn4
       , cloumn5
       , cloumn6
;

ポイント

• MAX関数とGROUP BY関数を混ぜたようなもの
• prestoとhiveでは使える集約関数
• MySQLやPostgreSQLでは使えない

雑感

一般的?に使いそうなMySQLやPostgresだと使えないけど大量データの集計や加工が必要なケースは今後もたくさんありそうなので覚えといて良さそう