Redshift中如何使用LAG函数回溯获取最近一次clic事件的url

固定偏移的LAG()函数只能处理相邻行的取值场景，无法适配两次clic事件中间间隔任意数量view行的需求，这类「回溯取最近一次满足条件的历史值」的场景，可以通过窗口函数LAST_VALUE()搭配IGNORE NULLS参数实现，代码逻辑简单且执行效率高。

核心实现思路 #

• 先构造临时计算字段：仅当行事件类型为clic时存入对应url，其余行该字段置为NULL
• 通过窗口范围限定，仅统计当前行之前的所有历史行，取上述临时字段的最后一个非空值，即为距离当前行最近的一次clic事件对应的url
• 最后加判断逻辑：仅当当前行满足event='view'且flag='✓'时返回取到的clic url，其余行统一返回空值

可直接运行的SQL代码 #

SELECT
  event,
  url,
  rank,
  flag,
  CASE
    WHEN event = 'view' AND flag = '✓'
    THEN LAST_VALUE(CASE WHEN event = 'clic' THEN url END) IGNORE NULLS OVER (
      -- 如果业务需要按用户、会话等维度分组统计，在这里加 PARTITION BY 对应分组字段
      ORDER BY rank
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )
    ELSE NULL
  END AS "CASE" -- 注：CASE是SQL保留关键字，必须加双引号引用才能作为列名，日常开发建议换用非保留字别名避免语法错误
FROM myTable;

关键参数说明 #

• IGNORE NULLS：告诉窗口函数计算时跳过空值，只取非空的clic事件url
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：强制限定窗口统计范围为「当前行之前的所有行」，不包含当前行本身，避免当前行是clic事件时错误取到自身的url值
• 如果你的数据存在分组维度（比如用户ID、访问会话ID），一定要在OVER()子句里加上PARTITION BY 分组字段，否则会把全量数据混排，取到不属于当前分组的clic事件值

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