如何使用expand on函数从含有效期的历史表中获取每月末最新记录

问题根因 #

你遇到的遗漏问题是锚点匹配逻辑和区间边界的冲突导致的：

• 用ANCHOR MONTH_END时，最后一条end_date为null的记录，有效区间结束是当前时间（比如9月中旬），而9月月末的锚点晚于当前时间，无法落在区间内，所以被过滤
• 用ANCHOR MONTH_BEGIN时，7月的月初锚点早于108这条记录的生效时间（7月15日），无法落在区间内，所以7月的记录被过滤

修正方案 #

只需要调整有效区间的右边界，保证当前月的月末能落在最后一条记录的区间内，再过滤掉未来月份的无效数据即可，不需要修改锚点配置。以下是兼容你原有业务逻辑（取下一条记录的start_date作为当前记录区间结束）的修正SQL：

SELECT 
    a.id,
    a.name,
    a.code,
    a.start_date,
    a.end_date,
    LAST_DAY(BEGIN(bg2)) AS end_of_month
FROM (
    SELECT 
        id,
        name,
        code,
        start_date,
        end_date,
        PERIOD(
            start_date,
            COALESCE(
                MIN(start_date) OVER (PARTITION BY name ORDER BY start_date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
                -- 仅修改这里的默认值，把当前时间改成当前月月末+1天，保证当月月末锚点能落在区间内
                LAST_DAY(CURRENT_DATE) + INTERVAL '1' DAY
            )
        ) AS bg 
    FROM CHESS.HST
) AS a
EXPAND ON bg AS bg2 BY ANCHOR MONTH_END
-- 新增过滤，避免出现未来月份的月末记录
WHERE LAST_DAY(BEGIN(bg2)) <= LAST_DAY(CURRENT_DATE)

如果你的数据库时间加1天的语法不同，把LAST_DAY(CURRENT_DATE) + INTERVAL '1' DAY替换为对应语法（比如DATEADD(day, 1, LAST_DAY(CURRENT_DATE))）即可。

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