Создание запросов с использованием стандартных функций в PostgreSQL

1. Введение в стандартные функции

Стандартные функции (также известные как встроенные функции) — это предопределенные операции в языке SQL, которые выполняют определенные задачи со значениями данных. Они принимают ноль или более входных аргументов и возвращают одно значение. Использование этих функций может значительно упростить обработку и извлечение данных, позволяя форматировать вывод, выполнять вычисления, обрабатывать значения NULL и извлекать определенные части информации непосредственно в вашем запросе.

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

Зачем использовать стандартные функции?

  • Преобразование данных: Изменение данных из одного формата в другой (например, текст в верхний регистр, округление чисел).
  • Извлечение данных: Получение определенных частей из значения данных (например, год из даты, подстрока из текста).
  • Вычисление: Выполнение математических операций.
  • Условная логика: Реализация простой логики if-then-else внутри запроса.
  • Эффективность: Часто операции выполняются более эффективно в движке базы данных, чем обработка необработанных данных во внешнем приложении.

2. Общие категории стандартных функций

Рассмотрим функции по категориям, используя гипотетические таблицы employees (со столбцами employee_id, first_name, last_name, hire_date, salary) и products (со столбцами product_id, product_name, price, stock_date).

-- Удалить таблицы, если они существуют, чтобы начать с чистого листа (необязательно, использовать с осторожностью)
-- DROP TABLE IF EXISTS products;
-- DROP TABLE IF EXISTS employees;

-- 1. Создание таблицы 'employees'
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,  -- SERIAL автоматически создает автоинкрементное целое число
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),         -- Разрешает NULL, используется в примере COALESCE
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,       -- Добавлено для примера LOWER(), UNIQUE гарантирует отсутствие дубликатов
    hire_date DATE,
    salary NUMERIC(10, 2)            -- Точность 10, Масштаб 2 (например, 12345678.99)
);

-- 2. Вставка образцов данных в таблицу 'employees'
INSERT INTO employees (first_name, middle_name, last_name, email, hire_date, salary) VALUES
('Alice', 'Jane', 'Smith', 'alice.j.smith@company.com', '2020-05-15', 60000.00),
('Bob', NULL, 'Johnson', 'B.Johnson@company.com', '2019-11-01', 75000.50),
('Charlie', 'Robert', 'Williams', 'charlie.w@company.com', '2021-01-20', 55000.00),
('Diana', NULL, 'Brown', 'diana.brown@company.com', '2020-05-15', 62000.75),
('Ethan', 'Michael', 'Davis', 'Ethan.M.Davis@company.com', '2022-08-30', 80000.00),
('Fiona', 'Grace', 'Miller', 'fiona.miller@company.com', '2023-12-10', 50000.00); -- Принят на работу в декабре

-- 3. Создание таблицы 'products'
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    product_code VARCHAR(20) UNIQUE,      -- Добавлено для примера TRIM()
    price NUMERIC(8, 2),                -- Точность 8, Масштаб 2 (например, 123456.78)
    stock_date DATE
);

-- 4. Вставка образцов данных в таблицу 'products'
INSERT INTO products (product_name, product_code, price, stock_date) VALUES
('Laptop Pro 15', ' LP15-001 ', 1499.99, '2023-01-20'), -- Обратите внимание на начальные/конечные пробелы в коде
('Wireless Mouse', 'WM-100A', 25.50, '2023-02-15'),
('Mechanical Keyboard', ' MK-X2000', 120.00, '2023-01-20'), -- Обратите внимание на начальный пробел в коде
('USB-C Hub', 'UCH-05B', 45.95, '2023-03-01'),
('Monitor 27 Inch', 'MON27-QHD ', 350.00, '2022-11-10'), -- Обратите внимание на конечный пробел в коде
('Webcam HD', 'WC-1080P', 75.25, '2023-02-15'),
('External SSD 1TB', 'SSD-EXT1T', 99.99, '2023-04-05');

2.1 Функции работы со строками

Эти функции работают с текстовыми данными (типы VARCHAR, TEXT, CHAR).

  • LENGTH(string): Возвращает количество символов в строке. 
    -- Найти длину фамилии каждого сотрудника
SELECT last_name, LENGTH(last_name) AS name_length
FROM employees;
  • UPPER(string) / LOWER(string): Преобразует строку в верхний или нижний регистр. 
    -- Отобразить названия продуктов в верхнем регистре
SELECT UPPER(product_name) AS upper_product_name
FROM products;

-- Отобразить адреса электронной почты (предполагается наличие столбца 'email') в нижнем регистре
SELECT LOWER(email) AS lower_email
FROM employees;
  • SUBSTRING(string FROM start [FOR length]): Извлекает подстроку. Отсчет позиции начинается с 1. 
    -- Получить первые 3 символа имени сотрудника
SELECT first_name, SUBSTRING(first_name FROM 1 FOR 3) AS first_initials
FROM employees;
  • CONCAT(str1, str2, ...) или оператор ||: Объединяет (соединяет) две или более строки. Оператор || часто предпочтительнее в PostgreSQL из-за его простоты. 
    -- Создать строку с полным именем из имени и фамилии
SELECT first_name, last_name, CONCAT(first_name, ' ', last_name) AS full_name_concat
FROM employees;

-- Использование оператора || (рекомендуется в PostgreSQL)
SELECT first_name, last_name, first_name || ' ' || last_name AS full_name_operator
FROM employees;
  • REPLACE(string, from_substring, to_substring): Заменяет все вхождения подстроки в строке другой подстрокой. 
    -- Заменить пробелы на подчеркивания в названиях продуктов (для примера использования)
SELECT product_name, REPLACE(product_name, ' ', '_') AS underscored_name
FROM products;
  • TRIM([LEADING | TRAILING | BOTH] [characters] FROM string): Удаляет начальные, конечные или оба (начальные и конечные) символа (по умолчанию - пробелы) из строки. 
    -- Предположим, что столбец 'product_code' может содержать начальные/конечные пробелы
SELECT product_code, TRIM(product_code) AS trimmed_code
FROM products;

-- Удалить начальные нули из кода
SELECT '00123', TRIM(LEADING '0' FROM '00123'); -- Возвращает '123'

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

Эти функции выполняют вычисления над числовыми типами данных (INTEGER, NUMERIC, DECIMAL, REAL, DOUBLE PRECISION и т.д.).

  • ABS(number): Возвращает абсолютное (неотрицательное) значение числа. 
    -- Вычислить разницу между зарплатой каждого сотрудника и целевым показателем в $65,000.
-- Затем отобразить абсолютное значение этой разницы, используя ABS().
SELECT
    employee_id,
    first_name,
    salary,
    salary - 65000.00 AS difference_from_target, -- Это может быть отрицательным
    ABS(salary - 65000.00) AS absolute_difference -- Это всегда будет положительным
FROM
    employees;
  • ROUND(number [, decimal_places]): Округляет число до указанного количества знаков после запятой. Если decimal_places опущено, округляет до ближайшего целого числа. 
    -- Округлить цены на товары до 1 знака после запятой
SELECT product_name, price, ROUND(price, 1) AS rounded_price
FROM products;

-- Округлить зарплаты до ближайшей 1000 (используя отрицательное значение decimal_places)
SELECT salary, ROUND(salary, -3) AS rounded_salary_k
FROM employees;
  • CEIL(number) или CEILING(number): Возвращает наименьшее целое число, большее или равное заданному числу (округляет вверх). 
    -- Демонстрация CEIL: Показать цену продукта, округленную ВВЕРХ до ближайшей целой суммы в долларах
SELECT
product_name,
price,
CEIL(price) AS price_rounded_up_dollar
-- Вы также можете использовать CEILING(price), который делает то же самое
FROM products;
  • FLOOR(number): Возвращает наибольшее целое число, меньшее или равное заданному числу (округляет вниз). 
    -- Найти целую часть цены в долларах
SELECT price, FLOOR(price) AS whole_dollars
FROM products;
  • MOD(dividend, divisor) или оператор %: Возвращает остаток от деления (операция взятия по модулю). 
    -- Определить, является ли ID сотрудника четным или нечетным
SELECT employee_id, MOD(employee_id, 2) AS remainder -- 0 для четного, 1 для нечетного
FROM employees;

-- Использование оператора %
SELECT employee_id, employee_id % 2 AS remainder
FROM employees;

2.3 Функции даты/времени

Эти функции работают с типами данных даты и времени (DATE, TIMESTAMP, TIME, INTERVAL).

  • NOW(): Возвращает текущую дату и время (в виде timestamp with time zone).
  • CURRENT_DATE: Возвращает текущую дату (в виде date).
  • CURRENT_TIME: Возвращает текущее время (в виде time with time zone). 
    -- Выбрать текущую дату и время
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;
  • EXTRACT(field FROM source): Извлекает определенную часть (например, год, месяц, день, час, минуту) из значения даты/времени. source может быть столбцом или значением даты/времени, а field может быть YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (день недели, 0=Воскресенье), DOY (день года) и т.д. 
    -- Извлечь год и месяц приема на работу для сотрудников
SELECT hire_date,
       EXTRACT(YEAR FROM hire_date) AS hire_year,
       EXTRACT(MONTH FROM hire_date) AS hire_month
FROM employees;

-- Найти сотрудников, принятых на работу в пятницу (DOW = 5 в PostgreSQL)
SELECT first_name, last_name, hire_date
FROM employees
WHERE EXTRACT(DOW FROM hire_date) = 5;

    Примечание: DATE_PART('field', source) является PostgreSQL-специфическим эквивалентом EXTRACT.

  • AGE(timestamp, timestamp) или AGE(timestamp): Вычисляет интервал между двумя временными метками. Если указан только один аргумент, он вычисляет интервал между аргументом и CURRENT_DATE. Результат имеет тип INTERVAL, часто отображается в годах, месяцах и днях. 
    -- Вычислить стаж каждого сотрудника на сегодняшний день
SELECT first_name, last_name, hire_date, AGE(hire_date) AS tenure
FROM employees;
  • Арифметика с датами: Вы можете выполнять арифметические операции с использованием INTERVAL. 
    -- Найти дату через 90 дней после даты приема на работу
SELECT hire_date, hire_date + INTERVAL '90 day' AS review_date
FROM employees;

-- Найти сотрудников, принятых на работу в течение последних 3 лет
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '3 year';

2.4 Функции преобразования типов

Эти функции преобразуют данные из одного типа в другой.

  • CAST(expression AS type): Стандартный SQL способ выполнения преобразований типов.
  • expression::type: Упрощенный синтаксис PostgreSQL для приведения типов. 
    -- Преобразовать числовую зарплату в текст для конкатенации
SELECT first_name, last_name, 'Salary: ' || CAST(salary AS VARCHAR) AS salary_text
FROM employees;

-- Использование сокращенной записи PostgreSQL
SELECT first_name, last_name, 'Salary: ' || salary::VARCHAR AS salary_text
FROM employees;

-- Преобразовать текстовую строку даты в тип DATE (если формат стандартный)
SELECT CAST('2023-10-26' AS DATE);
    
SELECT '2023-10-26'::DATE;

    Внимание: Убедитесь, что данные совместимы с целевым типом, иначе возникнет ошибка.

2.5 Условные выражения (часто используются как функции)

Обеспечивают условную логику внутри запроса.

  • COALESCE(value1, value2, ...): Возвращает первое значение, не равное NULL, в списке аргументов. Очень полезно для предоставления значений по умолчанию для столбцов, содержащих NULL. 
    -- Отображать 'N/A', если столбец middle_name равен NULL
SELECT first_name, COALESCE(middle_name, 'N/A') AS middle_name_display, last_name
FROM employees; -- Предполагая столбец 'middle_name', допускающий значения NULL
  • NULLIF(value1, value2): Возвращает NULL, если value1 равно value2, в противном случае возвращает value1. Полезно для предотвращения ошибок деления на ноль или обработки определенных значений как NULL. 
    -- Демонстрация NULLIF путем возврата NULL, если цена продукта соответствует определенному значению.
-- Выберите название продукта, первоначальную цену и цену, измененную с помощью NULLIF.
SELECT
    product_name,
    price,
    NULLIF(price, 75.25) AS price_excluding_webcam_value
FROM
    products;

-- Демонстрация NULLIF путем возврата NULL, если зарплата сотрудника соответствует определенному значению.
SELECT
    first_name,
    last_name,
    salary,
    NULLIF(salary, 55000.00) AS salary_excluding_charlie_value
FROM
    employees;
  • CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE else_result] END: Стандартный SQL способ реализации логики if-then-else. 
    -- Категоризация продуктов на основе цены
SELECT product_name, price,
       CASE
           WHEN price < 50.00 THEN 'Budget'
           WHEN price >= 50.00 AND price < 200.00 THEN 'Mid-Range'
           ELSE 'Premium'
       END AS price_category
FROM products;

3. Комбинирование функций

Функции можно вкладывать друг в друга или использовать вместе в выражениях.

-- Получить первую инициаль в верхнем регистре для всех сотрудников
SELECT first_name, UPPER(SUBSTRING(first_name FROM 1 FOR 1)) AS initial
FROM employees;

-- Отобразить год и месяц приема на работу, дополненный ведущим нулем, если месяц < 10
SELECT hire_date,
       EXTRACT(YEAR FROM hire_date) AS hire_year,
       LPAD(CAST(EXTRACT(MONTH FROM hire_date) AS TEXT), 2, '0') AS hire_month_padded -- LPAD добавляет дополнение
FROM employees;
-- Примечание: LPAD - еще одна полезная строковая функция для форматирования.

4. Использование функций в других предложениях

Функции не ограничиваются списком SELECT. Они могут использоваться в WHERE, ORDER BY и иногда в GROUP BY (однако будьте осторожны, не используйте здесь агрегатные функции).

  • В WHERE: 
    -- Найти сотрудников, принятых на работу в декабре (Месяц = 12)
SELECT first_name, last_name, hire_date
FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = 12;

-- Найти продукты, названия которых (в нижнем регистре) начинаются с 'w'
SELECT product_name
FROM products
WHERE LOWER(product_name) LIKE 'w%';
  • В ORDER BY: 
    -- Отсортировать сотрудников по длине их фамилии
SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(last_name) DESC; -- Самые длинные фамилии в начале

-- Отсортировать продукты по последним 3 символам их названия
SELECT product_name
FROM products
ORDER BY SUBSTRING(product_name FROM LENGTH(product_name) - 2);