PostgreSQL与Grafana：时序数据A的时间差计算及可视化问题

1. 替换interval的内容 #

不同数据库的时间间隔语法存在差异，以下是Grafana常用数据库的写法示例：

• PostgreSQL：直接写interval '1 hour'，可替换时间单位，比如interval '30 minutes'代表30分钟
• MySQL/MariaDB：使用INTERVAL 1 HOUR格式，数字+时间单位，例如INTERVAL 15 MINUTE
• InfluxDB：采用简化写法，比如1h代表1小时，30m代表30分钟
• SQL Server：不支持直接减interval的语法，需用DATEADD(hour, -1, DateTimeUTC)替代DateTimeUTC - interval

2. 解决time未定义的问题 #

你将DateTimeUTC别名设为time后，子查询无法直接引用这个别名——SQL子查询的作用域不包含外层的列别名。正确做法是给外层表起别名，直接引用原列名：

SELECT
  d."DateTimeUTC" as "time",
  d."A" - (
    SELECT "A" 
    FROM "Data" 
    WHERE "DateTimeUTC" <= d."DateTimeUTC" - interval '1 hour' -- 引用外层表的d."DateTimeUTC"
    ORDER BY "DateTimeUTC" DESC 
    LIMIT 1
  ) as "A_diff"
FROM
  "Data" d -- 给外层表起别名d

3. 更优的实现方式 #

子查询的执行效率较低，尤其数据量大时，推荐以下两种更高效的方案：

场景1：计算当前行与上一行（按时间排序）的A差值 #

如果只需按时间顺序，计算当前A与前一条记录A的差值，直接用LAG()窗口函数：

SELECT
  "DateTimeUTC" as "time",
  "A" - LAG("A") OVER (ORDER BY "DateTimeUTC") as "A_diff"
FROM
  "Data"

LAG("A")默认取上一行的A值，也可指定偏移行数，比如LAG("A", 2)取前两行的A值。

场景2：计算当前时间与固定时间间隔前的A差值（如1小时前） #

如果要精准匹配N个时间单位前的A值（不管中间是否有数据），推荐用自连接结合时间条件：

SELECT
  d1."DateTimeUTC" as "time",
  d1."A" - d2."A" as "A_diff"
FROM
  "Data" d1
LEFT JOIN
  "Data" d2 ON d2."DateTimeUTC" = (
    SELECT MAX("DateTimeUTC") 
    FROM "Data" 
    WHERE "DateTimeUTC" <= d1."DateTimeUTC" - interval '1 hour'
  )
ORDER BY
  d1."DateTimeUTC"

若使用PostgreSQL 11+，还可以用更简洁的时间范围窗口写法：

SELECT
  "DateTimeUTC" as "time",
  "A" - LAG("A") OVER (
    ORDER BY "DateTimeUTC" 
    RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND INTERVAL '1 hour' PRECEDING
  ) as "A_diff"
FROM
  "Data"

这种写法会匹配1小时前的A值，若无对应时间点数据，结果为NULL。

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