GSP412
總覽
BigQuery 是 Google 提供的全代管數據分析資料庫,不但免人工管理,而且價格低廉。您可以使用 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 控制台
-
按一下「Start Lab」(開始研究室) 按鈕。如果研究室會產生費用,畫面中會出現選擇付款方式的彈出式視窗。左側的「Lab Details」窗格會顯示下列項目:
- 「Open Google Cloud console」按鈕
- 剩餘時間
- 必須在這個研究室中使用的暫時憑證
- 完成這個實驗室所需的其他資訊 (如有)
-
點選「Open Google Cloud console」;如果使用 Chrome 瀏覽器,也能按一下滑鼠右鍵,然後選取「在無痕式視窗中開啟連結」。
接著,實驗室會啟動相關資源並開啟另一個分頁,當中顯示「登入」頁面。
提示:您可以在不同的視窗中並排開啟分頁。
注意:如果頁面中顯示「選擇帳戶」對話方塊,請點選「使用其他帳戶」。
-
如有必要,請將下方的 Username 貼到「登入」對話方塊。
{{{user_0.username | "Username"}}}
您也可以在「Lab Details」窗格找到 Username。
-
點選「下一步」。
-
複製下方的 Password,並貼到「歡迎使用」對話方塊。
{{{user_0.password | "Password"}}}
您也可以在「Lab Details」窗格找到 Password。
-
點選「下一步」。
重要事項:請務必使用實驗室提供的憑證,而非自己的 Google Cloud 帳戶憑證。
注意:如果使用自己的 Google Cloud 帳戶來進行這個實驗室,可能會產生額外費用。
-
按過後續的所有頁面:
- 接受條款及細則。
- 由於這是臨時帳戶,請勿新增救援選項或雙重驗證機制。
- 請勿申請免費試用。
Google Cloud 控制台稍後會在這個分頁開啟。
注意:如要查看列出 Google Cloud 產品和服務的選單,請點選左上角的「導覽選單」。
開啟 BigQuery 控制台
- 在 Google Cloud 控制台中,依序選取「導覽選單」>「BigQuery」。
接著,畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊,當中會列出快速入門導覽課程指南的連結和版本資訊。
- 點選「完成」。
BigQuery 控制台會隨即開啟。
工作 1:建立新資料集來儲存資料表
在 BigQuery 專案中,建立標題為 ecommerce
的新資料集。
- 點選專案 ID 旁的三點圖示,選取「建立資料集」。
「建立資料集」對話方塊會隨即開啟。
-
將「資料集 ID」設為 ecommerce
。
-
其他選項保留預設值,然後點選「建立資料集」。
在左側窗格中,專案下方會列出 ecommerce
資料表。
點選「Check my progress」確認目標已達成。
建立新的資料集
工作 2:將實驗室專案固定在 BigQuery
情境:您的團隊提供了新的資料集,其中包含公司電子商務網站上各銷售產品的庫存量。您想瞭解哪些網站產品和欄位可彙整至其他資料集。
這個新資料集所在的專案為 data-to-insights。
- 依序點按「導覽選單」圖示 >「BigQuery」。
接著,畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊。
注意:「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊,會列出快速入門指南的連結和使用者介面更新內容。
-
點按「完成」。
-
根據預設,系統不會顯示 BigQuery 公開資料集。如要開啟公開資料集專案,請複製「data-to-insights」 這段文字。
-
依序點按「+ 新增」>「依據名稱為專案加上星號」,然後貼上「data-to-insights」。
-
點按「加上星號」。
Explorer 專區會列出名稱為 data-to-insights
的專案。
工作 3:查看欄位
接下來,請瞭解哪些網站產品和欄位可用來建立查詢,分析資料集。
-
在「資源」部分的左側窗格中,依序前往「data-to-insights
」>「ecommerce
」>「all_sessions_raw
」。
-
在右側的「查詢編輯器」下方,點選「結構定義」分頁標籤,查看各欄位和相關資訊。
工作 4:找出電子商務資料集的鍵欄位
進一步查看產品和欄位後,您想瞭解哪些網站產品和欄位可彙整至其他資料集。
查看記錄
在這一節中,您會找出網站上的產品名稱和 SKU 數量,看看對應的欄位是否都不重複。
- 找出網站上的產品名稱和 SKU 數量。複製下列查詢,貼入 BigQuery 的「編輯器」:
#standardSQL
# how many products are on the website?
SELECT DISTINCT
productSKU,
v2ProductName
FROM `data-to-insights.ecommerce.all_sessions_raw`
- 點選「執行」。
前往控制台的查詢結果分頁,查看傳回的記錄總數。
不過,真的有這麼多不重複的產品 SKU?身為資料分析師,您將執行第一個查詢,確認傳回的資料值是否重複。
- 清除先前的查詢,然後使用
DISTINCT
執行下列查詢,列出不重複的 SKU 數量:
#standardSQL
# find the count of unique SKUs
SELECT
DISTINCT
productSKU
FROM `data-to-insights.ecommerce.all_sessions_raw`
查看 SKU 與名稱之間的關係
現在請找出哪些產品有多個 SKU,以及哪些 SKU 有多個產品名稱。
- 清除先前的查詢,然後執行下列查詢,確認各產品名稱是否有多個 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
- 點選「執行」。
結果:
電子商務網站目錄顯示,各產品名稱包含多個選項 (尺寸、顏色),每個選項以獨立的 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 條件的基礎。如下所示,鍵重複將造成嚴重的資料問題。
-
編寫查詢,找出 SKU「
'GGOEGPJC019099'
」的所有產品名稱。
參考解法:
SELECT DISTINCT
v2ProductName,
productSKU
FROM `data-to-insights.ecommerce.all_sessions_raw`
WHERE productSKU = 'GGOEGPJC019099'
- 點選「執行」。
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 彙整庫存資料集,得出網站上各銷售產品的庫存量。
- 清除先前的查詢,然後執行下列查詢:
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 函式計算各產品的現有庫存量。
- 清除先前的查詢,然後執行下列查詢:
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,也就是現有庫存量的三倍!這稱為非預期的交叉聯結,我們稍後會再次探討這個主題。
點選「Check my progress」確認目標已達成。
錯誤:鍵重複
工作 6:彙整錯誤解法:彙整前,先使用不重複的 SKU
怎麼做才能解決算出三倍總和的問題呢?首先,您需要先從網站上選出不重複的 SKU,再彙整至其他資料集。
您知道一個 SKU 可能對應多個產品名稱,例如 7" Dog Frisbee。
- 收集所有可能的名稱,並匯總成陣列:
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。
- 如要移除重複的產品名稱,還能使用 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
彙整錯誤:彙整後遺失資料記錄
現在請再次彙整產品庫存資料集。
- 清除先前的查詢,然後執行下列查詢:
#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 資料欄) 來進行調查:
- 清除先前的查詢,然後執行下列查詢:
#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,才會傳回記錄。
-
重新編寫先前的查詢,改用其他彙整類型,納入網站資料表的所有記錄 (無論是否有相符的產品庫存 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
- 點選「執行」。
您成功使用 LEFT JOIN,在結果中完整傳回 1,909 個網站上的原始 SKU。
產品庫存資料集遺漏多少 SKU?
-
編寫查詢,篩選庫存資料表的 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
- 點選「執行」。
問:遺漏的產品數量為何?
答:產品庫存資料集遺漏了 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
如果情況相反呢?是否有任何產品記錄在產品庫存資料集中,但未顯示在網站資料集?
- 編寫查詢,使用其他彙整類型進行調查。
參考解法:
#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
- 點選「執行」。
答:是。網站資料集遺漏了兩個產品 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,然後變更資料表順序即可。
如要列出網站或庫存資料集遺漏的所有產品,該如何執行查詢?
- 使用其他彙整類型編寫查詢。
參考解法:
#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
- 點選「執行」。
結果是 819 + 2 = 821 個產品 SKU。
LEFT JOIN + RIGHT JOIN = FULL JOIN,這表示無論是否有相符的彙整鍵,都會傳回兩個資料表的所有記錄。隨後只要篩選兩側資料表中不相符的項目即可。
彙整錯誤:非預期的交叉聯結
不瞭解資料表鍵之間的關係 (1 對 1、1 對多、多對多),可能會傳回非預期的結果,大幅降低查詢效能。
最後介紹的彙整類型為 CROSS JOIN。
請建立新資料表,記錄清倉類別產品要套用的網站折扣百分比。
- 清除先前的查詢,然後執行下列查詢:
#standardSQL
CREATE OR REPLACE TABLE ecommerce.site_wide_promotion AS
SELECT .05 AS discount;
在左側窗格中,您會看到 site_wide_promotion
目前列在專案和資料集下方的「資源」部分。
- 清除先前的查詢,然後執行下列查詢,找出清倉產品數量:
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% 折扣。
看看將多筆記錄意外加入折扣資料表後,會造成什麼影響。
- 清除先前的查詢,然後執行下列查詢,在促銷資料表中多插入兩筆記錄:
INSERT INTO ecommerce.site_wide_promotion (discount)
VALUES (.04),
(.03);
接著查看促銷資料表的資料值。
- 清除先前的查詢,然後執行下列查詢:
SELECT discount FROM ecommerce.site_wide_promotion
系統傳回幾筆記錄?
答:3 筆
將折扣全面套用到 82 項清倉產品後,會發生什麼事?
- 清除先前的查詢,然後執行下列查詢:
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,調查根本原因:
- 清除先前的查詢,然後執行下列查詢:
#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 Cloud 教育訓練與認證
協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法,讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程,並有隨選、線上和虛擬課程等選項,方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。
使用手冊上次更新日期:2024 年 2 月 3 日
實驗室上次測試日期:2023 年 9 月 20 日
Copyright 2025 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標,其他公司和產品名稱則有可能是其關聯公司的商標。