Funkcje GREATEST() i LEAST() oraz NULL

Dziś zanurzymy się w jeszcze bardziej specyficzny, ale ważny temat: funkcje GREATEST() i LEAST(). Dowiesz się, jak znaleźć największe i najmniejsze wartości z kilku kolumn i – co najważniejsze – jak NULL wpływa na ich działanie.

Jeśli kiedyś szukałeś najważniejszej rzeczy w swoim życiu (miłości, wymarzonej pracy albo najlepszego przepisu na pizzę), to od razu załapiesz, po co są funkcje GREATEST() i LEAST(). Te funkcje pomagają znaleźć największą lub najmniejszą wartość na liście rzeczy. Tylko zamiast pizzy pracujesz z liczbami, datami, stringami i innymi danymi w PostgreSQL.

GREATEST()

GREATEST() zwraca największą wartość z przekazanego zestawu.

Składnia:

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

LEAST()

LEAST() robi odwrotnie: szuka najmniejszej wartości.

Składnia:

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

Przykład:

Załóżmy, że mamy tabelę students_scores, gdzie trzymamy oceny studentów z trzech egzaminów:

Użycie GREATEST() i 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;

Wynik:

Jak NULL wpływa na GREATEST() i LEAST()

No i teraz robi się najciekawiej. W tabeli razem z wartościami mogą pojawić się też NULL. A jak już wiemy, NULL to taka tajemnicza istota, która oznacza brak danych albo nieznaną wartość. Sprawdźmy, co się dzieje, jeśli NULL trafi do funkcji GREATEST() i LEAST() w PostgreSQL.

Zachowanie NULL:

W PostgreSQL funkcje GREATEST() i LEAST() mają specjalne zachowanie: ignorują wartości NULL przy szukaniu największej lub najmniejszej wartości wśród swoich argumentów. Ważne: Jedyny przypadek, kiedy te funkcje zwrócą NULL, to jeśli wszystkie ich argumenty są NULL.

Przykład:

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

Wynik:

Jak widzisz, NULL został zignorowany i funkcje zwróciły największą i najmniejszą wartość z obecnych (10, 20, 5).

A tu przykład, gdy wszystkie argumenty to NULL:

Przykład:

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

Wynik:

Jak uniknąć problemów z NULL?

Chociaż PostgreSQL domyślnie ignoruje NULL, czasem możesz chcieć inne zachowanie. Na przykład, jeśli chcesz, żeby NULL był traktowany jako konkretna wartość (np. 0 albo coś innego domyślnego) przy określaniu największej/najmniejszej. W takich sytuacjach możesz użyć funkcji COALESCE().

Funkcja COALESCE(arg1, arg2, ...) zwraca pierwszy nie-NULL argument ze swojej listy. Dzięki temu możesz wcześniej podmienić NULL na sensowną wartość przed przekazaniem do GREATEST() albo LEAST().

Przykład 1: Zamiana NULL na 0

Załóżmy, że chcemy uznać brak oceny NULL za 0. Możemy użyć COALESCE(), żeby podstawić wartość domyślną.

Oto nasza wyjściowa tabela:

Zapytanie:

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;

Wynik:

Przykład 2: Zamiana NULL na wartość z innej kolumny

Czasem zamiast stałej wartości (np. 0) trzeba podstawić wartość z innej kolumny. Na przykład, jeśli exam_3 nie ma, chcemy użyć wartości z exam_1.

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

Załóżmy, że mamy taką tabelę:

Wynik działania zapytania:

Praktyczne case'y

Case 1: Szukanie maksymalnej zniżki

Pracujesz z tabelą orders, gdzie każde zamówienie może mieć trzy różne typy zniżek. Musisz znaleźć największą ze wszystkich zniżek dla każdego zamówienia.

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

Wynik:

Case 2: Szukanie minimalnej ceny produktu

W tabeli products trzymane są ceny produktów w trzech walutach (USD, EUR, GBP). Twoje zadanie – znaleźć minimalną cenę dla każdego produktu.

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

Jeśli wszystkie ceny to NULL, wynik też będzie NULL

Typowe błędy przy użyciu GREATEST() i LEAST()

Błąd 1: Nieoczekiwany wynik przez NULL.

Wcześniej w wykładzie dokładnie omówiliśmy, jak NULL wpływa na GREATEST() i LEAST() w PostgreSQL. Główny błąd polega na tym, że użytkownicy przyzwyczajeni do zachowania NULL w innych bazach (gdzie jeden NULL "zatruwa" cały wynik), spodziewają się tego samego po PostgreSQL.

Jak się objawia błąd: Możesz błędnie założyć, że jeśli wśród argumentów jest NULL, funkcja zawsze zwróci NULL. W efekcie możesz niepotrzebnie używać COALESCE() do wszystkich argumentów, co może skomplikować zapytanie i spowolnić jego wykonanie, jeśli w twoim przypadku NULL powinien być po prostu zignorowany.

Błąd 2: Użycie GREATEST() i LEAST() z niekompatybilnymi typami.

Funkcje GREATEST() i LEAST() są przeznaczone do porównywania wartości tego samego typu danych albo typów, które mogą być niejawnie przekonwertowane do siebie. Próba porównania zupełnie różnych, niekompatybilnych typów skończy się błędem.

Jak się objawia błąd: Dostaniesz komunikat o błędzie, wskazujący na niezgodność typów danych.