pandas or sql计算前后两行数据间的增值方法


            遇到这样一个需求，有一张表，要给这张表新增一个字段delta，delta的值等于每行的c1列的值减去上一行c1列的值。

我的解决方案，可以通过python的pandas的diff来实现，也可以通过sql来实现，如下





import pandas as pd



srcTable = pd.read_csv('pos1.csv')

print(srcTable)

destTable = srcTable.loc[srcTable.tid == 1, ['ts1', 'ts2']].sort_values(by='ts1')

destTable.columns = ['deltaTs1', 'deltaTs2']

destTable = destTable.diff()

destTable = destTable.fillna(0)

destTable['delay'] = destTable['deltaTs2'] - destTable['deltaTs1']

print(destTable)





出来的效果如下：





 tid   ts1   ts2

0 1 1500443161000 1500443161240

1 1 1500443162000 1500443162994

2 1 1500443163000 1500443163067

3 1 1500443164000 1500443164993

 deltaTs1 deltaTs2 delay

0  0.0  0.0 0.0

1 1000.0 1754.0 754.0

2 1000.0  73.0 -927.0

3 1000.0 1926.0 926.0



若是用sql语句，我用的是mysql，自己构造行号rn





mysql> select main.t_id,

main.ts1,

ifnull(main.ts1-sub.ts1,0) deltaTs1,

main.ts2,

ifnull(main.ts2-sub.ts2,0) deltaTs2 from

(SELECT t_id,ts1,ts2,(@r1 :=@r1 + 1) rn FROM pos1,(SELECT @r1 := 0) r where t_id=1 ORDER BY ts1) main

left join 

(SELECT t_id,ts1,ts2,(@r2 :=@r2 + 1) rn FROM pos1,(SELECT @r2 := 0) r where t_id=1 ORDER BY ts1) sub 

on main.rn-1=sub.rn;

+------+---------------+----------+---------------+----------+

| t_id | ts1   | deltaTs1 | ts2   | deltaTs2 |

+------+---------------+----------+---------------+----------+

| 1 | 1500443161000 |  0 | 1500443161240 |  0 |

| 1 | 1500443162000 |  1000 | 1500443162994 |  1754 |

| 1 | 1500443163000 |  1000 | 1500443163067 |  73 |

| 1 | 1500443164000 |  1000 | 1500443164993 |  1926 |

+------+---------------+----------+---------------+----------+







测试数据如下

pos1.csv





1,1500443161000,1500443161240

1,1500443162000,1500443162994

1,1500443163000,1500443163067

1,1500443164000,1500443164993







CREATE TABLE `pos1` (

 `t_id` int(11) DEFAULT NULL,

 `ts1` bigint(22) DEFAULT NULL,

 `ts2` bigint(22) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO pos1 VALUES (1, 1500443161000, 1500443161240);

INSERT INTO pos1 VALUES (1, 1500443162000, 1500443162994);

INSERT INTO pos1 VALUES (1, 1500443163000, 1500443163067);

INSERT INTO pos1 VALUES (1, 1500443164000, 1500443164993);



貌似有些数据库有这种当前行减去上一行数据的函数，具体我没有研究过。有知道的朋友可以告诉我一下，我印象中像Sqlserver好像有。