Функции 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;

Допустим, у нас есть такая таблица:

Результат работы запроса:

Практические кейсы

Кейc 1: Поиск максимальной скидки

Вы работаете с таблицей orders, где каждый заказ может иметь три разных типа скидки. Вам нужно найти максимальную из всех скидок для каждого заказа.

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

Результат:

Кейc 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() предназначены для сравнения значений одного и того же типа данных или типов, которые могут быть неявно приведены друг к другу. Попытка сравнить значения совершенно разных, несовместимых типов, приведет к ошибке.

Как проявляется ошибка: Вы получите сообщение об ошибке, указывающее на несовместимость типов данных.

2

Задача

SQL SELF, 10 уровень, 2 лекция

Недоступна

Использование `GREATEST()` и `LEAST()`

Использование `GREATEST()` и `LEAST()`

Открыть

2

Задача

SQL SELF, 10 уровень, 2 лекция

Недоступна

Использование `COALESCE()` с `GREATEST()` и `LEAST()`

Использование `COALESCE()` с `GREATEST()` и `LEAST()`

Открыть