SQL - LEAD()函数

Question

我尝试使用以下代码：

 SELECT SkillTargetID, DateTime,
    lead(EventName,1) over (partition by EventName order by DateTime) as next_eventname,
    lead(ReasonCode,1) over (partition by ReasonCode order by DateTime) as next_ReasonCode
 FROM ucce1_awdb.dbo.Agent_State_Trace ast
 WHERE 
 EventName = 3 and
 ReasonCode= 0 and
 next_eventname = 0 and
 next_ReasonCode = 114

但是由于next_eventname和next_ReasonCode的原因，我无法执行该查询。有人能建议我该如何处理这件事吗？

Answer 1

您需要一个子查询：

SELECT SkillTargetID, DateTime, next_eventname, next_ReasonCode
FROM (SELECT ast.*
             lead(EventName) over (partition by EventName order by DateTime) as next_eventname,
             lead(ReasonCode) over (partition by ReasonCode order by DateTime) as next_ReasonCode
      FROM ucce1_awdb.dbo.Agent_State_Trace ast
     ) ast
WHERE EventName = 3 and
      ReasonCode= 0 and
      next_eventname = 0 and
      next_ReasonCode = 114

Answer 2

您不能在where子句中使用在select子句中定义的别名...并且不能在where子句中使用窗口函数。您需要将筛选移动到外部查询：

SELECT *
FROM (
    SELECT 
        SkillTargetID, 
        DateTime,
        lead(EventName,1) 
            over(partition by EventName order by DateTime) as next_eventname,
        lead(ReasonCode,1) 
            over(partition by ReasonCode order by DateTime) as next_ReasonCode
    FROM ucce1_awdb.dbo.Agent_State_Trace ast
) t
WHERE 
    EventName = 3 
    AND ReasonCode= 0
    AND next_eventname = 0 
    AND next_ReasonCode = 114

请注意，我将所有过滤器移到了外部查询(不仅仅是那些应用于窗口计算的过滤器)：这是因为您(可能！)在应用过滤器之前，需要窗口函数来查看整个数据集。