遇到这样一个需求，有一张表，要给这张表新增一个字段delta，delta的值等于每行的c1列的值减去上一行c1列的值。
我的解决方案，可以通过python的pandas的diff来实现，也可以通过sql来实现，如下
|
1
2
3
4
5
6
7
8
9
10
|
import pandas as pdsrcTable = 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) |
出来的效果如下：
|
1
2
3
4
5
6
7
8
9
10
|
tid ts1 ts20 1 1500443161000 15004431612401 1 1500443162000 15004431629942 1 1500443163000 15004431630673 1 1500443164000 1500443164993 deltaTs1 deltaTs2 delay0 0.0 0.0 0.01 1000.0 1754.0 754.02 1000.0 73.0 -927.03 1000.0 1926.0 926.0 |
若是用sql语句，我用的是mysql，自己构造行号rn
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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) mainleft 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
2
3
4
|
1,1500443161000,15004431612401,1500443162000,15004431629941,1500443163000,15004431630671,1500443164000,1500443164993 |
|
1
2
3
4
5
6
7
8
9
|
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好像有。
原文链接：https://blog.csdn.net/tulinying/article/details/75381886
