SQL

R 快速入門 (1)

基本介紹

教學目標

R 語言的快速入門和如何取得第三方資料。

重點概念

最近參與公司指派的 R 語言二天專業課程,主要是由 Teradata 資深顧問來授課,老師非常有經驗,過程中教了許多重點概念,一開始先教如何安裝 R 軟體和 RStudio 軟體,並且建議安裝版本為 3.1.1。接著針對 R 語言的資料型態進行基本介紹,R 語言有許多不同種類的資料型態,主要有純量、向量、矩陣、資料框、清單和因數,首先我們主要透過 <- 指定資料內容,透過 [] 取得資料內容和透過 is. 判斷資料型態和 as. 轉換資料型態。

指定資料內容

1
2
3
4
5
6
7
8
a <- 1
a <- a + 1
n <- c(1,2,3,4,5)
s <- c("one","two","three","four","five")
l <- c(TRUE,FALSE,TRUE,FALSE,FALSE)
m <- matrix(1:30, nrow=5,ncol=6)
d <- data.frame(n,s,l)
l <- list(name="Leo", company="Cathaybk", skill=c("Teradata","IBM","SAS"))

(註: 其中純量、向量、矩陣中的內容必為相同型態,僅有資料框和清單能為不同型態)

取得資料內容

1
2
3
4
n_v <- n[n>3]
s_v <- s[c(4,5)]
m_v <- m[1,3]
l_v <- l$skill

(註: 一般來說判斷式會比索引值處理較快)

轉換資料型態

1
2
3
4
5
6
7
8
9
is.vector(n)
is.matrix(m)
is.data.frame(d)
is.list(l)

m_v <- as.vector(m)
n_v <- as.matrix(n)
l_v <- as.data.frame(l)
d_v <- as.list(d)

(註: 此時輸入 as.list(d)$s 會發現有 Levels 的資訊,這主要就是自動轉換後的因數 (Factor) ,在未來若發生將資料呈現圖表發生錯誤時,可先確認資料是否已經向動轉換成因數。)

接著 R 語言有許多不同資料處理的方式,首先當沒有資料進行分析時,我們可以先透過函數產生隨機變數和產生客製圖表,接著透過函數讀取檔案內容,以及針對多個有規則的檔名搭配 seq 、 format 和 paste 函數進行處理,會將資料處理的過程匯整成使用者自訂函數重複應用。

產生隨機圖表

1
2
3
4
# set.seed(1234)
x <- rnorm(15,sd=5,mean=20)
y <- 3.5 * x - 2.0 + rnorm(5,sd=9,mean=0)
plot(x,y,xlab="X 軸",ylab="Y 軸",main="隨機變數")

(註: 若要每次隨機執行結果皆一樣可以透過 set.seed() 指令,若要客製不同種類的混合圖表,則可以疊圖方式進行處理。)

選取特定檔案

1
data <- read.table("c:/資料夾/記錄201501.csv", header = TRUE, sep = ",")

(註: 若要以選取檔案的方式進行讀取,則可以透過 choose.file() 取代檔名實作。)

讀取多個檔案

1
2
3
4
5
6
7
link <- "c:/資料夾/"
seq_date <- format(seq(as.Date("2015-01-01"), as.Date("2015-12-31"), by="month"), "%Y%m")
data_source <- paste(link, "記錄", seq_date, ".csv", sep="")
for (i in seq(length(data_source))) {
data <- read.table(data_source[i], header = TRUE, sep = ",")
# 資料處理
}

使用者自訂函數

1
2
3
4
5
myfunc <- function (arg1 = "參數1" ,arg2 = "參數2") {
data <- c()
# 資料處理
return (data)
}

當然 R 語言更能夠針對網站內容進行文字探勘應用,首先我們會先從開始觀察網址的變化開始,接著透過檢視原始碼確保網址是否有特別規則和特別參數,待確認之後就能開始透過 RCurl 套件中的函數讀取網站內容。針對不同網站的原始碼內容,接著再透過 paste、cat、substr、substring、grep、regexpr、gsub、sub、strsplit、… 等字串處理和正規表示式函數等應用。當然我們最好分析如何進行拆解成許多不同區塊的處理函數,以避免當網站內容修改時,在最短的時間內找出問題進行修改,更能搭配 RSelenium 等自動化套件模擬使用者行為動態存取網站資料,並且將匯整至 R 語言中進行資料處理與分析應用,例如: 透過政府公開資料匯整天氣資訊圖表、透過新聞網站進行關鍵字資料探勘、透過一卡通網站獲取生活商圈的特店資料、…等相關應用。

取得網頁內容

1
2
3
4
library(RCurl)
# curl <- getCurlHandle()
# curlSetOpt(.opts = list(proxy = '代理伺服器網址:80'), curl = curl)
html <- getURL("http://www.google.com.tw")

(註: 透過 getURL 可以存取 HTTPS 的網站,以及可以透過 getCurlHandle 設定代理伺服器。)

最後針對初學者,特別強調解決問題流程,下述範例以如何使用 R 透過 ODBC 連接至 Teradata 資料倉儲連線執行 SQL 指令,並將結果回傳,以利進行後續資料處理,進行示範。

解決問題流程

  1. 透過 Google 搜尋關鍵字。

    1
    > R ODBC TERADATA SQL
  2. 安裝相關套件。

    1
    > install.packages("RODBC")
  3. 載入相關套件。

    1
    > library("RODBC")
  4. 查詢套件使用方法。

    1
    > ?RODBC
  5. 開始使用套件。

    1
    2
    3
    > channel <- odbcConnect("Teradata", uid="使用者名稱", pwd="密碼")
    > odbcGetInfo(channel)
    > dataframe <- sqlQuery(channel, "SELECT * FROM TABLENAME SAMPLE 100;")

此外 Teradata 資源顧問和我們分享 R 語言實作的相關經驗,並且提到 R 語言比較適用於概念性的驗證,但是實際部署應用則必須針對現有環境選擇最適當的解決方案。總結雖然碩士論文和新創公司工作時有應用 R 語言解決問題的經驗分別為分群演算法和推薦分析應用,但上完二門課之後對 R 又有更深一層的了解,其實教的很不錯。

相關資源

教育訓練之 Teradata SQL 基本介紹 (1)

基本介紹

教學目標

有關針對非技術來自不同部門學員進行一個半小時之 SQL 基本介紹的課程心得分享。

重點概念

何謂 SQL,全名為 Structured Query Language ,也就是結構化查詢語言,主要是讓我們能夠和資料庫進行溝通,請它產生我們所需要的資料,例如: 我們透過 SELECT 告知資料庫所需要呈現的欄位、透過 FROM 告知資料庫所需要撈取資料的資料表、透過 WHERE 主要告知資料庫需要呈現哪些符合條件的資料、透過 ORDER BY 主要告知資料庫需要如何呈現排序資料,透過 GROUP BY 主要告知資料庫需要如何進行分群以進行聚合函數等相關語法,然而這些語法主要在遠端資料庫中進行 SQL 陳述式解析之後,以最有效率的方式從資料庫找查詢符合的資料。

1
2
3
4
5
6
7
8
結構化		 查詢語言
SELECT TXN_DATE
,SUM(TXN_AMT) AS "TOT_AMT"
FROM BANK_TXN
WHERE TXN_DATE BETWEEN '2015-11-01'
AND '2015-11-30'
ORDER BY TXN_DATE DESC
GROUP BY TXN_DATE;

企業中有各式各樣的符合不同需求的資料庫,例如: 針對客戶直接的資料存取的資料庫,主要會以檢視表為主,檢視表會將有關個資機敏資料進行加密處理。在資料庫中針對不同主題會有許多資料表,資料表中存有許多記錄和欄位,欄位代表每筆記錄所代表的資訊,其儲存不同類型的資料,像是文字、數字、日期等。然而若我們要得知有客戶消費行為的資訊,則相關資料存在許多資料表中,此時就必需將相關資料表進行關聯,才能夠透過資料關聯分析現況。此外我們都知道大數據常見特點有資料大小 (Volume)、時效性 (Velocity) 和多樣性 (Variety),若以 Teradata 資料庫為例,在大企業中常見的有許多相關資料表符合多樣性,資料表的大小皆有至少幾千萬筆以上的資料符合資料大小,並且藉由 Teradata 資料庫的分散式處理架構,即可在適當的時間內產生可供不同部門分析的資訊,符合時效性,然而大數據的應用最重要還是在於價值 (Value),我們要如何從資料庫中許多的資料表「查詢」出適當的資訊,進行分析之後,產生有助於企業的價值,此時 SQL 就是一個不錯的查詢語言。

此外對於目標群眾為非技術人員我們要如何在一個半小時教會學員們如何使用 SQL 語言與資料庫溝通產生相關資訊,主要可以先介紹 SQL 的定義,再藉由上述 SQL 陳述式讓學員們實際練習,練習過程中不同學員一定會有許多的問題,此時我們可以匯整所發現的問題在一次的進行講解,大概二十分鐘。待有初步的操作經驗之後,再回簡報介紹資料表的基本概念,以及目前所面臨的問題,為何要學習 SQL,主要如何解決所面臨的問題,接著講解如何透過專屬的工具,例如: Teradata SQL Assistant,透過 SQL 與資料庫溝通取得我們所需的資料之後,透過專屬工具中的功能匯出文字檔,接著轉換至 Excel 試算表中進行辦公室常用之應用,大概十分鐘。待有更進一步的操作經驗之後,再回簡報介紹資料庫的基本概念,接著講解如何在暫存資料表將透過 SQL 查詢建立為資料表,以利解決更複雜的查詢問題,若有時間則可以講解基本資料 ETL 處理的概念,畢竟數據管理有 70% 以上皆是進行 資料 ETL 處理,大概十分鐘。最後用二十分鐘至三十分鐘介紹更多有關 SELECT 之 SQL 陳述式基本操作和透過 EXPLAIN 指令解決執行時的效能問題,透過基本操作間接的說明更多該注意的語法細節和執行效能等經驗,例如:針對 SELECT 選擇只應該包含需要的欄位以利節省 SPOOL 空間提高效能,針對 WHERE 條件其中判斷日期區間的運算建議採用 BETWEEN 查詢效能更優於邏輯判斷式、… 等。

最後以 SQL 資料定義語言 (Data Definition Language,DDL) 、資料操作語言 (Data Manipulation Language,DDL)和資料控制語言 (Data Control Language,DCL) 進行指令總結。此外每上完初學者課程講師收獲最大,因為我們要儘可能用非技術話語教會學員們基本操作之外,還要能在簡報過程中教些眉眉角角的經驗,以及現場上機互動和課後問答皆是最困難的部份,值得我們更深入的學習該課程的核心原理。

相關資源

資料處理 Relational Model (1)

基本介紹

教學目標

初步了解資料庫領域的理論基礎 - 關聯式模型 (Relational Model)。

重點概念

理論基礎

SQL 不等於關聯式模型,關聯式模型並非與特定產品有關,而是一種理論基礎,代表其它事件依據或洐生的根本,在關聯式模型中我們將資料表對應關聯,列對應值列 (tuple) ,欄對應屬性,以及型別對應值域。原始模型主要可分為結構、完整和操作。

  • 結構
    結構的特色主要就是關係,關係主要按照值域所定義,也就是值的概念群集,其中實際關係的屬性會附著實際的值,其中 N 個屬性代表 N 階關係,此外每個關係附支援不同的鍵,主要可分為候選鍵主要是指每個關係至少都有一個候選鍵,同時也是唯一識別,以及外來鍵主要是指關係中的一組屬性,其值必需對應其它關係中候選鍵的值。

  • 完整
    任何資料庫都有許多特定限制條件必須符合,並且以關係表示,主要以布林運算式表示,其運算值必須為「真」,也就是完整性限制, 此外每個關係皆適用兩個通用限制,主要可分為實體完整性主要是指主鍵的屬性不可以是空值 (NULL),以及參考完整性主要是指不允許有任何未對應外來鍵的屬性。

  • 處理
    處理可以分為兩種,第一種為關聯式代數的關係運算子主要是指任何關係運算子皆定義成符合至少輸入一個關係,但是只輸出一個關係的條件,其中運算子包括限制、投影、乘積、交集、聯集、差集、合併、除法、延展和摘要,相當於 SQL 中 WHERE 、 HAVING 和 JOIN 等應用。第二種為關聯式指定運算子主要是指將某些關聯運算式的值指派至關係中,任何關係運算子皆會定義成符合至少輸入一個關係,而得出另一個關係,同時也因為封閉性因此能夠寫出巢狀關聯運算式,其中運算子包括插入、刪除、更新,相當於 SQL 中 CREATE 、 UPDATE 、 DELETE 等應用。

然而每個關係皆有標頭和本體,標頭代表一群屬性,本體則是一群符合標頭的值列 (Tuple),然而關係絕不會帶重複的值列,因為本體是值列的集合,集合並不包含重複的元素,有趣的是 SQL 並不符合這項特性,故我們可以透過 DISTINCT 和 GROUP BY 進行處理,以及集合不沒有順序關係,可是 SQL 卻能透過 ORDER BY 進行排序優化顯示結果,並非關係的運算子。

最後關係又可分為基本關係和衍生關係,差別在於若我們能透過運算中得出更多的關係即是基本關係,反之則是衍生關係,關係的值永遠是依當下運算特定關聯運算式的結果為主,相當於 SQL 中檢視表之應用,此外實體儲存的資料和基本關係必需有某種對應,以便需要時快速建立出基本關係,相當於 SQL 中索引、雜湊、分割和叢集等應用。

名詞定義

  • 值列
    假設 T1、T2、…、Tn (n > -1) 是型別名稱,且不一定完全不同。每個 Ti 各搭配不同的屬性名稱 Ai,所得出的每個「屬性名稱」和「型別名稱」的組合即是屬性。接著每個屬性搭配一個屬於 Ti 型別的值 vi,所得出的 n 個「屬性 : 值」組合就是元件,如此定義的 n 元件之集合,假設叫 t 帶著屬性 A1、A2、…、An 的值即是值列,其中 n 是 t 的階數,通常 n 階值列即是 n 元,此時全部 n 個屬性的集合 即是 t 的標頭。

  • 關係
    假設 {H} 是值列的標頭,而且 t1、t2、…、tm (m > -1) 是帶標頭的不 tuple,則標頭與值列集合 {t1,t2,…,tm} 的組合,假設叫 r ,其帶著屬性 A1、A2、…、An 的值即是關係。此外 A1、A2、…、An 是標頭內的屬性,r 的標頭是 {H},所以 r 和標頭一樣具有相同的屬性及階數,因此 r 的本體是值列的集合 {t1,t2,…,tm},m 值為 r 的基數。

  • 候選鍵
    假設 K 是關係變數 R 之標頭的子集,則當 K 能完全符合唯一性和最簡性時,K 即是 R 的候選鍵,其中唯一性指的是 R 不可能有兩個不同值列帶相同的 K 值,最簡性指的是 K 的真子集都不具唯一性,其中真子集指的是若 B 為 A 的子集合和 A 是 B 的超集合則包含 A 可能與 B 相等之可能性,則真子集會將其可能性排除。

  • 外來鍵
    假設 R1 與 R2 是關係,但未必不同,而且假設 K 是 R1 的鍵,FK 是 R2 標頭的子集,則可能經過屬性重新命名之後,會得到和 K 一樣的屬性。所以當 R2 內每個值列總是有個 FK 值和 R1 內某個必然唯一的值列之
    K 值相等時,則 FK 即是外來鍵。

  • 衍生關係
    關係 V,是把 t 時點衡量某關聯運算式的結果當成值的關係,則該運算式是在定義 V 時所訂,而且必須提到至少一個關係。

相關資源

資料處理 SQL (2)

基本介紹

教學目標

初步了解從資料倉儲談起 SQL 對於資料分析的重要性。

重點概念

最早 1960 年開始就有大量的檔案和報表需要進行同步處理、1970 年開始有資料庫管理系統,1975 年開始有線上交易處理 (Online-transaction Processing,OLTP) 的應用,直到 1980 年才開始有 PC 和 SQL 語言等應用,雖然當時已有管理資訊系統 (Management Information Systems,MIS) 和決策資源系統 (Decision Support Systems,DSS) 應用資料至管理決策,可是卻沒有資料庫可以同時處理操作交易處理和分析處理,也因此在 1990 年 W. H. Inmon 就提出資料倉儲 (Data Warehouse) 之應用。在過去企業談資料倉儲最常見的就是 Teradata 它是早在 1992 年就採用在分散式架構以大規模平行處理 (Massively Parallel Processing,MPP) 的系統進行資料倉儲之商業應用,更在 1994 年被 Gartner 稱為商業平行處理之領導者,保持領先地位直到 2001 年介紹金融產業針對資料分析架構的解決方案,以台灣為例就有國泰世華銀行和中國信託銀行是採用 Teradata 的解決方案,資訊人員只需要學習 SQL 語言 即可撈取資料倉儲的資料進行分析。

然而目前針對大數據議題進行資料分析之應用,我們最常聽到的就是由 Doug Cutting 在 2006 年所建立的 Hadoop 開放源始碼架構,主要是從 1997 年的搜尋引擎專案 (Apache Lucene) 至 2001 年的網路爬蟲 (Apache Nutch) 陸續實作 Google 所發表的分散式儲存技術 (Google File System,GFS) 和平行化計算 (MapReduce) 論文理論,接著在 2006 年將相關技術匯整至搜尋引擎專案 (Apache Lucene) 中,重新命名為 Hadoop。在此之後就有許許多多的大公司陸續延伸 Hadoop 專案的整合應用,例如 Yahoo!、Facebook、Twitter、LinkedIn、Cloudera、Hortonworks、MapR、Amazon、Microsoft、IBM、… 等網路大公司,其中 Facebook 在 2012 年釋出 Hive 專案主要就是在基於 Hadoop 專案架構上提供資料匯整和即席查詢 (Ad-hoc Query) 的資料倉儲架構,簡單來說就是資訊人員只需要學習 SQL 語言 即可撈取資料倉儲的資料進行分析。

其中 Teradata 主要以關聯式資料庫處理結構化資料,Hive 則是以在 HDFS 架構上處理非結構化資料 (MapReduce 處理) 皆是透過 SQL 語言 進行資料分析和查詢,當然 Teradata 更提供 Hadoop 連接器 (Teradata Connector for Hadoop,TDCH) 直接與底層的 Hadoop 相關專案架構 (Cloudera、Hortonworks、MapR) 進行整合應用,此外 Facebook 在 2013 年推出針對大數據 (>1PB) 的分散式 SQL 查詢引擎,至 2015 年 Teradata 成為全球第一家為 Presto 提供商業支援的公司,可以從不斷成長的 Presto 社群取得大量的回饋資訊,並且根據這些資訊提供最佳的 SQL on Hadoop 之應用。

因此在企業結構化關聯性資料之大小為 TB (terabyte) 世代 Teradata 是最適合的資料分析架構,然而在社群網路非結構化關聯性資料之大小為 PB (petabyte) 世代 Hadoop 將會是最適合的資料分析架構,那接著下來 EB (exabyte) 、ZB (zettabyte) 或 YB (yottabyte) 的不同世代因應不同需求的資料分析,將會有最先進技術開發出的資料分析架構我們需要不斷學習精進,若是對於初學者而言需要先學會其中一項專業技術建議先學習 SQL 結構化查詢語言

相關資源

資料分析 Data Science & Machine Learning (1)

基本介紹

教學目標

初步了解如何透過 edX 網站學習資料科學和機器學習。

重點概念

資料科學簡介

資料科學是有關使用資料進行決策進而採取行動。

處理過程主要包括五個步驟:

  1. 尋找資料 (資料選擇)
  2. 獲取資料 (前置處理)
  3. 清理和轉換資料 (資料轉換)
  4. 了解資料關係 (資料探勘)
  5. 透過資料傳遞價值 (解釋與評估)

資料科學理論

相關理論主要有五個應用:

  1. 機器學習 (Machine Learning)
  2. 迴歸 (Regression)
  3. 分類 (Classification)
  4. 分群 (Clustering)
  5. 推薦 (Recommendation)

實際操作應用

首先學習如何透過 Azure ML 進行資料處理,其中會搭配使用 R 、 Python 和 SQL ,包括資料獲取、資料取樣、資料量化,資料清理和資料轉換。

接著學習如何進行資料探勘和視覺化呈現,更重要透過 Azure ML 進行建模與評估,以及迴歸、分類和分群等實作操作。

最後則是完成推薦應用,以及如何透過建立好的模型進行預測分析,主要有兩種方式提供預測分析的網路服務:

  • Request-Response Service
    為了單一或多個資料列進行同步預測分析。
  • Batch Execution Service
    為了巨量整批資料進行非同步預測分析。

課程總共五堂課,每堂課有五題測驗 (單複選題,有兩次機會) 佔 50% ,以及期末考有二十題測驗 (單複選題,只有一次機會) 佔 50% ,合計總分達 70% ,題目中等不會太難基本上有看影片和重點整理就能夠通過,接著付 49 美元等待身份驗證之後,即可取得證書

相關資源