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