### [SQL Server][R Language]In-Database R(二)安裝R Package(R包)

R套件(Package)的資源非常豐富，截自2016-06-18已經有8,195個R包可以下載，安裝完R Service(In-DataBase R)之後，

1.我們來試試2013-01~05 月R包兵器下載排行榜第一名plyr

2.首先在R Script製作出一個Data frame，假設資料行有國家名、人口數及FIFA世界足協男子組分數。

測試資料取歐洲五大聯賽所在的國家抽樣(西甲、英超、法甲、德甲、義甲)，假設過去1年，每個國家都增加了100萬人

execute sp_execute_external_script
@language = N'R'
, @script = N'
country <- c("France","Germany","Spain","England","Italy","France","Germany","Spain","England","Italy");
year <- c(2016,2016,2016,2016,2016,2015,2015,2015,2015,2015);
population <- c(6500,8221,4666,6111,5933,6400,8121,4566,6011,5833);
FIFAScore <- c(925,1310,1267,1069,982,907,1277,1309,1016,959);
OutputDataSet <- data.frame(country,population,year,FIFAScore)'
, @input_data_1 = N''
WITH RESULT SETS (([country] varchar(20) NOT NULL,[population] int,[year] int,[FIFAScore] int));

3.我們想利用plyr裡頭的ddply分組統計計算人口增長與足球成績相關係數(Correlation coefficient)

execute sp_execute_external_script
@language = N'R'
, @script = N'
library("plyr")
country <- c("France","Germany","Spain","England","Italy","France","Germany","Spain","England","Italy");
year <- c(2016,2016,2016,2016,2016,2015,2015,2015,2015,2015);
population <- c(6500,8221,4666,6111,5933,6400,8121,4566,6011,5833);
FIFAScore <- c(925,1310,1267,1069,982,907,1277,1309,1016,959);
d <- data.frame(country,population,year,FIFAScore)
OutputDataSet <- ddply(d, c("country"), function(df) cor(df$FIFAScore,df$population));
'
, @input_data_1 = N''
WITH RESULT SETS (([Country] nvarchar(20) NOT NULL,[Cor] numeric(6,3)));

Error in library("plyr") : there is no package called 'plyr'
Calls: source -> withVisible -> eval -> eval -> library

4.如何在R Service 安裝R包? 查MSDN

Install Additional R Packages on SQL Server

a.在安裝SQL Server  R SERVICE的目錄中找到Rgui執行程式，按下右鍵以系統管理員身份執行。

C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\R_SERVICES\bin\x64

(SQL2016是Instance的名稱)

b.輸入以下R script

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.SQL2016\\R_SERVICES\\library"
install.packages("plyr",lib=lib.SQL)



Console上的訊息顯示已經下載並安裝成功

c.查詢R package Library目錄

4.重新執行以下剛剛相關係數的語法

• 最佳資料的來源: 透過T-SQL語法將資料表的資料讀出輸入到@input_data_1

例如像是 @input_data_1 = N'SELECT * FROM Sales.Customer'，下一篇筆記。

• FIFA成績和人口增長應該沒有必然的關係啦。
• 冰島人口30萬人，FIFA排名34。

Top 100 R packages for 2013 (Jan-May)!

FIFA Rank

Install Additional R Packages on SQL Server