# RUNNING_DIFFERENCE

## running_difference​

### description​

#### Syntax​

`T running_difference(T x)` 计算数据块中连续行值的差值。该函数的结果取决于受影响的数据块和块中数据的顺序。

``-- running difference(x)SELECT running_difference(x) FROM t ORDER BY k;-- 窗口函数SELECT x - lag(x, 1, 0) OVER (ORDER BY k) FROM t;``

#### Arguments​

`x` - 一列数据.数据类型可以是TINYINT,SMALLINT,INT,BIGINT,LARGEINT,FLOAT,DOUBLE,DATE,DATETIME,DECIMAL

### example​

``DROP TABLE IF EXISTS running_difference_test;CREATE TABLE running_difference_test (    `id` int NOT NULL COMMENT 'id',    `day` date COMMENT 'day',     `time_val` datetime COMMENT 'time_val',    `doublenum` double NULL COMMENT 'doublenum')DUPLICATE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 3 PROPERTIES (     "replication_num" = "1");                                                   INSERT into running_difference_test (id, day, time_val,doublenum) values ('1', '2022-10-28', '2022-03-12 10:41:00', null),                                                   ('2','2022-10-27', '2022-03-12 10:41:02', 2.6),                                                   ('3','2022-10-28', '2022-03-12 10:41:03', 2.5),                                                   ('4','2022-9-29', '2022-03-12 10:41:03', null),                                                   ('5','2022-10-31', '2022-03-12 10:42:01', 3.3),                                                   ('6', '2022-11-08', '2022-03-12 11:05:04', 4.7); SELECT * from running_difference_test ORDER BY id ASC;+------+------------+---------------------+-----------+| id   | day        | time_val            | doublenum |+------+------------+---------------------+-----------+|    1 | 2022-10-28 | 2022-03-12 10:41:00 |      NULL ||    2 | 2022-10-27 | 2022-03-12 10:41:02 |       2.6 ||    3 | 2022-10-28 | 2022-03-12 10:41:03 |       2.5 ||    4 | 2022-09-29 | 2022-03-12 10:41:03 |      NULL ||    5 | 2022-10-31 | 2022-03-12 10:42:01 |       3.3 ||    6 | 2022-11-08 | 2022-03-12 11:05:04 |       4.7 |+------+------------+---------------------+-----------+SELECT    id,    running_difference(id) AS deltaFROM(    SELECT        id,        day,        time_val,        doublenum    FROM running_difference_test)as runningDifference ORDER BY id ASC;+------+-------+| id   | delta |+------+-------+|    1 |     0 ||    2 |     1 ||    3 |     1 ||    4 |     1 ||    5 |     1 ||    6 |     1 |+------+-------+SELECT    day,    running_difference(day) AS deltaFROM(    SELECT        id,        day,        time_val,        doublenum    FROM running_difference_test)as runningDifference ORDER BY id ASC;+------------+-------+| day        | delta |+------------+-------+| 2022-10-28 |     0 || 2022-10-27 |    -1 || 2022-10-28 |     1 || 2022-09-29 |   -29 || 2022-10-31 |    32 || 2022-11-08 |     8 |+------------+-------+SELECT    time_val,    running_difference(time_val) AS deltaFROM(    SELECT        id,        day,        time_val,        doublenum    FROM running_difference_test)as runningDifference ORDER BY id ASC;+---------------------+-------+| time_val            | delta |+---------------------+-------+| 2022-03-12 10:41:00 |     0 || 2022-03-12 10:41:02 |     2 || 2022-03-12 10:41:03 |     1 || 2022-03-12 10:41:03 |     0 || 2022-03-12 10:42:01 |    58 || 2022-03-12 11:05:04 |  1383 |+---------------------+-------+SELECT    doublenum,    running_difference(doublenum) AS deltaFROM(    SELECT        id,        day,        time_val,        doublenum    FROM running_difference_test)as runningDifference ORDER BY id ASC;+-----------+----------------------+| doublenum | delta                |+-----------+----------------------+|      NULL |                 NULL ||       2.6 |                 NULL ||       2.5 | -0.10000000000000009 ||      NULL |                 NULL ||       3.3 |                 NULL ||       4.7 |   1.4000000000000004 |+-----------+----------------------+``

### keywords​

running_difference