mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-14 17:23:09 +00:00
2f637a262e
* chore: update datafusion to 50 Signed-off-by: luofucong <luofc@foxmail.com> * fix ci Signed-off-by: luofucong <luofc@foxmail.com> * fix: update datafusion_pg_catalog import * chore: fix toml format * chore: fix toml format again * fix nextest Signed-off-by: luofucong <luofc@foxmail.com> * fix sqlness Signed-off-by: luofucong <luofc@foxmail.com> * chore: switch datafusion-orc to upstream tag * fix sqlness Signed-off-by: luofucong <luofc@foxmail.com> * resolve PR comments Signed-off-by: luofucong <luofc@foxmail.com> --------- Signed-off-by: luofucong <luofc@foxmail.com> Co-authored-by: Ning Sun <sunning@greptime.com>
212 lines
15 KiB
Plaintext
212 lines
15 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 |
|
|
+---------------------+-------+-------------------------------+
|
|
|
|
SELECT ts, host, approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
|
|
|
|
+---------------------+-------+--------------------------------------------------------------------------------------+
|
|
| ts | host | approx_percentile_cont(Float64(0.5)) WITHIN GROUP [host.val ASC NULLS LAST] RANGE 5s |
|
|
+---------------------+-------+--------------------------------------------------------------------------------------+
|
|
| 1970-01-01T00:00:00 | host1 | 0 |
|
|
| 1970-01-01T00:00:05 | host1 | |
|
|
| 1970-01-01T00:00:10 | host1 | 1 |
|
|
| 1970-01-01T00:00:15 | host1 | |
|
|
| 1970-01-01T00:00:20 | host1 | 2 |
|
|
| 1970-01-01T00:00:00 | host2 | 3 |
|
|
| 1970-01-01T00:00:05 | host2 | |
|
|
| 1970-01-01T00:00:10 | host2 | 4 |
|
|
| 1970-01-01T00:00:15 | host2 | |
|
|
| 1970-01-01T00:00:20 | host2 | 5 |
|
|
+---------------------+-------+--------------------------------------------------------------------------------------+
|
|
|
|
-- 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) * 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
|
|
|