BigQuery按family_id分组取最早数据关联表报错如何解决？

• 现有两张表：table1包含字段id、title、abstract；table2包含字段id、pubdate、family_id
• 所有id唯一，同个family_id可对应多条记录
• 需求：取出每个family_id中pubdate最小的记录，输出对应记录的id、title、pubdate

期望结果示例：

原有尝试SQL：

SELECT
  t1.id, t1.title, nt2.pubdate
FROM
  (SELECT
     id, family_id, MIN(pubdate) AS pubdate
   FROM
     table2
   GROUP BY
     family_id) AS nt2
INNER JOIN
  table1 t1
ON
  t1.id = nt2.id

执行报错：SELECT list expression references column id which is neither grouped nor aggregated at [position]

报错原因是GROUP BY查询的SELECT列表中，所有字段要么是分组维度，要么是聚合结果，你的子查询中id不属于这两类，因此触发报错。

方案1：窗口函数实现（BigQuery优先推荐） #

通过ROW_NUMBER()对同family_id的记录按pubdate升序排序，取排序第一的记录即可得到每个family_id最早发布的对应数据：

SELECT
  t1.id,
  t1.title,
  t2.pubdate
FROM (
  SELECT
    id,
    pubdate,
    ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY pubdate ASC) AS rn
  FROM table2
) t2
INNER JOIN table1 t1
  ON t2.id = t1.id
WHERE t2.rn = 1

如果同family_id下存在多条pubdate相同的最小时间记录，需要全部返回时，将ROW_NUMBER()替换为RANK()即可。

方案2：聚合结果关联实现（兼容通用SQL语法） #

先按family_id分组取出最小发布时间，再关联回原表拿到对应记录的id即可：

SELECT
  t1.id,
  t1.title,
  t2.pubdate
FROM table2 t2
INNER JOIN (
  SELECT
    family_id,
    MIN(pubdate) AS min_pub
  FROM table2
  GROUP BY family_id
) t2_min
  ON t2.family_id = t2_min.family_id
  AND t2.pubdate = t2_min.min_pub
INNER JOIN table1 t1
  ON t2.id = t1.id

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