Функції GREATEST() і LEAST() та NULL

Сьогодні занурюємось у ще більш специфічну, але важливу тему: функції GREATEST() і LEAST(). Ти дізнаєшся, як знаходити максимальні та мінімальні значення з кількох стовпців, і, найголовніше, як NULL впливає на їхню роботу.

Якщо ти колись шукав(ла) щось найголовніше у своєму житті (кохання, роботу мрії чи найкращий рецепт піци), то одразу зрозумієш, навіщо потрібні функції GREATEST() і LEAST(). Ці функції допомагають знайти найбільше або найменше значення у списку штук. Тільки замість піци ти працюєш з числами, датами, рядками та іншими даними в PostgreSQL.

GREATEST()

GREATEST() повертає найбільше значення з переданого набору.

Синтаксис:

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

LEAST()

LEAST() робить навпаки: вона шукає найменше значення.

Синтаксис:

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

Приклад:

Уявімо, що у нас є таблиця students_scores, де зберігаються оцінки студентів за три екзамени:

Використання 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 потрапляє у функції GREATEST() і LEAST() в PostgreSQL.

Поведінка 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 аргумент зі свого списку. Це дозволяє попередньо замінити NULL на осмислене значення перед передачею у GREATEST() або LEAST().

Приклад 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, де кожне замовлення може мати три різних типи знижки. Треба знайти максимальну з усіх знижок для кожного замовлення.

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

Результат:

Кейс 2: Пошук мінімальної ціни товару

У таблиці products зберігаються ціни товарів у трьох валютах (USD, EUR, GBP). Твоє завдання — знайти мінімальну ціну для кожного товару.

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

Якщо всі ціни NULL, результат теж NULL

Типові помилки при використанні GREATEST() і LEAST()

Помилка 1: Неочікуваний результат через NULL.

Раніше в лекції ми детально розібрали, як NULL впливає на GREATEST() і LEAST() в PostgreSQL. Основна помилка полягає в тому, що користувачі, звиклі до поведінки NULL в інших СУБД (де один NULL "отруює" весь результат), очікують такого ж і від PostgreSQL.

Як проявляється помилка: Ти можеш помилково думати, що якщо у списку аргументів є NULL, функція завжди поверне NULL. В результаті, можеш без потреби застосовувати COALESCE() до всіх аргументів, що може ускладнити запит і сповільнити його виконання, якщо у твоєму сценарії NULL має бути просто проігнорований.

Помилка 2: Використання GREATEST() і LEAST() з несумісними типами.

Функції GREATEST() і LEAST() призначені для порівняння значень одного і того ж типу даних або типів, які можуть бути неявно приведені один до одного. Спроба порівняти значення зовсім різних, несумісних типів, призведе до помилки.

Як проявляється помилка: Ти отримаєш повідомлення про помилку, яке вказує на несумісність типів даних.