PostgreSQL中时间序列LAG函数计算返回NULL的问题排查

嘿，你的问题核心在于SQL的执行顺序，这也是很多人用窗口函数时容易踩的坑！

你当前的查询里，WHERE warm_transfer_status_id <> 'LOG_OUT' AND time_in_status_seconds IS NULL这个过滤条件是在窗口函数LAG(...) OVER (...)之前执行的。也就是说，数据库先把所有time_in_status_seconds不为NULL的记录都筛掉了，剩下的只有NULL值的行。这时候再对这些剩下的行做LAG操作，根本找不到同一用户的前一条有效记录（因为那些记录已经被WHERE过滤掉了），所以time_in_status自然返回NULL。

正确的做法：先计算窗口函数，再过滤 #

你需要把窗口函数的计算和最终的过滤分开，用子查询或者CTE（公共表表达式）先处理所有符合warm_transfer_status_id <> 'LOG_OUT'的记录，计算出LAG值，然后在外层查询再筛选time_in_status_seconds IS NULL的行。

举个例子，用CTE的写法：

WITH status_log_with_lag AS (
  SELECT 
    *,
    -- 先对所有符合条件的行计算LAG，不管time_in_status_seconds是否为NULL
    LAG(time_in_status_seconds, 1) OVER (PARTITION BY user_email ORDER BY updated_at) AS time_in_status
  FROM "public"."Warm_Transfer_Status_Log"
  WHERE warm_transfer_status_id <> 'LOG_OUT'
)
-- 在外层筛选出time_in_status_seconds为NULL的记录
SELECT *
FROM status_log_with_lag
WHERE time_in_status_seconds IS NULL;

这样一来，窗口函数会基于所有排除了LOG_OUT状态的记录来计算，就能正确拿到同一用户上一条记录的time_in_status_seconds值，最后再筛选出你需要的NULL行，结果里的time_in_status就会是你预期的LAG值啦。

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