arrow_back

排解資料彙整錯誤

登录 加入
欢迎加入我们的社区,一起测试和分享您的知识!
done
学习 700 多个动手实验和课程并获得相关技能徽章

排解資料彙整錯誤

实验 1 小时 universal_currency_alt 5 积分 show_chart 中级
info 此实验可能会提供 AI 工具来支持您学习。
欢迎加入我们的社区,一起测试和分享您的知识!
done
学习 700 多个动手实验和课程并获得相关技能徽章

GSP412

Google Cloud 自學實驗室標誌

總覽

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 控制台

  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」訊息方塊,當中會列出快速入門導覽課程指南的連結和版本資訊。

  1. 點選「完成」

BigQuery 控制台會隨即開啟。

工作 1:建立新資料集來儲存資料表

在 BigQuery 專案中,建立標題為 ecommerce 的新資料集。

  1. 點選專案 ID 旁的三點圖示,選取「建立資料集」

醒目顯示的「建立資料集」選項

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

  1. 將「資料集 ID」設為 ecommerce

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

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

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

建立新的資料集

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

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

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

  1. 依序點按「導覽選單」圖示 「導覽選單」圖示 >「BigQuery」

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

注意:「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊,會列出快速入門指南的連結和使用者介面更新內容。
  1. 點按「完成」

  2. 根據預設,系統不會顯示 BigQuery 公開資料集。如要開啟公開資料集專案,請複製「data-to-insights」 這段文字。

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

  4. 點按「加上星號」

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`
  1. 點選「執行」

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

醒目顯示的查詢結果分頁

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

  1. 清除先前的查詢,然後使用 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
  1. 點選「執行」

結果:

查詢結果

電子商務網站目錄顯示,各產品名稱包含多個選項 (尺寸、顏色),每個選項以獨立的 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'
  1. 點選「執行」

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 函式計算各產品的現有庫存量。

  1. 清除先前的查詢,然後執行下列查詢:
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。

  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。

  1. 如要移除重複的產品名稱,還能使用 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 資料欄) 來進行調查:

  1. 清除先前的查詢,然後執行下列查詢:
#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
  1. 點選「執行」

您成功使用 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
  1. 點選「執行」

問:遺漏的產品數量為何?

答:產品庫存資料集遺漏了 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
  1. 點選「執行」

答:是。網站資料集遺漏了兩個產品 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
  1. 點選「執行」

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

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

彙整錯誤:非預期的交叉聯結

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

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

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

  1. 清除先前的查詢,然後執行下列查詢:
#standardSQL CREATE OR REPLACE TABLE ecommerce.site_wide_promotion AS SELECT .05 AS discount;

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

  1. 清除先前的查詢,然後執行下列查詢,找出清倉產品數量:
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% 折扣。

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

  1. 清除先前的查詢,然後執行下列查詢,在促銷資料表中多插入兩筆記錄:
INSERT INTO ecommerce.site_wide_promotion (discount) VALUES (.04), (.03);

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

  1. 清除先前的查詢,然後執行下列查詢:
SELECT discount FROM ecommerce.site_wide_promotion

系統傳回幾筆記錄?

答:3 筆

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

  1. 清除先前的查詢,然後執行下列查詢:
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,調查根本原因:

  1. 清除先前的查詢,然後執行下列查詢:
#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 的商標,其他公司和產品名稱則有可能是其關聯公司的商標。