Bigquery 在单个选择中多次取消嵌套 [英] Bigquery multiple unnest in a single select

问题描述

跟进此事 -Bigquery 组合来自 2 个不同表的重复字段

@ElliottBrossard 的上述解决方案正是我正在寻找的.谢谢！我需要从上面取消嵌套字段以获取学生的聚合.比如说，有一个额外的成本字段(每门课程)例如假设上面的输出在 student_courses 表中，我需要做类似的事情，

The above solution from @ElliottBrossard was what I was looking for. Thanks! I need to UNNEST the fields from above to get aggregations by student. Say, there is an additional field for cost (per course) For e.g. assuming the output from the above in a student_courses table, I will need to do something like,

SELECT
  COUNT(DISTINCT phone.number),
  COUNT(DISTINCT courses.Id),
  SUM(courses.Cost)
FROM
  student_courses,
  UNNEST(phone),
  UNNEST(courses)

我对上面的预期答案是 3、4、800 美元(假设 4 门课程的费用为 200 美元).上面的查询最终表现得像一个交叉连接.是否可以使用单个 Select 从多个嵌套中获取聚合?

My expected answer from the above is something like, 3, 4, $800 (assuming the 4 courses cost $200 each). The above query ends up acting like a cross join. Is it possible to get aggregate from multiple nests using a single Select?

推荐答案

这是一个想法，虽然有点冗长:

Here's an idea, although it's a bit verbose:

#standardSQL
SELECT
  (SELECT COUNT(DISTINCT number)
   FROM UNNEST(numbers) AS number),
  (SELECT COUNT(DISTINCT course_id)
   FROM UNNEST(course_ids) AS course_id),
  course_sum
FROM (
  SELECT
    ARRAY_CONCAT_AGG(
      ARRAY(SELECT number FROM UNNEST(phone))
    ) AS numbers,
    ARRAY_CONCAT_AGG(
      ARRAY(SELECT id FROM UNNEST(courses))
    ) AS course_ids,
    SUM((SELECT SUM(cost) FROM UNNEST(courses))) AS course_sum
  FROM YourTable
);

参考文献:

• ARRAY_CONCAT_AGG
• UNNEST

这篇关于Bigquery 在单个选择中多次取消嵌套的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！