如何使用BigQuery窗口函数获取最近匹配条件的历史记录convPageName值

你需要为每条记录找到最近的历史记录，满足两个条件：

• 历史记录的convPageName不为空
• 当前记录的convPageName等于历史记录的pageName

以下是基于窗口函数的解决方案，利用last_value结合条件过滤和ignore nulls实现需求：

with a as (
  select 1 as hitNumber, 'xx' as sessionID, 'aaa' as pageName, null as convPageName
  union all
  select 2 as hitNumber, 'xx' as sessionID, 'ccc' as pageName, 'bbb' as convPageName
  union all
  select 3 as hitNumber, 'xx' as sessionID, 'ccc' as pageName, null as convPageName
  union all
  select 4 as hitNumber, 'xx' as sessionID, 'ddd' as pageName, 'qqq' as convPageName
  union all
  select 5 as hitNumber, 'xx' as sessionID, 'eee' as pageName, 'ccc' as convPageName
)
select 
  *,
  last_value(
    -- 仅保留符合条件的历史记录的convPageName，不符合的转为null
    case when pageName = current_conv and convPageName is not null then convPageName end ignore nulls
  ) over (
    -- 按会话分组，确保只在同一会话内查找
    partition by sessionID
    -- 按hitNumber排序，保证历史记录在当前记录之前
    order by hitNumber
    -- 窗口范围限定为当前记录之前的所有行
    rows between unbounded preceding and 1 preceding
  ) as prevConvPageName
from a
-- 把当前行的convPageName拆分为独立字段，方便窗口内引用
cross join unnest([convPageName]) as current_conv

逻辑说明 #

1. 通过cross join unnest([convPageName])将当前行的convPageName转为独立字段current_conv，便于在窗口函数中引用匹配条件。
2. case语句筛选出历史记录中满足pageName = current_conv且convPageName不为空的记录，不符合条件的结果转为null。
3. last_value(...) ignore nulls会忽略窗口内的null值，取最后一个（即最近的）符合条件的convPageName。
4. 窗口范围rows between unbounded preceding and 1 preceding确保只处理当前记录之前的历史数据。

执行后，hitNumber=5的记录会得到prevConvPageName = 'bbb'，完全符合预期。

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