如何在没有本机透视功能的情况下将 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中文网其他相关文章！