在一个SELECT语句中使用多个UNNEST的BigQuery

Question

跟进这个问题 - Bigquery合并来自2个不同表的重复字段

@ElliottBrossard提供的解决方案是我在寻找的。谢谢！我需要从上面的字段中展开，以便按学生聚合。假设有一个额外的费用字段（每门课程），例如，假设上述输出在一个student_courses表中，我将需要执行类似以下操作：

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从多个嵌套中获取聚合？

- Subah

Answer 1

这里有一个想法，虽然有点冗长：

#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操作符

注：以上为it技术相关内容。

- Elliott Brossard

---

感谢@Elliott的快速回复，这很有帮助。我应该补充一些背景。我们正在尝试构建像上面那样的数据结构，以支持使用Tableau进行特别报告。鉴于使用嵌套结构的建议，我们正在尝试替换我们的非规范化/扁平模型，而不会从最终用户的角度牺牲简单性。由于我们有数百个度量标准，因此从实施的角度来看，上述方法可能不可行。 - Subah

澄清一下，您有数百个独立重复的字段需要执行不同的计数吗？我为您提出了您所提出的问题的答案...而不是您没有传达的问题。 - Elliott Brossard

因此，这是我澄清的部分 :) - Subah

因此，我在这里进行澄清：我们正在使用BigQuery作为我们的数据仓库解决方案，并尝试通过整合来推动极限。一个简单的例子是客户跟踪。客户产生收入，在我们的网站上有多个触点，并独立维护着多个账户。对于想要对客户行为进行分析的业务用户，他们想要追踪访问量、产生的收入以及他们的账户如何影响保留率，我们正在评估嵌套结构是否适合我们。 - Subah

在这种情况下，请提交一个带有所有相关要求的不同问题。 - Elliott Brossard