sql - 在 BigQuery 中取消嵌套多个数组

Question

在这个例子中，我有一个书籍数据库，每本书有一条记录。记录包含图书所有者、流派和一些其他信息。我需要返回每个所有者、每个流派的前 20 个示例，以及该行中的所有数据。

我有这段代码，它可以为行中的一个数据点 (Data_one) 执行我需要的操作:

WITH `project.dataset.table` AS (
  SELECT 
    Name name, 
    Genre genre, 
    Data_one org
  FROM `project.dataset.booktable`
), search AS (
  SELECT name, genre FROM
  UNNEST(['Alex','James']) name, 
  UNNEST(['HORROR','COMEDY']) genre
)
SELECT name, genre, org 
FROM (
  SELECT t.name, t.genre, ARRAY_AGG(t.org LIMIT 20) orgs
  FROM `project.dataset.table` t JOIN search s 
  ON LOWER(s.name) = LOWER(t.name) 
  AND LOWER(s.genre) = LOWER(t.genre) 
  WHERE RAND() < 0.5
  GROUP BY t.name, t.genre
), UNNEST(orgs) org
ORDER BY name, genre, org

但是当我尝试将其扩展为处理行中的第二段(最终是相当多的)数据时，它会将返回的记录膨胀 200 倍:

WITH `project.dataset.table` AS (
  SELECT 
    Name name, 
    Genre genre, 
    Data_one org,
    Data_two org2
  FROM `project.dataset.booktable`
), search AS (
  SELECT name, genre FROM
  UNNEST(['Alex','James']) name, 
  UNNEST(['HORROR','COMEDY']) genre
)
SELECT name, genre, org, org2 
FROM (
  SELECT t.name, t.genre, ARRAY_AGG(t.org LIMIT 20) orgs, ARRAY_AGG(t.org2 LIMIT 20) orgs2
  FROM `project.dataset.table` t JOIN search s 
  ON LOWER(s.name) = LOWER(t.name) 
  AND LOWER(s.genre) = LOWER(t.genre) 
  WHERE RAND() < 0.5
  GROUP BY t.name, t.genre
), UNNEST(orgs) org, UNNEST(orgs2) org2
ORDER BY name, genre, org, org2

我知道 UNNEST 将一个数组转换为一个表，但这是否以某种方式创建了一个数组的数组并取消嵌套？我不熟悉语法。

编辑:我试图获取的数据都在同一级别，所有单个数据点(无数组)和 NULLABLE STRINGS、INTEGERS、TIMESTAMPS、FLOATS 的混合

例如:

Genre   STRING  NULLABLE
Name    STRING  NULLABLE    
Data_one    STRING  NULLABLE    
Data_two    STRING  NULLABLE    
Data_three  INTEGER NULLABLE    
Data_four   TIMESTAMP   NULLABLE    

Owner   |   Genre    |   Data_one    | Data_two   |Data_three|Data_four
Alex    |   Horror   |  Stephen King |    IT      |    3     |2018-01-02
Alex    |   Sci-fi   |   Andy Weir   |The Martian |    5     |2018-01-02
James   |   Horror   |  Bram Stoker  |   Dracula  |    2     |2018-01-02
Sarah   |   Horror   |  Stephen King | The Stand  |    3     |2018-01-02
James   |   Horror   |  Stephen King |Pet Sematary|    2     |2018-01-02

Answer 1

由于您的问题泄露了细节 - 下面的答案只是您探索的方向

#standardSQL
SELECT name, genre, data_one, data_two FROM (
  SELECT t.name, t.genre, ARRAY_AGG(t.org LIMIT 20) orgs, ARRAY_AGG(t.org2 LIMIT 20) orgs2
  FROM `project.dataset.table` t JOIN search s 
  ON LOWER(s.name) = LOWER(t.name) 
  AND LOWER(s.genre) = LOWER(t.genre) 
  WHERE RAND() < 0.5
  GROUP BY t.name, t.genre
), UNNEST(orgs) data_one WITH OFFSET pos1
, UNNEST(orgs2) data_two WITH OFFSET pos2
WHERE pos1 = pos2
ORDER BY name, genre, data_one

如您所见 - 这里引入了 OFFSET 来识别元素在数组中的位置，然后只在结果中留下那些具有相同位置的组合

在实际用例中——您很可能还有另一个字段来标识哪个 data_one 和 data_two 属于同一行，并且该字段可用于配对那些 data_one 和 data_two

希望这能帮助你找到方向

Update

当您添加架构/示例时 - 见下文

#standardSQL
SELECT name, genre, data.data_one, data.data_two, data.data_three, data.data_four 
FROM (
  SELECT t.name, t.genre, 
    ARRAY_AGG(STRUCT(data_one, data_two, data_three, data_four) LIMIT 20) data
  FROM `project.dataset.table` t JOIN search s 
  ON LOWER(s.name) = LOWER(t.name) 
  AND LOWER(s.genre) = LOWER(t.genre) 
  WHERE RAND() < 0.5
  GROUP BY t.name, t.genre
), UNNEST(data) data
ORDER BY name, genre

这正是我在另一篇文章中对您的第一个相关问题的评论中提到的内容(您可以在选择语句中使用 org.data_one、org.data_two)