今回はGoogle BigQueryにおけるGREATEST関数の使い方を紹介する。
GREATEST関数はPostgreSQLやAmazon Redshift等の様々なRDBMSで使用することが出来る。
ただ、NULLの取り扱いなど挙動が異なる部分があったりするので、BigQueryのGREATEST関数に絞って解説したいと思う。
GREATEST関数とは？
GREATEST関数とは指定した引数の中で最大値を返してくれる関数だ。
「複数列の値を比較して最大値を取得したい時」や「定数（100とか150など）と対象列の値を比較して大きい方を取得したい時」などに便利な関数だ。
例えば、下記データの①sales_1 ②sales_2 ③sales_3カラムの値から、最大値を取得する場合のクエリとその結果は下記の通りだ。
| date | sales_1 | sales_2 | sales_3 |
| 2024-02-01 | 10875 | 15522 | 13930 |
| 2024-02-02 | 13626 | 19399 | 18906 |
| 2024-02-03 | 14203 | 12014 | 19154 |
| 2024-02-04 | 15221 | 17049 | 18333 |
| 2024-02-05 | 19953 | 16711 | 15858 |
| 2024-02-06 | 16881 | 16831 | 16389 |
| 2024-02-07 | 11249 | 14738 | 10247 |
| 2024-02-08 | 12069 | 19624 | 16680 |
| 2024-02-09 | 16176 | 13991 | 14603 |
| 2024-02-10 | 14337 | 15332 | 13864 |
1 2 3 4 5 6 7 8 9 | SELECT date ,sales_1 ,sales_2 ,sales_3 ,GREATEST(sales_1,sales_2,sales_3) AS max_sales FROM `sample_dateset.sample_table` ORDER BY date |
GREATEST関数により、指定した3カラムの各行において、最大値のみを取得し、max_salesで表示していることがお分かりいただけると思う。
このように、GREATEST関数は指定した列の同じ行の値を比較し、最大値を取得したい時に非常に便利だ。
GREATEST関数の使い方
GREATEST関数の使い方はシンプルで、下記を指定して利用する。
- 値を比較したい列（カラム）or 定数
基本的に指定したい列や定数を,（カンマ）で区切って指定するだけだ。
指定するカラム数に特に制限はないが、実務で使用する場合は2~5カラム程度がほとんどだろう。
具体例で使い方を詳しく見ていこう。
列同士の比較
まずは列（カラム）同士を比較する場合の具体例だ。
先ほどと同様に下記データを使う。
| date | sales_1 | sales_2 | sales_3 |
| 2024-02-01 | 10875 | 15522 | 13930 |
| 2024-02-02 | 13626 | 19399 | 18906 |
| 2024-02-03 | 14203 | 12014 | 19154 |
| 2024-02-04 | 15221 | 17049 | 18333 |
| 2024-02-05 | 19953 | 16711 | 15858 |
| 2024-02-06 | 16881 | 16831 | 16389 |
| 2024-02-07 | 11249 | 14738 | 10247 |
| 2024-02-08 | 12069 | 19624 | 16680 |
| 2024-02-09 | 16176 | 13991 | 14603 |
| 2024-02-10 | 14337 | 15332 | 13864 |
上記データの内、①sales_1、②sales_2、③sales_3の3つのカラムを比較し、その内の最大値をmax_salesとして取得するクエリとその具体的な指定の仕方は下記の通りだ。
1 2 3 4 5 6 7 8 9 | SELECT date ,sales_1 ,sales_2 ,sales_3 ,GREATEST(sales_1,sales_2,sales_3) AS max_sales FROM `sample_dataset.sample_table` ORDER BY date |
その結果は先ほどの例と同様で、下記となる。
この例では同じ行における3列（カラム）の値の比較をして最大値を取得してmax_salesで表示している。
このように列同士を比較して最大値を取得するのがGREATEST関数の使い方の1つだ。
列と定数の比較
次に列と定数を比較する使い方の具体例を見ていこう。
今度は2カラムのシンプルなデータを使用する。
| date | sales_1 |
| 2024-02-01 | 10875 |
| 2024-02-02 | 13626 |
| 2024-02-03 | 14203 |
| 2024-02-04 | 15221 |
| 2024-02-05 | 19953 |
| 2024-02-06 | 16881 |
| 2024-02-07 | 11249 |
| 2024-02-08 | 12069 |
| 2024-02-09 | 16176 |
| 2024-02-10 | 14337 |
sales_1カラムの各値と定数（15000）の内、大きい方の値を取得したい場合(max_sales)のGREATEST関数を利用したクエリとその指定方法は下記の通りだ。
1 2 3 4 5 6 7 | SELECT date ,sales_1 ,GREATEST(sales_1,15000) AS max_sales FROM `sample_dataset.sample_table` ORDER BY date |
このクエリの結果は下記の通りだ。
max_salesでは定数で指定した15000とsales_1の値の内、大きい方を取得していることがお分かりいただけると思う。
これが列と定数を比較するGREATEST関数の使い方だ。
「基準値を設定し、その基準値よりも小さい場合は基準値を取得、基準値を超えている場合はそのカラムの値を取得したい時」に便利な使い方だ。
もちろん、2つ以上のカラムを指定し、そのカラムの値と定数を比較することも可能だ。
注意点：値にNULLが含まれる場合
BigQueryでGREATEST関数を使用する際にはNULLの取り扱いに注意する必要がある。
もし、GREATEST関数で指定するカラムの値の中に NULL が含まれている場合、他の値が NULL でなくても、結果は NULL になってしまう。
例えば、下記データでは2024-02-02のsales_2の値がNULLになっている。
| date | sales_1 | sales_2 | sales_3 |
| 2024-02-01 | 10875 | 15522 | 13930 |
| 2024-02-02 | 13626 | null | 18906 |
| 2024-02-03 | 14203 | 12014 | 19154 |
この場合にGREATEST関数を使用して、sale1 , sales_2 , sales_3の中で最大値を取得しようとすると下記結果になる。
sales_2がNULLの行だけ、結果がNULLになっていることがお分かりいただけると思う。
このようにGREATEST関数で指定するカラムにNULLが含まれている場合は、IFNULL関数等でNULLの場合は0を補完する処理を加える必要があることを覚えておきたい。
まとめ
今回紹介したBigQueryにおけるGREATEST関数の要点は下記の通りだ。
- GREATEST関数は指定した引数の中での最大値を返してくれる関数
- 列（カラム）同士の値を比較して大きい方を取得したい時に便利
- 列と定数を比較することも可能
- 指定したカラムの中に NULL が含まれている場合、結果が NULL になる点には要注意
他のBigQueryに関する関数は下記で紹介しているので是非参考にして欲しい。
