如何在SQL中结合窗口函数使用DATEDIFF？

原查询与报错 #

你最初尝试在窗口函数中直接嵌套DATEDIFF，SQL语句如下：

select 
  post_visid_high || ':' || post_visid_low as visitor_id
 , datediff(minute, lag(date_time), date_time) over (partition by visitor_id order by date_time asc)
from adobe_data

执行后触发报错：

Invalid function type [DATEDIFF] for window function.
Invalid function type [TIMEDIFF] for window function.

原因是多数SQL引擎不允许将DATEDIFF这类非窗口函数直接作为窗口函数的参数使用——窗口函数（如LAG）的计算结果必须先单独输出，才能作为其他函数的输入。

你已实现的可行方案 #

你通过拆分窗口函数和日期计算的方式解决了问题，改写后的SQL逻辑清晰、符合规范：

select 
  post_visid_high || ':' || post_visid_low as visitor_id
  , lag(date_time) over (partition by visitor_id order by date_time asc) as previous_date
  , datediff(minute, previous_date, date_time) as difference_in_minutes
from adobe_data 

是否存在更优实现？ #

这种拆分写法已经是最优方案之一，无论是性能还是可读性都表现出色。

部分支持LATERAL JOIN（横向连接）的SQL引擎，也可以用以下写法实现相同逻辑，但本质和你当前的写法没有性能差异，反而增加了语法复杂度：

select 
  post_visid_high || ':' || post_visid_low as visitor_id
  , prev.previous_date
  , datediff(minute, prev.previous_date, ad.date_time) as difference_in_minutes
from adobe_data ad
left join lateral (
  select lag(date_time) over (partition by post_visid_high || ':' || post_visid_low order by date_time asc) as previous_date
  from adobe_data ad2
  where ad2.post_visid_high = ad.post_visid_high 
    and ad2.post_visid_low = ad.post_visid_low
) prev on true
order by visitor_id, date_time

综上，你当前使用的拆分LAG和DATEDIFF的写法，是最简洁高效的选择，无需进一步调整。

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