Physical Address

304 North Cardinal St.
Dorchester Center, MA 02124

GCP 筆記: Introduction to SQL for BigQuery and Cloud SQL

SQL 基礎

SQL 是用來查詢結構化的資料集的語言,資料集有明確的規則。

資料庫是同時儲存一個或多個資料表的集合。

大多數的情況,SQL 是用來查詢部分資料表,以及資料表間聯結 (join) 的資料。

SELECT 與 FROM

SQL 有預先定義的語法,使用者用制式的語法向資料庫「問問題」。

其中最基礎的就是 SELECTFROM

  • SELECT 是用來提取想要選取的欄位。
  • FROM 用來指定想要從哪 (幾) 張資料表中提取資料。

例如「從 example_table 資料表中,提取 USER 欄位的資料」。

SELECT USER FROM example_table

或者「從 example_table 資料表中,提取 USERGENDER 欄位的資料」。

SELECT USER, GENDER FROM example_table

WHERE

WHERE 是用來篩選資料條件的語法,譬如:「從 example_table 資料表中,提取 GENDERMALEUSER 資料」

SELECT USER FROM example_table WHERE SHIPPED='YES'

BigQuery 主控台

從導覽列中點選 [BigQuery],點擊 [新增資料] > [探索公開資料集],取得公開的資料。

Qwiklab 使用的範例是 London Bicycle Hires 這個資料集,搜尋後,點擊 [查看資料集 (View Dataset)]。此時編輯區會加入名為 bigquery-public-data 的新專案。

展開 bigquery-public-data,尋找 london-bicycles 資料集,選擇 cycle-hires 資料表。點擊編輯區的 [Preview],可以以表格的方式預覽資料。

接著到編輯器中,查詢自行車還車點的名稱。

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

總共有 2,400 多萬筆的資料。

利用 SELECT * 選取所有欄位,並篩選查詢「超過 20 分鐘行程」的借還紀錄。

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

查詢出來共有 730 多萬筆資料。換句話說,倫敦有 30% 以上的共享單車租用超過 20 分鐘。

GROUP BY

利用 GROUP BY 語法,可以將資料依據欄位的微一值進行分組。

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

上述的例子便能透過 start_station_name 找到倫敦總共有 880 個共享單車的起租點。

COUNT

COUNT() 函數功能可以用來回傳共通特性的計次結果,常與 GROUP BY 一起使用。

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

執行後可以看到起租點的租借次數。

AS

AS 語法是用來製作別名 (alias) 用的。換句話說,就是將欄位名稱重新命名為新的名稱。配合 COUNT() 函數可以這樣執行。

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

執行結果中,原本未定義的欄位名稱,變成 num_starts

ORDER BY

用來排序查詢結果,有升序 (ASC)、降序 (DESC) 之分。

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name;
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num;
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

上述的指令會回傳以下結果:

  • Return a table that contains the number of bikeshare rides that begin in each starting station, organized alphabetically by the starting station.
  • Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from lowest to highest.
  • Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from highest to lowest.

執行 Cloud SQL

先將最後執行的查詢結果儲存成 CSV 檔備用。接著執行下列指令:

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

將查詢結果儲存成 CSV 檔並下載。

建立 Cloud Storage 貯體,並將下載的 CSV 檔個別命名為 start_station_name.csvend_station_name.csv 後,上傳至貯體。

從導覽選單中,選擇 [SQL]。接著建立新的實體,選擇 [MySQL],輸入資料庫名稱與 root 的密碼後,建立新的實體。

利用 Cloud Shell 建立資料庫與資料表

gcloud sql connect  qwiklabs-demo --user=root

輸入密碼後,便會進入 MySQL 的指令介面。建立資料庫 bike,接著用 USE 指令選擇資料庫,並 CREATE TABLE 建立新的資料表 london1,加入欄位 start_station_namenum

CREATE DATABASE bike;
USE bike;
CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

接著建立資料表 london2,加入欄位 end_station_namenum

USE bike;
CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

可以嘗試查詢兩個資料表的結果,雖然應該會回傳 Empty

SELECT * FROM london1;
SELECT * FROM london2;

從主控台中,回到實體的 Overview,點選 IMPORT,從 Cloud Storage 貯體將資料匯入到指定的資料表中。

DELETE

利用 DELETE FROM 語法,將符合條件的資料列刪除。

DELETE FROM london1 WHERE num=0;
DELETE FROM london2 WHERE num=0;

INSERT INTO

利用 INSERT INTO 語法,插入新的資料列。

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

UNION

利用 UNION 可以將兩個搜尋結果合併。

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000
UNION
SELECT end_station_name, num FROM london2 WHERE num>100000
ORDER BY top_stations DESC;
預設圖片
Eric Chuang

正職是廣告行銷人員,因為 Google Tag Manager 的關係開始踏入網站製作的領域,進一步把 WordPress 當成 PHP + HTML + CSS + JavaScript 的學習教材。此外,因為工作的關係,曾經用 Automattic 的 Underscores (_s) 替客戶與公司官網進行全客製化佈景主題開發。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料