- Информация о материале
- Автор: Андрей К.
- Категория: Scripts
- Просмотров: 395
Многие знают, что в PostgreSQL есть тип данных JSONB. Но не все понимают, зачем он нужен в реляционной базе и какие задачи помогает решать.
Сегодня на примере тестовых таблиц интернет-магазина мы разберём:
-
где
JSONBвыигрывает у обычных колонок и массивов; -
как с ним работать эффективно;
-
основные операции: извлечение, фильтрация, обновление.
Исходная структура: категории и товары
У нас есть две таблицы.
Категории — с иерархической структурой (например, «Электроника» → «Смартфоны» → «Apple»).
Товары — с базовыми полями и массивом tags для хранения простых меток:
CREATE TABLE products ( id SERIAL PRIMARY KEY, name TEXT, category_id INT, tags TEXT[] );
Массив tags удобен для хранения простых меток: {'смартфон', 'apple', 'флагман'}.
Когда массивов достаточно (и даже хорошо)
Запрос 1: найти все товары с тегом «apple»
SELECT * FROM products WHERE 'apple' = ANY(tags);
Запрос 2: найти товары с тегами «смартфон» и «apple» одновременно
SELECT * FROM products WHERE tags @> ARRAY['смартфон', 'apple'];
Благодаря GIN-индексу такие запросы выполняются быстро даже на больших объёмах:
CREATE INDEX idx_products_tags ON products USING GIN(tags);
📌 Массивы — отличный инструмент для простых списков строк.
Когда массивов становится недостаточно
Представьте, что для тега «популярный» мы хотим хранить:
-
дату, когда товар стал популярным;
-
вес тега для поисковой выдачи.
Массив строк этого не умеет. Пришлось бы создавать отдельную таблицу product_tags с колонками tag, date, weight. Это усложняет схему и запросы.
Другой пример — характеристики товаров:
-
у смартфонов: процессор, память, объём батареи;
-
у наушников: время работы, тип подключения;
-
у одежды: размер, материал.
В классической реляционной модели это решается через:
-
таблицу атрибутов (
EAV); -
или множество колонок, большинство из которых будут
NULL.
Оба подхода — громоздкие и медленные.
Решение: JSONB
Добавим в таблицу товаров колонку properties типа JSONB:
ALTER TABLE products ADD COLUMN properties JSONB;
Теперь заполним её характеристиками в зависимости от категории.
Для смартфонов
UPDATE products SET properties = '{ "processor": "A17 Bionic", "ram": 8, "storage": 256, "battery": 4000, "water_resistance": "IP68" }' WHERE category_id = 1;
Для наушников
UPDATE products SET properties = '{ "battery_life": 30, "connection_type": "Bluetooth 5.3", "noise_cancelling": true }' WHERE category_id = 2;
Для книг
UPDATE products SET properties = '{ "author": "Лев Толстой", "pages": 1225, "genres": ["роман", "классика"] }' WHERE category_id = 3;
Основные операции с JSONB
1. Извлечение значения (оператор ->>)
SELECT name, properties ->> 'processor' AS cpu, properties ->> 'ram' AS memory FROM products WHERE properties ? 'processor';
2. Фильтрация по вложенному значению
Найти все товары с влагозащитой IP68:
SELECT * FROM products WHERE properties ->> 'water_resistance' = 'IP68';
3. Фильтрация по диапазону чисел
Найти смартфоны с батареей выше 4500 мАч:
SELECT * FROM products WHERE (properties ->> 'battery')::int > 4500;
📌 Обратите внимание на приведение типов: ->> возвращает текст.
Более эффективный вариант для фильтрации — использовать @> или создать индекс по выражению.
4. Поиск внутри массива, хранящегося в JSONB
Найти товары, у которых в жанрах указано «роман»:
SELECT * FROM products WHERE properties -> 'genres' ? 'роман';
Или любой из жанров:
SELECT * FROM products WHERE properties -> 'genres' ?| ARRAY['роман', 'основы'];
Изменение JSONB-данных без перезаписи всего объекта
PostgreSQL позволяет точечно обновлять JSONB.
Добавить новый цвет в массив colors
UPDATE products SET properties = jsonb_set( properties, '{colors}', COALESCE(properties -> 'colors', '[]'::jsonb) || '["черный"]'::jsonb ) WHERE id = 1;
Добавить новое поле
UPDATE products SET properties = properties || '{"warranty_months": 12}'::jsonb WHERE id = 1;
Удалить поле
UPDATE products SET properties = properties - 'warranty_months' WHERE id = 1;
Индексы для JSONB
Для ускорения запросов используйте GIN-индексы:
-- индекс для операторов ?, ?|, ?&, @> CREATE INDEX idx_products_properties ON products USING GIN(properties); -- индекс для конкретного пути (например, battery) CREATE INDEX idx_products_battery ON products ((properties ->> 'battery'));
Итог: когда использовать JSONB, а когда — нет
| Задача | Решение |
|---|---|
| Простой список строк (теги, метки) | TEXT[] + GIN |
| Ключ-значение с разными типами значений | JSONB |
| Динамические атрибуты (характеристики товаров) | JSONB |
| Вложенные структуры (объекты в объектах) | JSONB |
| Данные, которые нужно часто фильтровать и индексировать | JSONB + GIN |
| Строгая схема с фиксированными колонками | Обычные колонки INT, TEXT и т.д. |
Главное правило: JSONB не заменяет реляционную модель, а дополняет её там, где гибкость важнее строгости схемы.
Что мы сегодня разобрали
-
✅ Чем
JSONBотличается от массивов и когда его применять. -
✅ Как добавить
JSONBв существующую таблицу и заполнить данными. -
✅ Основные операции: извлечение, фильтрация, обновление.
-
✅ Индексация для быстрых запросов.
Статья подготовлена по материалам обучающего видео для вашего удобства.
- Информация о материале
- Автор: Андрей К.
- Категория: Scripts
- Просмотров: 598
Группировка данных по временным интервалам — задача, с которой сталкивается каждый разработчик SQL. Будь то «заказы по 15-минуткам», «активность пользователей по часам» или «средний чек за 10 минут» — рано или поздно это нужно сделать.
Раньше, в версиях PostgreSQL до 14, это требовало изворотливости: date_trunc, extract, деление с округлением, снова умножение… А если нужно было сменить шаг с 15 минут на 10 — переписывать половину запроса.
Но начиная с PostgreSQL 14, у нас есть элегантное решение — функция date_bin.
Давайте разберёмся, как она работает и почему она лучше.
Как раньше делали группировку по интервалам
Предположим, у нас есть таблица заказов с полем created_at (тип timestamp). Нужно посчитать количество заказов за каждые 15 минут.
Пример запроса «до date_bin»:
WITH orders AS ( SELECT generate_series( '2024-01-01 00:00:00', '2024-01-01 23:59:59', interval '1 minute' ) AS created_at LIMIT 1000 -- генерируем 1000 случайных заказов ) SELECT date_trunc('hour', created_at) + (extract(minute FROM created_at)::int / 15) * interval '15 minutes' AS bin_start, count(*) FROM orders GROUP BY bin_start ORDER BY bin_start;
Проблемы такого подхода:
-
Код сложно читать.
-
Вычисления происходят для каждой строки.
-
Индексы по полю
created_atиспользуются неэффективно. -
Смена шага (например, на 10 минут) требует правки нескольких мест.
Знакомьтесь: date_bin
В PostgreSQL 14 появилась функция, которая делает то же самое в одну строку.
Синтаксис:
date_bin(stride, source, origin)
Где:
-
stride— шаг (интервал). Например:interval '15 minutes'. -
source— исходная временная метка (timestamp). -
origin— точка отсчёта, от которой начинаются интервалы.
Функция возвращает начало того интервала, в который попадает source.
📌 Простыми словами: date_bin отвечает на вопрос:
«В каком интервале длины N, если считать от точки O, лежит момент времени T?»
Переписываем пример с date_bin
Возьмём тот же набор данных и ту же задачу:
WITH orders AS ( SELECT generate_series( '2024-01-01 00:00:00', '2024-01-01 23:59:59', interval '1 minute' ) AS created_at LIMIT 1000 ) SELECT date_bin(interval '15 minutes', created_at, '2024-01-01 00:00:00') AS bin_start, count(*) FROM orders GROUP BY bin_start ORDER BY bin_start;
✅ Код стал короче, понятнее и быстрее.
Наглядный пример работы
Давайте посмотрим, как date_bin округляет события по 15-минутным интервалам.
WITH events(time) AS (VALUES ('09:03'::time), ('09:07'::time), ('09:12'::time), ('09:19'::time), ('09:27'::time), ('09:33'::time) ) SELECT time, date_bin(interval '15 minutes', time, '09:00'::time) AS bin_start FROM events;
Результат:
| time | bin_start |
|---|---|
| 09:03 | 09:00 |
| 09:07 | 09:00 |
| 09:12 | 09:00 |
| 09:19 | 09:15 |
| 09:27 | 09:15 |
| 09:33 | 09:30 |
Видно, что все события «корректно» попадают в свои 15-минутные корзины.
Важный нюанс: параметр origin
Параметр origin становится критически важным, когда вы работаете не с «полуночным» отсчётом, а с бизнес-часами.
Пример: ваш рабочий день начинается в 9:00. Если в качестве origin указать '2024-01-01 09:00:00', интервалы будут строиться именно от этого момента.
Без этого группировка может «съехать» относительно реальных бизнес-процессов.
Бонус: строим гистограмму прямо в консоли
С помощью date_bin можно быстро построить примитивную гистограмму без выгрузки в Excel.
WITH minutes AS ( SELECT generate_series('09:00'::timestamp, '10:00'::timestamp, interval '1 minute') AS t ), events AS ( SELECT t FROM minutes WHERE random() < 0.3 -- примерно треть событий ) SELECT date_bin(interval '15 minutes', t, '09:00') AS bin_start, count(*) AS cnt, repeat('#', count(*)) AS bar FROM events GROUP BY bin_start ORDER BY bin_start;
Пример вывода:
| bin_start | cnt | bar |
|---|---|---|
| 09:00 | 5 | ##### |
| 09:15 | 7 | ####### |
| 09:30 | 3 | ### |
| 09:45 | 6 | ###### |
Очень удобно для быстрого анализа на месте.
Итог
date_bin — это:
-
✅ Читаемый код
-
✅ Простота смены шага
-
✅ Поддержка индексов
-
✅ Контроль начала интервалов через
origin
Если вы работаете с временными рядами в PostgreSQL, используйте date_bin — это современный, быстрый и правильный подход.
Статья подготовлена по материалам обучающего видео для вашего удобства.
- Информация о материале
- Автор: Андрей К.
- Категория: Scripts
- Просмотров: 1478
Введение
Вы написали функцию в PostgreSQL. Она компилируется без ошибок. Вы запускаете её — и получаете результат. Но что-то не так. Средняя цена подозрительно высокая. Количество товаров — ноль, хотя вы точно знаете, что они есть. Или функция просто возвращает NULL, а вы теряетесь в догадках.
Где ошибка?
Многие разработчики начинают «гадать»: переписывать WHERE, менять JOIN, добавлять COALESCE наугад. Это долго и неэффективно.
Сегодня я покажу вам самый простой, встроенный и не требующий плагинов инструмент отладки в PostgreSQL — RAISE NOTICE.
Это ваш print() или console.log() в мире хранимых процедур. Мы разберем его на реальных данных нашего учебного магазина канцтоваров и электроники.
Что такое RAISE NOTICE?
RAISE NOTICE — это команда языка plpgsql, которая выводит сообщения в консоль клиента (например, вкладка «Messages» в DBeaver или pgAdmin) или в лог-файл сервера PostgreSQL.
Ключевые особенности:
-
Не прерывает выполнение: В отличие от
RAISE EXCEPTION, функция продолжает работать дальше. -
Подстановка значений: Поддерживает плейсхолдеры
%, работающие аналогичноprintfв языке C илиformatв Python. -
Автоматическое приведение типов: PostgreSQL сам преобразует числа, даты, булевы значения и даже массивы в читаемый текст.
Базовый синтаксис
-- Простое сообщение RAISE NOTICE 'Привет, мир!'; -- С подстановкой одного значения RAISE NOTICE 'Цена товара: %', price; -- С подстановкой нескольких значений RAISE NOTICE 'Товар: %, Цена: %, Категория: %', name, price, category_name;
Просто, не правда ли? Но именно эта простота экономит часы отладки. Давайте посмотрим, как это работает на практике.
Пример 1: Базовая отладка функции с агрегацией
Представьте, что мы написали функцию для расчета средней цены в категории. Она принимает category_id и возвращает число. Но иногда возвращает NULL.
Где проблема? То ли в категории нет товаров, то ли все товары неактивны (is_active = false), то ли цены не заполнены?
Добавим RAISE NOTICE на каждом шаге.
Код функции
CREATE OR REPLACE FUNCTION debug_avg_price(p_category_id INTEGER) RETURNS NUMERIC AS $$ DECLARE v_avg_price NUMERIC; v_count INTEGER; BEGIN -- Отладка: выводим входной параметр RAISE NOTICE '=== НАЧАЛО ФУНКЦИИ ==='; RAISE NOTICE 'Начинаем анализ категории: %', p_category_id; -- Основной запрос SELECT COUNT(*), AVG(price) INTO v_count, v_avg_price FROM products WHERE category = p_category_id AND is_active = true; -- Отладка: выводим промежуточные результаты RAISE NOTICE 'Найдено товаров в категории: %', v_count; RAISE NOTICE 'Рассчитанная средняя цена: %', v_avg_price; -- Отладка: выводим предупреждение, если товаров нет IF v_count = 0 THEN RAISE NOTICE 'ВНИМАНИЕ: В категории % нет активных товаров!', p_category_id; END IF; RAISE NOTICE '=== ЗАВЕРШЕНИЕ ФУНКЦИИ ==='; RETURN v_avg_price; END; $$ LANGUAGE plpgsql;
Тестирование
Сценарий А: Категория со смартфонами (ID = 6)
SELECT debug_avg_price(6);
Вывод в консоли (Messages):
NOTICE: === НАЧАЛО ФУНКЦИИ === NOTICE: Начинаем анализ категории: 6 NOTICE: Найдено товаров в категории: 3 NOTICE: Рассчитанная средняя цена: 65323.333333333333 NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===
Сценарий Б: Пустая категория «Телевизоры» (ID = 8)
SELECT debug_avg_price(8);
Вывод в консоли:
NOTICE: === НАЧАЛО ФУНКЦИИ === NOTICE: Начинаем анализ категории: 8 NOTICE: Найдено товаров в категории: 0 NOTICE: Рассчитанная средняя цена: <NULL> NOTICE: ВНИМАНИЕ: В категории 8 нет активных товаров! NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===
Анализ:
Без единого изменения в бизнес-логике мы получили полную картину происходящего внутри функции. Мы видим, что запрос отработал, но вернул ноль строк. Это сразу указывает на проблему с данными, а не с синтаксисом.
Пример 2: Отладка внутри циклов
Ошибки особенно коварны, когда они прячутся внутри циклов. Предположим, функция должна массово обновить цены (например, применить скидку или наценку ко всем товарам категории). Если на пятом товаре цена становится отрицательной или происходит сбой — отследить это без логирования невозможно.
Добавим RAISE NOTICE на каждую итерацию.
Код функции с циклом
CREATE OR REPLACE FUNCTION debug_loop_example(p_category_id INTEGER, p_multiplier NUMERIC) RETURNS INTEGER AS $$ DECLARE v_product RECORD; v_counter INTEGER := 0; v_new_price NUMERIC; BEGIN RAISE NOTICE '=== МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН ==='; RAISE NOTICE 'Категория ID: %, Множитель: %', p_category_id, p_multiplier; FOR v_product IN SELECT product_id, product_name, price FROM products WHERE category = p_category_id AND is_active = true LOOP v_counter := v_counter + 1; v_new_price := ROUND(v_product.price * p_multiplier, 2); -- Отладка: выводим информацию по каждому товару RAISE NOTICE 'Товар #%: %, старая цена=%, новая цена=%', v_counter, v_product.product_name, v_product.price, v_new_price; -- Выполняем обновление UPDATE products SET price = v_new_price, updated_at = CURRENT_TIMESTAMP WHERE product_id = v_product.product_id; -- Отладка: проверяем, что обновилось IF FOUND THEN RAISE NOTICE ' -> Обновление успешно для товара #%', v_counter; ELSE RAISE NOTICE ' -> ОШИБКА: товар #% не обновился!', v_counter; END IF; END LOOP; RAISE NOTICE '=== ИТОГО ОБРАБОТАНО ТОВАРОВ: % ===', v_counter; RETURN v_counter; END; $$ LANGUAGE plpgsql;
Тестирование
Поднимем цены в категории «Смартфоны» на 10%:
SELECT debug_loop_example(6, 1.1);
Вывод в консоли (фрагмент):
NOTICE: === МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН === NOTICE: Категория ID: 6, Множитель: 1.1 NOTICE: Товар #1: iPhone 15 Pro, старая цена=89990, новая цена=98989.00 NOTICE: -> Обновление успешно для товара #1 NOTICE: Товар #2: Samsung Galaxy S24, старая цена=79990, новая цена=87989.00 NOTICE: -> Обновление успешно для товара #2 NOTICE: Товар #3: Xiaomi Redmi Note 13, старая цена=25990, новая цена=28589.00 NOTICE: -> Обновление успешно для товара #3 NOTICE: === ИТОГО ОБРАБОТАНО ТОВАРОВ: 3 ===
Польза:
Теперь мы видим каждую итерацию. Если бы на втором товаре цена вдруг стала NULL или отрицательной (например, из-за ошибки в формуле), мы бы сразу заметили это в логе, не ломая голову над итоговым отчетом.
Пример 3: Сравнение «ДО» и «ПОСЛЕ»
Один из самых эффективных приемов отладки — логировать состояние данных до выполнения операции и сразу после неё. Это особенно полезно при обновлении сложных полей, таких как массивы (TEXT[]) или JSON.
Код функции сравнения тегов
CREATE OR REPLACE FUNCTION debug_compare_before_after(p_product_id INTEGER, p_new_tags TEXT[]) RETURNS BOOLEAN AS $$ DECLARE v_old_tags TEXT[]; v_new_tags TEXT[]; v_product_name TEXT; BEGIN -- Получаем текущее состояние SELECT product_name, tags INTO v_product_name, v_old_tags FROM products WHERE product_id = p_product_id; RAISE NOTICE '=== ОБНОВЛЕНИЕ ТЕГОВ ==='; RAISE NOTICE 'Товар: % (ID: %)', v_product_name, p_product_id; RAISE NOTICE 'ТЕГИ ДО: %', v_old_tags; RAISE NOTICE 'ТЕГИ ПОСЛЕ (планируемые): %', p_new_tags; -- Выполняем обновление UPDATE products SET tags = p_new_tags, updated_at = CURRENT_TIMESTAMP WHERE product_id = p_product_id; -- Проверяем результат SELECT tags INTO v_new_tags FROM products WHERE product_id = p_product_id; RAISE NOTICE 'ТЕГИ ПОСЛЕ (фактические): %', v_new_tags; -- Сравниваем IF v_new_tags = p_new_tags THEN RAISE NOTICE '✅ Обновление успешно: теги совпадают с ожидаемыми'; RETURN TRUE; ELSE RAISE NOTICE '❌ ОШИБКА: теги не совпадают! Ожидали: %, Получили: %', p_new_tags, v_new_tags; RETURN FALSE; END IF; END; $$ LANGUAGE plpgsql;
Тестирование
Обновим теги для iPhone 15 Pro:
SELECT debug_compare_before_after(1, ARRAY['смартфон', 'apple', 'флагман', 'хит']);
Вывод в консоли:
NOTICE: === ОБНОВЛЕНИЕ ТЕГОВ === NOTICE: Товар: iPhone 15 Pro (ID: 1) NOTICE: ТЕГИ ДО: {смартфон,apple,флагман,популярный} NOTICE: ТЕГИ ПОСЛЕ (планируемые): {смартфон,apple,флагман,хит} NOTICE: ТЕГИ ПОСЛЕ (фактические): {смартфон,apple,флагман,хит} NOTICE: ✅ Обновление успешно: теги совпадают с ожидаемыми
Три золотых правила отладки с RAISE NOTICE
Запомните эти простые правила, и любая функция перестанет быть «черным ящиком»:
-
На входе: Всегда выводите входящие параметры. Это помогает понять, с какими данными функция была вызвана на самом деле.
RAISE NOTICE 'Вызов функции my_func с параметрами: p1=%, p2=%', p1, p2; -
После важных запросов: Выводите количество найденных/измененных строк (
ROW_COUNT,FOUND) или ключевые агрегаты (v_avg,v_sum).GET DIAGNOSTICS v_row_count = ROW_COUNT; RAISE NOTICE 'Запрос затронул строк: %', v_row_count;
-
Внутри циклов: Выводите номер итерации и значения переменных, которые меняются. Это спасет вас при обработке 10 000 записей, если ошибка возникает только на 342-й строке.
Заключение
RAISE NOTICE — это не высокотехнологичный дебаггер с точками останова, но это самый надежный и доступный инструмент в арсенале разработчика PostgreSQL. Он не требует установки расширений, работает в любой среде и дает вам «рентгеновское зрение» внутрь ваших хранимых процедур.
В следующий раз, когда ваша функция поведет себя странно, не гадайте — просто добавьте RAISE NOTICE.
Статья подготовлена по материалам обучающего видео для вашего удобства.
- Информация о материале
- Автор: Андрей К.
- Категория: Scripts
- Просмотров: 529
Введение
Вы уже освоили GROUP BY, оконные функции и сложные JOIN. Ваши запросы стали мощными, но... громоздкими. Огромные простыни SQL-кода с вложенными подзапросами становятся нечитаемыми, а при повторном использовании приходится копировать десятки строк.
Знакомая ситуация? Тогда пришло время познакомиться с временными таблицами и научиться упаковывать бизнес-логику в функции PostgreSQL.
Сегодня на примере всё той же базы интернет-магазина (products и categories) мы разберем:
-
Как создавать временные таблицы и зачем они нужны.
-
Как использовать их внутри пользовательских функций.
-
Как анализировать массивы тегов через две временные таблицы.
-
Как ускорять работу через индексы.
-
Как строить иерархические отчеты с рекурсией.
Часть 1. Зачем нужны временные таблицы?
Представьте задачу: нужно собрать отчет по «витрине» — дорогие товары из электроники, но без учета самых дешевых позиций в каждой категории.
Обычный запрос может легко разрастись на 20-30 строк с вложенными WITH, двойными фильтрациями и самоприсоединениями. Читать и отлаживать такое — боль.
Временная таблица (CREATE TEMP TABLE) позволяет разбить задачу на два простых шага:
-
Сначала положить промежуточный результат в «песочницу».
-
Потом работать с ней как с обычной таблицей.
Пример 1: Создаем витрину дорогой электроники
-- Шаг 1. Создаём временную таблицу с дорогой электроникой CREATE TEMP TABLE temp_expensive_electronics AS SELECT p.product_name, p.price, c.categori_name FROM products p JOIN categories c ON p.category = c.categori_id WHERE c.categori_name IN ('Смартфоны', 'Ноутбуки') AND p.price > 50000; -- Шаг 2. Используем её для аналитики SELECT LEFT(product_name, 20) as short_name, price, CASE WHEN price > 100000 THEN 'Флагман' ELSE 'Средний+' END as segment FROM temp_expensive_electronics ORDER BY price DESC;
Что важно знать о CREATE TEMP TABLE:
-
Область видимости: Таблица существует только в рамках вашей текущей сессии. Другие пользователи или даже вы сами из другого окна редактора её не увидят.
-
Время жизни: Автоматически удаляется при отключении от базы данных.
-
Удобство отладки: Если запрос упал с ошибкой, данные во временной таблице остаются. Вы можете спокойно сделать
SELECT * FROM temp_...и посмотреть, на каком этапе данные «поломались».
Часть 2. Временные таблицы внутри функций
Теперь самое интересное — объединим мощь временных таблиц и пользовательских функций. Это позволяет создавать настоящие программные модули прямо в SQL.
Задача: найти ценовые аномалии — товары, которые отличаются от средней цены по своей категории более чем на 30%.
Пример 2: Функция поиска аномалий
Здесь мы не будем дважды сканировать таблицу products. Мы создадим временную таблицу, внутри которой оконной функцией сразу посчитаем среднее, а затем просто отфильтруем её.
CREATE OR REPLACE FUNCTION get_price_anomalies(p_category_id INTEGER) RETURNS TABLE( product_name VARCHAR(255), price NUMERIC, avg_price NUMERIC, deviation_percent NUMERIC ) AS $$ BEGIN -- Временная таблица со статистикой CREATE TEMP TABLE temp_products_with_stats AS SELECT p.product_name::VARCHAR(255) as product_name, p.price, AVG(p.price) OVER (PARTITION BY p.category) as avg_price FROM products p WHERE p.category = p_category_id AND p.is_active = true; -- Возвращаем аномалии RETURN QUERY SELECT t.product_name, t.price, ROUND(t.avg_price, 2), ROUND(ABS(t.price - t.avg_price) / t.avg_price * 100, 1) FROM temp_products_with_stats t WHERE ABS(t.price - t.avg_price) > t.avg_price * 0.3; -- Чистим за собой (хороший тон) DROP TABLE temp_products_with_stats; END; $$ LANGUAGE plpgsql; -- Вызываем функцию для категории "Смартфоны" (ID = 6) SELECT * FROM get_price_anomalies(6);
Ключевые моменты кода:
-
RETURNS TABLE: Позволяет функции возвращать набор строк, к которому можно применитьSELECT * FROM. -
OVER (PARTITION BY...): Мы использовали оконную функцию внутри создания временной таблицы. Это гениальный ход: средняя цена считается на лету, без дополнительного сканирования диска. -
Явное приведение типов (
::VARCHAR(255)): Важный нюанс для тех, кто использует строгую типизацию. Без этого PostgreSQL может ругаться на несоответствиеTEXTиVARCHAR. -
DROP TABLEв конце: Хотя таблица и так умрет после завершения сессии или транзакции, внутри функций, которые вызываются часто, лучше чистить мусор вручную, чтобы не забивать системные каталогиpg_temp.
Часть 3. Высший пилотаж: Две временные таблицы и массивы
У нас в таблице products есть поле tags типа TEXT[] (массив). Это прекрасный способ хранить характеристики, но анализировать массивы напрямую сложно.
Задача: Написать функцию, которая принимает два артикула (sku1 и sku2) и возвращает:
-
Количество общих тегов.
-
Уникальные теги первого товара.
-
Уникальные теги второго товара.
Пример 3: Сравнение тегов через нормализацию
Здесь мы создадим две временные таблицы, «развернув» массивы в строки с помощью unnest.
CREATE OR REPLACE FUNCTION compare_tag_sets(sku1 TEXT, sku2 TEXT) RETURNS TABLE( common_tags BIGINT, unique_tags_1 TEXT[], unique_tags_2 TEXT[] ) AS $$ DECLARE tags1 TEXT[]; tags2 TEXT[]; BEGIN -- Получаем массивы тегов SELECT tags INTO tags1 FROM products WHERE sku = sku1; SELECT tags INTO tags2 FROM products WHERE sku = sku2; -- Создаем две независимые временные таблицы CREATE TEMP TABLE temp_tags1 AS SELECT unnest(tags1) as tag; CREATE TEMP TABLE temp_tags2 AS SELECT unnest(tags2) as tag; -- Считаем метрики RETURN QUERY SELECT (SELECT COUNT(*) FROM temp_tags1 t1 INNER JOIN temp_tags2 t2 ON t1.tag = t2.tag), ARRAY(SELECT tag FROM temp_tags1 EXCEPT SELECT tag FROM temp_tags2), ARRAY(SELECT tag FROM temp_tags2 EXCEPT SELECT tag FROM temp_tags1); DROP TABLE temp_tags1, temp_tags2; END; $$ LANGUAGE plpgsql; -- Сравниваем iPhone 15 Pro и Samsung Galaxy S24 SELECT * FROM compare_tag_sets('IPH15PRO256', 'SGS24U512');
Что мы получили?
Мы превратили сложную задачу по работе с массивами в элементарные операции теории множеств (INNER JOIN и EXCEPT) над обычными таблицами.
Практическое применение: Такую функцию можно легко встроить в блок рекомендаций на сайте: «Товар X имеет 3 общих тега с товаром Y. Возможно, вас заинтересует?».
Часть 4. Производительность: Не забываем про индексы!
Существует опасное заблуждение: «Временная таблица маленькая, индексы ей не нужны». Это правда только если в ней 10 строк. Но что если в категории «Книги» 10 000 товаров, и мы кладем их во временную таблицу, а потом несколько раз фильтруем по цене?
По умолчанию временные таблицы не наследуют индексы исходных таблиц. Без индекса сканирование будет последовательным (Seq Scan).
Пример 4: Ускоряем работу явным индексом
CREATE OR REPLACE FUNCTION fast_category_stats(category_id INTEGER) RETURNS TABLE(price_segment TEXT, count INTEGER, avg_price NUMERIC) AS $$ BEGIN CREATE TEMP TABLE temp_filtered_products AS SELECT product_id, price, product_name FROM products WHERE category = category_id AND is_active = true; -- ВОТ ЭТО ВАЖНО! Создаём индекс для ускорения WHERE и GROUP BY CREATE INDEX idx_temp_price ON temp_filtered_products(price); RETURN QUERY SELECT CASE WHEN price < 10000 THEN 'Бюджет' WHEN price < 50000 THEN 'Средний' ELSE 'Премиум' END, COUNT(*), ROUND(AVG(price), 2) FROM temp_filtered_products GROUP BY 1 ORDER BY MIN(price); DROP TABLE temp_filtered_products; END; $$ LANGUAGE plpgsql;
Совет: Если вы используете временную таблицу для JOIN с другой таблицей или для сложной фильтрации (WHERE price > 1000), всегда оценивайте объем данных. Для 500+ строк индекс на колонках фильтрации даст прирост скорости в 5-10 раз.
Часть 5. Финальный аккорд: Рекурсия + Временная таблица
Соберем все знания вместе и решим реальную бизнес-задачу. Категории у нас иерархические (Электроника -> Смартфоны -> ...). Нужно построить отчет по всей ветке каталога: сколько товаров и какова их суммарная стоимость на каждом уровне вложенности.
Пример 5: Отчет по иерархии
CREATE OR REPLACE FUNCTION get_hierarchy_report(root_category_id INTEGER) RETURNS TABLE( category_name TEXT, level INT, products_count BIGINT, total_value NUMERIC ) AS $$ BEGIN -- Шаг 1. Строим дерево через рекурсивный CTE и сохраняем во временную таблицу CREATE TEMP TABLE temp_category_tree AS WITH RECURSIVE cat_tree AS ( SELECT c.categori_id, c.categori_name, c.level, c.parent_id FROM categories c WHERE c.categori_id = root_category_id UNION ALL SELECT c.categori_id, c.categori_name, c.level, c.parent_id FROM categories c INNER JOIN cat_tree ct ON c.parent_id = ct.categori_id ) SELECT * FROM cat_tree; -- Шаг 2. Джойним временную таблицу с товарами и считаем агрегаты RETURN QUERY SELECT ct.categori_name::TEXT, ct.level, COUNT(p.product_id)::BIGINT, COALESCE(SUM(p.price), 0)::NUMERIC FROM temp_category_tree ct LEFT JOIN products p ON p.category = ct.categori_id GROUP BY ct.categori_name, ct.level ORDER BY ct.level, ct.categori_name; DROP TABLE temp_category_tree; END; $$ LANGUAGE plpgsql; -- Получаем весь отчет по электронике (ID = 1) SELECT * FROM get_hierarchy_report(1);
Разбор решения:
-
WITH RECURSIVE: Строит дерево от корня (root_category_id) до последнего листа. -
CREATE TEMP TABLE ... AS: Мы сохраняем результат рекурсии. Это позволяет не повторять тяжелый рекурсивный обход при каждом обращении к данным. -
LEFT JOIN: Присоединяем товары.LEFTгарантирует, что мы увидим папку «Телевизоры», даже если там пусто.
На выходе — готовая сводка по всему дереву категорий одним вызовом функции.
Заключение
Временные таблицы в PostgreSQL — это не просто «костыль для новичков». Это мощный инструмент архитектуры данных, который позволяет:
-
Упрощать код: Убивать многоэтажные вложенные запросы.
-
Ускорять работу: За счет материализации промежуточных итогов и добавления индексов.
-
Инкапсулировать логику: Создавать чистые функции, которые удобно переиспользовать в отчетах, триггерах и ETL-процессах.
Сегодня мы научились создавать временные таблицы, использовать их в функциях для анализа аномалий, сравнивать массивы тегов, ускорять код индексами и строить иерархические отчеты.
Статья подготовлена по материалам обучающего видео для вашего удобства.