基于BigQuery获取工单最新关闭备注的SQL查询需求及修正

原始工单数据 #

number     Created_Date      Closed_Date       Close_Notes
T1         8/10/2024          null              null    
T1         8/10/2024          8/11/2024         Completed Successfully
T2         8/11/2024          null              null    
T2         8/11/2024          8/13/2024         null
T2         8/11/2024          8/13/2024         Completed Successfully

期望输出 #

需按工单编号（number）聚合，输出对应创建日期、最新关闭日期及有效关闭备注：

number     Created_Date      Closed_Date       Close_Notes
T1          8/10/2024        8/11/2024          Completed Successfully
T2          8/11/2024        8/13/2024          Completed Successfully

当前尝试的SQL及问题 #

当前查询语句：

SELECT
    Number,
    Close_Notes,
    min(Created_Date),
    max(Closed_Date)
  FROM
    SN_Change_Request group by Number

该语句存在两个核心问题：

• BigQuery中Close_Notes未包含在GROUP BY子句，也未使用聚合函数，执行会直接报错；
• 未筛选有效（非空）的关闭备注，无法保证返回正确的备注内容。

BigQuery 正确查询语句 #

基础场景（同一工单仅一条有效备注） #

SELECT
  number,
  MIN(Created_Date) AS Created_Date,
  MAX(Closed_Date) AS Closed_Date,
  MAX(Close_Notes) AS Close_Notes  -- 自动忽略NULL值，提取非空的有效备注
FROM SN_Change_Request
GROUP BY number

复杂场景（同一工单多条非空备注，取最新关闭日期对应备注） #

如果存在同一工单有多个非空备注的情况，可通过窗口函数精准匹配最新关闭日期的有效备注：

WITH ranked_records AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY number 
      ORDER BY Closed_Date DESC, 
        CASE WHEN Close_Notes IS NOT NULL THEN 1 ELSE 0 END DESC
    ) AS rn
  FROM SN_Change_Request
)
SELECT
  number,
  Created_Date,
  Closed_Date,
  Close_Notes
FROM ranked_records
WHERE rn = 1

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