寻求SQL实现Pandas diff()一阶离散差分的ANSI标准方案

嘿，我懂你想要用SQL复刻Pandas里.diff()的一阶差分效果对吧？完全没问题，用ANSI标准的窗口函数就能轻松实现，Oracle也完美支持这个写法～

核心思路 #

Pandas的.diff()默认逻辑是当前行值减去前一行值，第一行因为没有前置行所以返回NaN。在SQL里，我们可以用LAG()窗口函数来获取前一行的对应列值，再做减法运算就能得到相同结果。

ANSI标准SQL实现 #

假设你的数据表名为your_table，并且有一个用来确定行顺序的列（比如id，SQL表本身是无序的，必须指定排序依据才能保证结果和Pandas一致），查询语句如下：

SELECT 
    A,
    B,
    A - LAG(A) OVER (ORDER BY id) AS diff_A,
    B - LAG(B) OVER (ORDER BY id) AS diff_B
FROM your_table;

代码解释

• LAG(A) OVER (ORDER BY id)：这个窗口函数会获取当前行的前一行的A列值，ORDER BY id用来定义行的顺序，和你Pandas里按索引排序的逻辑对应。
• 第一行没有前一行数据，所以LAG()会返回NULL，对应Pandas里的NaN，后续行则自动计算当前值与前一行的差值。

模拟你的测试数据验证 #

我们可以创建一个和你Pandas示例一致的测试表来验证效果：

-- 创建测试表
CREATE TABLE test_data (
    id NUMBER,
    A NUMBER,
    B NUMBER
);

-- 插入示例数据
INSERT INTO test_data VALUES (0, 36, 14);
INSERT INTO test_data VALUES (1, 32, 13);
INSERT INTO test_data VALUES (2, 31, 87);
INSERT INTO test_data VALUES (3, 58, 88);
INSERT INTO test_data VALUES (4, 44, 34);
INSERT INTO test_data VALUES (5, 2, 43);
INSERT INTO test_data VALUES (6, 15, 94);
INSERT INTO test_data VALUES (7, 46, 74);
INSERT INTO test_data VALUES (8, 60, 9);
INSERT INTO test_data VALUES (9, 43, 57);

运行前面的查询语句，得到的结果和你Pandas输出的完全匹配（仅NULL代替NaN，逻辑完全一致）：

注意事项 #

• 一定要指定ORDER BY子句：SQL表本身不保证行的顺序，如果没有明确的排序依据，LAG()的结果会不可预测，必须和你Pandas里的排序逻辑保持一致。
• 兼容性：这个写法是纯ANSI标准，除了Oracle，在PostgreSQL、SQL Server、MySQL 8.0+等支持窗口函数的数据库中都能直接使用，非常通用。

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