CnosDB技术问询：如何替代EXTRACT实现时间窗口导数计算

你的核心逻辑是通过**(当前值与前一值的差)除以(时间差的秒数)**计算导数，EXTRACT(EPOCH FROM ...)是PostgreSQL专属写法，若在其他数据库不生效，可根据你使用的数据库选择以下替代方案：

1. MySQL 适配写法 #

使用TIMESTAMPDIFF(SECOND, 前一时间, 当前时间)直接获取秒级时间差：

SELECT
    time,
    value,
    (value - LAG(value) OVER (ORDER BY time)) / TIMESTAMPDIFF(SECOND, LAG(time) OVER (ORDER BY time), time) AS derivative
FROM
  (VALUES 
    ('2024-01-01 00:00:00', 10.0),
    ('2024-01-01 00:01:00', 12.0),
    ('2024-01-01 00:02:00', 15.0),
    ('2024-01-01 00:03:00', 18.0),
    ('2024-01-01 00:04:00', 20.0)
  ) AS data(time, value);

2. SQL Server 适配写法 #

用DATEDIFF(SECOND, ...)获取秒差，同时需将时间差转为浮点型避免整数截断：

SELECT
    time,
    value,
    (value - LAG(value) OVER (ORDER BY time)) / CAST(DATEDIFF(SECOND, LAG(time) OVER (ORDER BY time), time) AS FLOAT) AS derivative
FROM
  (VALUES 
    ('2024-01-01 00:00:00', 10.0),
    ('2024-01-01 00:01:00', 12.0),
    ('2024-01-01 00:02:00', 15.0),
    ('2024-01-01 00:03:00', 18.0),
    ('2024-01-01 00:04:00', 20.0)
  ) AS data(time DATETIME, value FLOAT);

3. Oracle 适配写法 #

Oracle中时间差默认以天为单位，乘以86400（一天的秒数）转为秒级：

SELECT
    time,
    value,
    (value - LAG(value) OVER (ORDER BY time)) / ((time - LAG(time) OVER (ORDER BY time)) * 86400) AS derivative
FROM
  (SELECT TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AS time, 10.0 AS value FROM DUAL
   UNION ALL
   SELECT TO_TIMESTAMP('2024-01-01 00:01:00', 'YYYY-MM-DD HH24:MI:SS') AS time, 12.0 AS value FROM DUAL
   UNION ALL
   SELECT TO_TIMESTAMP('2024-01-01 00:02:00', 'YYYY-MM-DD HH24:MI:SS') AS time, 15.0 AS value FROM DUAL
   UNION ALL
   SELECT TO_TIMESTAMP('2024-01-01 00:03:00', 'YYYY-MM-DD HH24:MI:SS') AS time, 18.0 AS value FROM DUAL
   UNION ALL
   SELECT TO_TIMESTAMP('2024-01-01 00:04:00', 'YYYY-MM-DD HH24:MI:SS') AS time, 20.0 AS value FROM DUAL);

通用优化：减少重复计算 #

不管用哪种数据库，都可以通过CTE先计算出前一时间和前一值，避免重复调用LAG函数，提升代码可读性：

-- 示例基于PostgreSQL，替换时间差计算部分即可适配其他数据库
WITH lagged_data AS (
    SELECT
        time,
        value,
        LAG(value) OVER (ORDER BY time) AS prev_value,
        LAG(time) OVER (ORDER BY time) AS prev_time
    FROM
      (VALUES 
        ('2024-01-01 00:00:00'::TIMESTAMP, 10.0),
        ('2024-01-01 00:01:00'::TIMESTAMP, 12.0),
        ('2024-01-01 00:02:00'::TIMESTAMP, 15.0),
        ('2024-01-01 00:03:00'::TIMESTAMP, 18.0),
        ('2024-01-01 00:04:00'::TIMESTAMP, 20.0)
      ) AS data(time, value)
)
SELECT
    time,
    value,
    (value - prev_value) / EXTRACT(EPOCH FROM (time - prev_time)) AS derivative
FROM lagged_data;

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