在BigQuery标准SQL中按字段分组查找日期最小值对应的记录

针对按col1分组后，提取每组中date字段最小值对应的value1、value2、value3（同日期记录任选其一）的需求，推荐用以下两种简洁方案实现：

方案一：窗口函数+临时表 #

WITH ranked_data AS (
  SELECT
    col1,
    date,
    value1,
    value2,
    value3,
    -- 按col1分组，日期升序排序，同日期随机排序实现任选其一
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY date ASC, RAND()) AS row_num
  FROM
    `你的项目ID.你的数据集ID.你的表名`
)
SELECT
  col1,
  date,
  value1,
  value2,
  value3
FROM
  ranked_data
WHERE
  row_num = 1;

方案二：QUALIFY子句简化写法（BigQuery专属） #

SELECT
  col1,
  date,
  value1,
  value2,
  value3
FROM
  `你的项目ID.你的数据集ID.你的表名`
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY date ASC, RAND()) = 1;

核心逻辑说明 #

• PARTITION BY col1：确保以col1为维度分组处理
• ORDER BY date ASC：让每组内最小日期的记录排在首位
• RAND()：针对同日期的多条记录，通过随机排序实现任选其一的要求
• 筛选row_num = 1或通过QUALIFY直接过滤，最终得到每组最小日期对应的目标字段值

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