Статья основана на 28 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.
Ранее в статье мы уже рассматривали типы данных в PostgreSQL, сегодня узнаем, как с этими типами данных можно работать.
Функции для работы с данными в PostgreSQL хорошо оптимизированы, поэтому работают быстро. Но лично моё мнение, если данные можно обработать на бэкэнде, то лучше передать данную задачу туда. Однако в некоторых случаях гораздо быстрее выполнять обработку непосредственно в базе данных.
При использовании функций важно обращать внимание на то, какие типы они принимают и какие возвращают. Не рекомендуется использовать автоприведение типов, так как возможны сайд эффекты со временем.
Строковые функции
В PostgreSQL масса строковых функций: конкатенация, длина строки, удаление символов, преобразование символов по регистрам и т.д.
Примеры функций:
right(string, n) – достать текст справа. Принимает исходную строку и количество. Возвращает строку, состоящую из указанного количества символов исходной строки, взятых с правой стороны.

reverse(text) – разворот строки. Принимает исходную строку. Возвращает её в обратном порядке.

regexp_replace(string, pattern, replacement, flags) – регулярное выражение. Принимает исходную строку, заменяемую подстроку, замену и флаги. Возвращает получившуюся строку.

С помощью substr() достанем первые три символа строки:
SELECT substr('4951234567',1,3);

Теперь достанем подстроку, начиная с третьего символа, заканчивая номером символа “длина строки – 5”:
SELECT substr(' Eugene ',3,length(' Eugene ')-5);

С помощью trim() уберем лишние пробелы:
SELECT trim(' Евгений ');

Подсчитаем длину строки с помощью length():
SELECT length(' Евгений ');

Числовые функции
Числовых функций в PostgreSQL тоже большое количество.
Примеры функций:
round(v, s) – округление. Принимает число и количество символов. Возвращает округленный вид исходного числа.

abs(num) – модуль. Принимает число. Возвращает модуль исходного числа.

Функции для работы с датой и временем
При работе с датой и временем основная проблема – отличие форматов даты. При выборе функции нужно внимательно смотреть, какой вид даты и времени принимает.

Достанем из даты год с помощью extract():
SELECT EXTRACT(year from date '2024-05-25');

Теперь достанем месяц используя date_part():
SELECT date_part('month','2024-05-25'::TIMESTAMP);

И квартал:
SELECT date_part('quarter','2024-05-25'::TIMESTAMP);

А как поделить год на три части? Такой встроенной функции нет. Поэтому используем набор математических операций.
Вычислим, какой трети года принадлежит указанная дата. Вычтем из номера месяца единицу, разделим на четыре, прибавим единицу и округлим вниз:
SELECT floor((date_part('month','2024-05-25'::TIMESTAMP)-1)/4+1) AS v1;

Другой вариант решения той же задачи. Прибавим к номеру месяца три, разделим на четыре и округлим вниз:
SELECT floor((date_part('month','2024-05-25'::TIMESTAMP)+3)/4)
AS v2;

Также можно просто разделить номер месяца на четыре и округлить вверх:
SELECT ceil((date_part('month','2024-05-25'::TIMESTAMP))/4)
AS v3;

Ещё один вариант получить треть года через оператор CASE:
SELECT CASE
WHEN '2024-05-25' IS NULL THEN 0
WHEN (date_part('month','2024-05-25'::TIMESTAMP))<5 THEN 1
WHEN (date_part('month','2024-05-25'::TIMESTAMP))<9 THEN 2
ELSE 3
END as test2;

Во всех запросах было явно указано приведение типа к дате “::TIMESTAMP” – используя двойное двоеточие.
Если не указать, то PostgreSQL не сможет преобразовать данные сам – автоприведение не сработает:
SELECT date_part(‘month’,’2024-05-25′);

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

Другие функции
Список всех функций для работы с данными
Помимо разобранного выше CASE, существуют и другие условные операторы, например COALESCE.
Создадим таблицу с двумя колонками:
CREATE TABLE tbl (
t1 text,
t2 text
);
Добавим в неё значения:
INSERT INTO tbl VALUES
(null, '2 column'),
('1 column', null),
('1','2'),
(null,null);

Нельзя конкатенировать строку NULL и NOT NULL, так как в результате всё равно получится NULL.
Можно сложить строки через CASE:
SELECT *,
CASE WHEN t1 IS NULL AND t2 IS NULL
THEN 'NULL'
WHEN t1 IS NOT NULL AND t2 IS NOT NULL
THEN t1|| '|' ||t2
WHEN t1 IS NOT NULL
THEN t1
WHEN t2 IS NOT NULL
THEN t2
ELSE 'err'
END case
FROM tbl;

Либо через COALESCE:
SELECT *,
COALESCE(t1||’|’||t2,COALESCE(t1,COALESCE(t2,NULL)))
FROM tbl;

Презентация к статье здесь.
29 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.
Добавить комментарий