首页 > 数据库 > mysql教程 > 如何在没有本机透视功能的情况下将 BigQuery 中的行透视为列?

如何在没有本机透视功能的情况下将 BigQuery 中的行透视为列?

Barbara Streisand
发布: 2025-01-03 14:34:40
原创
793 人浏览过

How to Pivot Rows into Columns in BigQuery Without Native Pivot Functionality?

在 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中文网其他相关文章!

来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板