rust/greptimedb
1
0
Fork 0
mirror of https://github.com/GreptimeTeam/greptimedb.git synced 2026-05-19 14:30:43 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
develop
greptimedb/tests/cases/standalone/common/range/calculate.result

195 lines
14 KiB
Plaintext
Raw Permalink Blame History

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 |
+---------------------+-------+---------------------------------------------------+
| 1969-12-31T23:59:50 | host1 | |
| 1970-01-01T00:00:00 | host1 | 0.5 |
| 1970-01-01T00:00:10 | host1 | 0.5 |
| 1970-01-01T00:00:20 | host1 | |
| 1969-12-31T23:59:50 | host2 | |
| 1970-01-01T00:00:00 | host2 | 0.5 |
| 1970-01-01T00:00:10 | host2 | 0.5 |
| 1970-01-01T00:00:20 | 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(val::DOUBLE) 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(val::DOUBLE)) 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) |
+---------------------+-------+------------------------------------------------------------------------------------+
| 1969-12-31T23:59:50 | host1 | 1.0 |
| 1970-01-01T00:00:00 | host1 | 2.0 |
| 1970-01-01T00:00:10 | host1 | 4.0 |
| 1970-01-01T00:00:20 | host1 | 5.0 |
| 1969-12-31T23:59:50 | host2 | 7.0 |
| 1970-01-01T00:00:00 | host2 | 8.0 |
| 1970-01-01T00:00:10 | host2 | 10.0 |
| 1970-01-01T00:00:20 | host2 | 11.0 |
+---------------------+-------+------------------------------------------------------------------------------------+
SELECT ts, host, covar(ceil(val::DOUBLE), floor(val::DOUBLE)) RANGE '20s' FROM host ALIGN '10s' ORDER BY host, ts;
+---------------------+-------+----------------------------------------------------------------+
| ts | host | COVARIANCE(ceil(host.val),floor(host.val)) RANGE 20s FILL NULL |
+---------------------+-------+----------------------------------------------------------------+
| 1969-12-31T23:59:50 | host1 | |
| 1970-01-01T00:00:00 | host1 | 0.5 |
| 1970-01-01T00:00:10 | host1 | 0.5 |
| 1970-01-01T00:00:20 | host1 | |
| 1969-12-31T23:59:50 | host2 | |
| 1970-01-01T00:00:00 | host2 | 0.5 |
| 1970-01-01T00:00:10 | host2 | 0.5 |
| 1970-01-01T00:00:20 | 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(val::DOUBLE)) 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) |
+---------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| 1969-12-31T23:59:55 | host1 | 1 |
| 1970-01-01T00:00:00 | host1 | 1 |
| 1970-01-01T00:00:05 | host1 | 21 |
| 1970-01-01T00:00:10 | host1 | 21 |
| 1970-01-01T00:00:15 | host1 | 41 |
| 1970-01-01T00:00:20 | host1 | 41 |
| 1969-12-31T23:59:55 | host2 | 61 |
| 1970-01-01T00:00:00 | host2 | 61 |
| 1970-01-01T00:00:05 | host2 | 81 |
| 1970-01-01T00:00:10 | host2 | 81 |
| 1970-01-01T00:00:15 | host2 | 101 |
| 1970-01-01T00:00:20 | host2 | 101 |
+---------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
DROP TABLE host;
Affected Rows: 0
Reference in New Issue View Git Blame Copy Permalink