GSP412

總覽

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

您可以透過彙整資料表，取得有參考價值的資料集洞察結果。不過彙整資料時，一些常見的錯誤可能會產生不良結果。本實驗室主要介紹如何避免這些錯誤。彙整類型：

• 交叉聯結 (Cross join)：會將第一個與第二個資料集的每一個資料列合併，並在輸出內容中顯示每一種組合。
• 內部彙整 (Inner join)：只有在兩個資料表有相符的鍵/值時，合併結果才會出現記錄。
• 左彙整 (Left join)：無論右資料表是否有相符項目，左資料表的每個資料列都會顯示在結果中。
• 右彙整 (Right join)：與左彙整相反，無論左資料表是否有相符項目，右資料表的每個資料列都會顯示在結果中。

如要進一步瞭解各種彙整方式，請參閱彙整頁面。

您會使用已載入 BigQuery 的電子商務資料集，其中包含數百萬筆 Google Analytics 的 Google 商品網路商店記錄。您可以在本實驗室中使用該資料集的副本，學習從可用的欄位和資料列中取得洞察結果。

如需語法相關資訊來追蹤及更新查詢，請參閱標準 SQL 查詢語法。

學習內容

本實驗室的內容包括：

• 使用 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. 點選專案 ID 旁的三點圖示，選取「建立資料集」。

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

2. 將「資料集 ID」設為 ecommerce。

3. 其他選項保留預設值，然後點選「建立資料集」。

在左側窗格中，專案下方會列出 ecommerce 資料表。

點選「Check my progress」確認目標已達成。

建立新的資料集

工作 2：將實驗室專案固定在 BigQuery

情境：您的團隊提供了新的資料集，其中包含公司電子商務網站上各銷售產品的庫存量。您想瞭解哪些網站產品和欄位可彙整至其他資料集。

這個新資料集所在的專案為 data-to-insights。

1. 前往 Google Cloud 控制台中的導覽選單 ()，然後點選「BigQuery」。

接著，畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊。

注意：「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊，會列出快速入門指南的連結和使用者介面更新內容。

2. 點按「完成」。

3. 根據預設，系統不會顯示 BigQuery 公開資料集。如要開啟公開資料集專案，請複製「data-to-insights」這段文字，貼到下一步的對話方塊。

4. 依序點按「+ 新增」>「依據名稱為專案加上星號」，然後貼上「data-to-insights」。

5. 點按「加上星號」。

「Explorer」專區會列出名稱為 data-to-insights 的專案。

工作 3：查看欄位

接下來，請瞭解哪些網站產品和欄位可用來建立查詢，分析資料集。

1. 在左側窗格的資源中，依序前往「data-to-insights」>「ecommerce」>「all_sessions_raw」。

2. 在右側的「查詢編輯器」下方，點選「結構定義」分頁標籤，查看各欄位和相關資訊。

工作 4：找出電子商務資料集的鍵欄位

進一步查看產品和欄位後，您想瞭解哪些網站產品和欄位可彙整至其他資料集。

查看記錄

在這一節中，您會找出網站上的產品名稱和 SKU 數量，看看對應的欄位是否都不重複。

1. 找出網站上的產品名稱和 SKU 數量。複製下列查詢，貼入 BigQuery 的「編輯器」：

#standardSQL # how many products are on the website? SELECT DISTINCT productSKU, v2ProductName FROM `data-to-insights.ecommerce.all_sessions_raw`

2. 點選「執行」。

前往控制台的查詢結果分頁，查看傳回的記錄總數。

不過，真的有這麼多不重複的產品 SKU？身為資料分析師，您將執行第一個查詢，確認傳回的資料值是否重複。

3. 清除先前的查詢，然後使用 DISTINCT 執行下列查詢，列出不重複的 SKU 數量：

#standardSQL # find the count of unique SKUs SELECT DISTINCT productSKU FROM `data-to-insights.ecommerce.all_sessions_raw`

查看 SKU 與名稱之間的關係

現在請找出哪些產品有多個 SKU，以及哪些 SKU 有多個產品名稱。

1. 清除先前的查詢，然後執行下列查詢，確認各產品名稱是否有多個 SKU。請使用 STRING_AGG() 函式，匯總單一產品名稱所有相關的 SKU，並以逗號分隔值顯示。

SELECT v2ProductName, COUNT(DISTINCT productSKU) AS SKU_count, STRING_AGG(DISTINCT productSKU LIMIT 5) AS SKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU IS NOT NULL GROUP BY v2ProductName HAVING SKU_count > 1 ORDER BY SKU_count DESC

2. 點選「執行」。

結果：

電子商務網站目錄顯示，各產品名稱包含多個選項 (尺寸、顏色)，每個選項以獨立的 SKU 銷售。

因此您會發現 1 項產品可能有 12 個 SKU。那麼 1 個 SKU 是否可以對應多項產品？

• 清除先前的查詢，然後執行下列查詢來找出答案：

SELECT productSKU, COUNT(DISTINCT v2ProductName) AS product_count, STRING_AGG(DISTINCT v2ProductName LIMIT 5) AS product_name FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE v2ProductName IS NOT NULL GROUP BY productSKU HAVING product_count > 1 ORDER BY product_count DESC

注意：您可以試著將 STRING_AGG() 換成 ARRAY_AGG()。很酷吧？BigQuery 原生支援巢狀結構陣列值，詳情請參閱陣列使用指南。

下一節將說明為何這個多對多資料關係會造成問題。

點選「Check my progress」確認目標已達成。

找出電子商務資料集的鍵欄位

工作 5：錯誤：鍵重複

追蹤庫存時，每個 SKU 只能對應一項產品，從其他資料表查詢資訊時，這項準則會是 JOIN 條件的基礎。如下所示，鍵重複將造成嚴重的資料問題。

1. 編寫查詢，找出 SKU「'GGOEGPJC019099'」的所有產品名稱。

參考解法：

SELECT DISTINCT v2ProductName, productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGPJC019099'

2. 點選「執行」。

v2ProductName	productSKU
7" Dog Frisbee	GGOEGPJC019099
7" Dog Frisbee	GGOEGPJC019099
Google 7-inch Dog Flying Disc Blue	GGOEGPJC019099

從查詢結果來看，這項產品有三個不同名稱。就這個例子來說，其中一個名稱包含特殊字元，另一個名稱略有不同：

彙整網站資料與產品庫存清單

現在來看看，當一個 SKU 對應多項產品時，彙整至資料集後會造成什麼影響。首先，探索產品庫存資料集 (products 資料表)，看看這個 SKU 在其中是否重複。

• 清除先前的查詢，然後執行下列查詢：

SELECT SKU, name, stockLevel FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGPJC019099'

彙整錯誤：非預期的多對一 SKU 關係

現在您有兩個資料集，一個為庫存量資料集，另一個為網站分析資料集。請根據網站產品名稱和 SKU，使用 JOIN 彙整庫存資料集，得出網站上各銷售產品的庫存量。

1. 清除先前的查詢，然後執行下列查詢：

SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099'

接續先前的查詢，使用簡單的 SUM 函式計算各產品的現有庫存量。

2. 清除先前的查詢，然後執行下列查詢：

WITH inventory_per_sku AS ( SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099' ) SELECT productSKU, SUM(stockLevel) AS total_inventory FROM inventory_per_sku GROUP BY productSKU

糟糕！結果為 154 x 3 = 462，也就是現有庫存量的三倍！這稱為非預期的 cross join，我們稍後會再次探討這個主題。

點選「Check my progress」確認目標已達成。

錯誤：鍵重複

工作 6：彙整錯誤解法：彙整前，先使用不重複的 SKU

怎麼做才能解決算出三倍總和的問題呢？首先，您需要先從網站上選出不重複的 SKU，再彙整至其他資料集。

您知道一個 SKU 可能對應多個產品名稱，例如 7" Dog Frisbee。

1. 收集所有可能的名稱，並匯總成陣列：

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

現在資料列顯示的不是各產品名稱，而是每個不重複 SKU。

2. 如要移除重複的產品名稱，還能使用 LIMIT 函式對陣列設限：

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName LIMIT 1) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

彙整錯誤：彙整後遺失資料記錄

現在請再次彙整產品庫存資料集。

1. 清除先前的查詢，然後執行下列查詢：

#standardSQL SELECT DISTINCT website.productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

彙整資料集後，似乎遺漏了 819 個 SKU。請在欄位中加入更具體的內容 (各資料集的一個 SKU 資料欄) 來進行調查：

2. 清除先前的查詢，然後執行下列查詢：

#standardSQL # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # IDs are present in both tables, how can you dig deeper?

彙整這 1,090 筆記錄後，兩個資料集似乎都有 SKU。您該如何找出遺漏的記錄？

彙整錯誤解法：選取正確的彙整類型，並使用 NULL 函式篩選

預設的 JOIN 類型為 INNER JOIN，這表示彙整的左右資料表有相符的 SKU，才會傳回記錄。

1. 重新編寫先前的查詢，改用其他彙整類型，納入網站資料表的所有記錄 (無論是否有相符的產品庫存 SKU 記錄)。可用的 JOIN 類型包括：INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN。

參考解法：

#standardSQL # the secret is in the JOIN type # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

2. 點選「執行」。

您成功使用 LEFT JOIN，在結果中完整傳回 1,909 個網站上的原始 SKU。

產品庫存資料集遺漏多少 SKU？

1. 編寫查詢，篩選庫存資料表的 NULL 值。

參考解法：

#standardSQL # find product SKUs in website table but not in product inventory table SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE inventory.SKU IS NULL

2. 點選「執行」。

問：遺漏的產品數量為何？

答：產品庫存資料集遺漏了 819 項產品 (SKU 為 NULL 值)。

• 清除先前的查詢，然後使用網站資料集的任一 SKU，執行下列查詢加以確認：

#standardSQL # you can even pick one and confirm SELECT * FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGATJ060517' # query returns zero results

如果情況相反呢？是否有任何產品記錄在產品庫存資料集中，但未顯示在網站資料集？

1. 編寫查詢，使用其他彙整類型進行調查。

參考解法：

#standardSQL # reverse the join # find records in website but not in inventory SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

2. 點選「執行」。

答：是。網站資料集遺漏了兩個產品 SKU。

接下來，請新增更多產品庫存資料集欄位，深入查看細節。

• 清除先前的查詢，然後執行下列查詢：

#standardSQL # what are these products? # add more fields in the SELECT STATEMENT SELECT DISTINCT website.productSKU AS website_SKU, inventory.* FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

為何電子商務網站資料集遺漏下列產品？

website_SKU	SKU	name	orderedQuantity	stockLevel	restockingLeadTime	sentimentScore	sentimentMagnitude
null	GGOBJGOWUSG69402	USB wired soundbar - in store only	10	15	2	1.0	1.0
null	GGADFBSBKS42347	PC gaming speakers	0	100	1	null	null

可能的答案：

• 其中一項是新產品 (無訂單，也無 sentimentScore 值)，另一項產品「僅限於店內購買」(in store only)
• 另一項是無訂單的新產品

為何新產品未顯示在網站資料集？

• 網站資料集是記錄以往的顧客訂單交易資料，而尚未售出的全新產品必須在有人瀏覽或購買後，才會顯示於網站分析資料集。

注意：在正式環境中執行查詢時，您通常不會用到 RIGHT JOIN。只需使用 LEFT JOIN，然後變更資料表順序即可。

如要列出網站或庫存資料集遺漏的所有產品，該如何執行查詢？

1. 使用其他彙整類型編寫查詢。

參考解法：

#standardSQL SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website FULL JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL OR inventory.SKU IS NULL

2. 點選「執行」。

結果是 819 + 2 = 821 個產品 SKU。

LEFT JOIN + RIGHT JOIN = FULL JOIN，這表示無論是否有相符的彙整鍵，都會傳回兩個資料表的所有記錄。隨後只要篩選兩側資料表中不相符的項目即可。

彙整錯誤：非預期的 cross join

不瞭解資料表鍵之間的關係 (1 對 1、1 對多、多對多)，可能會傳回非預期的結果，大幅降低查詢效能。

最後介紹的彙整類型為 CROSS JOIN。

請建立新資料表，記錄清倉類別產品要套用的網站折扣百分比。

1. 清除先前的查詢，然後執行下列查詢：

#standardSQL CREATE OR REPLACE TABLE ecommerce.site_wide_promotion AS SELECT .05 AS discount;

在左側窗格中，您會看到 site_wide_promotion 目前列在專案和資料集下方的「資源」部分。

2. 清除先前的查詢，然後執行下列查詢，找出清倉產品數量：

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

注意：使用 CROSS JOIN 時，您會發現沒有彙整條件，例如 ON 或 USING。欄位只是與第一個資料集相乘，或是為所有商品加上 5% 折扣。

看看將多筆記錄意外加入折扣資料表後，會造成什麼影響。

3. 清除先前的查詢，然後執行下列查詢，在促銷資料表中多插入兩筆記錄：

INSERT INTO ecommerce.site_wide_promotion (discount) VALUES (.04), (.03);

接著查看促銷資料表的資料值。

4. 清除先前的查詢，然後執行下列查詢：

SELECT discount FROM ecommerce.site_wide_promotion

系統傳回幾筆記錄？

答：3 筆

將折扣全面套用到 82 項清倉產品後，會發生什麼事？

5. 清除先前的查詢，然後執行下列查詢：

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

系統傳回多少產品？

答：246 項產品，而不是 82 項產品。傳回的記錄數量比一開始使用的原始資料表多。

現在請查看單一產品 SKU，調查根本原因：

6. 清除先前的查詢，然後執行下列查詢：

#standardSQL SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%' AND productSKU = 'GGOEGOLC013299'

使用 CROSS JOIN 的影響為何？

答：由於交叉聯結的折扣代碼為 3 個，相當於將原始資料集乘以 3。

注意：並非只有交叉聯結會產生這種行為。如果資料關係為多對多，即使進行一般彙整也很容易變成交叉聯結，導致意外傳回數百萬、甚至是數十億筆記錄。

解法是先瞭解資料關係再彙整，且務必假設鍵可能重複。

點選「Check my progress」確認目標已達成。

彙整錯誤解法

恭喜！

您已順利完成本實驗室的工作，透過找出重複記錄及判斷使用各種 JOIN 的時機，排解重大的 SQL 彙整錯誤。做得好！

後續行動/瞭解詳情

• 您有 Google Analytics 帳戶，並且想在 BigQuery 中查詢自己的資料集嗎？請按照 Export 指南的說明操作。
• 參閱指南，進一步瞭解最佳化查詢運算的做法。
• 如要進一步練習 SQL 的 JOIN 語法，請參閱 BigQuery JOIN 說明文件。
• 參考下列實驗室：
  • 在 BigQuery 使用 JSON、陣列和結構體
  • 在 BigQuery 和 Cloud SQL 中使用 SQL 簡介
  • 使用 BigQuery ML 預測模型預測計程車費用

Google Cloud 教育訓練與認證

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

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

實驗室上次測試日期：2023 年 9 月 20 日

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