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