BigQuery中高效统计数组元素频次并聚合求和的方法

我有一个包含INT64类型数组列的BigQuery表，数组长度固定为5，元素取值为1-5。需要将每行数组转换为5个值，分别对应1-5在该行数组中的出现频次，再对每列进行求和聚合。例如数组[1, 4, 5, 5, 4]转换后的结果如下：

当前使用的SQL代码如下：

SUM((
    SELECT
      COUNT(*)
    FROM
      UNNEST(arr) AS x
    WHERE
      x = 5)) AS five,
  SUM((
    SELECT
      COUNT(*)
    FROM
      UNNEST(arr) AS x
    WHERE
      x = 4)) AS four,
  SUM((
    SELECT
      COUNT(*)
    FROM
      UNNEST(arr) AS x
    WHERE
      x = 3)) AS three,
  SUM((
    SELECT
      COUNT(*)
    FROM
      UNNEST(arr) AS x
    WHERE
      x = 2)) AS two,
  SUM((
    SELECT
      COUNT(*)
    FROM
      UNNEST(arr) AS x
    WHERE
      x = 1)) AS one

请问是否有更高效的实现方式？

当前写法需要对每行数组执行5次UNNEST操作，数据量较大时会产生不必要的性能开销。可以通过仅一次UNNEST数组+条件聚合的方式优化，大幅减少计算量：

方案一：直接全局统计频次 #

SELECT
  SUM(IF(x = 1, 1, 0)) AS one,
  SUM(IF(x = 2, 1, 0)) AS two,
  SUM(IF(x = 3, 1, 0)) AS three,
  SUM(IF(x = 4, 1, 0)) AS four,
  SUM(IF(x = 5, 1, 0)) AS five
FROM
  your_table,
  UNNEST(arr) AS x

方案二：先按行统计再全局求和（与原逻辑完全对齐） #

如果需要先计算每行的频次再求和，可先按行聚合再累加：

SELECT
  SUM(one) AS one,
  SUM(two) AS two,
  SUM(three) AS three,
  SUM(four) AS four,
  SUM(five) AS five
FROM (
  SELECT
    COUNTIF(x = 1) AS one,
    COUNTIF(x = 2) AS two,
    COUNTIF(x = 3) AS three,
    COUNTIF(x = 4) AS four,
    COUNTIF(x = 5) AS five
  FROM
    your_table,
    UNNEST(arr) AS x
  GROUP BY
    your_table.id -- 替换为表的主键或唯一标识行的字段
)

优化原理 #

1. 仅对数组执行一次UNNEST，避免重复展开数组的冗余操作
2. 用IF或COUNTIF函数直接标记并统计匹配值，逻辑简洁且计算效率更高

内容的提问来源于stack exchange，提问作者WobbleMeister