R 分析 SQL 資料庫的小技巧

小編前一陣子幫忙另一個專案項目進行簡單的資料分析，因為大數據這個詞在全世界的流行，很多人對於資料分析會覺得神奇，甚至有點把它當成算命了！但其實資料分析只是幫助決策者了解實際發生的狀況，讓決策者不再憑經驗或感覺瞎猜。在這篇說明一些小編在分析時使用的指令，希望這篇可以幫助想使用 R 分析 SQL 資料庫的人，如果有更好的做法，希望也不吝跟小編說。 😀
R 是一個自由軟體語言，擁有豐富的統計、財務、資料視覺化、機器學習等等的套件。而 SQL 則是查詢資料庫的標準語言，可以快速的整理出想要的資料格式。當兩者一起使用在資料分析上有如虎添翼的效果。

RJDBC

R 取得 SQL 資料庫的套件有很多，其中最推薦 RJDBC。RJDBC 可以支援不同的作業系統，不需要特別的設定，只需要安裝 java, rjava, 然後下載資料庫的 JDBC driver。在之前的在 Ubuntu 系統上使用 R 取得 SQL Server 資料有介紹 RJDBC 安裝方式，Windows 的使用者則比較簡單，安裝 java 後，在 R console 輸入

install.packages(“rJava”) 
library(rJava)

之後可以使用 RJDBC 連結 SQL 資料庫。

實用函數

這裡介紹在 R 連接 SQL 資料庫時的一些常用的函數與技巧，因為小編使用 R 整理資料比較熟悉，所以接下來的介紹都會以 R 為主，但是透過 RJDBC 也可以直接對資料庫寫 SQL，直接整理出想要的表格內容，所以如果對 SQL 比較熟練的朋友，也可以只使用 SQL 就好。這裡同時也會使用 dplyr 套件。

# 有的人會安裝不同語系的 R，小編安裝的就是英語版的 R，而資料庫有中文資料，所以需要將系統換成中文，不然顯示時會出現亂碼，是當切換不同語系版本的 R 也是很重要的。

Sys.setlocale("LC_CTYPE", "cht")

# 使用JDBC連結 SQL Server

drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "C:\\sqljdbc_6.0\\cht\\sqljdbc42.jar")

conn <- dbConnect(drv, "jdbc:sqlserver:// 資料庫的位置:port; databasename = 資料庫名字", "username", "password")

#dbListTables(): 列出在這個資料庫有幾個表。

dbListTables(conn)

# colnames(): 列出在這個表的欄位名稱。

sqltext <- paste0("SELECT * FROM  ta")

ta <- dbGetQuery(conn, sqltext)

dbListTables(ta)

# merge: 合併兩張表，有時候想要的資料有可能分散在不同的表，這個函數可以把兩張表合併在一起， 之後可以做資料處理。 merge 跟 SQL 的 Join 一樣，一樣有Inner join, Full join,  Left join, Right join，在 R 只需給定函數參數的值即可。

authors <- data.frame(

  surname = I(c("Tukey", "Venables", "Tierney", "Ripley", "McNeil")),

  nationality = c("US", "Australia", "US", "UK", "Australia"),

  deceased = c("yes", rep("no", 4)))

books <- data.frame(

  name = I(c("Tukey", "Venables", "Tierney",

             "Ripley", "Ripley", "McNeil", "R Core")),

  title = c("Exploratory Data Analysis",

            "Modern Applied Statistics ...",

            "LISP-STAT",

            "Spatial Statistics", "Stochastic Simulation",

            "Interactive Data Analysis",

            "An Introduction to R"),

  other.author = c(NA, "Ripley", NA, NA, NA, NA,

                   "Venables & Smith"))

# inner join

m1 <- merge(authors, books, by.x = "surname", by.y = "name")

# full join

m2 <- merge(authors, books, by.x = "surname", by.y = "name", all=T)

# left join

m3 <- merge(authors, books, by.x = "surname", by.y = "name", all.x=T)

# right join

m4 <- merge(authors, books, by.x = "surname", by.y = "name", all.y=T)

### 使用 dplyr

# 安裝 dplyr 

install.packages(“dplyr”) 

library(dplyr)

# 使用 dplyr 會遇到特別的 %>%，不過不用害怕這個符號，假設 x %>% y 只是宣告你要對前面 x 表單做 y 的運算或處理，讓我們舉的例子。

# 這裡使用 iris 資料集做示範

x <- iris

# filter(): 挑選特定值資料。例如想要知道 Sepal.Width 大於 3 的資料。

x %>% dplyr::filter(Sepal.Width > 3)

# group_by(): 將資料分群後，在分群的資料作分析，group_by 通常會與 summarise() 一起使用。 

# summarise(): 對資料做總整理的計算，像是 mean(): 平均數 max():最大值， min(): 最小值，var(): 變異數， n(): 個數。例如想知道 Species 不同群 Sepal.Length 的平均數。

x %>% dplyr::group_by(Species) %>% summarise(Sepal.Length.mean = mean(Sepal.Length))