BigQuery 标准 SQL：如何按 ARRAY 字段进行分组

使用 BigQuery 标准 SQL 按 ARRAY 字段进行分组

sql
SELECT
  ARRAY_AGG(array_field) AS grouped_array,
  other_column
FROM
  your_table
GROUP BY
  other_column;

sql
-- 创建示例数据表
CREATE TABLE your_dataset.user_colors AS (
  SELECT 1 AS user_id, ARRAY['red', 'blue'] AS favorite_colors UNION ALL
  SELECT 2 AS user_id, ARRAY['green', 'red'] AS favorite_colors UNION ALL
  SELECT 3 AS user_id, ARRAY['blue'] AS favorite_colors
);
-- 查询并按颜色分组
SELECT
  ARRAY_AGG(user_id) AS users,
  favorite_color
FROM
  your_dataset.user_colors,
  UNNEST(favorite_colors) AS favorite_color
GROUP BY
  favorite_color;