running_difference

description

Syntax

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

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

1. -- running difference(x)
2. SELECT running_difference(x) FROM t ORDER BY k;

4. -- 窗口函数
5. 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

1. DROP TABLE IF EXISTS running_difference_test;

3. CREATE TABLE running_difference_test (
4. `id` int NOT NULL COMMENT 'id',
5. `day` date COMMENT 'day',
6. `time_val` datetime COMMENT 'time_val',
7. `doublenum` double NULL COMMENT 'doublenum'
8. )
9. DUPLICATE KEY(id)
10. DISTRIBUTED BY HASH(id) BUCKETS 3
11. PROPERTIES (
12. "replication_num" = "1"
13. );

15. INSERT into running_difference_test (id, day, time_val,doublenum) values ('1', '2022-10-28', '2022-03-12 10:41:00', null),
16. ('2','2022-10-27', '2022-03-12 10:41:02', 2.6),
17. ('3','2022-10-28', '2022-03-12 10:41:03', 2.5),
18. ('4','2022-9-29', '2022-03-12 10:41:03', null),
19. ('5','2022-10-31', '2022-03-12 10:42:01', 3.3),
20. ('6', '2022-11-08', '2022-03-12 11:05:04', 4.7);

22. SELECT * from running_difference_test ORDER BY id ASC;

24. +------+------------+---------------------+-----------+
25. | id   | day        | time_val            | doublenum |
26. +------+------------+---------------------+-----------+
27. |    1 | 2022-10-28 | 2022-03-12 10:41:00 |      NULL |
28. |    2 | 2022-10-27 | 2022-03-12 10:41:02 |       2.6 |
29. |    3 | 2022-10-28 | 2022-03-12 10:41:03 |       2.5 |
30. |    4 | 2022-09-29 | 2022-03-12 10:41:03 |      NULL |
31. |    5 | 2022-10-31 | 2022-03-12 10:42:01 |       3.3 |
32. |    6 | 2022-11-08 | 2022-03-12 11:05:04 |       4.7 |
33. +------+------------+---------------------+-----------+

35. SELECT
36. id,
37. running_difference(id) AS delta
38. FROM
39. (
40. SELECT
41. id,
42. day,
43. time_val,
44. doublenum
45. FROM running_difference_test
46. )as runningDifference ORDER BY id ASC;

48. +------+-------+
49. | id   | delta |
50. +------+-------+
51. |    1 |     0 |
52. |    2 |     1 |
53. |    3 |     1 |
54. |    4 |     1 |
55. |    5 |     1 |
56. |    6 |     1 |
57. +------+-------+

59. SELECT
60. day,
61. running_difference(day) AS delta
62. FROM
63. (
64. SELECT
65. id,
66. day,
67. time_val,
68. doublenum
69. FROM running_difference_test
70. )as runningDifference ORDER BY id ASC;

72. +------------+-------+
73. | day        | delta |
74. +------------+-------+
75. | 2022-10-28 |     0 |
76. | 2022-10-27 |    -1 |
77. | 2022-10-28 |     1 |
78. | 2022-09-29 |   -29 |
79. | 2022-10-31 |    32 |
80. | 2022-11-08 |     8 |
81. +------------+-------+

83. SELECT
84. time_val,
85. running_difference(time_val) AS delta
86. FROM
87. (
88. SELECT
89. id,
90. day,
91. time_val,
92. doublenum
93. FROM running_difference_test
94. )as runningDifference ORDER BY id ASC;

96. +---------------------+-------+
97. | time_val            | delta |
98. +---------------------+-------+
99. | 2022-03-12 10:41:00 |     0 |
100. | 2022-03-12 10:41:02 |     2 |
101. | 2022-03-12 10:41:03 |     1 |
102. | 2022-03-12 10:41:03 |     0 |
103. | 2022-03-12 10:42:01 |    58 |
104. | 2022-03-12 11:05:04 |  1383 |
105. +---------------------+-------+

107. SELECT
108. doublenum,
109. running_difference(doublenum) AS delta
110. FROM
111. (
112. SELECT
113. id,
114. day,
115. time_val,
116. doublenum
117. FROM running_difference_test
118. )as runningDifference ORDER BY id ASC;

120. +-----------+----------------------+
121. | doublenum | delta                |
122. +-----------+----------------------+
123. |      NULL |                 NULL |
124. |       2.6 |                 NULL |
125. |       2.5 | -0.10000000000000009 |
126. |      NULL |                 NULL |
127. |       3.3 |                 NULL |
128. |       4.7 |   1.4000000000000004 |
129. +-----------+----------------------+

keywords

running_difference