SQL Server中基于梯形法计算时间序列曲线下方面积的技术求助

核心原理 #

梯形法计算曲线下面积的逻辑是：对每一对相邻的数据点，计算两点构成的梯形面积，再将所有梯形面积求和。单个梯形的面积公式为：

面积 = (当前值 + 下一个值) / 2 * 时间间隔（小时为单位）

SQL实现方案 #

假设你的表名为TimeSeries，包含两列：

• dt：datetime类型的时间戳
• value：对应数值

使用LEAD()窗口函数获取每个数据点的下一个相邻点（自动跳过缺失的数据），然后计算每个梯形的面积并求和：

SELECT SUM(
    (value + next_value) / 2.0 
    * DATEDIFF(second, dt, next_dt) / 3600.0
) AS total_area
FROM (
    SELECT 
        dt,
        value,
        LEAD(dt) OVER (ORDER BY dt) AS next_dt,
        LEAD(value) OVER (ORDER BY dt) AS next_value
    FROM TimeSeries
) t
WHERE next_dt IS NOT NULL; -- 过滤最后一行（无后续数据点）

代码解释 #

1. LEAD()窗口函数：按时间顺序ORDER BY dt，为每一行获取下一行的时间戳next_dt和数值next_value，自动处理数据缺失的情况——直接取实际存在的下一个数据点，而非假设每分钟都有数据。
2. 时间间隔计算：用DATEDIFF(second, dt, next_dt)获取两点的时间差（秒），除以3600转换为小时单位，保证面积基准为小时。
3. 梯形面积求和：对每个有效相邻点对计算梯形面积，最后用SUM()得到总面积。

示例数据验证 #

用你提供的18条示例数据执行上述查询，计算结果约为2.26，与预期结果一致。

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