PostgreSQL
Presto

[PosgreSQL][Presto]MAX_BY(MIN_BY)がpostgreで使えなくて困った話

PostgreSQLでPrestoのmax_byを実現しよう。

Prestoでものすごく便利なUDFである、MAX_BYやMIN_BY。
でも、PosgreSQLでは使用することができない。
とはいえ、なんらかの理由からPostgreSQLで同じことをやらないといけないことがある。

データセット

今回の説明に使うデータはuser(ユーザー), log(オウンド訪問ログ)の2つで、以下のようなテーブル。

user

id name
1 A
2 B

log

id url user_id created_at
1 http://xxx.jp/page/1 1 2018-05-09 10:00
2 http://xxx.jp/page/2 1 2018-05-10 11:00
3 http://xxx.jp/page/3 1 2018-05-11 12:00
4 http://xxx.jp/home 2 2018-04-09 13:00

クエリ

各ユーザーの最終訪問ログを取得したいとする。
結果は

id url
1 http://xxx.jp/page/3
2 http://xxx.jp/home

Presto

select
  user.id
  , max_by( log.url, td_time_parse( log.created_at ) ) as url
from
  user
  left outer join
    log
    on  user.id = log.user_id
group by
  user.id

Postgre SQL

select distinct
  user.id
  , first_value( log.url ) over( partition by user.id order by log.created_at desc ) as url
from
  user
  left outer join
    log
    on  user.id = log.user_id

ポイント

  1. ウィンドウ関数を使用すること
  2. distinctを使って重複を省いてあげること(group byはウィンドウ関数に使用できないため)

min_byの場合は、ウィンドウ関数部分のorder byをascにしてあげるなどすればよい。

備考

他にもっとよいやり方ありそうなので募集しています。