如何在同FY（财年）中获取最早月份的result值？SQL实现疑问

我有一个按不同时间点收集数据的数据集，结构如下：

我需要新增一列year_ago，展示满足以下条件的result值：

• 与当前月份处于同一FY（财年）
• 对应时间最早

注意：数据集中部分指标没有12个月的历史数据，可能只有3、7或11个月等。

期望输出如下：

我尝试用CASE语句结合LAG函数遍历过去12个月，但返回结果全为0，不确定是表达式匹配问题还是字符串与整数不匹配？尝试的代码如下：

CASE     
    WHEN result <> 0 AND LAG(FY,1) = FY THEN COALESCE(LAG(result,1) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,2) = FY THEN COALESCE(LAG(result,2) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,3) = FY THEN COALESCE(LAG(result,3) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,4) = FY THEN COALESCE(LAG(result,4) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,5) = FY THEN COALESCE(LAG(result,5) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,6) = FY THEN COALESCE(LAG(result,6) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,7) = FY THEN COALESCE(LAG(result,7) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,8) = FY THEN COALESCE(LAG(result,8) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,9) = FY THEN COALESCE(LAG(result,9) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,10) = FY THEN COALESCE(LAG(result,10) OVER (PARTITION BY date ORDER BY date),0)     
    WHEN result <> 0 AND LAG(FY,11) = FY THEN COALESCE(LAG(result,11) OVER (PARTITION BY date ORDER BY date),0) 
ELSE 0 END AS year_ago

你的代码存在几个关键错误：

1. 分区字段错误：PARTITION BY date会把每个日期单独划分为一个窗口，窗口内只有当前日期的一行数据，LAG函数根本无法获取到同一财年其他月份的数据，最终只能返回ELSE分支的0。
2. 逻辑方向错误：你不需要用LAG逐个遍历过去12个月，需求是取同一财年的最早值，而非同比去年同月数据，完全可以直接通过窗口函数提取分区内的首行值。
3. LAG函数使用不规范：LAG(FY,1)没有指定窗口范围，默认使用的窗口是按date分区，这导致该判断毫无意义。

正确方案1：使用FIRST_VALUE窗口函数 #

这是最简洁高效的方法，直接按metric和FY分区，在每个分区内按日期升序排列，提取第一行的result值：

SELECT
    metric,
    date,
    FY,
    result,
    FIRST_VALUE(result) OVER (
        PARTITION BY metric, FY
        ORDER BY date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS year_ago
FROM your_table_name;

• PARTITION BY metric, FY：确保每个指标的每个财年单独处理
• ORDER BY date ASC：让窗口内的行按日期从早到晚排序
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：确保FIRST_VALUE能覆盖整个分区的所有行，即使数据顺序不是按日期排列也能正确取到最早值

正确方案2：子查询关联取最早值 #

如果你的SQL环境对窗口函数支持有限，可以用子查询先找到每个metric+FY组合的最早日期对应的result，再关联回原表：

SELECT
    t1.metric,
    t1.date,
    t1.FY,
    t1.result,
    t2.first_result AS year_ago
FROM your_table_name t1
JOIN (
    SELECT
        metric,
        FY,
        result AS first_result
    FROM your_table_name
    WHERE (metric, FY, date) IN (
        SELECT metric, FY, MIN(date)
        FROM your_table_name
        GROUP BY metric, FY
    )
) t2 ON t1.metric = t2.metric AND t1.FY = t2.FY;

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