如何在沒有本機透視功能的情況下將 BigQuery 中的行透視為列？

在BigQuery 中將行轉置為列（資料透視實作）

在本BigQuery 教學課程中，我們旨在解決資料轉換的普遍需求：將行轉換為列，同時保留鍵值對。這種技術稱為數據透視，廣泛用於數據分析和報告。然而，與原生支援透視操作的傳統資料庫系統不同，BigQuery 目前缺乏此功能。

使用附加分組列進行透視

為了克服此限制，我們提出了解決方案涉及輸入資料中的附加列，該列用於對應合併到輸出中的單行中的行進行分組。此方法的工作原理如下：

第 1 步：建立資料透視查詢

我們首先建立一個將產生實際資料透視查詢的查詢。此查詢動態建構基於輸入資料集執行旋轉操作所需的程式碼。

SELECT 'SELECT id, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(key = "' + key + '", value, NULL)) as [' + key + ']'
   ) 
   + ' FROM yourTable GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM yourTable
  GROUP BY key
  ORDER BY key
) 

此查詢標識輸入資料中的所有唯一鍵值，並產生一個字串，當作為單獨的查詢執行時該字串，將執行旋轉操作。

第 2 步：執行產生的透視查詢

第一個查詢的結果是定義實際資料透視查詢的字串。我們只需複製該字串並將其作為常規 BigQuery 查詢運行即可。結果將是一個轉置資料集，其中鍵作為列名稱，值作為列值。

輸入和輸出範例

考慮以下輸入表：

id	Key	Value
1	channel_title	Mahendra Guru
1	youtube_id	ugEGMG4-MdA
1	channel_id	UCiDKcjKocimAO1tV
1	examId	72975611-4a5e-11e5
1	postId	1189e340-b08f
2	channel_title	Ab Live
2	youtube_id	3TNbtTwLY0U
2	channel_id	UCODeKM_D6JLf8jJt
2	examId	72975611-4a5e-11e5
2	postId	0c3e6590-afeb

產生的資料透視資料集將如下所示：

id	channel_id	channel_title	examId	postId	youtube_id
1	UCiDKcjKocimAO1tV	Mahendra Guru	72975611-4a5e-11e5	1189e340-b08f	ugEGMG4-MdA
2	UCODeKM_D6JLf8jJt	Ab Live	72975611-4a5e-11e5	0c3e6590-afeb	3TNbtTwLY0U

限制和替代方案

雖然這種方法有效地模仿了旋轉行為，但它也有局限性。最值得注意的是，BigQuery 對每個表施加 10,000 列的限制，這可能會阻礙具有大量唯一鍵的大型資料集的旋轉。在這種情況下，請考慮探索替代解決方案，例如使用 Python 或 R 進行外部資料透視，或使用聯合查詢方法和本地支援資料透視的資料庫系統。

以上是如何在沒有本機透視功能的情況下將 BigQuery 中的行透視為列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！