BigQuery SQL 如何对文本列取MAX值筛选FY20-Q4M系列最大值

你的核心需求是：所有属于FYxx-QxM#格式的滚动月度场景记录，仅保留同季度、同项目下M后缀数字最大的单条记录，其余非QxM类型的记录（ACTUALS、BUDGET、OUTLOOK类）全部正常保留。

具体修改点 #

• 调整原SQL中存在的几处无法正常运行的问题：
  • 移除SELECT列表中PROGRAM_NAME后面多余的逗号，避免BigQuery触发语法校验报错
  • 修正原WHERE中的模糊匹配逻辑：BigQuery的CONTAINS运算符不支持%通配符，原语句SCENARIO CONTAINS 'Q%M%'无法匹配到对应的QM类记录，需替换为标准LIKE匹配写法
  • 将重复查询3次的日历表当前季度值逻辑，简化为单次CTE计算，减少对日历表的重复扫描消耗
• 新增QxM类记录的过滤规则：
  对同项目、同季度下的QxM类记录，提取M后缀对应的数字值，按数字倒序排序后仅保留排名第一的最大值记录，实现只保留Q4M2这类最大序号的预期输出，其余低序号的Q4M0、Q4M1类记录会被自动过滤

调整后可直接运行的SQL #

WITH calendar_current AS (
  -- 单次查询当前所在财季，避免重复扫描日历表
  SELECT XYZ_PER_NAME_QUARTER AS current_quarter
  FROM `XXXXMaster_Data.CALENDAR`
  WHERE CALENDAR_DATE_STR = CURRENT_DATE()
),
base_data AS (
  SELECT  
    CASE WHEN UPPER(SCENARIO) LIKE '%ACTUALS%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER, '_ACTUALS')
        WHEN UPPER(SCENARIO) LIKE 'Q%M%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER, SUBSTR(SCENARIO,3,2))
        WHEN UPPER(SCENARIO) LIKE '%BUDGET%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER,  '_BUDGET')
        END AS YY_QQ_STATUS,
    CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER) AS fiscal_quarter,
    PROGRAM_NAME,
    XYZ_PER_NAME_QUARTER,
    SCENARIO
  FROM `XXXXFinance.FINANCE_OPS_REPORT_V` FIN  
  CROSS JOIN calendar_current
  WHERE PROGRAM_NAME = 'Kiev20'
    AND (
      XYZ_PER_NAME_QUARTER >= current_quarter
      OR (
        XYZ_PER_NAME_QUARTER < current_quarter 
        AND (UPPER(SCENARIO) LIKE '%ACTUALS%' OR UPPER(SCENARIO) LIKE '%BUDGET%')
      )
      OR (
        XYZ_PER_NAME_QUARTER < current_quarter 
        AND UPPER(SCENARIO) LIKE '%Q%M%'
      )
    )
  GROUP BY 1,2,3,4,5
),
ranked_m_records AS (
  SELECT 
    *,
    -- 识别QxM类记录，提取M后的数字做倒序排序
    ROW_NUMBER() OVER (
      PARTITION BY PROGRAM_NAME, fiscal_quarter
      ORDER BY 
        CASE WHEN YY_QQ_STATUS LIKE '%Q_M%' THEN
          CAST(SUBSTR(YY_QQ_STATUS, STRPOS(YY_QQ_STATUS, 'M')+1, 1) AS INT64) 
        ELSE -1 END DESC
    ) AS rn
  FROM base_data
)
-- 最终仅保留：非QxM类记录，以及QxM类记录里M序号最大的那条
SELECT YY_QQ_STATUS, PROGRAM_NAME
FROM ranked_m_records
WHERE 
  -- 非QxM类记录直接保留
  YY_QQ_STATUS NOT LIKE '%Q_M%'
  OR (YY_QQ_STATUS LIKE '%Q_M%' AND rn = 1)
ORDER BY YY_QQ_STATUS

效果验证逻辑 #

以你提供的样例数据为例：

• Q2_ACTUALS、Q3_ACTUALS、Q3_BUDGET、Q4_BUDGET、Q4_OUTLOOK这类非QxM格式的记录，会直接保留
• Q4M0、Q4M1、Q4M2属于QxM格式的记录，会提取M后面的0、1、2做数字排序，仅保留数字最大的Q4M2，完全匹配你期望的输出结果。

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