С ростом спроса на аналитику бизнеса, ориентированную на создание большей ценности, стало необходимо использовать несколько инструментов, сред разработки и аналитических методов. В этой статье мы рассмотрим интеграцию R и SQL Server и то, как она позволяет нам работать без проблем в обоих средах, используя T-SQL.
Почему R и SQL Server?
В недавнем опросе R был определен как язык статистики, выбранный большинством SQL-профессионалов для аналитики. Это делает R ценным инструментом для SQL-специалистов, которые хотят выполнять сложную аналитику. К счастью, в R есть пакет под названием rsqlserver, который упрощает работу с базами данных SQL Server с использованием T-SQL для извлечения данных и выполнения запросов на манипулирование данными. Этот пакет оптимизирован для прямого доступа к SQL Server, что обеспечивает более быструю производительность по сравнению с другими вариантами, такими как ODBC.
SQL и обработка данных
В реальном мире данные редко бывают чистыми и готовыми для анализа. Ученые-данные и аналитики тратят значительное количество времени на сбор и подготовку данных, прежде чем они смогут начать исследование для выявления инсайтов. SQL и реляционные базы данных хорошо подходят для этого процесса обработки данных, особенно для структурированных данных. Кроме того, с появлением SQL-ориентированных абстракций Hadoop теперь возможно выполнять запросы к полуструктурированным большим данным с использованием SQL, что дает SQL-профессионалам преимущество при работе с разнообразными источниками данных.
Работа с R и SQL Server
Для работы с R и SQL Server нам необходимо установить соединение с базой данных. Пакет rsqlserver предоставляет функции для подключения к базе данных SQL Server с использованием строки подключения. После установления соединения мы можем выполнять запросы T-SQL и извлекать данные в R-фреймы данных.
Вот пример подключения к локальному SQL Server и создания сложного представления в базе данных AdventureWorks:
library("rClr")
library("rsqlserver")
url = "Server=localhost;Database=AdventureWorksDW2012;Trusted_Connection=True;"
conn = dbConnect('SqlServer', url=url)
query = "CREATE VIEW [dbo].[vInternetSales] AS
WITH internetsales AS (
SELECT pc.englishproductcategoryname,
COALESCE (p.modelname, p.englishproductname) AS Model,
c.customerkey,
s.salesterritorygroup AS Region,
...
INNER JOIN dbo.dimsalesterritory AS s ON g.salesterritorykey = s.salesterritorykey
),
customersummary AS (
SELECT customerkey,
region,
age,
IncomeGroup,
city,
statecode,
statename,
countrycode,
countryname,
postalcode,
sum(Quantity) as quantity,
cast(sum(Amount) as numeric) amount,
Sum(CASE [englishproductcategoryname] WHEN 'Bikes' THEN 1 ELSE 0 END) AS Bikes
FROM internetsales AS InternetSales_1
GROUP BY customerkey,
region,
age,
city,
statecode,
statename,
countrycode,
countryname,
postalcode,
IncomeGroup
)
SELECT c.customerkey,
c.geographykey,
c.customeralternatekey,
c.title,
c.firstname,
c.middlename,
c.lastname,
...
x.region,
x.age,
x.IncomeGroup,
cast(x.Amount as int) as Amount,
x.Quantity,
CASE x.[bikes] WHEN 0 THEN 0 ELSE 1 END AS BikeBuyer
FROM dbo.dimcustomer AS c
INNER JOIN customersummary AS x ON c.customerkey = x.customerkey"
dbSendQuery(conn, query)
results = dbSendQuery(conn, "SELECT * FROM vInternetSales")
InternetSales_Dataframe = fetch(results, n = -1)
dbDisconnect(conn)
В приведенном выше примере мы сначала устанавливаем соединение с базой данных AdventureWorks. Затем мы создаем сложное представление с именем vInternetSales с помощью запроса T-SQL. Наконец, мы извлекаем данные из представления в R-фрейм данных с именем InternetSales_Dataframe.
Синтаксис R в стиле SQL
Одним из преимуществ работы с R и SQL Server является возможность использования синтаксиса SQL для манипулирования данными в R. Пакет dplyr в R предоставляет функции, соответствующие SQL-глаголам, таким как SELECT, FROM, WHERE, GROUP BY и ORDER BY. Эти функции могут быть объединены с помощью символа %>% для создания сложных запросов.
Вот пример использования синтаксиса R в стиле SQL для фильтрации, группировки, выбора и упорядочивания данных:
InternetSales_Dataframe %>%
filter(firstname != "NA" & lastname != "NA", gender == "F") %>%
group_by(statecode, countrycode, countryname) %>%
select(statecode, countrycode, countryname) %>%
summarise(count = n(),
TotYrIncome = sum(YearlyIncome),
MaxYrIncome = max(YearlyIncome),
MinYrIncome = min(YearlyIncome)) %>%
arrange(desc(countryname, statecode))
В приведенном выше примере мы фильтруем фрейм данных на основе определенных условий, группируем данные по statecode, countrycode и countryname, выбираем определенные столбцы, вычисляем сводные статистики и упорядочиваем результаты в порядке убывания.
Статистический и графический анализ
R хорошо известен своей обширной коллекцией пакетов для статистического анализа и визуализации данных. С помощью R мы легко можем выполнять различные статистические анализы и создавать информативные визуализации.
Например, мы можем использовать пакет ggplot2 для создания графических графиков. Вот пример построения графика продаж по расстоянию до работы и региону:
library("ggplot2")
qplot(x = BikeBuyer, data = InternetSales_Dataframe, fill = factor(BikeBuyer), geom = "bar") +
facet_grid(commutedistance ~ region)
Мы также можем выполнять анализ временных рядов и прогнозирование с использованием R. R предоставляет функции для декомпозиции временных рядов, подгонки экспоненциальных моделей и генерации прогнозов. Вот пример декомпозиции временного ряда и прогнозирования будущих продаж:
# Декомпозиция временного ряда
LogM200Sales_DecomTS <- stl(LogM200Sales_TS[,1], s.window = "periodic")
# Построение графика декомпозиции
plot(LogM200Sales_DecomTS, main = "Сезонная декомпозиция логарифма продаж с использованием Loess")
# Прогнозирование будущих продаж
fcstM200Sales_TS <- forecast.HoltWinters(fitM200Sales_TS, h = 12)
plot.forecast(fcstM200Sales_TS)
Предсказательное моделирование с использованием ансамблевых методов
R предлагает передовые методы машинного обучения, такие как бустинг и случайный лес, для предсказательного моделирования. Эти ансамблевые методы часто превосходят традиционные классификаторы, такие как деревья решений и логистическая регрессия.
Вот пример использования алгоритма бустинга adaboost для прогнозирования покупателей велосипедов:
library(ada)
model.fit <- ada(model.formula, data = train)
in.model.predict <- predict(model.fit, train, type = "prob")
in.model.prediction <- prediction(in.model.predict[, 2], train$BikeBuyer)
in.model.auc <- attributes(performance(in.model.prediction, 'auc'))$y.values[[1]]
out.model.predict <- predict(model.fit, test, type = "prob")
out.model.prediction <- prediction(out.model.predict[, 2], test$BikeBuyer)
out.model.auc <- attributes(performance(out.model.prediction, 'auc'))$y.values[[1]]
outROCPerf <- performance(out.model.prediction, "tpr", "fpr")
inROCPerf <- performance(in.model.prediction, "tpr", "fpr")
plot(inROCPerf, main = "Бустинг", col = "red")
legend(0.2, 0.5, c(paste('В выборке: AUC = ', round(in.model.auc, digit = 4))), text.col = "red", border = "white", cex = 1.0, box.col = "white")
plot(outROCPerf, col = "blue", add = TRUE)
legend(0.2, 0.8, c(paste('Вне выборки: AUC = ', round(in.model.auc, digit = 4))), text.col = "blue", border = "white", cex = 1.0, box.col = "white")
Заключение
Интеграция SQL Server и R предоставляет SQL-профессионалам мощное сочетание инструментов для аналитики, ориентированной на создание ценности. Используя преимущества SQL и R, аналитики могут эффективно обрабатывать данные, выполнять сложные манипуляции данными с использованием синтаксиса SQL, проводить статистический и графический анализ и создавать предсказательные модели с использованием передовых методов машинного обучения. Эта интеграция открывает новые возможности для SQL-профессионалов для исследования и извлечения ценных инсайтов из их данных.