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 Console」(開啟 Google 控制台) 按鈕
   • 剩餘時間
   • 必須在這個研究室中使用的暫時憑證
   • 完成這個研究室所需的其他資訊 (如有)

2. 按一下「Open Google Console」(開啟 Google 控制台)。接著，研究室會啟動相關資源並開啟另一個分頁，當中會顯示「Sign in」(登入) 頁面。

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

   注意事項：如果頁面中顯示了「Choose an account」(選擇帳戶) 對話方塊，請按一下「Use Another Account」(使用其他帳戶)。

3. 如有必要，請複製「Lab Details」(研究室詳細資料) 面板中的使用者名稱，然後貼到「Sign in」(登入) 對話方塊。按一下「Next」(下一步)。

4. 複製「Lab Details」(研究室詳細資料) 面板中的密碼，然後貼到「Welcome」(歡迎使用) 對話方塊。按一下「Next」(下一步)。

   重要注意事項：請務必使用左側面板中的憑證，而非 Google Cloud 技能重點加強的憑證。 注意事項：如果使用自己的 Google Cloud 帳戶來進行這個研究室，可能會產生額外費用。

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

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

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 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。