巨量資料與統計分析 政治大學統計系余清祥 2017 年 10 月 3 日第三週 :SQL 和 R 軟體介紹 http://csyue.nccu.edu.tw 1
SQL 介紹 使用方法 2
Agenda 認識 SQL 與資料庫 Database Basic, Data Input SQL 的基本指令實例操作
CH1 認識 SQL 與資料庫
1.1 SQL 簡介 結構化查詢語言 (Structured Query Language), 簡稱 SQL, 為專門用於關聯式資料庫的一種查詢語言 可用來定義資料庫結構 建立表格 指定欄位型態與長度 ; 也能新增 異動或查詢資料 統計分析軟體結合 SQL 的程式能力為必備技能
1.2 資料庫簡介 資料庫 (Data Base) 為一存放大量資料的地方, 由各式各樣的資料匯集而成 資料庫管理系統 (Database Management System) 提供使用者一個環境, 使其能有效率且方便地對資料庫進行管理 透過 SQL 語法, 順利達到資料庫之間的溝通與管理資料
CH2 Database Basic & Input
2.1 Database Basic 使用資料庫一開始, 需在電腦內建立一個資料夾來作為存放 SQL 之 Database 的檔案路徑 如下圖, 先在 D 槽, 新增一個資料夾 SQL SYSTEM DATABASE
2.1 Database Basic 開啟 SQL Server Management Studio, 並點選連結, 接著點選新增查詢, 就會出現可輸入程式碼的工作區, 如下圖 :
2.1 Database Basic 在工作區, 輸入下列程式碼, 並按執行, 以建立資料庫 程式碼 : CREATE DATABASE 教學資料庫 ON(NAME = 教學資料庫, FILENAME= 'D:\SQL SYSTEM DATABASE\ 教學資料庫.MDF')
2.1 Database Basic 執行完成後, 在左邊物件總管上方, 先按重新整理, 再點開資料庫, 就會看到剛才所建立的教學資料庫
2.2 Data Input 首先, 將 Excel 檔匯入資料庫成資料表 Step1 : 在欲加入資料表之資料庫按右鍵 工作 匯入資料
2.2 Data Input Step2 : 選取資料來源 設定檔案路徑和版本 瀏覽選取要匯入的檔案
2.2 Data Input Step3 : 將框線處 ( 工作表 1$) 改成欲在 SQL 內顯示之資料表名稱 (table1) 完成
2.2 Data Input Step4 : 執行完成後, 在左邊物件總管上方, 先按重新整理, 再點開教學資料庫, 就會看到剛才所匯入的資料表 在資料表按右鍵, 點選選取前 1000 個資料列 (W), 就可以看到資料輸入後的型態
2.2 Data Input NOTE: 若匯入 EXCEL 檔有問題, 請至以下網站下載 : 2007 Office system 驅動程式 : 資料連線元件 https://www.microsoft.com/zhtw/download/confirmation.aspx?id=23734
2.2 Data Input 練習 : 在 Excel 輸入以下五個資料表, 並匯入 SQL Server A : icd9 c1 c2 101 11 22 B1 : ID SEX AGE Disease A F 1 A5 B F 2 B6 C M 3 C7
3 103 c 2.2 Data Input B2 : B3 : ID SEX AGE Disease A F 1 A4 D M 2 D5 E M 5 E4 ID SEX AGE Disease C M 3 C7 F F 4 F2 G M 6 G3 CD : ID icd9 col1 1 101 a 2 102 b
CH3 SQL 的基本指令
3.1 Learning Concept SQL 的資料欄位, 主要分為數值欄位 字串欄位兩大類 數值欄位 : int: 整數, 範圍從 -2^31~ 2^31 bigint: 整數, 範圍從 -2^63 ~ 2^63-1 float: 近似小數資料的資料類型, 範圍從 -1.79E+308~1.79E+308 real: 近似小數資料的資料類型, 範圍從 -3.04E+38~3.04E+38
3.1 Learning Concept 字串欄位 : char: 固定大小浪費空間, 所需計算時間少, 只能儲存英文字元 varchar: 不固定長度, 必須要花費較多的 CPU 計算時間, 只能儲存英文字元 nchar nvarchar: 與前兩者的差異, 可儲存其他字元 ( 中文 ) NOTE: 數值欄位常用 float, 字串欄位則是 nvarchar 若欄位名稱是由中文與數字組成, 則在指令中需加上 [ ] (ex.[ 資料行 0])
3.2 Basic Command 3.2.1 SQL 基本符號介紹 : *:ALL ' ': 中間輸入特定值, 主要是用於加入字串 --: 後面可輸入註解, 不會被程式語言讀入 /* */: 中間可輸入註解, 不會被程式語言讀入
3.2 Basic Command 3.2.2 SQL 基本語法 : SELECT 欄位名稱 INTO 表名 FROM 表名 WHERE 條件敘述 GROUP BY 欄位名稱 ORDER BY 欄位名稱
3.2 Basic Command EX1 : 將 CD 表中,col1 欄位是 a 的所有欄位, 挑出並存到新的資料表 CD1 中 EX2 : 將 CD 表中, 欄位順序調整, 並存到新的資料表 CD2 中 有問題都可以互相討論噢!
3.2 Basic Command <Ans1> : SELECT * /* 挑出所有欄位的資料 */ INTO CD1 /* 存到新的資料表 CD1 中 */FROM CD /* 從 CD 表抓資料 */ WHERE col1 = 'a' /* 選取資料的條件為 : col1 欄位是 a */
3.2 Basic Command <Ans2> : select col1,icd9,id /* 依序挑出 col1,icd9,id 欄位的資料 */ into CD2 /* 存到新的資料表 CD1 中 */ from CD /* 從 CD 表抓資料 */
3.2 Basic Command 3.2.3 資料表合併 : 將 B1 與 B2 合併成 B
3.2 Basic Command Code: SELECT * /* 挑出所有欄位的資料 */ INTO B /* 存到新的資料表 B */ FROM B1 /* 從 B1 表抓資料 */ UNION ALL SELECT * FROM B2 /* 再從 B2 表抓資料 */
3.2 Basic Command EX3: 將 B1 與 B3 合併成 B4 B1: B4: B3:
3.2 Basic Command <Ans3>: SELECT * /* 挑出所有欄位的資料 */ INTO B4 /* 存到新的資料表 B4 */ FROM B1 /* 從 B1 表抓資料 */ UNION SELECT * FROM B3 /* 再從 B3 表抓資料 */
3.2 Basic Command 3.2.4 修改欄位屬性 Step1: 先在要修改欄位所屬的資料表按右鍵, 點選設計 (G), 可以看到目前個欄位的資料類型
3.2 Basic Command Step2: 現在想把 AGE 的欄位格式, 從 float 轉換成 nvarchar(15) Code: ALTER TABLE B /* 欲修改的欄位在 B 表中 */ ALTER COLUMN AGE nvarchar(15) /* 將 AGE 欄位格式, 從原本的 float 轉換成 nvarchar(15)*/
3.2 Basic Command 3.2.5 修改欄位名稱 : 將 Disease 欄位名稱改為 ID_Disease Code: SP_RENAME 'B.Disease', 'ID_Disease', 'COLUMN' /* 將 B 表中之 Disease 欄位名稱修改成 ID_Disease */
3.2 Basic Command 3.2.6 新增欄位 : 新增出生年份欄位 Code: ALTER TABLE B ADD 出生年份 NVARCHAR(15) /* 將 B 表加入 ' 出生年份 ', 欄位格式為 NVARCHAR(15) */
3.2 Basic Command 3.2.7 刪除欄位 : 刪除出生年份欄位 Code: ALTER TABLE B DROP COLUMN 出生年份 /* 將 B 表中出生年份欄位刪除 */
3.2 Basic Command 3.2.8 填入欄位 Step1: 在 B 表中新增欄位 ID1 Code: ALTER TABLE B ADD ID1 NVARCHAR(15) /* 在 B 表中新增 ID1 欄位 */
3.2 Basic Command Step2: 將 ID_Disease 欄位的第一個英文字母, 填入 ID1 Code: UPDATE B /* 欲填入的欄位, 位於資料表 B 中 */ SET ID1 = SUBSTRING (ID_Disease,1,1) /* 將 ID_Disease 欄位的第一個英文字母, 填入 ID1*/
3.2 Basic Command NOTE:SUBSTRING(str,pos,len), 是由 <str> 中的第 <pos> 位置開始, 選出接下去的 <len> 個字元 EX4: 在 B4 表中新增 ID_SEX 欄位, 並將 ID 與 SEX 合併填入其中
3.2 Basic Command <Ans4>: Step1: ALTER TABLE B4 ADD ID_SEX NVARCHAR(15) Step2: UPDATE B4 SET ID_SEX = ID+SEX
3.2 Basic Command 3.2.9 進階條件指令 (WHERE IN): 從 B 表中抓出 ID 有出現在 B2 表中的資料 (A D E)
3.2 Basic Command Code: select * /* 挑出所有欄位的資料 */ into where_in /* 存到新的資料表 where_in 中 */ from B /* 從 B 表抓資料 */ where ID IN (SELECT ID FROM B2) /* 條件為 ID 有出現在 B2 表中 (A D E) 資料 */
3.2 Basic Command 3.2.10 進階條件指令 (LEFT JOIN): 以表 CD 為主, 去表 A 中擷取在表 CD 中沒有的資料欄位, 並用 icd9 去連結兩表
3.2 Basic Command Code: select CD.ID, 教學資料庫.dbo.CD.icd9,col1,A.c1,c2 /* 表 CD 挑出 ID,icd9,col1 欄位的資料 ; 表 A 挑出 C1,C2 欄位的資料 */ into Left_join /* 存到新的資料表 Left_join 中 */ from CD LEFT JOIN A /* 以表 CD 為主, 去表 A 中擷取在表 CD 中沒有的資料欄位 */ on CD.icd9 = A.icd9 /* 用欄位 icd9 去連結兩個表 */
CH4 實例操作
4.1 表單合併 Question: 如何利用第二章的指令, 將左邊兩個表單, 整理成右邊的表單?
4.1 表單合併 <Answer2>: Step1: 手動匯入 table9 與 table10 Step2: 將 table9 與 table10 合併成 table11 SELECT * INTO table11 FROM table9 UNION ALL SELECT * FROM table10
4.1 表單合併 Step3: 將 table11, 依據 ID, 求出 Stat 與 Doe 的總和, 存入 table12 SELECT ID, Class, Region, SUM(Stat) as Stat, SUM(Doe) as Doe into table12 FROM table11 GROUP BY ID, Class, Region Step4: 於 table12 新增欄位 ID_Region, 並填入 ID 加上 Region a. ALTER TABLE table12 ADD ID_Region nvarchar(15) b. UPDATE table12 SET ID_Region = ID+Region
4.1 表單合併 Step5: 於 table12 刪除欄位 Region ALTER TABLE table12 DROP COLUMN Region Step6: 重新排列 table12 的欄位, 依序為 ID, Class, ID_Region, Stat, Doe, 並存入 table13 SELECT ID, Class, ID_Region, Stat, Doe INTO table13 FROM table12
4.1 表單合併 Step7: 新增欄位 Total, 並填入 Stat 與 Doe 的加總 a. ALTER TABLE table13 ADD Total float b. UPDATE table13 SET Total = Stat+Doe
Thanks for your listening!
R 語言與文字採礦 51
R 語言簡介 52 R 語言自 1993 年問世, 用於統計分析 繪圖 資料採礦 矩陣運算與機器學習等多個面向 兩大特色 : 免費下載 開放原始碼 套件 :ggmap, ggplot R Studio/R Pubs/GitHub
53 安裝軟體 請到 R 軟體的網站,https://www.r-project.org/ 點左側 Download: CRAN 之後, 搜尋 Taiwan 並任選一個下載點 ( 臺灣大學或元智大學 ), 再選擇作業系統 Download R for Windows ( 以 Windows 為例 ), 點選 base 即可下載安裝
進入使用者介面 54 進入圖形使用者介面程式 進行簡單的程式練習
55 讀取文字資料 利用 readline() 語法讀取文字檔
56 刪除標點符號與數字 安裝套件 tm 或 tmcn 後, 輸入 library () 語法載入套件, 即可使用 removepunctuation() removenumbers() 等語法依序將標點符號 數字移除
57 將各句連結成一整個不分行的段落
58 中文斷詞
59 雙字詞出現次數統計
60 安裝套件 wordcloud 繪製文字雲