Sql LAG函数通过丢弃空值返回前一行

诀窍由两部分组成--使用IGNORE NULLS for your window function并仅在当前值不为空时应用窗口函数:

sample data
WITH dataset(id, visit_time, webpage_visited) AS (
   values (1,'2024-03-14 10:00:01','google.com'),
        (1,'2024-03-14 10:00:07',NULL),
        (1,'2024-03-14 10:01:15',NULL),
        (1,'2024-03-14 10:01:10','espn.com'),
        (1,'2024-03-14 10:02:01',NULL)
)

-- query
select id,
       visit_time,
       webpage_visited,
       if(webpage_visited is not null,
           coalesce(lag(webpage_visited, 1) IGNORE NULLS over(partition by id order by visit_time), 'none'))
           as previous_webpage_visited
from dataset
order by visit_time;

输出:

UPD个

要处理空/空白条目，您可以对其进行预处理，例如使用子查询:

select id,
       visit_time,
       webpage_visited,
       if(webpage_visited_sanitized is not null,
           coalesce(lag(webpage_visited_sanitized, 1) IGNORE NULLS over(partition by id order by visit_time), 'none'))
           as previous_webpage_visited
from (select *,
             if(trim(webpage_visited) = '', null, webpage_visited) webpage_visited_sanitized
      from dataset
     );