跳到主要内容
跳到主要内容

running_difference

running_difference

description

Syntax

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

计算 running_difference 期间使用的行顺序可能与返回给用户的行顺序不同。所以结果是不稳定的。此函数会在后续版本中废弃。 推荐使用窗口函数完成预期功能。举例如下:

-- 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

Returned value

第一行返回 0,随后的每一行返回与前一行的差值。

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 delta
FROM
(
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 delta
FROM
(
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 delta
FROM
(
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 delta
FROM
(
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