mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-19 06:20:38 +00:00
c2218f8be8
* build(deps): bump datafusion 20240528 Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * another update Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update expected sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix first/last value Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * reformat comment Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix remaining errors Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * revert toml format Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * fix pyo3 feature Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * remove dead code Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * Apply suggestions from code review Co-authored-by: Jeremyhi <jiachun_feng@proton.me> * format file Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com> Co-authored-by: Jeremyhi <jiachun_feng@proton.me>
195 lines
13 KiB
Plaintext
195 lines
13 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 | covar_samp(host.val,host.val) RANGE 20s |
|
|
+---------------------+-------+-----------------------------------------+
|
|
| 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 |
|
|
+---------------------+-------+-----------------------------------+
|
|
| 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 |
|
|
+---------------------+-------+-----------------------------------+
|
|
| 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 |
|
|
+---------------------+-------+------------------------+
|
|
| 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 |
|
|
+---------------------+-------+-------------------------------+
|
|
| 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) |
|
|
+---------------------+-------+-------------------------------+
|
|
| 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 + MAX(host.val) RANGE 20s + 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 | covar_samp(ceil(host.val),floor(host.val)) RANGE 20s |
|
|
+---------------------+-------+------------------------------------------------------+
|
|
| 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)))) |
|
|
+---------------------+-------+-----------------------------------------------+
|
|
| 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(arrow_cast(MAX(floor(host.val)) RANGE 10s FILL PREV,Utf8("Int64")) * 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
|
|
|