PL/R в PostgreSQL

Привет, Хабр!

PL/R — это процедурный язык для PostgreSQL, который позволяет писать функции на языке R. Когда хочется использовать все богатство статистических пакетов и алгоритмов R, не покидая привычного SQL‑окружения, PL/R приходит на помощь. Плюс ко всему: нет необходимости создавать отдельные сервисы для анализа, что уменьшает задержки и упрощает архитектуру приложения.

Создаем расширение:

CREATE EXTENSION IF NOT EXISTS plr;

Основы создания функций на PL/R: от простого к сложному

Базовый пример

Начнем с самого простого примера, который поможет понять синтаксис PL/R и основы работы с аргументами.

CREATE OR REPLACE FUNCTION add_numbers(a double precision, b double precision) RETURNS double precision AS $$   # Просто складываем два числа – прям как на пальцах   return(a + b) $$ LANGUAGE plr;

Запустим её:

SELECT add_numbers(2.5, 3.5);  -- Результат: 6.0

Так можно вызывать R‑код из SQL.

Работа с массивами

PL/R позволяет передавать массивы данных и обрабатывать их как в обычном R. Пример функции, которая принимает массив чисел и возвращает массив их квадратов:

CREATE OR REPLACE FUNCTION square_elements(numbers numeric[]) RETURNS numeric[] AS $$   # Преобразуем входной массив в вектор R   num_vector <- as.numeric(numbers)      # Вычисляем квадрат каждого элемента   squared <- num_vector^2      # Возвращаем результат обратно в виде массива   return(squared) $$ LANGUAGE plr;

Запускаем функцию:

SELECT square_elements(ARRAY[1, 2, 3, 4, 5]) AS squared_values;

Увидим: {1, 4, 9, 16, 25}.

Возврат таблиц

В PL/R можно возвращать не только скаляры или массивы, но и целые таблицы. Представьте ситуацию, когда у вас есть два массива — с названиями товаров и их ценами. Функция должна вернуть таблицу с названием, ценой и рассчитанной скидкой (скажем, 10% от цены).

CREATE OR REPLACE FUNCTION product_discounts(names text[], prices numeric[]) RETURNS TABLE(product_name text, price numeric, discount numeric) AS $$   # Преобразуем входные массивы в векторы R   name_vec <- as.character(names)   price_vec <- as.numeric(prices)      # Вычисляем скидку в 10%   discount_vec <- price_vec * 0.10      # Формируем результирующий data.frame   result <- data.frame(product_name = name_vec, price = price_vec, discount = discount_vec)      return(result) $$ LANGUAGE plr;

Вызов функции:

SELECT * FROM product_discounts(     ARRAY['Товар A', 'Товар B', 'Товар C'],     ARRAY[100.0, 250.0, 400.0] );

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

Валидация входных данных и обработка ошибок

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

CREATE OR REPLACE FUNCTION safe_mean(numbers numeric[]) RETURNS numeric AS $$   # Если массив пуст или равен NULL, генерируем ошибку   if (is.null(numbers) || length(numbers) == 0) {       plr.error("Пустой массив чисел: вычисление среднего невозможно!")   }      num_vector <- as.numeric(numbers)   mean_value <- mean(num_vector)      return(mean_value) $$ LANGUAGE plr;

При вызове с пустым массивом PostgreSQL вернет ошибку.

Логирование и отладка

Когда функция начинает вести себя не так, как ожидается, полезно добавить логирование. PL/R позволяет выводить сообщения в логи PostgreSQL через plr.notice(). Пример функции с логированием:

CREATE OR REPLACE FUNCTION debug_add_numbers(a double precision, b double precision) RETURNS double precision AS $$   # Логируем входные параметры   plr.notice("Запуск debug_add_numbers: a = " %+% a %+% ", b = " %+% b)      result <- a + b      # Логируем результат   plr.notice("Результат сложения: " %+% result)      return(result) $$ LANGUAGE plr;

Запустив эту функцию, увидите сообщения в логах.

Преобразование типов и динамическая обработка данных

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

CREATE OR REPLACE FUNCTION date_range(dates text[]) RETURNS integer AS $$   # Преобразуем строки в объекты Date   date_vec <- as.Date(dates)      if (length(date_vec) == 0) {       plr.error("Массив дат пустой!")   }      min_date <- min(date_vec)   max_date <- max(date_vec)   day_diff <- as.integer(max_date - min_date)      # Логируем промежуточные результаты   plr.notice("Минимальная дата: " %+% min_date)   plr.notice("Максимальная дата: " %+% max_date)   plr.notice("Разница в днях: " %+% day_diff)      return(day_diff) $$ LANGUAGE plr;

Вызов:

SELECT date_range(ARRAY['2023-01-01', '2023-02-15', '2023-03-10']) AS day_difference;

Совмещение массивов и возврата таблиц: нормализация данных

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

CREATE OR REPLACE FUNCTION normalize_values(names text[], values numeric[]) RETURNS TABLE(name text, original_value numeric, normalized_value numeric) AS $$   # Проверяем, что массивы совпадают по длине   if (length(names) != length(values)) {       plr.error("Массивы names и values должны иметь одинаковую длину")   }      name_vec <- as.character(names)   value_vec <- as.numeric(values)      # Нормализация: (x - min) / (max - min)   min_val <- min(value_vec)   max_val <- max(value_vec)   normalized <- (value_vec - min_val) / (max_val - min_val)      result <- data.frame(       name = name_vec,       original_value = value_vec,       normalized_value = normalized   )      return(result) $$ LANGUAGE plr;

Вызов:

SELECT * FROM normalize_values(     ARRAY['Alpha', 'Beta', 'Gamma', 'Delta'],     ARRAY[10, 20, 15, 25] );

Результатом станет таблица, готовая для дальнейшего анализа или визуализации.

Примеры применения PL/R

Линейная регрессия для прогнозирования продаж

Предположим, есть таблица с данными по продажам:

CREATE TABLE sales (   sale_date DATE NOT NULL,   amount NUMERIC NOT NULL );  INSERT INTO sales (sale_date, amount) VALUES   ('2023-01-01', 100),   ('2023-01-02', 120),   ('2023-01-03', 130),   ('2023-01-04', 150),   ('2023-01-05', 160),   ('2023-01-06', 170),   ('2023-01-07', 200);

Функция линейной регрессии принимает два массива — даты и суммы продаж, преобразует даты в числовой формат, строит модель и возвращает коэффициенты:

CREATE OR REPLACE FUNCTION linear_regression(dates date[], amounts numeric[]) RETURNS TABLE(intercept numeric, slope numeric) AS $$   numeric_dates <- as.numeric(as.Date(dates))   numeric_amounts <- as.numeric(amounts)      model <- lm(numeric_amounts ~ numeric_dates)   coefs <- coef(model)      return(data.frame(intercept = coefs[1], slope = coefs[2])) $$ LANGUAGE plr;

Вызов:

WITH data AS (   SELECT array_agg(sale_date ORDER BY sale_date) AS dates,          array_agg(amount ORDER BY sale_date) AS amounts   FROM sales ) SELECT * FROM data, linear_regression(dates, amounts);

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

Прогнозирование с ARIMA с использованием пакета forecast

Если нужно предсказать будущие значения временного ряда, можно использовать модель ARIMA. Пример функции, которая принимает массив сумм продаж и возвращает прогноз на заданное количество периодов:

CREATE OR REPLACE FUNCTION forecast_arima(amounts numeric[], forecast_period integer) RETURNS numeric[] AS $$   if (!require("forecast", quietly = TRUE)) {       install.packages("forecast", repos="http://cran.r-project.org")       library(forecast)   }      ts_data <- ts(as.numeric(amounts), frequency = 7)   model <- auto.arima(ts_data)   fc <- forecast(model, h = forecast_period)      return(as.numeric(fc$mean)) $$ LANGUAGE plr;

Запуск:

WITH daily_sales AS (   SELECT array_agg(amount ORDER BY sale_date) AS amounts   FROM sales   WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-07' ) SELECT forecast_arima(amounts, 7) AS predicted_sales FROM daily_sales;

Так можно оперативно получить прогноз продаж, не покидая базы данных.

Кластеризация геоданных: алгоритм k-means

Рассмотрим задачу кластеризации пользователей по координатам. Допустим, есть таблица с данными пользователей:

CREATE TABLE users (   id serial PRIMARY KEY,   name text,   longitude double precision,   latitude double precision,   active boolean DEFAULT true );  INSERT INTO users (name, longitude, latitude) VALUES   ('User1', 37.62, 55.75),   ('User2', 37.60, 55.76),   ('User3', 37.64, 55.74),   ('User4', 37.65, 55.73),   ('User5', 37.70, 55.72);

Функция для кластеризации:

CREATE OR REPLACE FUNCTION kmeans_clustering(coords double precision[][], clusters integer) RETURNS TABLE(cluster_id integer, centroid_x double precision, centroid_y double precision) AS $$   if (length(coords) == 0) {       plr.error("Пустой массив координат")   }      data_matrix <- do.call(rbind, coords)   km_result <- kmeans(data_matrix, centers = clusters)   centers <- km_result$centers      result <- data.frame(       cluster_id = 1:nrow(centers),       centroid_x = centers[,1],       centroid_y = centers[,2]   )      return(result) $$ LANGUAGE plr;

Вызов:

WITH user_coords AS (   SELECT array_agg(ARRAY[longitude, latitude]) AS coords   FROM users   WHERE active = true ) SELECT * FROM user_coords, kmeans_clustering(coords, 2);

Результатом станут кластеры с координатами центроидов.

Классификация клиентов с randomForest

В качестве бонуса — пример машинного обучения для классификации клиентов. Допустим, есть таблица клиентов:

CREATE TABLE customers (   id serial PRIMARY KEY,   age integer,   income numeric,   loyalty_score numeric,   churn boolean );  INSERT INTO customers (age, income, loyalty_score, churn) VALUES   (25, 30000, 0.7, false),   (45, 70000, 0.9, false),   (35, 50000, 0.5, true),   (50, 90000, 0.8, false),   (30, 40000, 0.6, true);

Функция для классификации на основе randomForest:

CREATE OR REPLACE FUNCTION classify_customers(     ages integer[], incomes numeric[], scores numeric[], churns boolean[] ) RETURNS TABLE(id integer, predicted_churn integer) AS $$   if (!require("randomForest", quietly = TRUE)) {       install.packages("randomForest", repos="http://cran.r-project.org")       library(randomForest)   }      df <- data.frame(       age = as.numeric(ages),       income = as.numeric(incomes),       loyalty = as.numeric(scores),       churn = as.factor(churns)   )      model <- randomForest(churn ~ age + income + loyalty, data = df, ntree = 100)   predictions <- predict(model, df)      result <- data.frame(id = 1:length(predictions), predicted_churn = as.integer(predictions) - 1)      return(result) $$ LANGUAGE plr;

Вызов функции:

WITH customer_data AS (   SELECT array_agg(age ORDER BY id) AS ages,          array_agg(income ORDER BY id) AS incomes,          array_agg(loyalty_score ORDER BY id) AS scores,          array_agg(churn ORDER BY id) AS churns   FROM customers ) SELECT * FROM customer_data, classify_customers(ages, incomes, scores, churns);

---

В заключение напоминаю про открытые уроки по PostgreSQL, которые пройдут в марте в Otus:

• 4 марта. От PostgreSQL к Arenadata DB. Подробнее

• 24 марта. Безопасность в PostgreSQL: защита данных, управление доступом и аудит. Подробнее

Больше уроков по аналитике и не только смотрите в календаре мероприятий.