Snowflake中如何获取字段值的有效变更区间（含当前日期作为最终截止）

嘿，我来帮你搞定这个需求！你想要的是把同一个ID下连续相同的NAME合并成一条记录，同时显示它的有效起止日期，最后一条记录的截止日期用当前日期对吧？

你之前用LAG的思路没问题，但没处理「连续相同NAME」的情况，所以会把同一个NAME的多条记录拆成不同行。咱们可以用「标记连续分组」的技巧来解决这个问题，也就是常说的「孤岛与间隙」问题处理方法，具体步骤如下：

完整SQL解决方案 #

WITH grouped_data AS (
    SELECT 
        ID,
        NAME,
        DATE,
        -- 核心：给连续相同NAME的记录分配同一个组ID
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE) 
        - ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY DATE) AS group_id
    FROM MY_TABLE
),
interval_groups AS (
    SELECT 
        ID,
        NAME,
        MIN(DATE) AS FROM_DATE
    FROM grouped_data
    GROUP BY ID, NAME, group_id
)
SELECT 
    ID,
    NAME,
    FROM_DATE,
    -- 用下一个组的起始日期作为当前组的截止，没有下一组就用当前日期
    COALESCE(LEAD(FROM_DATE) OVER (PARTITION BY ID ORDER BY FROM_DATE), CURRENT_DATE()) AS TO_DATE
FROM interval_groups
ORDER BY ID, FROM_DATE;

分步解释 #

1. 标记连续分组（grouped_data）
   这里用了两个ROW_NUMBER()的差值来给连续相同的NAME分组：

   • 第一个ROW_NUMBER()是按ID分组、按DATE排序的全局序号
   • 第二个ROW_NUMBER()是按ID+NAME分组、按DATE排序的局部序号
     两者的差值会让连续相同NAME的记录得到同一个group_id，比如你样本里的两条MICHELLE记录，差值都是0，会被分到同一组；MITCH的记录差值是1，单独成组。

2. 提取每组的起始日期（interval_groups）
   按ID、NAME、group_id分组，取每组的最小DATE作为这个NAME开始生效的日期（FROM_DATE）。

3. 生成截止日期
   用LEAD()窗口函数获取下一个分组的起始日期，作为当前组的截止日期（TO_DATE）；如果是最后一个分组（没有下一个组），就用CURRENT_DATE()填充，完美符合你要的「最后一条记录截止到当前日期」的需求。

对你样本数据的输出结果 #

用你的测试数据跑这个SQL，会得到 exactly 你想要的结果：

如果你的排序依据是ORDER字段而不是DATE，只需要把所有ORDER BY DATE改成ORDER BY ORDER就行，灵活调整~

备注：内容来源于stack exchange，提问作者Angie