Presto クエリーで group by クエリーが返す列の値に最新のレコードの値を返すようにするクエリーを紹介します。

概要

下図のテーブルを例とします。

ProductsA テーブル

id	name	price	category
1	Penguin	500	Bird
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	550	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

一般的にSQLでは、group by を利用すると、group by した列以外の列は選択できません。

select category from ProductsA group by category

select category, price from ProductsA group by category

しかし、実際の利用では特定のフィールドが最大値のレコードの指定した列を取り出したいことがあります。
group byを利用して、category でグループ化しmax_name列に、グループの中で一番Priceの高いレコードのnameを表示したいといった場合があります。先のテーブルでの具体例では下表の結果となるクエリです。

category	max_name
Bird	Penguin
Mammal	Bear

対応方法

一般的なSQLでは対応できませんが Presto であれば、MAX_BYを利用すると、最大の値を抽出できます。

書式

使用例

先のProductsA テーブルの例では下記のPresto クエリとなります。

select category, MAX_BY(name, price) as max_name from ProductsA group by category

ログのテーブルの場合は下記のような利用例もあります。

ログテーブル

id	user_id	user_name	time
1	pen	ぺんた＠眠い	2019/2/4
2	tori	とりーの＠だりー	2019/2/4
3	tori	とりーのまっくす	2019/2/5
4	pen	ぺんた＠おなかいっぱい	2019/2/5
5	pen	ぺんた＠やる気	2019/2/6
6	tori	とりーの	2019/2/6

select user_id, MAX_BY(user_name, time) as user_name_max from my_table group by user_id

最新のアクセス時のユーザー名を取り出せます。

user_id	user_name_max
pen	ぺんた＠やる気
tori	とりーの

補足

SQL Server (Transact SQL)では MAX_BY句はありませんが、同様の処理を記述できます。SQL Server での実行方法に関してはこちらの記事を参照してください。