LEAD/OVER窗口函数过滤数据触发SQL语法错误咨询

• SQL执行逻辑中，WHERE子句的执行顺序早于SELECT子句的字段计算、别名生成，也早于窗口函数的计算环节，你直接在WHERE中引用SELECT层定义的title_change_flag别名，本质是在窗口函数还没完成计算的阶段就调用其结果，违反了窗口函数仅允许出现在SELECT/QUALIFY/ORDER BY子句的语法约束，直接触发报错。
• 原语句存在两处逻辑错误：
  • 取相邻记录的函数用错：要获取排序后当前行的上一条记录值，应使用LAG()函数，LEAD()是取当前行之后的N行值，和需求逻辑相反。
  • 窗口缺少分区规则：原窗口仅做了全局排序，没有按员工IDASSOCIATE_ID分区，会出现不同员工的任职记录跨人比对的问题，结果完全错误。

根据报错格式判断你使用的是Snowflake引擎（支持QUALIFY语法），有两种可落地的写法：

写法1：用QUALIFY子句（最简洁，适配支持QUALIFY的数仓引擎） #

不需要嵌套子查询，直接在窗口计算层完成过滤：

SELECT
  ASSOCIATE_ID,
  JOB_TITLE_DESCRIPTION,
  POSITION_EFFECTIVE_DATE
FROM SENSITIVE_DATA.ADP.EMP_JOB_HISTORY
QUALIFY LAG(JOB_TITLE_DESCRIPTION, 1, '0') OVER (
  PARTITION BY ASSOCIATE_ID
  ORDER BY POSITION_EFFECTIVE_DATE ASC
) <> JOB_TITLE_DESCRIPTION

语句逻辑说明：

• 加PARTITION BY ASSOCIATE_ID保证窗口计算仅在单个员工的任职记录范围内执行，不会跨员工串数据
• 按职位生效日期升序排列，LAG()取当前记录之前的上一条任职记录的职位名称
• 默认值设为'0'时，员工的第一条任职记录（没有上一条历史）会因为比对不等被保留，符合职位变更链路的留存要求；如果不需要保留初始入职记录，去掉LAG的第三个默认值参数，在QUALIFY条件中加AND LAG(JOB_TITLE_DESCRIPTION) OVER (...) IS NOT NULL即可。

写法2：通用子查询写法（适配所有支持窗口函数的SQL引擎） #

如果你的引擎不支持QUALIFY语法，用子查询先完成窗口计算，再外层过滤即可：

SELECT
  ASSOCIATE_ID,
  JOB_TITLE_DESCRIPTION,
  POSITION_EFFECTIVE_DATE
FROM (
  SELECT
    ASSOCIATE_ID,
    JOB_TITLE_DESCRIPTION,
    POSITION_EFFECTIVE_DATE,
    LAG(JOB_TITLE_DESCRIPTION, 1, '0') OVER (
      PARTITION BY ASSOCIATE_ID
      ORDER BY POSITION_EFFECTIVE_DATE ASC
    ) AS PrevTitle
  FROM SENSITIVE_DATA.ADP.EMP_JOB_HISTORY
) t
WHERE t.PrevTitle <> t.JOB_TITLE_DESCRIPTION

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