Course 5 - Analyze Data to Answer Questions

簡介

This is the fifth course in the Google Data Analytics Certificate. In this course, you’ll explore what it means to actually analyze your data. You’ll take what you’ve learned up to this point and apply it to make sense of the data you’ve collected. You’ll learn how to organize and format your data using spreadsheets and SQL to help you look at and think about your data in different ways. You’ll also find out how to perform complex calculations with your data to address business objectives. You’ll learn how to use formulas, functions, and SQL queries as you conduct your analysis.

章節大綱

  1. Organize data for more effective analysis
    了解如何使用試算表以及 SQL 進行排序和篩選。
  2. Format and adjust data
    了解轉換和格式化資料，以及學習利用 SQL 組合資料。
  3. Aggregate data for analysis
    學習如何使用 SQL 從多個資料庫提取資料進行組合。
  4. Perform data calculations
    利用試算表中的公式、函數和資料透視表以及 SQL 幫助計算，並且學習使用 SQL 管理臨時表格。

內容筆記

Module 1 - Organize data for more effective analysis

試算表中的排序

如果排序的條件較少的話，可以使用內建的 SORT 函數，而如果條件較多則建議使用內建的排序功能。

  • SORT(範圍, 排序欄, 遞增, [排序欄_2, 遞增_2, …])
    以一欄或多欄中的值為依據，排列指定陣列或範圍內的資料列順序。
  • 全選資料，點擊 資料 > 排序範圍 > 範圍排序進階選項

SQL 中的排序

預設為升序排序，如果要改成降序只要在後面加上 DESC 即可

1
ORDER BY column_name1 ASC|DESC, column_name2 ASC|DESC...

Module 2 - Format and adjust data

試算表轉換單位

CONVERT(數值, 初始單位, 目標單位)，利用 CONVERT 函數可以將數值轉換成其他計量單位。

1
=CONVERT (A1, "F", “C”)

常用的單位 (使用時須注意大小寫)：

  • 重量：u、grain、g、ozm、lbm、ton…
  • 距離：ang、Picapt、pica、in、ft、yd、m…
  • 時間：sec、min、hr、day、yr
  • 溫度：C、F、K
  • 資訊：bit、byte
  • 速度：m/hr、mph、kn、admkn、m/s

SQL 字串合併

函數 範例 結果
CONCAT(string1, string2, …, string_n) CONCAT(‘Google’, ‘.com’) Google.com
CONCAT_WS(separator, string1, string2, …, string_n) CONCAT_WS(’ . ', ‘www’, ‘google’, ‘com’) www.google.com
|| or + ‘Google’ || ‘.com’ Google.com

Module 3 - Aggregate data for analysis

JOIN 函數

JOIN 函數可以根據相關欄位來組合多個表中的列，根據組合的關係可以分為 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 OUTER JOIN，可以表示如下圖 (取自 How to Learn SQL JOINs) ：

1
2
3
4
5
6
SELECT
  column_name(s)
FROM
  table1 --left table
INNER JOIN table2 --right table
ON table1.column_name = table2.column_name --共同欄位

子查詢

透過子查詢可以組合不同的邏輯，透過單一查詢完成更多工作，並且使程式更有效率且容易閱讀。

  1. SELECT 中的子查詢
1
2
3
4
5
6
7
8
SELECT
	station_id,
	num_bikes_available,
	(SELECT
		AVG(num_bikes_available)
	FROM bigquery-public-data.new_york.citibike_stations) AS avg_num_bikes_available --子查詢
FROM
  bigquery-public-data.new_york.citibike_stations

透過子查詢，加入 avg_num_bikes_available 作為欄位。

  1. FROM 中的子查詢
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
	station_id,
	name,
	number_of_rides AS number_of_rides_starting_at_station
FROM
	(
		SELECT
			CAST(start_station_id AS STRING) AS start_station_id_str,
			COUNT(*) AS number_of_rides
		FROM 
      		bigquery-public-data.new_york.citibike_trips
		GROUP BY 
			CAST(start_station_id AS STRING)
	) --子查詢
	AS station_num_trips
INNER JOIN 
	bigquery-public-data.new_york.citibike_stations 
ON 
	station_id = start_station_id_str
ORDER BY 
	number_of_rides DESC

利用子查詢直接建立一個表 station_num_trips，並透過 FROM 去存取。

  1. WHERE 中的子查詢
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
	station_id,
	name
FROM
	bigquery-public-data.new_york.citibike_stations
WHERE
	station_id IN
	(
		SELECT
			CAST(start_station_id AS STRING) AS start_station_id_str
		FROM
	    bigquery-public-data.new_york.citibike_trips
	  WHERE
		  usertype = 'Subscriber'
  )

利用子查詢建立一個表，之後用 IN 來篩選出有在表內的特定 id。

HAVING 子句

因為 WHERE 沒辦法和聚合函數一起使用，因此必須使用 HAVING 來替代。

  • 聚合函數類型
    • MIN()
    • MAX()
    • COUNT()
    • SUM()
    • AVG()
1
HAVING condition

Module 4 - Perform data calculations

更多試算表函數

  1. SUMIF(範圍, 條件, [加總範圍])
    傳回範圍內符合條件的項目總和，若未指定加總範圍，會使用範圍引數指定的範圍來計算總和。
  2. SUMIFS(加總範圍, 條件核對範圍1, 條件1, [條件核對範圍2, 條件2, …])
    傳回根據多個條件而得出的範圍總和，須注意和 SUMIF 的參數順序不同。
  3. COUNTIFS(條件核對範圍1, 條件1, [條件核對範圍2, 條件2, …])
    傳回根據多個條件而得出的範圍大小。
  4. SUMPRODUCT(陣列1, [陣列2], …)
    針對兩個或更多大小相同的陣列或範圍，計算彼此對應項目的乘積總和。

資料驗證

資料驗證有助於確認資料的完整性，並確保數據是乾淨的。

類型 解釋 範例
資料類型 資料是否與與欄位定義的資料類型相符 年齡值必須是數字類型
資料範圍 資料是否落在為欄位定義可接受的範圍內 分數的數據應介於 0 和 100 之間
資料約束 資料是否符合欄位的某些條件或標準 顧客數量必須為整數
資料連續 根據背景判斷資料是否有意義 產品出貨日期不能早於生產日期
資料結構 檢查資料是否符合規定的結構 網頁資料出現錯誤顯示
程式檢查 檢查程式在輸入期間是否出現前述錯誤 在某些輸入值可能出現異常

臨時表

在進行分析前，可以利用 WITH 子句建立臨時的表供之後的分析使用，類似把子查詢的表先建立出來的概念，而結束查詢之後它們會自動從資料庫中刪除。

1
2
3
4
5
6
7
WITH table_name AS(
  SELECT
    column_name(s)
  FROM
    table
  ...
)

詞彙

  1. Data aggregation (資料聚合): The process of gathering data from multiple sources and combining it into a single, summarized collection.
  2. Subquery (子查詢): A SQL query that is nested inside a larger query
  3. Temporary table (臨時表): A database table that is created and exists temporarily on a database server.