🤔
BigQueryの重複排除、結局どれ使ってますか？
BigQueryの重複排除で、最近これいいなと思った書き方があります。
SELECT
user_id,
MAX_BY(product, order_date) AS latest_product,
MAX(order_date) AS latest_order_date
FROM orders
GROUP BY user_id;
理由は単純で、書きやすい・読みやすい・速いからです。
重複排除の書き方を比較してみた
ユーザーごとに最新の注文を取得したいとき、どの書き方がいいか3つのパターンで比較してみました。
サンプルデータ
以下のような注文テーブルを例に、ユーザーごとに最新の注文を取得するケースを考えます。
-- orders テーブル
id | user_id | product | price | order_date
1 | alice | iPhone | 1000 | 2024-01-01
2 | alice | iPad | 800 | 2024-01-03 -- 最新
3 | bob | MacBook | 2000 | 2024-01-02 -- 最新
4 | bob | AirPods | 200 | 2024-01-01
5 | carol | iPhone | 1000 | 2024-01-01 -- 最新
この例では、各ユーザー（alice, bob, carol）の最新注文を1件ずつ取得したいとします。
パターン1: ARRAY_AGG
WITH latest_orders AS (
SELECT
ARRAY_AGG(t ORDER BY order_date DESC LIMIT 1)[OFFSET(0)] AS order_info
FROM `project.dataset.orders` AS t
GROUP BY user_id
)
SELECT
order_info.id,
order_info.user_id,
order_info.product,
order_info.price,
order_info.order_date
FROM latest_orders;
感想：
- 配列操作でなんかカッコいい感じ
- でも書くの面倒...
- CTEで長くなって読みにくい
パターン2: QUALIFY + ROW_NUMBER
SELECT
id,
user_id,
product,
price,
order_date
FROM `project.dataset.orders`
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY order_date DESC
) = 1;
感想：
- QUALIFYでスッキリ書けて良い
- SQLっぽくて綺麗
- でも大きなテーブルだと重い
パターン3: MIN_BY/MAX_BY
SELECT
user_id,
MAX_BY(id, order_date) AS id,
MAX_BY(product, order_date) AS product,
MAX_BY(price, order_date) AS price,
MAX(order_date) AS order_date
FROM `project.dataset.orders`
GROUP BY user_id;
感想：
- 一番シンプル
- 実行時間が明らかに速い
- チームの人にも分かりやすい
MIN_BY/MAX_BYの活用例
-- 各ユーザーの最新・最古注文を同時に取得
SELECT
user_id,
MAX_BY(product, order_date) AS latest_product,
MAX(order_date) AS latest_date,
MIN_BY(product, order_date) AS first_product,
MIN(order_date) AS first_date
FROM orders
GROUP BY user_id;
-- 各商品の最安値と最高値の詳細情報
SELECT
product,
MIN(price) AS min_price,
MIN_BY(user_id, price) AS cheapest_buyer,
MAX(price) AS max_price,
MAX_BY(user_id, price) AS highest_payer
FROM orders
GROUP BY product;
実際のパフォーマンス差
同じデータで試してみた時の体感値です：
| 手法 | 書きやすさ | 実行時間 | 課金 | 可読性 |
|---|---|---|---|---|
| ARRAY_AGG | △ | 遅い | 高い | △ |
| QUALIFY + ROW_NUMBER | ○ | 普通 | 普通 | ○ |
| MIN_BY/MAX_BY | ◎ | 速い | 安い | ◎ |
※ データ量やクエリによって変わるので、あくまで個人的な感想です
なんでMIN_BY/MAX_BYが速いんだろう？
これは推測ですが、たぶんBigQueryの内部で集計関数として最適化されてるからだと思います。ウィンドウ関数だと全体をソートしたりする必要があるけど、MIN_BYは「グループごとに最大/最小を見つける」だけなので、効率的に処理できるんじゃないかと。詳しい人がいたら教えてください🙏
どんな時に使い分けてる？
MIN_BY/MAX_BY（90%以上）：
- 普通の重複排除
- 最新/最古レコードの取得
- チームで共有するクエリ
QUALIFY + ROW_NUMBER（たまに）：
- 複雑な条件でランキングしたい時
- レガシーなSQLに合わせる必要がある時
ARRAY_AGG（ほぼ使わない）：
- 正直、もう使ってません...
まとめ
BigQueryで重複排除したいときは、MIN_BY/MAX_BYを使えば間違いありません
✅ 速い: BigQueryで最適化済み
✅ 安い: スキャン量とクエリ実行時間を最小化
✅ 分かりやすい: 読みやすく保守しやすいコード
✅ 安心: 大規模データでも性能が落ちにくい
SELECT
user_id,
MAX_BY(product, order_date) AS latest_product,
MAX_BY(price, order_date) AS latest_price,
MAX(order_date) AS latest_order_date
FROM orders
GROUP BY user_id;
BigQueryの特性を理解して、適切な関数を選ぶことで、速くて安いデータ処理ができるようになります。迷ったときはMIN_BY/MAX_BYを使ってみてください！
Discussion