BigQuery:展平两个重复的列 [英] BigQuery: flatten two repeated columns

问题描述

我目前在 BigQuery 上处理一个非常简单的表，每行有两个重复的列 class_numbers [REPEATED INTEGER] 和 class_descriptions [REPEATED STRING]，这两个重复的属性具有相同的长度并且每个属性的索引都有对应关系，例如:对于给定的记录 class_numbers[1] 描述将是在 class_descriptions[1] 上.

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?

使用使用标准 SQL 的 SELECT class_numbers, class_descriptions FROM test.mytable 返回相同的表(带有重复字段)，并且在旧 SQL 上执行相同操作会引发错误无法在以下位置输出多个独立重复字段同时.".

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?

谢谢，

推荐答案

使用标准 SQL，您可以使用 UNNEST(...) WITH OFFSET.例如，

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:展平两个重复的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！