Расширенные возможности PostgreSQL: функции для упрощения разработки

-- Расчет рангов с учетом ties
SELECT 
 name,
 salary,
 RANK() OVER (ORDER BY salary DESC) as salary_rank,
 DENSE_RANK() OVER (ORDER BY salary DESC) as dense_salary_rank,
 PERCENT_RANK() OVER (ORDER BY salary DESC) as salary_percentile,
 ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

-- Скользящее средние продаж за последние 3 месяца
SELECT 
 date,
 sales,
 AVG(sales) OVER (
 ORDER BY date 
 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 ) as moving_avg_3months
FROM sales_data;

-- Рекурсивный CTE для построения иерархии категорий
WITH RECURSIVE category_tree AS (
 SELECT id, name, parent_id, 0 AS depth
 FROM categories WHERE id = 1 -- Начинаем с корня
 UNION ALL
 SELECT c.id, c.name, c.parent_id, ct.depth + 1
 FROM categories c
 JOIN category_tree ct ON c.parent_id = ct.id
 WHERE ct.depth < 10 -- Предотвращаем бесконечные циклы
)
SELECT * FROM category_tree ORDER BY depth, name;

-- PostgreSQL 12+: Управление материализацией
WITH expensive_calculation AS MATERIALIZED (
 -- Принудительная материализация (вычисляется один раз)
 SELECT id, complex_function(data) AS result
 FROM big_table
)
SELECT * FROM expensive_calculation WHERE result > 100;

WITH simple_filter AS NOT MATERIALIZED (
 -- Встраивание в основной запрос (может быть оптимизировано лучше)
 SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM simple_filter WHERE created_at > '2025-01-01';

-- Создание пространственного индекса
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

-- Поиск объектов в радиусе 10 км
SELECT id, name, ST_Distance(geom, ST_MakePoint(30.3, 59.9)::geography) as distance
FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(30.3, 59.9)::geography, 10000)
ORDER BY distance;

-- Пересечение полигонов
SELECT a.id, b.id
FROM polygons a, polygons b
WHERE ST_Intersects(a.geom, b.geom);

-- Создание гипертаблицы
CREATE TABLE metrics (
 time TIMESTAMPTZ NOT NULL,
 value DOUBLE PRECISION,
 device_id INTEGER
);
SELECT create_hypertable('metrics', 'time');

-- Агрегация данных за час
SELECT 
 time_bucket('1 hour', time) as bucket,
 AVG(value) as avg_value,
 COUNT(*) as count
FROM metrics
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY bucket
ORDER BY bucket;

-- Запрос с непрерывными временными интервалами
SELECT 
 time_bucket('1 hour', time) as bucket,
 device_id,
 FIRST(value, time) as first_value,
 LAST(value, time) as last_value,
 MAX(value) - MIN(value) as range_value
FROM metrics
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY bucket, device_id;

-- Хеширование паролей
SELECT crypt('mypasswd', gen_salt('bf'));

-- Шифрование данных
SELECT encrypt('sensitive data', 'secret_key', 'aes');

-- Генерация случайных данных
SELECT gen_random_uuid();
SELECT gen_random_bytes(16);

-- Создание триграммного индекса
CREATE INDEX idx_trgm ON products USING gin (name gin_trgm_ops);

-- Поиск похожих названий
SELECT name FROM products 
WHERE name % 'apple' -- Поиск по вхождению
ORDER BY similarity(name, 'apple') DESC;

-- Индексация JSON
CREATE INDEX idx_json_data ON documents USING gin (data jsonb_path_ops);

-- Запросы по JSON без парсинга в приложении
SELECT * FROM documents 
WHERE data @? '$.metadata.priority == "high"';

-- Создание и работа с массивами
SELECT ARRAY[1, 2, 3] || 4; -- Добавление элемента
SELECT unnest(ARRAY[1, 2, 3]); -- "Распаковка" массива
SELECT array_remove(ARRAY[1, 2, 3, 2], 2); -- Удаление элементов
SELECT array_to_string(ARRAY['a', 'b', 'c'], ', '); -- Преобразование в строку

-- Агрегация результатов в массив
SELECT user_id, ARRAY_AGG(product_id) as purchased_products
FROM orders
GROUP BY user_id;

-- Проверка наличия элемента в массиве
SELECT * FROM documents 
WHERE tags @> 'important'::text[]; -- Содержит
SELECT * FROM documents 
WHERE tags <@ ['tag1', 'tag2']::text[]; -- Содержится в

-- Индексация JSON полей
CREATE INDEX idx_json_data ON documents USING gin (data jsonb_path_ops);

-- Запросы по JSON
SELECT * FROM documents 
WHERE data->>'status' = 'published';
SELECT * FROM documents 
WHERE data @> '{"metadata": {"priority": "high"}}';

-- Агрегация JSON данных
SELECT jsonb_agg(data) FROM documents WHERE category = 'news';

-- Преобразование JSON в таблицу
SELECT * FROM jsonb_each_text('{"a": 1, "b": 2}');

-- Полный текстовый поиск
SELECT to_tsvector('russian', 'Быстрая коричневая лиса прыгает через ленивую собаку') @@ 
to_tsquery('russian', 'коричневая & собака');

-- Нормализация текста
SELECT unaccent('café'); -- Удаление диакритических знаков
SELECT lower('Text FOR Search'); -- Приведение к нижнему регистру

-- Продвинутая работа с датами
SELECT date_trunc('hour', now()); -- Округление до часа
SELECT generate_series('2025-01-01'::date, '2025-01-31'::date, '1 day'::interval); -- Генерация диапазона дат
SELECT EXTRACT(EPOCH FROM now()); -- Unix timestamp

-- Работа с интервалами
SELECT now() + INTERVAL '1 month 2 days';
SELECT AGE(now(), '2025-01-01');

-- Планирование запроса
EXPLAIN ANALYZE
SELECT * FROM large_table 
WHERE indexed_column = 'value' 
AND created_at > '2025-01-01';

-- Просмотр медленных запросов
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Мониторинг блокировок
SELECT blocked_locks.pid AS blocked_pid,
 blocked_activity.usename AS blocked_user,
 blocking_locks.pid AS blocking_pid,
 blocking_activity.usename AS blocking_user,
 blocked_activity.query AS blocked_statement,
 blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
 JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
 JOIN pg_catalog.pg_locks blocking_locks 
 ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid != blocked_locks.pid
 JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

-- Обновление статистики
ANALYZE large_table;

-- Просмотр статистики по таблице
SELECT attname, n_distinct, correlation 
FROM pg_stats 
WHERE tablename = 'large_table';

-- Анализ сессий пользователей
WITH user_sessions AS (
 SELECT 
 user_id,
 event_time,
 event_type,
 -- Определение начала сессии (более 30 минут без активности)
 CASE WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) 
 IS NULL OR 
 event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) 
 > INTERVAL '30 minutes'
 THEN 1 ELSE 0 END as session_start
 FROM user_events
)
SELECT 
 user_id,
 COUNT(*) FILTER (WHERE session_start = 1) as session_count,
 COUNT(*) as total_events,
 AVG(event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)) 
 as avg_session_duration
FROM user_sessions
GROUP BY user_id;

-- Месячный отчет с накопительными показателями
WITH monthly_metrics AS (
 SELECT 
 DATE_TRUNC('month', created_at) as month,
 COUNT(*) as new_users,
 SUM(amount) as total_revenue
 FROM users
 GROUP BY month
),
cumulative_metrics AS (
 SELECT 
 month,
 new_users,
 total_revenue,
 SUM(new_users) OVER (ORDER BY month) as cumulative_users,
 SUM(total_revenue) OVER (ORDER BY month) as cumulative_revenue,
 LAG(new_users, 1) OVER (ORDER BY month) as prev_month_users,
 LAG(total_revenue, 1) OVER (ORDER BY month) as prev_month_revenue
 FROM monthly_metrics
)
SELECT 
 TO_CHAR(month, 'YYYY-MM') as period,
 new_users,
 total_revenue,
 cumulative_users,
 cumulative_revenue,
 ROUND((new_users - COALESCE(prev_month_users, 0)) * 100.0 / 
 COALESCE(prev_month_users, new_users), 2) as user_growth,
 ROUND((total_revenue - COALESCE(prev_month_revenue, 0)) * 100.0 / 
 COALESCE(prev_month_revenue, total_revenue), 2) as revenue_growth
FROM cumulative_metrics
ORDER BY month;

-- Построение хлебных крошек для категории
WITH RECURSIVE category_path AS (
 SELECT id, name, parent_id, ARRAY[id] as path
 FROM categories WHERE id = 123 -- Текущая категория
 UNION ALL
 SELECT c.id, c.name, c.parent_id, cp.path || c.id
 FROM categories c
 JOIN category_path cp ON c.id = cp.parent_id
)
SELECT 
 id,
 name,
 array_to_string(path, ' > ') as breadcrumbs
FROM category_path;