[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にしてあげるなどすればよい。

備考

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