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))

結論

在上面列出的函數是小編比較常用的函數,在整理資料時會依據不同的狀況,對資料做特別的處理。當遇到問題時,推薦大家查詢 Stackoverflow 是否有跟自己相似的問題,大部分都可以得到解決。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s