資料庫管理 Homework 期末專題 楊立偉教授 台灣大學工管系 2018.

Presentation on theme: "資料庫管理 Homework 期末專題 楊立偉教授 台灣大學工管系 2018."— Presentation transcript:

1 資料庫管理 Homework 期末專題 楊立偉教授 台灣大學工管系 2018

2 Requirement 應用真實世界的大數據進行探索性分析，找出有商業價值之應用

3 主題資料集 主題資料集 下載連結 以7zip解壓縮(含密碼) 2017/1/1 ~ 2017/11/30 bbs、forum、news
匯入至MySQL

4 2 1 4 3 用MySQL Workbench連接至MySQL後，點選左側Administration 選擇Data Import/Restore，選擇Self-contained File，選擇課程網頁上下載的hw5_content.sql檔案，開始匯入；完成後請選擇Schema中的hw5 (需要一段時間才能完成匯入，注意是否有足夠的磁碟空間)

5 題目分派 主題資料集 每組任選其一（可與它組交換），分派如下 繳交紙本報告 期末每組上台簡報 組別 資料集1 資料集2 1 (財經)新聞
Movie 6 11 2 Food 7 12 3 Stock 8 13 4 通訊板 9 14 5 合購版 10

6 作業要求 指派領域 針對該領域所收到的資料，進行分析 設計十個以上具商業價值的問題，並以SQL回答 將問題設計（含SQL及答案）作成報告
有層次地講出一個觀察、一個故事、或一種預測

7 評分標準 分成10個等級 指派領域 依詳盡用心程度加分 商業問題設計，正確合理 發掘不為人知的資訊
使用MS Excel (Power) Pivot 製作有意義之圖或表，並詮釋之 使用Weka或R或類似工具進行分析，印出結果，並詮釋之 使用一種以上資料彼此解釋某種現象，並詮釋之 例如股市新聞加TEJ股價資料，或Movie加網路公開之票房資料等

8 Deadline 於 2018.1.3 前繳至 ceiba，並於當天簡報 由組長繳交即可 以 Word檔，做文字說明報告
取名為「組別_hw5.docx」 以 PowerPoint，做簡報（10分鐘內） 取名為「組別_hw5.pptx」 兩者一同壓縮為「組別_hw5.zip」

9 簡易社群資料分析範例 結合結構性與非結構性資料 結構性資料 非結構性資料 (文字) 以一或多句SQL查詢分析 以標記技巧轉為結構化資料
輔以字串處理函數 再以一或多句SQL查詢分析

10 社群資料分析 – 以Food為例 列出推噓最熱門的主文 列出正面情緒最高的主文
SELECT title, comment_count, post_time, author FROM content WHERE s_area_name='Food' and content_type='main' ORDER BY comment_count DESC; SELECT title, comment_count, positive_score, post_time, author FROM content WHERE s_area_name='Food' and content_type='main' ORDER BY positive_score DESC;

11 社群資料分析 – 以Food為例 配合字串處理，統計文章標題類別 left(string, len) 自左邊取len個字
right(string, len) 自右邊取len個字 mid(string, start, len) 自start起取len個字 instr(string, substring) 取出子字串在字串中的位置 可查詢更多字串函數及其使用方法 SELECT mid(title,2,2), count(*) FROM content WHERE s_area_name='Food' and content_type='main' and left(title,1)='[' GROUP BY mid(title,2,2) ORDER BY count(*) DESC;

12 社群資料分析 – 以Food為例 依餐廳名稱之討論熱門度進行統計 Step 1 以字串處理找到餐廳名稱的起迄位置
ALTER TABLE content ADD pos1 Integer; ALTER TABLE content ADD pos2 Integer; UPDATE content SET pos1=instr(content,'名稱') WHERE s_area_name='Food' and content_type='main'; UPDATE content SET pos2=instr(mid(content, pos1+3, 20), ' ') 增加pos1及pos2欄位 內文有「名稱：○ ○ ○ ○(空格)」者，予以計算 格式不符合或英文餐廳名稱可能取不正確

13 社群資料分析 – 以Food為例 Step 2 以字串處理取出餐廳名稱，暫存為tmp Step 3 與原表JOIN統計最熱門的餐廳
CREATE TABLE tmp AS SELECT id, title, author, mid(content,pos1+3,pos2) AS shopname FROM content WHERE s_area_name='Food' and content_type='main'; SELECT t.shopname, count(*) FROM content AS c, tmp AS t WHERE s_area_name='Food' and c.content_type='main' and c.id=t.id AND t.shopname<>'' GROUP BY t.shopname ORDER BY count(*) DESC; 若要加速，可將 JOIN 及WHERE條件欄位轉為短文字varchar並建立索引，最後人工過濾雜訊資料

14 社群資料分析 – 以Food為例 依內容關鍵字詞或特殊人事物做標記 分次逐步進行標記，例如分為「貴」與「便宜」
之後可單用該表，或與原表JOIN做查詢分析 首次 CREATE TABLE tmp2 AS SELECT id, title, author, "很貴" AS tag FROM content WHERE s_area_name='Food' and content_type='main' AND (content like '%好貴%' OR content like '%有點貴%' OR content like '%不便宜%' OR content like '%CP值不高%') 之後 INSERT INTO tmp2 SELECT id, title, author, "便宜" AS tag FROM content AND (content like '%便宜%' AND NOT content like '%不便宜%' OR content like '%CP值高%' OR content like '%高CP%')

15 社群資料分析 – 以Food為例 列出貴的餐聽 列出便宜的餐廳 SELECT t.shopname, count(*)
FROM tmp AS t, tmp2 as g WHERE t.id=g.id AND tag='很貴' and t.shopname<>'' GROUP BY t.shopname ORDER BY count(*) DESC; SELECT t.shopname, count(*) FROM tmp AS t, tmp2 as g WHERE t.id=g.id AND tag='便宜' and t.shopname<>'' GROUP BY t.shopname ORDER BY count(*) DESC;

16 GOOD LUCK !