GSP408

總覽

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

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

本實驗室將逐步講解有關排解查詢問題的邏輯，並搭配模擬實際情境的練習活動。進行本實驗室活動時，請假設您正在與團隊中新的資料分析師合作，為回答與電子商務資料集相關的問題，對方提供了下列查詢。請根據答案修正查詢，取得有意義的結果。

課程內容

在本實驗室中，您將瞭解如何執行下列工作：

• 將專案釘選在 BigQuery 資源樹狀結構
• 使用 BigQuery 查詢編輯器和查詢驗證工具，找出及排解 SQL 語法及邏輯錯誤

設定和需求

瞭解以下事項後，再點選「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 產品和服務，請點選「導覽選單」，或在「搜尋」欄位輸入服務或產品名稱。

工作 1：將專案釘選在 BigQuery 資源樹狀結構

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

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

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

2. 點按「完成」。

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

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

5. 點按「加上星號」。

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

BigQuery 查詢編輯器和查詢驗證工具

本實驗室在以下各節的每個活動中，提供了含有常見錯誤的查詢，供您練習疑難排解。課程會引導您留意重要部分，並提供語法修正建議，以傳回有意義的結果。

請複製查詢並貼到 BigQuery 查詢編輯器中，按照疑難排解步驟及建議操作。如有錯誤，系統會在含有錯誤那一行與查詢驗證工具 (底部角落) 中顯示紅色驚嘆號。

如果執行含有錯誤的查詢，該查詢會失敗，「工作資訊」分頁也會列出該錯誤。

如果查詢正確無誤，查詢驗證工具中會顯示綠色勾號。如果看到綠色勾號，請按一下「執行」來執行查詢和檢視結果。

注意： 如需語法相關資訊，請參閱「標準 SQL 查詢語法」。

工作 2：找出完成結帳的顧客總數

本章節的目標是建立查詢，取得在您網站中成功完成結帳程序的不重複訪客數量。您的資料分析師團隊在 rev_transactions 資料表中準備了相關資料，並提供查詢範例來協助您開始分析。不過，您不確定查詢是否編寫正確。

注意事項：所有查詢均須順利執行且沒有錯誤，才能獲得滿分。

解決查詢問題 (查詢驗證工具錯誤、別名錯誤和常見錯誤)

• 查看下方查詢並回答一系列問題：

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• 底下這段更新過的查詢有問題嗎？

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• 以下使用標準 SQL 的查詢有問題嗎？

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• 現在呢？以下查詢新增了一欄：

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• 現在呢？以下查詢新增了網頁標題：

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 現在呢？缺少逗號的問題已修正。

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

答案：此查詢傳回結果了，但您確定沒有重複計算訪客人數嗎？此外，查詢只傳回一列資訊，回答了「有多少不重複訪客完成結帳」這個問題。下一節您將學習如何匯總結果。

對含有邏輯錯誤、「GROUP BY」陳述式，以及萬用字元篩選條件的查詢進行疑難排解

• 匯總下列查詢並回答問題：有多少不重複訪客完成結帳？

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 那這個呢？以下查詢新增了 COUNT() 匯總函式：

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• 下一個查詢新增了 GROUP BY 和 DISTINCT 陳述式：

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

結果

太好了！結果很不錯，但看起來怪怪的。

• 請篩選資料，讓結果只顯示「Checkout Confirmation」訪客數量：

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

點選「Check my progress」，確認目標已達成。 找出完成結帳的顧客總數

工作 3：列出電子商務網站中交易次數最多的城市

注意事項：所有查詢均須順利執行且沒有錯誤，才能獲得滿分。

在匯總錯誤後解決有關排序、計算結果欄位和篩選的問題

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

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

參考解法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. 更新上一個查詢，優先按交易次數由高到低排序城市。

參考解法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. 更新查詢並建立新的計算結果欄位，傳回各城市每筆訂單的平均產品數。

參考解法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

結果

篩選匯總結果，只傳回 avg_products_ordered 值超過 20 的城市。

• 下列查詢有什麼問題？

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

參考解法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

點選「Check my progress」，確認目標已達成。 列出電子商務網站中交易次數最多的城市

工作 4：找出各個產品類別中的產品總數

注意事項：所有查詢均須順利執行且沒有錯誤，才能獲得滿分。

使用 NULL 值篩選，找出最暢銷的產品

1. 下列查詢有什麼問題？該如何修正？

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. 下列查詢有什麼問題？

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. 更新上一個查詢，只計算每個產品類別中的相異產品數。

參考解法：

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

注意：

(not set) 可能表示該產品不屬於任何類別

${productitem.product.origCatName} 是用來呈現類別的前端程式碼，這可能代表在頁面完全顯示前，Google Analytics 追蹤指令碼已觸發

點選「Check my progress」，確認目標已達成。 找出每個產品類別中的產品總數

恭喜！

您已排解並修正 BigQuery 標準 SQL 中無法正常執行的查詢。請記得使用查詢驗證工具檢查錯誤的語法，即使查詢成功執行，也請務必留意其結果。

後續步驟/瞭解詳情

• 探索 BigQuery 公開資料集。
• 您有 Google Analytics 帳戶，並且想在 BigQuery 中查詢自己的資料集嗎？請按照匯出指南的說明操作。
• 查看「15 Awesome things you probably didn’t know about BigQuery」(15 項您可能不知道的 BigQuery 優點)。
• 查看其他實驗室，進一步瞭解 BigQuery：
  • 在 BigQuery 中使用 SQL 探索電子商務資料集
  • BigQuery 中的天氣資料
  • 在 BigQuery 建立按照日期分區的資料表

Google Cloud 教育訓練與認證

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

使用手冊上次更新日期：2024 年 8 月 26 日

實驗室上次測試日期：2024 年 8 月 26 日

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