如何在BigQuery中按分组筛选含Struct和Array类型数据的最大日期

我在BigQuery中存储的数据以Array和Struct类型组织，展开后的数据如下：

需要按每个ID的RECOMMEND.CATEGORY筛选对应最大RECOMMEND.DATE的数据，预期结果如下：

假设你的表结构为：ID是字符串类型，RECOMMEND是数组类型，每个数组元素是包含CATEGORY、RANK、DATE、HIT_GOODS（子数组，元素为含GOODS_CODE、WEIGHT的Struct）的Struct。以下两种方式可实现需求：

方式1：返回展开后的行格式 #

该方式直接输出与预期结果一致的展开行结构：

WITH unnested_data AS (
  SELECT
    ID,
    rec.CATEGORY AS recommend_category,
    rec.RANK AS recommend_rank,
    rec.DATE AS recommend_date,
    hit_goods
  FROM
    `your-project.your-dataset.your-table`,
    UNNEST(RECOMMEND) AS rec,
    UNNEST(rec.HIT_GOODS) AS hit_goods
),
max_date_marker AS (
  SELECT
    *,
    MAX(recommend_date) OVER (PARTITION BY ID, recommend_category) AS max_date
  FROM
    unnested_data
)
SELECT
  ID,
  recommend_category AS `RECOMMEND.CATEGORY`,
  recommend_rank AS `RECOMMEND.RANK`,
  recommend_date AS `RECOMMEND.DATE`,
  hit_goods.GOODS_CODE AS `RECOMMEND.HIT_GOODS.GOODS_CODE`,
  hit_goods.WEIGHT AS `RECOMMEND.HIT_GOODS.WEIGHT`
FROM
  max_date_marker
WHERE
  recommend_date = max_date
ORDER BY
  ID,
  recommend_category,
  hit_goods.WEIGHT DESC;

方式2：返回原数组结构 #

如果需要保留原始的Array+Struct存储格式，可使用该方式：

WITH unnested_recs AS (
  SELECT
    ID,
    rec,
    MAX(rec.DATE) OVER (PARTITION BY ID, rec.CATEGORY) AS max_date
  FROM
    `your-project.your-dataset.your-table`,
    UNNEST(RECOMMEND) AS rec
)
SELECT
  ID,
  ARRAY_AGG(rec) AS RECOMMEND
FROM
  unnested_recs
WHERE
  rec.DATE = max_date
GROUP BY
  ID;

注意事项 #

• 替换代码中的your-project.your-dataset.your-table为你的实际表路径。
• 两种方式的核心逻辑都是先展开数组，再通过窗口函数标记每个ID+CATEGORY分组的最大日期，最后筛选出符合条件的记录。

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