Союз R и PostgreSQL. Анализируем работу аэропортов, рассчитываем пенсии
Часть 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 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 удобно использовать не только для анализа данных, но и для их генерации. Где есть богатые статистические функции, там не может не быть разнообразных алгоритмов создания случайных последовательностей. В том числе можно использовать типичные (Гауссовские) и не совсем типичные (Ципфовские) распределения и для симуляции запросов к базе.
Но об этом в следующей части.
