雪花 SQL 错误 - 函数 '-' 的参数类型无效：(TIMESTAMP_NTZ(9)、TIMESTAMP_NTZ(9))

Question

当我尝试减去时间戳并执行窗口函数(lead、lag和partition by时，我收到此错误：

函数 '-' 的参数类型无效：(TIMESTAMP_NTZ(9(， TIMESTAMP_NTZ(9((

尝试date_diff，但这不适用于窗口功能

SELECT 
user_id,
event,
received_at,
received_at - LAG( received_at,1) OVER (PARTITION BY user_id ORDER BY received_at) AS last_event
FROM 
segment_javascript.help_center_opened

Answer 1

你不能通过只减去两个日期来得到一个数字来做"甲骨文方式"，你必须使用带有度量单位/比例的 diff 函数，例如：

SELECT 
ts, 
TIMESTAMPDIFF(MILLISECONDS, LAG(ts, 1) OVER (ORDER BY ts), ts) tsd
FROM 
(VALUES (CURRENT_TIMESTAMP), (DATEADD(DAY, 1, CURRENT_TIMESTAMP))) v(ts);

Answer 2

我想您需要计算与上次事件接收时间相同user_id的时间差。

如果是这样，我认为这将起作用：

SELECT 
user_id,
event,
received_at,
DATEDIFF(
MINUTE, -- or any other supported date/time part
received_at, -- start time 
LAG( received_at,1) OVER (PARTITION BY user_id ORDER BY received_at) -- end time
) AS last_event
FROM 
segment_javascript.help_center_opened