DML. Блок WHERE и операторы фильтрации
Дорогу осилит идущийСегодня мы продолжаем знакомство с условиями фильтрации данных в PostgreSQL и рассмотрим более продвинутые, но популярные в типовых задачах, операторы.
LIKE и ILIKE
Операторы с достаточно простым механизмом сопоставления строки с шаблоном.
LIKE
Пример 1:
select * from passenger where first_name like 'Iv%';
Спецсимвол «%» заменяет любую последовательность символов (сюда подходит и несколько разных символов, и последовательность из одного символа и пустая строка).
Таким образом, 'Ivan' подойдет как под шаблон 'Iv%', так и под шабшлоны 'Ivan%', 'I%n' и т.д.
Пример 2:
select * from passenger where first_name like 'I__n';
Спецсимвол «_» заменяет любой одиночный символ. Т.е. на месте первого «_» в шаблоне должен быть какой-то символ, и на месте второго «_» – тоже какой-то символ. Один и тот же или разные – не имеет значения.
Также есть возможность экранирования – обозначения, что «%» или «_» в конкретном шаблоне – это именно символы процента и подчеркивания, а не спецфимволы шаблона. В качестве экранирующего символа по умолчанию используется «\» (как и в Java).
Так, например, шаблон 'I\_\_n' будет корректным только для строки 'I__n'.
Кроме описанных примеров можно ориентироваться на таблицу из документации:
'abc' LIKE 'abc' --true 'abc' LIKE 'a%' --true 'abc' LIKE '_b_' --true 'abc' LIKE 'c' --false
ILIKE
Особенность LIKE заключается в том, что он чувствителен к регистру. Так, шаблон 'i__n' не подойдет для строки 'Ivan'. Если же нужен регистронезависимый вариант – можно использовать оператор ILIKE. Для реальных запросов это может быть спорным решением (почему – разберем в теме «индексы»), но для одиночных запросов – вполне.
Бонус: альтернативно, мы можем добиться регистронезависимости у LIKE с помощью функции lower() – она приводит строку к нижнему регистру. Так, lower(first_name) like lower('I__n') будет эквивалентна first_name ilike 'I__n'. Почему подобный подход имеет смысл – узнаем в следующих уроках.
NOT LIKE и NOT ILIKE
Кроме обозначенных операторов существуют и их варианты с отрицанием: NOT LIKE и NOT ILIKE. Полагаю, очевидно, что использование таких операторов будет возвращать все записи, в которых проверяемая строка НЕ подошла под шаблон.
IN и NOT IN
Еще один полезный оператор позволяет избежать ситуаций вроде
column = 'value1' or column = 'value2' or column = 'valueN'*
Вместо этого мы можем использовать оператор IN со следующим синтаксисом:
column in ('value1', 'value2', 'valueN')
Например:
select * from passenger where first_name in ('Ivan', 'Petr');
select * from passenger where birth_date in ('1990-07-02', '1993-07-02');
Оператор IN подходит для любых типов данных, возвращая true, если результат указанного слева выражения (в наших примерах – колонки first_name и birth_date соответственно) совпадает хотя бы с одним из значений в скобках.
* На самом деле, возможности IN шире, чем просто замена множественных операций сравнения через ИЛИ. Но для демонстарции придется обратиться к подзапросам, с которыми мы еще не знакомы.
Так же, как и в случае с множеством другим операторов, существует оператор с отрицанием: NOT IN. Он будет возвращать true, если значение слева не совпало ни с одним из значений в скобках.
BETWEEN и NOT BETWEEN. BETWEEN SYMMETRIC
Достаточно удобные операторы для проверки вхождения конкретного значения в диапазон (или промежуток).
Пример:
select * from passenger where birth_date between '1990-07-02' and '1993-07-02';
В данном случае оператор AND не объединяет два логических выражения, а является частью выражения BETWEEN, разделяя начальное и конечное значение промежутка. BETWEEN может применяться для любого типа данных.
В такой форме записи оператор аналогичен выражению с «>=» и «<=». Для примера выше:
select * from passenger where birth_date >= '1990-07-02' and birth_date <= '1993-07-02';
Но в ряде ситуаций нас не интересует порядок элементов в диапазоне. Например, первым параметром BETWEEN (до AND) может быть передано большее значение, а вторым – меньшее. В таком случае, ни одно значение не войдет в заявленный диапазон, исходя из логики BETWEEN.
Для задач, в которых описанное поведение некорректно, существует оператор BETWEEN SYMMETRIC. Если применять его, результаты запросов
select * from passenger where birth_date between symmetric '1990-07-02' and '1993-07-02';
и
select * from passenger where birth_date between symmetric '1993-07-02' and '1990-07-02';
будут идентичны. Если же использовать обычный BETWEEN – второй запрос вернет пустую таблицу.
Как и в других описанных операторах, для BETWEEN и BETWEEN SYMMETRIC доступно использование варианта с NOT. В целом, его работа очевидна, но помните: запрос вроде
select * from passenger where birth_date not between '1993-07-02' and '1990-07-02';
Вернет вам все записи таблицы passenger. Ведь ни одна из записей не входят в указанный диапазон, т.е. ВСЕ записи подходят под условие. Для BETWEEN SYMMETRIC такой проблемы, очевидно, нет.
В целом, все описанные вариации имеют право на жизнь и могут быть применены в зависимости от условий задачи.
OVERLAPS
Продолжая работу с диапазонами, SQL предоставляет оператор OVERLAPS, который позволяет проверить, являются ли проверяемые промежутки даты и/или времени пересекающимися:
select * from passenger where (birth_date, now()) overlaps ('1990-07-02'::date, '1993-07-02'::date);
Обратите внимание, в данном запросе используется приведение типов – в SQL оно происходит через оператор «::» и указание типа, к которому необходимо привести. Это может быть необходимо, если тип данных не очевиден или оригинальный тип данных не подходит. Если проводить параллели с Java – это, скорее, похоже на приведение у примитивов, нежели у ссылочных типов.
Итак, в данном запросе мы запрашиваем только те записи, которые в течении жизни (от даты рождения до текущего момента) пересекались с указанным литералами промежутком. Каждый из 4 параметров этого выражения может быть любым выражением – обращение к колонке (birth_date), функции (now()) или литералом ('1990-07-02'). Будь мы знакомы с типом interval чуть ближе – можно было бы использовать и выражения вроде «date + interval».
Рассматриваемый оператор часто бывает полезен, когда таблица содержит колонкци отвечающие за начало и конец какого-то периода. Больничный в поликлинике, сроки отключения горячей воды и т.д. В т.ч. возможны варианты, когда все 4 параметра являются колонками (одной или нескольких разных таблиц).
Отдельно стоит отметить, что при использовании OVERLAPS не имеет значения порядок параметров в диапазоне (параметры, расположенные в одних скобках) – дату конца периода можно располагать и перед, и после даты начала (как при использовании BETWEEN SYMMETRIC).
Вторая важная особенность – обработка пересечения по одной границе. Например, следующие сценарии:
('1989-07-02'::date, '1990-07-02') overlaps ('1990-07-02', '1993-07-02') --false
('1990-07-02'::date, '1990-07-02') overlaps ('1990-07-02', '1993-07-02') --true
Вернут разные результаты. Для сценариев, где диапазоны соприкасаются только одной границей, решающим является размер самих диапазонов.
Если хотя бы у одного из двух диапазонов совпадают даты начала и конца (т.е. диапазон – один момент времени, как первый диапазон из второго примера), то результатом будет true.
Если же оба диапазона имеют отличные даты начала и конца (все остальные диапазоны в примерах выше) – соприкосновение по одной границе не будет считаться пересечением.
В заключение, отмечу, что варианта с NOT для OVERLAPS не предусмотрено. Но можно использовать NOT как самостоятельный оператор отрицания:
not ('1989-07-02'::date, '1990-07-02') overlaps ('1990-07-02', '1993-07-02')
Что дальше?
На самом деле, операторов, которые используются для фильтров, несколько больше. Кроме того, некоторые рассмотренные операторы имеют «близких родственников» - речь о других операторах, которые позволяют решать похожие задачи, но по ряду причин менее популярны, чем уже рассмотренные.
Тем не менее, со временем эти операторы, как и предоставляемые ими возможности, стоит изучить, чтобы не изобретать велосипеды, когда их функциональность понадобится.
Ниже кратко обозначены такие операторы (или группы операторов):
1. Операторы для работы с json/jsonb. Это не совсем операторы фильтрации, но именно они позволяют работать с конкретными полями JSON-объекта в рамках WHERE-блока;
2. Операторы EXIST, ANY, ALL. Эти операторы, в той или иной степени, напоминают разобранный сегодня IN, но имеют более широкую функциональность. Кроме того, они требуют использования подзапросов (вложенного SELECT’а внутри основного запроса), с которыми мы еще не знакомы. Скорее всего, мы кратко затронем эти операторы при знакомстве с подзапросами, но их подробный разбор в рамках курса производить не будем;
3. Запросы с использованием регулярных выражений. Сегодня мы познакомились с самым простым инструментом для сопоставления строки с шаблоном – LIKE (и ILIKE). Кроме него существует механизм сравнения на базе регулярных выражений POSIX – regex’ы, знакомые нам по Java. А также некий промежуточный вариант между LIKE и POSIX regex – оператор SIMILAR TO.
Безусловно, даже это не конец пути. Тема ограничения выборки не ограничена WHERE-блоком и операторами для фильтрации, она плотно связана с другими – от подзапросов и CTE до индексов и плана выполнения. С чем-то из этого мы будем знакомиться, что-то – проговорим тезисно, отдельные вещи – вообще не будем затрагивать в рамках курса. Эффективная работа с фильтрацией записей – обширная тема. Но даже знаний, усвоенных в рамках курса должно с избытком хватить для решения абсолютного большинства типовых задач.
С теорией на сегодня все!
Переходим к практике. Напоминаю, что практика по SQL в рамках курса – символическая, для лучшего закрепления материала стоит воспользоваться любым иным сервисом.
Задача 1
Получите всех пассажиров, у которых имя начинается с буквы «I».
Задача 2
Получите всех пассажиров, у которых имя содержит букву «I» (в любом регистре).
Задача 3
Получите всех пассажиров, у которых первым из любимых аэропортов является Minsk, Moscow или Tallinn.
Задача 4
Найдите всех пассажиров, родившихся в XX веке.
Задача 5
Найдите всех пассажиров, родившихся не в XX веке.
Задача 6
Найдите всех пассажиров, которые застали Вторую мировую войну (1 сентября 1939 – 2 сентября 1945).
Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)
Канал: https://t.me/ViamSupervadetVadens
Мой тг: https://t.me/ironicMotherfucker
Дорогу осилит идущий!