如何使用LAG函数忽略空值获取用户上一个访问网页

需求：从web表中提取用户访问的上一个有效网页，仅当当前记录为有效访问页面（webpage_visited非空）时，返回对应的上一个有效页面；空页面记录无需返回上一页信息。原SQL使用LAG函数未忽略空值，无法得到正确结果。

原表数据 #

原SQL语句 #

select id, 
visit_time, 
webpage_visited, 
coalesce(lag(webpage_visited, 1) over (partition by id order by visit_time  asc), 'none') as previous_webpage_visited
from web

期望输出 #

解决方案 #

方法1：使用LAST_VALUE + IGNORE NULLS（支持的数据库：PostgreSQL、Oracle、MySQL 8.0+等）

利用LAST_VALUE函数并指定IGNORE NULLS，直接在窗口范围内跳过空值，获取上一个非空的有效页面。再通过CASE语句控制仅在当前页面有效时返回结果：

SELECT 
    id,
    visit_time,
    webpage_visited,
    CASE 
        WHEN webpage_visited IS NOT NULL THEN 
            COALESCE(
                LAST_VALUE(webpage_visited) IGNORE NULLS OVER (
                    PARTITION BY id 
                    ORDER BY visit_time ASC 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ), 'None'
            )
        ELSE NULL
    END AS previous_webpage_visited
FROM web
ORDER BY visit_time ASC;

方法2：CTE筛选有效页面后关联（兼容性更强）

如果数据库不支持IGNORE NULLS，先筛选出所有有效访问记录，用LAG获取每个有效页面的上一个有效页面，再关联回原表：

WITH valid_pages AS (
    SELECT 
        id,
        visit_time,
        webpage_visited,
        LAG(webpage_visited) OVER (PARTITION BY id ORDER BY visit_time ASC) AS prev_valid_page
    FROM web
    WHERE webpage_visited IS NOT NULL
)
SELECT 
    w.id,
    w.visit_time,
    w.webpage_visited,
    CASE 
        WHEN w.webpage_visited IS NOT NULL THEN COALESCE(vp.prev_valid_page, 'None')
        ELSE NULL
    END AS previous_webpage_visited
FROM web w
LEFT JOIN valid_pages vp 
    ON w.id = vp.id 
    AND w.visit_time = vp.visit_time
ORDER BY w.visit_time ASC;

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