mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-11 15:52:55 +00:00
5d8b0e8154
* fix all hard error Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix nextest Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * trivial changes Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix order by Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix sql keyword and data type Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix range exec's input partitioning Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix cover input type Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix explain analyze Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update distributed mode sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix lints Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update locks Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * downgrade dlv-list Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
195 lines
14 KiB
Plaintext
195 lines
14 KiB
Plaintext
CREATE TABLE host (
|
|
ts timestamp(3) time index,
|
|
host STRING PRIMARY KEY,
|
|
val BIGINT,
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO TABLE host VALUES
|
|
(0, 'host1', 0),
|
|
(5000, 'host1', null),
|
|
(10000, 'host1', 1),
|
|
(15000, 'host1', null),
|
|
(20000, 'host1', 2),
|
|
(0, 'host2', 3),
|
|
(5000, 'host2', null),
|
|
(10000, 'host2', 4),
|
|
(15000, 'host2', null),
|
|
(20000, 'host2', 5);
|
|
|
|
Affected Rows: 10
|
|
|
|
-- Test range expr calculate
|
|
SELECT ts, host, covar(val::DOUBLE, val::DOUBLE) RANGE '20s' FROM host ALIGN '10s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+---------------------------------------------------+
|
|
| ts | host | COVARIANCE(host.val,host.val) RANGE 20s FILL NULL |
|
|
+---------------------+-------+---------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 0.5 |
|
|
| 1970-01-01T00:00:20 | host1 | 0.5 |
|
|
| 1970-01-01T00:00:30 | host1 | |
|
|
| 1970-01-01T00:00:00 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 0.5 |
|
|
| 1970-01-01T00:00:20 | host2 | 0.5 |
|
|
| 1970-01-01T00:00:30 | host2 | |
|
|
+---------------------+-------+---------------------------------------------------+
|
|
|
|
SELECT ts, host, 2 * min(val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+---------------------------------------------+
|
|
| ts | host | Int64(2) * MIN(host.val) RANGE 5s FILL NULL |
|
|
+---------------------+-------+---------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 2 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 4 |
|
|
| 1970-01-01T00:00:00 | host2 | 6 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 8 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 10 |
|
|
+---------------------+-------+---------------------------------------------+
|
|
|
|
SELECT ts, host, min(val * 2) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+---------------------------------------------+
|
|
| ts | host | MIN(host.val * Int64(2)) RANGE 5s FILL NULL |
|
|
+---------------------+-------+---------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 2 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 4 |
|
|
| 1970-01-01T00:00:00 | host2 | 6 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 8 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 10 |
|
|
+---------------------+-------+---------------------------------------------+
|
|
|
|
SELECT ts, host, min(CAST(val as Float64)) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+----------------------------------+
|
|
| ts | host | MIN(host.val) RANGE 5s FILL NULL |
|
|
+---------------------+-------+----------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0.0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 1.0 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 2.0 |
|
|
| 1970-01-01T00:00:00 | host2 | 3.0 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 4.0 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 5.0 |
|
|
+---------------------+-------+----------------------------------+
|
|
|
|
SELECT ts, host, min(floor(CAST(val as Float64))) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+-----------------------------------------+
|
|
| ts | host | MIN(floor(host.val)) RANGE 5s FILL NULL |
|
|
+---------------------+-------+-----------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0.0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 1.0 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 2.0 |
|
|
| 1970-01-01T00:00:00 | host2 | 3.0 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 4.0 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 5.0 |
|
|
+---------------------+-------+-----------------------------------------+
|
|
|
|
SELECT ts, host, floor(min(val) RANGE '5s') FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+-----------------------------------------+
|
|
| ts | host | floor(MIN(host.val) RANGE 5s FILL NULL) |
|
|
+---------------------+-------+-----------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0.0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 1.0 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 2.0 |
|
|
| 1970-01-01T00:00:00 | host2 | 3.0 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 4.0 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 5.0 |
|
|
+---------------------+-------+-----------------------------------------+
|
|
|
|
-- Test complex range expr calculate
|
|
SELECT ts, host, (min(val) + max(val)) RANGE '20s' + 1.0 FROM host ALIGN '10s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+------------------------------------------------------------------------------------+
|
|
| ts | host | MIN(host.val) RANGE 20s FILL NULL + MAX(host.val) RANGE 20s FILL NULL + Float64(1) |
|
|
+---------------------+-------+------------------------------------------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 1.0 |
|
|
| 1970-01-01T00:00:10 | host1 | 2.0 |
|
|
| 1970-01-01T00:00:20 | host1 | 4.0 |
|
|
| 1970-01-01T00:00:30 | host1 | 5.0 |
|
|
| 1970-01-01T00:00:00 | host2 | 7.0 |
|
|
| 1970-01-01T00:00:10 | host2 | 8.0 |
|
|
| 1970-01-01T00:00:20 | host2 | 10.0 |
|
|
| 1970-01-01T00:00:30 | host2 | 11.0 |
|
|
+---------------------+-------+------------------------------------------------------------------------------------+
|
|
|
|
SELECT ts, host, covar(ceil(CAST(val as Float64)), floor(CAST(val as Float64))) RANGE '20s' FROM host ALIGN '10s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+----------------------------------------------------------------+
|
|
| ts | host | COVARIANCE(ceil(host.val),floor(host.val)) RANGE 20s FILL NULL |
|
|
+---------------------+-------+----------------------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 0.5 |
|
|
| 1970-01-01T00:00:20 | host1 | 0.5 |
|
|
| 1970-01-01T00:00:30 | host1 | |
|
|
| 1970-01-01T00:00:00 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 0.5 |
|
|
| 1970-01-01T00:00:20 | host2 | 0.5 |
|
|
| 1970-01-01T00:00:30 | host2 | |
|
|
+---------------------+-------+----------------------------------------------------------------+
|
|
|
|
SELECT ts, host, floor(cos(ceil(sin(min(val) RANGE '5s')))) FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+---------------------------------------------------------+
|
|
| ts | host | floor(cos(ceil(sin(MIN(host.val) RANGE 5s FILL NULL)))) |
|
|
+---------------------+-------+---------------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 1.0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 0.0 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 0.0 |
|
|
| 1970-01-01T00:00:00 | host2 | 0.0 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 1.0 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 1.0 |
|
|
+---------------------+-------+---------------------------------------------------------+
|
|
|
|
SELECT ts, host, gcd(CAST(max(floor(CAST(val as Float64))) RANGE '10s' FILL PREV as INT64) * 4, max(val * 4) RANGE '10s' FILL PREV) * length(host) + 1 FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| ts | host | gcd(MAX(floor(host.val)) RANGE 10s FILL PREV * Int64(4),MAX(host.val * Int64(4)) RANGE 10s FILL PREV) * character_length(host.host) + Int64(1) |
|
|
+---------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 1 |
|
|
| 1970-01-01T00:00:05 | host1 | 1 |
|
|
| 1970-01-01T00:00:10 | host1 | 21 |
|
|
| 1970-01-01T00:00:15 | host1 | 21 |
|
|
| 1970-01-01T00:00:20 | host1 | 41 |
|
|
| 1970-01-01T00:00:25 | host1 | 41 |
|
|
| 1970-01-01T00:00:00 | host2 | 61 |
|
|
| 1970-01-01T00:00:05 | host2 | 61 |
|
|
| 1970-01-01T00:00:10 | host2 | 81 |
|
|
| 1970-01-01T00:00:15 | host2 | 81 |
|
|
| 1970-01-01T00:00:20 | host2 | 101 |
|
|
| 1970-01-01T00:00:25 | host2 | 101 |
|
|
+---------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
DROP TABLE host;
|
|
|
|
Affected Rows: 0
|
|
|