Базовый курс SQL. Функции() для обработки данных. TRIM, DATE_PART, SQRT

[к оглавлению]

Функции в SQL, как и в других языках программирования, помогают выполнять часто реализуемые операции. Например, с помощью функции RTRIM() можно "обрезать" лишние пробелы, справа от значения столбца. Как это сделать, мы рассмотрели на предыдущем уроке.

Очень удобно то, что типовые задачи уже реализованы, и нет необходимости повторять их на стороне клиентского приложения, однако используя функции в SQL, можно столкнуться с ощутимой проблемой. В отличии от универсальных операторов (SELECT, WHERE, FROM), функции в различных СУБД имеют разные названия для аналогичных действий.

Например,

• Получить часть строки.
  • В PostgreSQL, Oracle, SQLite - SUBSTR()
  • В MySQL, MariaDB, SQL Server - SUBSTRING()
• Преобразовать тип данных.
  • В PostgreSQL, DB2 - CAST()
  • В MySQL, MariaDB, SQL Server - CONVERT()
• Получить текущую дату.
  • В PostgreSQL - CURRENT_DATE
  • В MySQL, MariaDB - CURDATE()
  • В Oracle - SYSDATE
  • В SQLite - DATE()

Что это значит?

• Во-первых, то, что следует каждый раз уточнять синтаксис для своей СУБД
• Во-вторых, то, что код становится менее переносимым

Переносимый код - код, который продолжит корректно работать при изменении СУБД. Это необходимо при переносе проекта в новое окружение, который повлечёт принудительную смену СУБД.

Многие программисты стараются не использовать функции по причине снижения переносимости, но это всегда менее удачно с точки зрения производительности. Решение зависит от вас: тут нет правильного или неправильного выбора. Если вы используете функции в своём коде, просто добавляйте комментарии о том, что должен делать данный код, чтобы сэкономить время и нервы разработчика, его поддерживающего.

Типы функций

1. Текстовые. Изменение регистра, отсечение или добавление пробелов
2. Числовые. Простые и более сложные математические операции: возведение в степень, извлечение корня
3. Даты и времени. Определение разницы во времени, корректности даты
4. Системные. Получение информации о пользователе БД и др

Обычно функции применяются после операторов SELECT или WHERE, но и не только. Вы поймёте это, рассматривая примеры данного и следующего урока.

Текстовые функции

В предыдущем уроке мы с вами уже познакомились с функциями удаления лишних пробелов, TRIM(). Следующая функция - UPPER().

SELECT teacher_surname, UPPER(teacher_surname) AS upped_teacher_surname
FROM Teachers
ORDER BY teacher_surname;

teacher_surname   |    upped_teacher_surname
-------------------------------------------------------
Донских           |         ДОНСКИХ
Игнатьева         |         ИГНАТЬЕВА
Колесникова       |         КОЛЕСНИКОВА
Потапова          |         ПОТАПОВА
Ростовская        |         РОСТОВСКАЯ
Ростовский        |         РОСТОВСКИЙ
Смирнов           |         СМИРНОВ
Спрут             |         СПРУТ

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

Добавим в нашу копилку ещё несколько часто используемых функций:

• LTRIM() и RTRIM() -- Удаляют пробелы в левой / правой части строки
• LEFT() и RIGTH() -- Возвращают символы из левой / правой части строки
• LENGTH() или DATALENGTH() или LEN() -- Возвращает длину строки
• LOWER() или LCASE() -- Преобразует строку в нижний регистр
• UPPER() или UCASE() -- Преобразует строку в верхний регистр
• SOUNDEX() -- Возвращает значение SOUNDEX строки

SOUNDEX - это алгоритм, преобразующий текст в буквенно-цифровой шаблон, который описывает его фонетическое представление. Иными словами, функция SOUNDEX() найдёт значения, созвучные предложенному. К сожалению, она не поддерживается в PostgreSQL, но, например, в MySQL запрос бы выглядел так:

SELECT teacher_id, teacher_surname
FROM Teachers
WHERE SOUNDEX(teacher_surname) = SOUNDEX('Игнатева');

 teacher_id  |  teacher_surname
-----------------------------------------
 778        |    Игнатьева

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

Функции для даты и времени

Дата и время хранятся в таблицах в соответствующем типе данных. Это позволяет эффективно сортировать, фильтровать а также хранить значения. Но в таком виде их невозможно использовать в приложениях, поэтому для преобразования приходится использовать специальные функции.

Функции даты и времени одни из наиболее важных в SQL, но и наименее согласованные между СУБД, а значит, снижают совместимость кода.

Рассмотрим на примерах разных СУБД, как можно извлечь часть даты.

PostgreSQL. Получим список конкурсов, проводившихся в 2020 году:

SELECT contest_name, contest_date
FROM Contests
WHERE DATE_PART('year', contest_date) = '2020';

Вывод:

contest_name                      |   contest_date
-------------------------------------------------------------
Фотовыставка Технологии будущего  |    2020-10-25

Данная функция принимает 2 аргумента: первый указывает, какой именно фрагмент даты необходимо извлечь, а второй - поле, в котором производится поиск значения. Т.е. константа 'year' говорит СУБД о том, что нам требуется только год из столбца contest_date.

В MySQL и MariaDB это же можно сделать ещё проще, т.к. у них есть более узкая функция YEAR(), позволяющая получить год, а не всю дату:

SELECT contest_name, contest_date
FROM Contests
WHERE YEAR(contest_date) = '2020';

В Oracle придётся, напротив, скомбинировать 2 функции:

SELECT contest_name, contest_date
FROM Contests
WHERE to_number(to_char(contest_date, 'YYYY')) = 2020;

Здесь to_char() используется чтобы извлечь лишь часть даты, а to_number() - чтобы преобразовать полученное значение в число, которое можно будет сравнить с 2020.
Или можно воспользоваться смекалкой и оператором BETWEEN, который мы рассматривали в главе 5:

SELECT contest_name, contest_date
FROM Contests
WHERE contest_date BETWEEN to_date('01-01-2020') AND to_date('12-31-2020');

Также в примере выше была использована функция to_date(), преобразующая строку в формат даты и времени, чтобы сравнение производилось между одинаковыми типами данных.

SQLite предполагает такой формат:

SELECT contest_name, contest_date
FROM Contests
WHERE strftime('%Y', contest_date) = 2020;

---
Функций для работы с датами гораздо больше, чем описано в этом уроке. Можно выполнять и сравнение дат, и арифметические операции, форматировать даты и др. Но, как упоминалось выше, их синтаксис может существенно различаться. За подробностями обращайтесь к документации вашей СУБД.

Числовые функции

В основном это функции для выполнения сложных алгебраических и тригонометрических вычислений. Вот список наиболее популярных:

• ABS() -- Модуль числа
• SQRT() -- Корень квадратный
• SIN() -- Синус угла
• COS() -- Косинус угла
• TAN() -- Тангенс угла
• EXP() -- Экспонента числа
• PI() -- Число π

Итак, мы познакомились с некоторыми функциями, используемыми SQL. Далее мы рассмотрим, какие функции применяются к результатам запроса.

Следующая страница. Функции() для обработки результатов запроса.
Оглавление.