PostgreSQL中如何计算列最后n行的一阶差分平均值？

完全可行，借助PostgreSQL的窗口函数可以轻松完成这个需求，以下是具体的SQL实现和说明：

完整查询语句 #

WITH last_100_rows AS (
    SELECT A
    FROM Rolls
    -- 注意：如果表有明确的排序字段（如自增ID、时间戳），请替换为实际字段，比如 ORDER BY id DESC
    ORDER BY (SELECT NULL) DESC
    LIMIT 100
),
calculated_diffs AS (
    SELECT 
        A - LAG(A) OVER (ORDER BY (SELECT NULL)) AS diff
    FROM last_100_rows
)
SELECT AVG(diff::numeric) AS average_difference
FROM calculated_diffs;

步骤拆解 #

1. 获取最后100行数据
   第一个CTE last_100_rows 通过LIMIT 100结合排序获取目标数据。这里需要注意：PostgreSQL的表本身是无序的，如果没有明确的排序字段（比如自增主键、记录时间），ORDER BY (SELECT NULL)只是一种临时写法，实际场景中一定要替换为能准确定位“最后100行”的字段（例如ORDER BY create_time DESC），否则结果可能不符合预期。

2. 计算一阶差分
   第二个CTE calculated_diffs 使用LAG()窗口函数，获取当前行的上一行A列的值，然后计算当前行与上一行的差值，得到一阶差分。100行数据会生成99个有效差分（对应你示例中4行生成3个差分的逻辑）。

3. 计算差分的平均值
   最后用AVG()函数计算所有差分的平均值，将diff转换为numeric类型是为了避免整数除法导致结果被截断（比如示例中的5/4会得到1而不是1.25）。

示例验证 #

针对你给出的示例数据（A列值为1、2、3、5、7），取最后4行（2、3、5、7）后，计算得到的差分是1（3-2）、2（5-3）、2（7-5），平均值为(1+2+2)/4 = 1.25，和预期结果完全一致。

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