如何用SQL计算连续行数值差值？以燃油价格为例

嘿，我来帮你搞定这个连续行差值计算的需求！针对你给出的包含日期、Speed、Petrol和Diesel的表格，这里有几种不同的SQL实现方案，你可以根据自己使用的数据库来选择：

通用方案（支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server、Oracle等） #

这是最简洁高效的方法，利用LAG()窗口函数直接获取前一行的对应数值，然后计算差值：

SELECT
    Date,
    Speed,
    Petrol,
    Diesel,
    -- 计算Speed与前一行的差值
    Speed - LAG(Speed) OVER (ORDER BY Date) AS Speed_Diff,
    -- 计算Petrol与前一行的差值
    Petrol - LAG(Petrol) OVER (ORDER BY Date) AS Petrol_Diff,
    -- 计算Diesel与前一行的差值
    Diesel - LAG(Diesel) OVER (ORDER BY Date) AS Diesel_Diff
FROM
    your_table_name; -- 记得把这里替换成你的实际表名

说明：LAG(column) OVER (ORDER BY Date)会按照日期排序，取出当前行的上一行对应列的值。因为第一行没有前一行数据，所以它的差值会显示为NULL，这是正常的结果。另外注意你的数据里2018-04-03是缺失的，这个方案会直接跳过该日期，只计算现有数据中连续行的差值。

老版本MySQL兼容方案（无窗口函数） #

如果你用的是MySQL 8.0以下的版本，不支持窗口函数，可以用自连接+子查询的方式实现：

SELECT
    t1.Date,
    t1.Speed,
    t1.Petrol,
    t1.Diesel,
    t1.Speed - t2.Speed AS Speed_Diff,
    t1.Petrol - t2.Petrol AS Petrol_Diff,
    t1.Diesel - t2.Diesel AS Diesel_Diff
FROM
    your_table_name t1
LEFT JOIN
    your_table_name t2 ON t2.Date = (SELECT MAX(Date) FROM your_table_name WHERE Date < t1.Date)
ORDER BY
    t1.Date;

说明：子查询会找到当前日期之前的最大日期（也就是前一行的日期），然后通过自连接把前后两行的数据关联起来，进而计算差值。同样第一行的差值会是NULL。

补全日历日期后的差值计算（以PostgreSQL为例） #

如果你的需求是按日历日计算差值，即使某天没有数据也要显示（比如补上2018-04-03的记录再计算），可以先生成连续的日期序列，再和原数据表关联后计算：

-- 先生成覆盖数据日期范围的连续日期序列
WITH date_series AS (
    SELECT generate_series(
        (SELECT MIN(Date) FROM your_table_name),
        (SELECT MAX(Date) FROM your_table_name),
        INTERVAL '1 day'
    ) AS date
),
-- 关联原数据，用前一天的值补全缺失日期的数值（也可以保留为NULL，根据需求调整）
filled_data AS (
    SELECT
        ds.date::DATE,
        COALESCE(t.Speed, LAG(t.Speed) OVER (ORDER BY ds.date)) AS Speed,
        COALESCE(t.Petrol, LAG(t.Petrol) OVER (ORDER BY ds.date)) AS Petrol,
        COALESCE(t.Diesel, LAG(t.Diesel) OVER (ORDER BY ds.date)) AS Diesel
    FROM date_series ds
    LEFT JOIN your_table_name t ON ds.date::DATE = t.Date
)
-- 最后计算每日与前一日的差值
SELECT
    date,
    Speed,
    Petrol,
    Diesel,
    Speed - LAG(Speed) OVER (ORDER BY date) AS Speed_Diff,
    Petrol - LAG(Petrol) OVER (ORDER BY date) AS Petrol_Diff,
    Diesel - LAG(Diesel) OVER (ORDER BY date) AS Diesel_Diff
FROM filled_data;

说明：这里用递归CTE生成了连续的日期，然后用COALESCE和LAG补全了缺失日期的数值，之后再计算差值，这样就能得到完整日历日的差值结果。

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