BigQuery：平铺两个重复的列

问题描述

我目前正在BigQuery上使用一个非常简单的表格，每行有两个重复的列 class_numbers [REPEATED INTEGER] 和 class_descriptions [REPEATED STRING] ，这两个重复的属性具有相同的长度，每一个的索引，例如：对于给定记录 class_numbers [1] 描述将在 class_descriptions [1] / p>

我需要做的是基本上将这两个重复的字段转换为一个简单的表格，保持两列之间的对应关系，我该如何实现这一点？

使用标准SQL使用 SELECT class_numbers，class_descriptions FROM test.mytable 返回同一个表（带有重复字段），并执行相同的操作遗留SQL会抛出一个错误无法同时输出多个独立重复的字段。

如何获得理想的结果？

谢谢，

解决方案

使用标准SQL，您可以使用 UNNEST（...）WITH OFFSET 。例如，

  SELECT 
 class_number，
 class_descriptions [OFFSET（off）] AS class_description 
 FROM MyTable，
 UNNEST（class_numbers）AS class_number WITH OFFSET off; 
  

I am currently working with a very simple table on BigQuery and each row has two repeated columns class_numbers [REPEATED INTEGER] and class_descriptions [REPEATED STRING], both these repeated properties have the same length and there is a correspondence on the index of each, eg: for a given record class_numbers[1] description will be on class_descriptions[1].

What I need to do is to basically transform these two repeated fields in a flat table, keeping the correspondence between both columns, how can I achieve that?

Using a SELECT class_numbers, class_descriptions FROM test.mytable using standard SQL returns the same table (with repeated fields) and doing the same on legacy SQL throws an error "Cannot output multiple independently repeated fields at the same time.".

How can I achieve the desired result?

Thanks,

解决方案

Using standard SQL, you can use UNNEST(...) WITH OFFSET. For example,

SELECT
  class_number,
  class_descriptions[OFFSET(off)] AS class_description
FROM MyTable,
  UNNEST(class_numbers) AS class_number WITH OFFSET off;

这篇关于BigQuery：平铺两个重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！