データ分析をしていると下記のような場面に遭遇する。
- 「売上が最も高い商品の名前が知りたい」
- 「最高年収の従業員がどの部署にいるか調べたい」
- 「各カテゴリで最も人気の商品を特定したい」
従来のSQL文法では、これらの問題を解決するのに複雑なサブクエリやウィンドウ関数を使う必要があった。
しかし、BigQueryのMAX_BY関数を使えば、たった一行でスマートに解決できる。
この記事では、MAX_BYの基本から応用まで、具体例を交えながら分かりやすく解説していく。
目次
MAX_BYとは？
MAX_BY（マックス・バイ）は、BigQueryで使用される集約関数の一つだ。
この関数は、指定した列の値が最大となる行から、別の列の値を取得する際に使用する。
簡単に言うと、「ある条件で最大値を持つ行の、別の列の値を知りたい」というときに便利な関数である。
MAX_BYの基本構文
MAX_BYの基本構文は下記のとおりだ。
|
1 2 |
MAX_BY(取得したい値, 基準となる値) |
- 取得したい値: 結果として返してほしい列
- 基準となる値: 最大値を判定する基準となる列
MAX_BYの使い方
MAX_BYの使い方を具体例で確認していこう。
例1：売上データから最高売上の商品名を取得
以下のような売上テーブルがあるとする：
| product_name | sales_amount |
|---|---|
| スマートフォン | 150000 |
| ノートPC | 120000 |
| タブレット | 80000 |
| イヤホン | 25000 |
最も売上が高い商品名を知りたい場合：
|
1 2 3 4 |
SELECT MAX_BY(product_name, sales_amount) AS top_product FROM sales_table; |
結果：
上記コードではsales_amountの金額が最も大きい product_nameを取得している。
金額が最も大きいスマートフォンを取得できていることがお分かりいただけると思う。
例2：従業員データから最高年収の部署を取得
| employee_name | department | salary |
|---|---|---|
| 田中太郎 | 営業部 | 5000000 |
| 佐藤花子 | IT部 | 6000000 |
| 山田次郎 | 営業部 | 4500000 |
| 鈴木一郎 | IT部 | 5500000 |
最高年収の従業員の部署を知りたい場合：
|
1 2 3 4 5 |
SELECT MAX_BY(department, salary) AS highest_salary_dept FROM employees; |
結果：
上記も同様で、年収が最も高い従業員の「部署」をMAX_BYで取得している。
グループ化と組み合わせた使用例
MAX_BYはGROUP BYと組み合わせることで、各グループ内での最大値を取得できる。
先ほどの具体例のようにシンプルな使い方でデータを確認することもできるが、GROUP BYを使うことでより、MAX_BYを便利に利用することができる。
例：各部署で最高年収の従業員名を取得
|
1 2 3 4 5 6 7 8 9 |
SELECT department ,MAX_BY(employee_name, salary) AS top_employee ,MAX(salary) AS max_salary FROM employees GROUP BY department; |
結果：
今回はGROUP BYで 「department」 (部署）ごとに集計し、その「department」ごとの最高年収の従業員名をMAX_BYで取得している。
MAX_BYと他の関数との比較
MAX関数との違い
MAX関数とMAX_BY関数は、どちらも「最大値」に関係するが、返すものが異なる。
MAX: 指定した列の最大値そのものを返す
|
1 2 3 4 5 |
SELECT MAX(sales_amount) FROM sales_table; -- 結果: 150000 |
使い所：単純に数値や日付などの最大値を知りたいとき。
MAX_BY: 「最大値を持つ行」の別の列の値を返す
|
1 2 3 4 5 6 |
SELECT MAX_BY(product_name, sales_amount) FROM sales_table; -- 結果: スマートフォン |
使い所：「最大値そのもの」ではなく、その最大値に関連する情報（商品名・日付など）を知りたいとき。
使い分けの例
- 「最高売上額を知りたい」 → MAX
- 「最高売上の商品名を知りたい」 → MAX_BY
ポイントとして、MAX関数は1つの列だけを評価するが、MAX_BY関数は「最大値の基準となる列」と「取得したい別の列」という2つの列を指定できる。
そのため、ランキング1位の詳細情報を簡単に取得できるのだ。
MIN_BYとの関係
MAX_BYと真逆の動作をするのがMIN_BY関数だ。
|
1 2 3 4 5 6 7 8 |
-- 最高売上の商品名 SELECT MAX_BY(product_name, sales_amount) AS top_product; -- 最低売上の商品名 SELECT MIN_BY(product_name, sales_amount) AS bottom_product; |
MIN_BYは「ある条件で最小値を持つ行の、別の列の値を知りたい」という場合に使える。
注意点
MAX_BYを使うにあたり、下記の点には注意しておきたい。
1. 同一値が複数存在する場合
基準となる値が同じ場合、どの行が選ばれるかは保証されない。
2. NULL値の扱い
基準となる列にNULL値がある行は無視される。
取得したい値がNULLの場合はNULLが返される。
まとめ
MAX_BY関数は、「最大値を持つ行の別の情報」を簡単に取得できる便利な関数だ。
- 構文:
MAX_BY(取得したい値, 基準となる値) - GROUP BYと組み合わせてグループごとの分析が可能
- MAX関数とは用途が異なる
データ分析の現場では「最高の〜における△△の情報」が必要になることが多い。
MAX_BY関数を使いこなせることで上記データの取得が簡単になったり、クエリをシンプルに書けるようになるはずだ。