GSP408

總覽

BigQuery 是 Google 的全代管數據分析資料庫，不但免人工管理，而且價格低廉。您可以使用 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. 依序點按「導覽選單」圖示 >「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 年 1 月 19 日

研究室上次測試日期：2023 年 8 月 28 日

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