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

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: защита данных, управление доступом и аудит. Подробнее

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