CREATE TABLE host ( ts timestamp(3) time index, host STRING PRIMARY KEY, val BIGINT, addon BIGINT, ); Affected Rows: 0 INSERT INTO TABLE host VALUES (0, 'host1', 0, 1), (1000, 'host1', 1, 2), (2000, 'host1', 2, 3), (5000, 'host1', null, 4), (6000, 'host1', null, 5), (7000, 'host1', null, 6), (10000, 'host1', null, 7), (11000, 'host1', 4, 8), (12000, 'host1', 5, 9), (15000, 'host1', 6, 10), (16000, 'host1', null, 11), (17000, 'host1', 7, 12), (20000, 'host1', 8, 13), (21000, 'host1', 9, 14), (22000, 'host1', null, 15), (0, 'host2', 0, 16), (1000, 'host2', 1, 17), (2000, 'host2', 2, 18), (5000, 'host2', null, 19), (6000, 'host2', null, 20), (7000, 'host2', null, 21), (10000, 'host2', null, 22), (11000, 'host2', 4, 23), (12000, 'host2', 5, 24), (15000, 'host2', 6, 25), (16000, 'host2', null, 26), (17000, 'host2', 7, 27), (20000, 'host2', 8, 28), (21000, 'host2', 9, 29), (22000, 'host2', null, 30); Affected Rows: 30 SELECT ts, host, first_value(val) RANGE '5s', last_value(val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+--------------------------------+-------------------------------+ | ts | host | first_value(host.val) RANGE 5s | last_value(host.val) RANGE 5s | +---------------------+-------+--------------------------------+-------------------------------+ | 1970-01-01T00:00:00 | host1 | 0 | 2 | | 1970-01-01T00:00:05 | host1 | | | | 1970-01-01T00:00:10 | host1 | | 5 | | 1970-01-01T00:00:15 | host1 | 6 | 7 | | 1970-01-01T00:00:20 | host1 | 8 | | | 1970-01-01T00:00:00 | host2 | 0 | 2 | | 1970-01-01T00:00:05 | host2 | | | | 1970-01-01T00:00:10 | host2 | | 5 | | 1970-01-01T00:00:15 | host2 | 6 | 7 | | 1970-01-01T00:00:20 | host2 | 8 | | +---------------------+-------+--------------------------------+-------------------------------+ SELECT ts, host, first_value(addon ORDER BY val DESC) RANGE '5s', last_value(addon ORDER BY val DESC) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+-----------------------------------------------------------------------+----------------------------------------------------------------------+ | ts | host | first_value(host.addon) ORDER BY [host.val DESC NULLS FIRST] RANGE 5s | last_value(host.addon) ORDER BY [host.val DESC NULLS FIRST] RANGE 5s | +---------------------+-------+-----------------------------------------------------------------------+----------------------------------------------------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | 1 | | 1970-01-01T00:00:05 | host1 | 4 | 6 | | 1970-01-01T00:00:10 | host1 | 7 | 8 | | 1970-01-01T00:00:15 | host1 | 11 | 10 | | 1970-01-01T00:00:20 | host1 | 15 | 13 | | 1970-01-01T00:00:00 | host2 | 18 | 16 | | 1970-01-01T00:00:05 | host2 | 19 | 21 | | 1970-01-01T00:00:10 | host2 | 22 | 23 | | 1970-01-01T00:00:15 | host2 | 26 | 25 | | 1970-01-01T00:00:20 | host2 | 30 | 28 | +---------------------+-------+-----------------------------------------------------------------------+----------------------------------------------------------------------+ SELECT ts, host, first_value(addon ORDER BY val DESC NULLS LAST) RANGE '5s', last_value(addon ORDER BY val DESC NULLS LAST) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ | ts | host | first_value(host.addon) ORDER BY [host.val DESC NULLS LAST] RANGE 5s | last_value(host.addon) ORDER BY [host.val DESC NULLS LAST] RANGE 5s | +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | 1 | | 1970-01-01T00:00:05 | host1 | 4 | 6 | | 1970-01-01T00:00:10 | host1 | 9 | 7 | | 1970-01-01T00:00:15 | host1 | 12 | 11 | | 1970-01-01T00:00:20 | host1 | 14 | 15 | | 1970-01-01T00:00:00 | host2 | 18 | 16 | | 1970-01-01T00:00:05 | host2 | 19 | 21 | | 1970-01-01T00:00:10 | host2 | 24 | 22 | | 1970-01-01T00:00:15 | host2 | 27 | 26 | | 1970-01-01T00:00:20 | host2 | 29 | 30 | +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ SELECT ts, host, first_value(addon ORDER BY val ASC) RANGE '5s', last_value(addon ORDER BY val ASC) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+---------------------------------------------------------------------+--------------------------------------------------------------------+ | ts | host | first_value(host.addon) ORDER BY [host.val ASC NULLS LAST] RANGE 5s | last_value(host.addon) ORDER BY [host.val ASC NULLS LAST] RANGE 5s | +---------------------+-------+---------------------------------------------------------------------+--------------------------------------------------------------------+ | 1970-01-01T00:00:00 | host1 | 1 | 3 | | 1970-01-01T00:00:05 | host1 | 4 | 6 | | 1970-01-01T00:00:10 | host1 | 8 | 7 | | 1970-01-01T00:00:15 | host1 | 10 | 11 | | 1970-01-01T00:00:20 | host1 | 13 | 15 | | 1970-01-01T00:00:00 | host2 | 16 | 18 | | 1970-01-01T00:00:05 | host2 | 19 | 21 | | 1970-01-01T00:00:10 | host2 | 23 | 22 | | 1970-01-01T00:00:15 | host2 | 25 | 26 | | 1970-01-01T00:00:20 | host2 | 28 | 30 | +---------------------+-------+---------------------------------------------------------------------+--------------------------------------------------------------------+ SELECT ts, host, first_value(addon ORDER BY val ASC NULLS FIRST) RANGE '5s', last_value(addon ORDER BY val ASC NULLS FIRST) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ | ts | host | first_value(host.addon) ORDER BY [host.val ASC NULLS FIRST] RANGE 5s | last_value(host.addon) ORDER BY [host.val ASC NULLS FIRST] RANGE 5s | +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ | 1970-01-01T00:00:00 | host1 | 1 | 3 | | 1970-01-01T00:00:05 | host1 | 4 | 6 | | 1970-01-01T00:00:10 | host1 | 7 | 9 | | 1970-01-01T00:00:15 | host1 | 11 | 12 | | 1970-01-01T00:00:20 | host1 | 15 | 14 | | 1970-01-01T00:00:00 | host2 | 16 | 18 | | 1970-01-01T00:00:05 | host2 | 19 | 21 | | 1970-01-01T00:00:10 | host2 | 22 | 24 | | 1970-01-01T00:00:15 | host2 | 26 | 27 | | 1970-01-01T00:00:20 | host2 | 30 | 29 | +---------------------+-------+----------------------------------------------------------------------+---------------------------------------------------------------------+ SELECT ts, host, first_value(addon ORDER BY val ASC, ts ASC) RANGE '5s', last_value(addon ORDER BY val ASC, ts ASC) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+ | ts | host | first_value(host.addon) ORDER BY [host.val ASC NULLS LAST, host.ts ASC NULLS LAST] RANGE 5s | last_value(host.addon) ORDER BY [host.val ASC NULLS LAST, host.ts ASC NULLS LAST] RANGE 5s | +---------------------+-------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+ | 1970-01-01T00:00:00 | host1 | 1 | 3 | | 1970-01-01T00:00:05 | host1 | 4 | 6 | | 1970-01-01T00:00:10 | host1 | 8 | 7 | | 1970-01-01T00:00:15 | host1 | 10 | 11 | | 1970-01-01T00:00:20 | host1 | 13 | 15 | | 1970-01-01T00:00:00 | host2 | 16 | 18 | | 1970-01-01T00:00:05 | host2 | 19 | 21 | | 1970-01-01T00:00:10 | host2 | 23 | 22 | | 1970-01-01T00:00:15 | host2 | 25 | 26 | | 1970-01-01T00:00:20 | host2 | 28 | 30 | +---------------------+-------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+ SELECT ts, host, count(val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+--------------------------+ | ts | host | count(host.val) RANGE 5s | +---------------------+-------+--------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | | 1970-01-01T00:00:05 | host1 | 0 | | 1970-01-01T00:00:10 | host1 | 2 | | 1970-01-01T00:00:15 | host1 | 2 | | 1970-01-01T00:00:20 | host1 | 2 | | 1970-01-01T00:00:00 | host2 | 3 | | 1970-01-01T00:00:05 | host2 | 0 | | 1970-01-01T00:00:10 | host2 | 2 | | 1970-01-01T00:00:15 | host2 | 2 | | 1970-01-01T00:00:20 | host2 | 2 | +---------------------+-------+--------------------------+ SELECT ts, host, count(distinct val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+-----------------------------------+ | ts | host | count(DISTINCT host.val) RANGE 5s | +---------------------+-------+-----------------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | | 1970-01-01T00:00:05 | host1 | 0 | | 1970-01-01T00:00:10 | host1 | 2 | | 1970-01-01T00:00:15 | host1 | 2 | | 1970-01-01T00:00:20 | host1 | 2 | | 1970-01-01T00:00:00 | host2 | 3 | | 1970-01-01T00:00:05 | host2 | 0 | | 1970-01-01T00:00:10 | host2 | 2 | | 1970-01-01T00:00:15 | host2 | 2 | | 1970-01-01T00:00:20 | host2 | 2 | +---------------------+-------+-----------------------------------+ SELECT ts, host, count(*) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+--------------------------+ | ts | host | count(Int64(1)) RANGE 5s | +---------------------+-------+--------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | | 1970-01-01T00:00:05 | host1 | 3 | | 1970-01-01T00:00:10 | host1 | 3 | | 1970-01-01T00:00:15 | host1 | 3 | | 1970-01-01T00:00:20 | host1 | 3 | | 1970-01-01T00:00:00 | host2 | 3 | | 1970-01-01T00:00:05 | host2 | 3 | | 1970-01-01T00:00:10 | host2 | 3 | | 1970-01-01T00:00:15 | host2 | 3 | | 1970-01-01T00:00:20 | host2 | 3 | +---------------------+-------+--------------------------+ SELECT ts, host, count(1) RANGE '5s' as abc FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+-----+ | ts | host | abc | +---------------------+-------+-----+ | 1970-01-01T00:00:00 | host1 | 3 | | 1970-01-01T00:00:05 | host1 | 3 | | 1970-01-01T00:00:10 | host1 | 3 | | 1970-01-01T00:00:15 | host1 | 3 | | 1970-01-01T00:00:20 | host1 | 3 | | 1970-01-01T00:00:00 | host2 | 3 | | 1970-01-01T00:00:05 | host2 | 3 | | 1970-01-01T00:00:10 | host2 | 3 | | 1970-01-01T00:00:15 | host2 | 3 | | 1970-01-01T00:00:20 | host2 | 3 | +---------------------+-------+-----+ SELECT ts, host, count(distinct *) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; +---------------------+-------+-----------------------------------+ | ts | host | count(DISTINCT Int64(1)) RANGE 5s | +---------------------+-------+-----------------------------------+ | 1970-01-01T00:00:00 | host1 | 3 | | 1970-01-01T00:00:05 | host1 | 3 | | 1970-01-01T00:00:10 | host1 | 3 | | 1970-01-01T00:00:15 | host1 | 3 | | 1970-01-01T00:00:20 | host1 | 3 | | 1970-01-01T00:00:00 | host2 | 3 | | 1970-01-01T00:00:05 | host2 | 3 | | 1970-01-01T00:00:10 | host2 | 3 | | 1970-01-01T00:00:15 | host2 | 3 | | 1970-01-01T00:00:20 | host2 | 3 | +---------------------+-------+-----------------------------------+ -- Test error first_value/last_value SELECT ts, host, first_value(val, val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts; Error: 3000(PlanQuery), Failed to plan SQL: Error during planning: The function 'first_value' expected 1 arguments but received 2 No function matches the given name and argument types 'first_value(Int64, Int64)'. You might need to add explicit type casts. Candidate functions: first_value(Any) DROP TABLE host; Affected Rows: 0 -- Test first_value/last_value will execute sort CREATE TABLE host ( ts timestamp(3) time index, host STRING PRIMARY KEY, val BIGINT, addon BIGINT, ); Affected Rows: 0 INSERT INTO TABLE host VALUES (0, 'host1', 0, 3), (1000, 'host1', 1, 2), (2000, 'host1', 2, 1); Affected Rows: 3 SELECT ts, first_value(val ORDER BY addon ASC) RANGE '5s', last_value(val ORDER BY addon ASC) RANGE '5s' FROM host ALIGN '5s'; +---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+ | ts | first_value(host.val) ORDER BY [host.addon ASC NULLS LAST] RANGE 5s | last_value(host.val) ORDER BY [host.addon ASC NULLS LAST] RANGE 5s | +---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+ | 1970-01-01T00:00:00 | 2 | 0 | +---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+ DROP TABLE host; Affected Rows: 0