Week7 - CS50x (SQL)

編輯 : Frank
日期 : 2020/03/12
參考網址

Favorite TV

表單連結
試算表連結

分析與統計群眾的喜愛節目

透過將excel檔案匯出成csv將更好進行後續操作。

方法1: 編程

流程規劃

  1. 讀取檔案 favoritetv.csv
  2. 抓取各列title資訊
  3. 統計次數
  4. 排列與顯示

這樣的操作過程是繁瑣對吧？是否能使用不同工具與語言來更好實踐它?對於 SQL 而言，這個答案是肯定的

favorites_03.py

1
2
3
4
5
6
7
8
9
10
11
12
import csv
counts = {}
with open("favoritetv.csv", "r") as file:
  reader = csv.DictReader(file)
  for row in reader:
    title = row["title"]
    if title in counts:
      counts[title] += 1
    else:
      counts[title] = 1
for title, count in sorted(counts.items(),key=lambda item: item[1],reverse=True):
  print(title, count, sep=" | ")

額外補充
pandas 也能進行良好的統計分析，但開檔讀檔仍需耗費相當龐大的開銷因此數據還是存在 SQL 才是上上策

favorites_4.py

1
2
3
4
import pandas as pd

df = pd.read_csv("favoritetv.csv")
print(df['title'].value_counts())

方法2: SQL

影片時間: 20:15

前置作業

1
2
3
4
//創建Table
sqlite3 favorites.db
sqlite > .mode csv
sqlite > .import "favoritetv.csv" favorites

資料統計與分析

1
2
3
4
5
6
7
8
9
10
11
12
//Table的格式
sqlite> .schema

//新增修改刪除
SELECT title FROM favorites;

SELECT title, COUNT(title) FROM favorites
    GROUP BY title;

SELECT title, COUNT(title) as n FROM favorites
    GROUP BY title
    ORDER BY n DESC;

SQL 介紹

優點

簡易

SQL 可以達成更簡易的搜尋，對於數據的分析將不用進行複雜的python編寫。
SQL 處理數據時，我們僅需要四個操作：

  • 創建: INSERT
  • 讀取: SELECT
  • 更新: UPDATE
  • 刪除: DELETE

快速

此外 SQL 是關係數據庫，我們將不必依賴csv文件檔案。每次csv使用時需開啟文件，並進行逐行的迭代，當csv文件包含大量數據時，這將變得很慢，SQL將提供更快的訪問權限。

功能介紹

數據類型

SQL 有不同大小的數據類型，這樣可以對於數據進行更良好的管理以避免惡意的操作來淹沒計算內存，這也許將會要造成服務器崩潰。

INTEGER

  • smallint
  • integer
  • bigint

指令功能

SQL 除了四個操作指令外，還包含許多好用功能能讓數據科學家能快速的分析與統計所需的操作。

好用的功能

  • GROUP BY
  • ORDER BY
  • COUNT
  • DESC

IMDb

IMDb 是一個關於電影演員、電影、電視節目、電視藝人、電子遊戲和電影製作小組的線上資料庫。

統計與分析IMDb

在開始之前請先下載 title.basics.tsv.gz

統計2019年的喜劇節目數量。

single_tables.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
import cs50
import csv

# Create database by opening and closing an empty file first
open(f"shows3.db", "w").close()
db = cs50.SQL("sqlite:///shows3.db")

# Create table called `shows`, and specify the columns we want,
# all of which will be text except `startYear`
db.execute("CREATE TABLE shows (tconst TEXT, primaryTitle TEXT, startYear NUMERIC, genres TEXT)")

# Open TSV file
# https://datasets.imdbws.com/title.basics.tsv.gz
with open("title.basics.tsv", "r") as titles:

    # Create DictReader
    reader = csv.DictReader(titles, delimiter="\t")

    # Iterate over TSV file
    for row in reader:

        # If non-adult TV show
        if row["titleType"] == "tvSeries" and row["isAdult"] == "0":

            # If year not missing
            if row["startYear"] != "\\N":

                # If since 1970
                startYear = int(row["startYear"])
                if startYear >= 1970:

                    # Insert show by substituting values into each ? placeholder
                    db.execute("INSERT INTO shows (tconst, primaryTitle, startYear, genres) VALUES(?, ?, ?, ?)",
                               row["tconst"], row["primaryTitle"], startYear, row["genres"])

Table格式

1
2
3
4
sqlite3 show3.db
sqlite > .schema

CREATE TABLE shows (id INT, title TEXT, year NUMERIC, genre TEXT);

資料統計與分析

1
2
3
SELECT COUNT(*) From shows WHERE genere ="Comdy" AND year = 2019;

SELECT COUNT(*) From shows WHERE genere LIKE "%Comdy%" AND year = 2019;

這樣的方式並不可靠，可能存在某種類型也包含Comdy這個單字，如果能僅搜尋genre的特定表單，這將可以解決這一問題。因此以下將多創立一個genre獨立表單來存取資料，並透過id來串連彼此的關係。

multipl_tables.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
import cs50
import csv

# Create database
open(f"shows4.db", "w").close()
db = cs50.SQL("sqlite:///shows4.db")

# Create tables
db.execute("CREATE TABLE shows (id INT, title TEXT, year NUMERIC, PRIMARY KEY(id))")

# The `genres` table will have a column called `show_id` that references
# the `shows` table above
db.execute("CREATE TABLE genres (show_id INT, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id))")

# Open TSV file
# https://datasets.imdbws.com/title.basics.tsv.gz
with open("title.basics.tsv", "r") as titles:

    # Create DictReader
    reader = csv.DictReader(titles, delimiter="\t")

    # Iterate over TSV file
    for row in reader:

        # If non-adult TV show
        if row["titleType"] == "tvSeries" and row["isAdult"] == "0":

            # If year not missing
            if row["startYear"] != "\\N":

                # If since 1970
                startYear = int(row["startYear"])
                if startYear >= 1970:

                    # Trim prefix from tconst
                    id = int(row["tconst"][2:])

                    # Insert show
                    db.execute("INSERT INTO shows (id, title, year) VALUES(?, ?, ?)", id, row["primaryTitle"], startYear)

                    # Insert genres
                    if row["genres"] != "\\N":
                        for genre in row["genres"].split(","):
                            db.execute("INSERT INTO genres (show_id, genre) VALUES(?, ?)", id, genre)

Table格式

1
2
3
4
5
sqlite3 show4.db
sqlite > .schema

CREATE TABLE shows (id INT, title TEXT, year NUMERIC, PRIMARY KEY(id));
CREATE TABLE genres (show_id INT, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id));

genre擷取

1
SELECT * FROM genres WHERE show_id = id

資料統計與分析

1
2
3
SELECT COUNT(*) FROM shows
    WHERE id IN (SELECT show_id FROM genres WHERE genre = "Comedy")
    AND year = 2019;

特殊類型

id = PRIMARY KEY
show_id = FOREIGN KEY

種類

  • PRIMARY KEY: 主鍵
  • FOREIGN KEY: 外來鍵
  • UNIQUE: 唯一值
  • INDEX: 索引值

創建索引值

1
CREATE INDEX show_index ON genres (show_id);

在沒有索引值時，SQL 會檢查每個元素，透過透過線性搜索。使用索引值後，SQL 透過B-Tree來搜索，可加速執行速度。