如何用SQL检索URL发生变更的最早有效记录

我有一张存储变更日志数据的表，需要检索出用户对URL进行变更的最早条目，URL未变更或为NULL的行不应出现在结果中。

表示例 #

期望结果 #

尝试过的SQL及问题 #

我尝试了以下SQL语句，但无法返回正确的valid_from值：

SELECT DISTINCT
        id,
        url,
        first_value(valid_from) OVER (PARTITION BY id ORDER BY valid_from) as valid_from,
        first_value([User_id]) OVER (PARTITION BY id ORDER BY valid_from DESC) as [User_id],
    FROM 
        changelog
    WHERE 
        url IS NOT NULL
    ORDER BY id, valid_from ASC

解决方案 #

你需要识别每个ID下首次出现的新URL（包括从NULL切换到非NULL的情况），可以通过LAG()窗口函数对比当前行与上一行的URL来实现：

WITH filtered_changelog AS (
    SELECT
        id,
        url,
        valid_from,
        User_Id,
        -- 获取同一ID下上一行的URL
        LAG(url) OVER (PARTITION BY id ORDER BY valid_from) AS previous_url
    FROM changelog
    -- 先过滤掉URL为NULL的行
    WHERE url IS NOT NULL
)
SELECT
    id,
    url,
    valid_from,
    User_Id
FROM filtered_changelog
-- 保留两种情况：1. 是该ID下第一个非NULL URL；2. 当前URL与上一行不同（即发生了变更）
WHERE previous_url IS NULL OR url != previous_url
ORDER BY id, valid_from;

逻辑说明 #

1. 首先用CTE过滤掉所有URL为NULL的行，同时通过LAG()获取同一ID中前一行的URL值。
2. 然后筛选出两种行：
   • 同一ID下的第一个非NULL URL（此时previous_url为NULL）
   • 当前URL与上一行URL不同的行（即发生了URL变更的首次条目）
3. 最终结果按ID和valid_from升序排列，与期望输出完全匹配。

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