GSP413

總覽

BigQuery 是 Google 提供的全代管 NoOps 數據分析資料庫，價格相當實惠。您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用 SQL，並提供「即付即用」計費模式。這項服務可讓您專心分析資料，找出有意義的深入分析結果。

您會用到一個電子商務資料集，其中包含數百萬筆 Google Analytics 的 Google 商品網路商店記錄。您將探索可用的欄位與列，以便深入分析。

本實驗室著重探討如何使用 SQL 的 JOIN 和 UNION 語法，建立新的報表資料表。

情境：行銷團隊向您和數據資料團隊提供了公司電子商務網站的所有產品評論。您正與他們合作在 BigQuery 中建立資料倉儲，需要彙整三個來源的資料：

• 網站的電子商務資料
• 目錄商品庫存量和前置時間
• 產品評論情緒分析

學習內容

本實驗室將說明如何執行下列工作：

• 執行情緒分析，探索新的電子商務資料。
• 彙整資料集及建立新資料表。
• 透過聯集和表格萬用字元附加歷來資料。

設定和需求

點選「Start Lab」按鈕前的須知事項

請詳閱以下操作說明。研究室活動會計時，而且中途無法暫停。點選「Start Lab」 後就會開始計時，讓您瞭解有多少時間可以使用 Google Cloud 資源。

您將在真正的雲端環境中完成實作研究室活動，而不是在模擬或示範環境。為達此目的，我們會提供新的暫時憑證，讓您用來在研究室活動期間登入及存取 Google Cloud。

如要完成這個研究室活動，請先確認：

• 您可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。

注意：請使用無痕模式或私密瀏覽視窗執行此研究室。這可以防止個人帳戶和學生帳戶之間的衝突，避免個人帳戶產生額外費用。

• 是時候完成研究室活動了！別忘了，活動一開始將無法暫停。

注意：如果您擁有個人 Google Cloud 帳戶或專案，請勿用於本研究室，以免產生額外費用。

如何開始研究室及登入 Google Cloud 控制台

1. 按一下「Start Lab」(開始研究室) 按鈕。如果研究室會產生費用，畫面中會出現選擇付款方式的彈出式視窗。左側的「Lab Details」窗格會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 剩餘時間
   • 必須在這個研究室中使用的暫時憑證
   • 完成這個實驗室所需的其他資訊 (如有)

2. 點選「Open Google Cloud console」；如果使用 Chrome 瀏覽器，也能按一下滑鼠右鍵，然後選取「在無痕式視窗中開啟連結」。

   接著，實驗室會啟動相關資源並開啟另一個分頁，當中顯示「登入」頁面。

   提示：您可以在不同的視窗中並排開啟分頁。

   注意：如果頁面中顯示「選擇帳戶」對話方塊，請點選「使用其他帳戶」。

3. 如有必要，請將下方的 Username 貼到「登入」對話方塊。

   {{{user_0.username | "Username"}}}

   您也可以在「Lab Details」窗格找到 Username。

4. 點選「下一步」。

5. 複製下方的 Password，並貼到「歡迎使用」對話方塊。

   {{{user_0.password | "Password"}}}

   您也可以在「Lab Details」窗格找到 Password。

6. 點選「下一步」。

   重要事項：請務必使用實驗室提供的憑證，而非自己的 Google Cloud 帳戶憑證。 注意：如果使用自己的 Google Cloud 帳戶來進行這個實驗室，可能會產生額外費用。

7. 按過後續的所有頁面：

   • 接受條款及細則。
   • 由於這是臨時帳戶，請勿新增救援選項或雙重驗證機制。
   • 請勿申請免費試用。

Google Cloud 控制台稍後會在這個分頁開啟。

注意：如要查看列出 Google Cloud 產品和服務的選單，請點選左上角的「導覽選單」。

開啟 BigQuery 控制台

1. 在 Google Cloud 控制台中，依序選取「導覽選單」>「BigQuery」。

接著，畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，當中會列出快速入門導覽課程指南的連結和版本資訊。

2. 點選「完成」。

BigQuery 控制台會隨即開啟。

工作 1：建立新資料集來儲存資料表

首先，請在 BigQuery 中建立新資料集 ecommerce 來儲存資料表。

1. 在左側窗格中，點選 BigQuery 專案名稱 (qwiklabs-gcp-xxxx)。

2. 點選專案名稱旁邊的三點圖示，然後選取「建立資料集」。

「建立資料集」對話方塊會隨即開啟。

3. 將「資料集 ID」設為 ecommerce，並保留其餘選項的預設值。

4. 點選「建立資料集」。

點選「Check my progress」，確認目標已達成。 建立新資料集來儲存資料表

工作 2：探索產品情緒資料集

數據資料團隊透過 API 處理所有產品評論後，向您提交了每款產品的平均情緒分數與規模。

專案 data-to-insights 內含行銷團隊的資料集。根據預設，BigQuery 不會顯示 BigQuery 公開資料集。因此，雖然您看不到，本實驗室中的查詢仍會使用 data-to-insights 資料集。

1. 請先複製數據資料團隊製作的資料表，以便讀取其中的資料：

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` 注意：此副本僅供參考，本實驗室中的查詢將使用 data-to-insights 專案。

2. 點選 ecommerce 資料集，查看 products 資料表。

使用「預覽」和「結構定義」分頁檢查資料

1. 依序前往 ecommerce 和 products 資料集，然後點選「預覽」分頁標籤查看資料。

2. 按一下「結構定義」分頁標籤。

建立查詢，顯示情緒評價最正面的 5 款產品

1. 在查詢編輯器中編寫 SQL 查詢。

參考解法：

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. 修改查詢，顯示情緒評價最負面的 5 款產品，然後濾除 NULL 值。

參考解法：

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

情緒評價最差的產品為何？

點選「Check my progress」，確認目標已達成。 探索產品評價情緒資料集

工作 3：彙整資料集，進行深入分析

情境：本月首日，商品目錄團隊通知您商品目錄資料集中的 orderedQuantity 欄位，資料並非最新，需要您協助查詢各產品在 2017 年 8 月 1 日的總銷量，然後與目錄商品的現有庫存量參照，找出哪些產品需要優先補貨。

按 productSKU 計算每日銷量

1. 按照以下需求條件在 ecommerce 資料集中建立新資料表：

• 將標題設為 sales_by_sku_20170801
• 將資料來源指定為 data-to-insights.ecommerce.all_sessions_raw
• 僅納入不同結果
• 傳回 productSKU
• 傳回總訂購數量 (productQuantity)。提示：請使用 SUM() with a IFNULL 條件
• 僅篩選 20170801 的銷量
• 使用 ORDER BY 將訂單數量最多的 SKU 排在前面

參考解法：

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. 點選 sales_by_sku 資料表，然後按一下「預覽」分頁標籤。

不同的產品 SKU 賣出多少呢？

答案：462

接下來，彙整兩個資料集，在銷售資料中加入商品目錄資訊。

彙整銷售資料和商品目錄資料

1. 使用 JOIN，在網站電子商務資料中加入商品目錄資料集的以下欄位：

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. 完成未編寫完畢的查詢：

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

參考解法：

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. 修改您編寫的查詢，加入以下內容：

• 計算結果欄位 (total_ordered/stockLevel)；別名請設為「ratio」。提示：請使用 SAFE_DIVIDE(field1,field2)，以免在庫存量為 0 時，出現除以 0 的錯誤。
• 篩選結果，僅納入月初庫存銷量就已達到 50% 以上的產品。

參考解法：

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

點選「Check my progress」，確認目標已達成。 彙整資料集，進行深入分析

工作 4：附加額外記錄

您想將國際團隊提供的 2017 年 8 月 2 日店內銷量，記錄到每日銷量資料表中，該怎麼做？

建立新的空白資料表，按 productSKU 儲存 2017 年 8 月 2 日的銷售資料

1. 指定以下結構定義欄位：

• 資料表名稱：ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered：INT64 欄位

參考解法：

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. 確認現在有兩個日期相同的銷量資料表，請使用 Sales_by_sku 資料表結果中、表格名稱旁的下拉式選單，或者重新整理瀏覽器，在左選單顯示相關資料表：

3. 插入銷售團隊提供的銷量記錄：

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. 預覽資料表，確認記錄已顯示，接著點選資料表名稱查看結果。

附加歷來資料

如要將結構定義相同的資料結合在一起，有很多方式，最常見的兩種是使用 UNION 和表格萬用字元。

• Union 這個 SQL 運算子可將不同結果集的資料列結合在一起。
• 表格萬用字元可讓您使用精簡的 SQL 陳述式查詢多個資料表。萬用字元表格僅在標準 SQL 中提供。

1. 編寫 UNION 查詢，傳回以下兩個資料表中的所有記錄：

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 注意：UNION 和 UNION ALL 的區別是 UNION 不含重複記錄。

使用多個每日銷量資料表的缺點，在於您需編寫許多鏈結在一起的 UNION 陳述式。

因此，使用表格萬用字元篩選條件和 _TABLE_SUFFIX 篩選條件，是更有效的解決方式。

2. 編寫採用「*」表格萬用字元的查詢，選取 ecommerce.sales_by_sku_ 在 2017 年的所有記錄。

參考解法：

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. 修改前一個查詢，新增篩選條件，僅顯示 2017 年 8 月 2 日的結果。

參考解法：

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' 注意：您還可以考慮另一個方式，那就是建立分區資料表，自動將每日銷量資料擷取到正確的分區中。

點選「Check my progress」，確認目標已達成。 附加額外記錄

恭喜！

您已學到如何建立報表資料表，然後使用 SQL 的 JOIN 和 UNION 處理檢視的資料，藉此探索電子商務範例資料。

後續步驟/瞭解詳情

• 您有 Google Analytics 帳戶，並且想在 BigQuery 中查詢自己的資料集嗎？請按照匯出指南的說明操作。
• 如果想詳細瞭解 SQL 的 JOIN 語法，請參閱 BigQuery JOIN 說明文件。
• 如要尋找資料集，「Google 資料集搜尋」非常實用，歡迎試用！
• 參考下列其他實驗室：
  • 透過 BigQuery 探索 NCAA 資料
  • Cloud Composer：複製不同位置的 BigQuery 資料表

Google Cloud 教育訓練與認證

協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法，讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程，並有隨選、線上和虛擬課程等選項，方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。

使用手冊上次更新日期：2024 年 2 月 3 日

實驗室上次測試日期：2023 年 10 月 31 日

Copyright 2024 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。