Часть I. R извлекает и рисует

Конечно, PostgreSQL с самого начала создавалась как универсальная СУБД, а не как специализированная OLAP-система. Но один из больших плюсов Постгреса — в поддержке языков программирования, с помощью которых из него можно сделать что угодно. По изобилию встроенных процедурных языков ему просто нет равных. PL/R — серверная реализация R — любимого языка аналитиков — один из них. Но об этом позже.

R – удивительный язык со своеобразными типами данных — list, например, может включать в себя не только данные разных типов, но и функции (вообще, язык эклектичный, и говорить о принадлежности его к определенному семейству не будем, чтобы не порождать отвлекающие дискуссии). В нем есть симпатичный тип данных data.frame, который подражает таблице РСУБД — это матрица, у которой столбцы содержат разные типы данных, общие на уровне столбца. Поэтому (и по другим причинам) работать в R с базами данных довольно удобно.

Мы будем работать в командной строке в среде RStudio и соединяться с PostgreSQL через драйвер ODBC RpostgreSQL. Их несложно установить.

Поскольку R создавался как этакий вариант языка S для тех, кто занимается статистикой, то и мы приведем примеры из простенькой статистики с простенькой графикой. У нас нет цели знакомить с языком, но есть цель показать взаимодействие R и PostgreSQL.

Обрабатывать данные, хранящиеся в PostgreSQL, можно тремя путями.

Во-первых, можно выкачать данные из базы любыми удобными средствами, упаковать их, скажем, в JSON – их понимает R – и обрабатывать дальше в R. Это обычно не самый эффективный способ и точно не самый интересный, мы его рассматривать здесь не будем.

Во-вторых, можно связываться с базой – читать из нее и сбрасывать данные в нее – из среды R как из клиента, используя драйвер ODBC/DBI, обрабатывая данные в R. Мы покажем, как это делается.

И, наконец, можно делать обработку средствами R уже на сервере базы, используя PL/R как встроенный процедурный язык. Это имеет смысл в ряде случаев, так как в R есть, например, удобные средства агрегирования данных, которых нет в pl/pgsql. Мы покажем и это.

Распространенный подход это использование 2-го и 3-го варианта в разных фазах проекта: сначала отладка кода как внешней программы, а затем перенос ее внутрь базы.

Начнём. R интерпретируемый язык. Поэтому можно действовать по шагам, а можно сбросить код в скрипт. Дело вкуса: примеры в этой статье коротенькие.

Сначала нужно, конечно, подключить соответствующий драйвер:

# install.packages("RPostgreSQL")
require("RPostgreSQL")
drv <- dbDriver("PostgreSQL")

Операция присвоения выглядит в R, как можно было заметить, своеобразно. Вообще в R a < — b значит то же, что и b -> a, но более распространен первый способ записи.

Базу данных возьмем готовую: демобазу авиаперевозок, которую используют учебных материалах Postgres Professional На этой странице можно выбрать вариант базы по вкусу (то есть по размеру) и почитать ее описание. Схему данных воспроизводим для удобства:



Предположим, что база установлена на сервере 192.168.1.100 и называется demo. Соединяемся:

con <- dbConnect(drv, dbname = "demo",
                 host = "192.168.1.100", port = 5434,
                 user = "u_r")

Продолжаем. Посмотрим вот таким запросом, в какие города чаще всего запаздывают рейсы:

SELECT ap.city, avg(extract(EPOCH FROM f.actual_arrival) - extract(EPOCH FROM f.scheduled_arrival))/60.0 t FROM airports ap, flights f WHERE ap.airport_code = f.departure_airport AND f.scheduled_arrival < f.actual_arrival AND f.departure_airport = ap.airport_code GROUP BY ap.city ORDER BY t DESC LIMIT 10;

Для получения минут опоздания мы использовали конструкцию postgres extract(EPOCH FROM ...) для извлечения «абсолютных» секунд из поля типа timestamp и поделили на 60.0, а не на 60, чтобы избежать отбрасывания остатка при делении, понятом как целочисленное. EXTRACT MINUTE использовать нельзя, так как встречаются опоздания больше часа. Усредняем времена опоздания оператором avg.

Передаем текст в переменную и отправляем запрос на сервер:

sql1 <- "SELECT ... ;" 
res1 <- dbGetQuery(con, sql1)

Теперь разберемся, в каком виде пришел запрос. Для этого в языке R имеется функция class()

class (res1)

Она покажет, что результат был упакован в тип data.frame, то есть, напоминаем, аналог таблицы базы: фактически это матрица со столбцами произвольных типов. Она, кстати, знает названия столбцов, а к столбцам, если что, можно обращаться, например, так:

print (res1$city)

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

• R-Bar Charts (линейчатые)
• R-Boxplots (биржевые)
• R-Histograms (гистограммы)
• R-Line Graphs (графики)
• R-Scatterplots (точечные)

Надо иметь в виду, что для каждого вида на вход подается подходящий для картинки тип данных. Выберем линейчатую диаграмму (лежачие столбики). Для нее требуются два вектора для значений по осям. Тип «вектор» в R это просто набор однотипных значений. c() — конструктор векторов.

Сформировать нужные два вектора из результата типа data.frame можно так:

Time <- res1[,c('t')]
City <- res1[,c('city')]
class (Time)
class (City)

Выражения в правых частях выглядит странновато, но это удобный прием. Более того, в R можно очень компактно записывать различные выражения. В квадратных скобках перед запятой индекс ряда, после запятой — индекс колонки. То, что перед запятой ничего не стоит, значит всего лишь, что будут выбраны все значения из соответствующей колонки.

Класс Time получится numeric, а класс City — character. Это разновидности векторов.

Теперь можно заняться самой визуализацией. Надо задать файл картинки.

png(file = "/home/igor_le/R/pics/bars_horiz.png")

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

• 0 и по умолчанию — параллельно осям;
• 1 — всегда горизонтально;
• 2 — перпендикулярно осям;
• 3 — всегда вертикально

Все параметры расписывать не будем. Вообще их много: поля, масштабы, цвета — ищите, экспериментируйте на досуге.

par(las=1)
par(mai=c(1,2,1,1))

Наконец, строим график из лежачих столбиков:

barplot(Time, names.arg=City, horiz=TRUE, xlab="Опоздание (мин)", col="green", main="Среднее время опоздания", border="red", cex.names=0.9)

Это не всё. Надо сказать напоследок:

dev.off()

Для разнообразия нарисуем еще точечную диаграмму опозданий. Из запроса уберем LIMIT, остальное то же самое. Но точечной диаграмме нужен один вектор, а не два.

Dots <- res2[,c('t')]
png(file = "/home/igor_le/R/scripts/scatter.png")
plot(input5, xlab="Кучность",ylab="Опоздания",main="Распределение опозданий")
dev.off()

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

library()

Часть II. R генерирует пенсионеров

R удобно использовать не только для анализа данных, но и для их генерации. Где есть богатые статистические функции, там не может не быть разнообразных алгоритмов создания случайных последовательностей. В том числе можно использовать типичные (Гауссовские) и не совсем типичные (Ципфовские) распределения и для симуляции запросов к базе.

Но об этом в следующей части.