SQL获取各分组最新日期对应金额的技术实现问题

我有一张存储各项目月度交易数据的表，包含对应业务区域（如Revenue、Cost等）信息。我需要获取每个项目各业务区域对应最新日期（由ACCT_YR_NUM和ACCT_MO_NUM组合而成）的交易金额。

我已成功计算出各分组的最后交易日期（LAST_DATE列），但无法正确获取该日期对应的交易金额。

我的代码 #

WITH init AS (
    SELECT 
        f.PRJCT_ID,
        f.ACT_PLNRT_USD_AMT,
        f.RPRTG_AREA_NM,
        last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) ACCT_DATE,
        MAX(last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15'))) over (partition by f.PRJCT_ID, f.RPRTG_AREA_NM) AS LAST_DATE
    FROM my.table f
)

SELECT 
    *,
    MAX(ACT_PLNRT_USD_AMT) OVER (PARTITION BY ACCT_DATE = LAST_DATE, RPRTG_AREA_NM, PRJCT_ID) AS MAX_DATE_AMT
FROM init 

ORDER BY 
    PRJCT_ID,
    RPRTG_AREA_NM,
    ACCT_DATE

错误结果 #

期望结果 #

源表示例（my.table） #

问题出在MAX_DATE_AMT的窗口分区逻辑：PARTITION BY ACCT_DATE = LAST_DATE会把数据分成"日期匹配"和"不匹配"两组，无法按项目+业务区域统一取最新日期的金额。以下是两种修正方案：

方法1：单窗口函数直接获取 #

在初始CTE中新增窗口函数，按项目和业务区域分组后倒序排序，取最新日期对应的金额：

WITH init AS (
    SELECT 
        f.PRJCT_ID,
        f.ACT_PLNRT_USD_AMT,
        f.RPRTG_AREA_NM,
        last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) ACCT_DATE,
        MAX(last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15'))) over (partition by f.PRJCT_ID, f.RPRTG_AREA_NM) AS LAST_DATE,
        -- 取分组内最新日期的金额，所有行共享该值
        FIRST_VALUE(f.ACT_PLNRT_USD_AMT) OVER (
            PARTITION BY f.PRJCT_ID, f.RPRTG_AREA_NM
            ORDER BY last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS MAX_DATE_AMT
    FROM my.table f
)

SELECT 
    PRJCT_ID,
    RPRTG_AREA_NM,
    ACT_PLNRT_USD_AMT,
    ACCT_DATE,
    LAST_DATE,
    MAX_DATE_AMT
FROM init 
ORDER BY 
    PRJCT_ID,
    RPRTG_AREA_NM,
    ACCT_DATE

方法2：关联子查询获取最新金额 #

先计算每个分组的最新日期，再关联原表拿到对应金额，最后和原表关联返回所有行：

WITH latest_dates AS (
    SELECT 
        PRJCT_ID,
        RPRTG_AREA_NM,
        MAX(last_day(date(ACCT_YR_NUM || '-' || ACCT_MO_NUM  || '-15'))) AS LAST_DATE
    FROM my.table
    GROUP BY PRJCT_ID, RPRTG_AREA_NM
),
latest_amt AS (
    SELECT 
        l.PRJCT_ID,
        l.RPRTG_AREA_NM,
        f.ACT_PLNRT_USD_AMT AS MAX_DATE_AMT
    FROM latest_dates l
    JOIN my.table f 
        ON l.PRJCT_ID = f.PRJCT_ID
        AND l.RPRTG_AREA_NM = f.RPRTG_AREA_NM
        AND l.LAST_DATE = last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15'))
)
SELECT 
    f.PRJCT_ID,
    f.RPRTG_AREA_NM,
    f.ACT_PLNRT_USD_AMT,
    last_day(date(f.ACCT_YR_NUM || '-' || f.ACCT_MO_NUM  || '-15')) ACCT_DATE,
    l.LAST_DATE,
    la.MAX_DATE_AMT
FROM my.table f
JOIN latest_dates l 
    ON f.PRJCT_ID = l.PRJCT_ID
    AND f.RPRTG_AREA_NM = l.RPRTG_AREA_NM
JOIN latest_amt la
    ON f.PRJCT_ID = la.PRJCT_ID
    AND f.RPRTG_AREA_NM = la.RPRTG_AREA_NM
ORDER BY 
    f.PRJCT_ID,
    f.RPRTG_AREA_NM,
    ACCT_DATE

说明 #

• 方法1用FIRST_VALUE倒序取分组内第一行（最新日期）的金额，通过窗口范围设置让所有行都能获取该值。
• 方法2分步计算：先拿最新日期，再拿对应金额，最后关联原表输出全量数据，逻辑更直观。

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