PostgreSQL使用lag函数计算相邻reported_date的小时差问题求助

代码修正说明 #

• 原窗口函数的ORDER BY逻辑存在问题：同一well_id分区下按well_id排序无法确定记录的先后顺序，会导致lag函数取到的不是时间维度上相邻的上一条记录，需改为按reported_date排序
• PostgreSQL中两个时间戳直接相减会得到interval类型，使用EXTRACT(EPOCH FROM 时间差) / 3600即可将差值转换为小时单位，支持跨天、跨月的差值计算

完整可运行代码 #

SELECT
  well_id,
  reported_date,
  -- 取同井号下上一条上报日期
  LAG(reported_date, 1) OVER (
    PARTITION BY well_id
    ORDER BY reported_date
  ) AS prev_reported_date,
  -- 计算与上一条记录的时间差，单位为小时，保留2位小数
  ROUND(
    EXTRACT(EPOCH FROM (reported_date - LAG(reported_date, 1) OVER (
      PARTITION BY well_id
      ORDER BY reported_date
    ))) / 3600,
    2
  ) AS hour_diff
FROM 
public.production;

补充说明 #

• 每个well_id的第一条记录因为没有前置上报数据，hour_diff会返回NULL，符合业务逻辑
• 不需要保留小数的话可以去掉ROUND函数，直接用EXTRACT计算结果取整即可

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