GSP413

總覽

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

您會用到一個電子商務資料集，其中包含數百萬筆Google 商品網路商店的 Google Analytics 記錄。您將探索可用的欄位與資料列，從中取得洞察資訊。

本實驗室著重探討如何使用 SQL 的 JOIN 子句和 UNION 運算子，建立新的報表表格。

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

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

學習內容

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

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

設定和需求

瞭解以下事項後，再點選「Start Lab」按鈕

請詳閱以下操作說明。實驗室活動會計時，且中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。

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

為了順利完成這個實驗室，請先確認：

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

注意事項：請使用無痕模式 (建議選項) 或私密瀏覽視窗執行此實驗室，這可以防止個人帳戶和學員帳戶之間的衝突，避免個人帳戶產生額外費用。

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

注意事項：務必使用實驗室專用的學員帳戶。如果使用其他 Google Cloud 帳戶，可能會產生額外費用。

如何開始實驗室及登入 Google Cloud 控制台

1. 按一下「Start Lab」按鈕。如果實驗室會產生費用，請在開啟的對話方塊中選取付款方式。右側的「Lab setup and access」面板會顯示下列項目：

   • 「Open Google Cloud console」按鈕
   • 此實驗室所需的臨時憑證 (使用者名稱和密碼)
   • 完成此實驗室所需的其他資訊 (如有)

   請注意，實驗室計時器位於頁面頂端附近，會顯示剩餘時間。

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

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

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

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

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

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

   您也可以在「Lab setup and access」面板找到「Username」。

4. 點選「下一步」。

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

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

   您也可以在「Lab setup and access」面板找到「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」，並將「資料位置」設為「us (多個美國區域)」。其他選項則保留預設值。

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 陳述式查詢多個資料表。Wildcard 資料表僅適用於標準 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 查詢自有資料集嗎？請按照匯出指南的說明操作。
• 如果想進一步瞭解 JOIN 的 SQL 語法，請參閱 BigQuery JOIN 說明文件。
• 歡迎試用 Google 資料集搜尋服務，這是非常實用的資源！
• 參考下列其他實驗室：
  • 透過 BigQuery 探索 NCAA 資料
  • Cloud Managed Service for Airflow：複製不同位置的 BigQuery 資料表

Google Cloud 教育訓練與認證

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

使用手冊上次更新日期：2026 年 5 月 18 日

實驗室上次測試日期：2026 年 2 月 23 日

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