GREATEST() と LEAST() と NULL の関数

今日はさらにピンポイントだけど大事なテーマに突っ込むよ：GREATEST()とLEAST()の関数。複数カラムから最大値・最小値をどうやって見つけるか、そして一番大事なのはNULLがどう影響するかを知ろう。

もし人生で一番大事なもの（愛とか、夢の仕事とか、最高のピザレシピとか）を探したことがあるなら、GREATEST()とLEAST()の関数が何のためにあるかすぐピンとくるはず。これらの関数はリストのものの中から一番大きいか一番小さい値を見つけてくれる。ただしピザじゃなくて、PostgreSQLの中で数字や日付や文字列とか色んなデータを扱うんだ。

GREATEST()

GREATEST()は渡されたセットの中で一番大きい値を返すよ。

シンタックス：

GREATEST(value1, value2, ..., valueN)

LEAST()

LEAST()は逆で、一番小さい値を探してくれる。

シンタックス：

LEAST(value1, value2, ..., valueN)

例：

例えば、students_scoresというテーブルがあって、学生の3つの試験の点数が入ってるとする：

GREATEST()とLEAST()の使い方：

SELECT
    student_id,
    GREATEST(exam_1, exam_2, exam_3) AS highest_score,
    LEAST(exam_1, exam_2, exam_3) AS lowest_score
FROM students_scores;

結果：

NULLがGREATEST()とLEAST()にどう影響するか

さて、ここからが一番面白いところ。テーブルの値の中にNULLが混じってることもあるよね。 もう知ってると思うけど、NULLは「データがない」とか「値が不明」っていう謎の存在。 じゃあ、NULLがPostgreSQLのGREATEST()やLEAST()に入ったらどうなるか見てみよう。

NULLのふるまい：

PostgreSQLではGREATEST()とLEAST()はちょっと特別な動きをする：これらの関数は引数の中でNULLを無視して最大・最小を探すんだ。 大事なポイント：唯一NULLが返るのは、全部の引数がNULLのときだけ。

例：

SELECT
    GREATEST(10, 20, NULL, 5) AS greatest_value,
    LEAST(10, 20, NULL, 5) AS least_value;

結果：

見ての通り、NULLは無視されて、関数は(10, 20, 5)の中から最大・最小を返してる。

じゃあ、全部の引数がNULLだったら：

例：

SELECT
    GREATEST(NULL, NULL) AS greatest_nulls,
    LEAST(NULL, NULL) AS least_nulls;

結果：

NULLで困らないためには？

PostgreSQLはデフォでNULLを無視してくれるけど、時には違う動きが欲しいこともあるよね。例えば、NULLを特定の値（例えば0とかデフォ値）として扱いたい場合。そんな時はCOALESCE()関数を使おう。

COALESCE(arg1, arg2, ...)はリストの中で最初にNULLじゃない値を返してくれる。これでGREATEST()やLEAST()に渡す前にNULLを意味ある値に置き換えられる。

例1：NULLを0に置き換える

例えば、点数がNULLなら0点とみなしたい場合。COALESCE()でデフォ値を入れよう。

これが元のテーブル：

クエリ：

SELECT
    student_id,
    GREATEST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS highest_score,
    LEAST(
        COALESCE(exam_1, 0), 
        COALESCE(exam_2, 0), 
        COALESCE(exam_3, 0)
    ) AS lowest_score
FROM students_scores;

結果：

例2：NULLを他のカラムの値に置き換える

時には固定値（例えば0）じゃなくて、他のカラムの値を使いたいこともある。例えばexam_3がなければexam_1の値を使いたい場合。

SELECT
    student_id,
    GREATEST(
        exam_1, 
        exam_2, 
        COALESCE(exam_3, exam_1)
    ) AS highest_score
FROM students_scores;

例えばこんなテーブル：

クエリの結果：

実践ケース

ケース1：最大割引の検索

ordersテーブルで、各注文に3種類の割引がある場合、それぞれの注文で一番大きい割引を探したい。

SELECT
    order_id,
    GREATEST(discount_1, discount_2, discount_3) AS max_discount
FROM orders;

結果：

ケース2：商品の最小価格の検索

productsテーブルには商品価格が3通貨（USD, EUR, GBP）で入ってる。各商品の最小価格を探そう。

SELECT
    product_id,
    LEAST(price_usd, price_eur, price_gbp) AS lowest_price
FROM products;

全部の価格がNULLなら、結果もNULLになるよ

GREATEST()とLEAST()でよくあるミス

ミス1：NULLによる予想外の結果。

このレクチャーの前半で、PostgreSQLでGREATEST()やLEAST()にNULLがどう影響するか詳しく説明したよ。他のDBMSだと（引数にNULLが1つでもあると全部NULLになる）けど、PostgreSQLは違うから、そこを勘違いしやすい。

どうやってミスが起きるか：引数リストにNULLがあると必ずNULLが返ると思い込んで、全部の引数に無駄にCOALESCE()をかけてしまう。これだとクエリが複雑になって遅くなるし、実はNULLは無視されるだけでよかったりする。

ミス2：GREATEST()やLEAST()で型が合わない値を比べる。

GREATEST()やLEAST()は同じデータ型か、暗黙的に変換できる型同士で比べるための関数。全然違う型を比べようとするとエラーになるよ。

どうやってミスが起きるか：データ型が合わないっていうエラーメッセージが出る。