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 by calculate
SELECT ts, length(host), max(val) RANGE '5s' FROM host ALIGN '20s' BY (length(host)) ORDER BY ts;

+---------------------+-----------------------------+----------------------------------+
| ts                  | character_length(host.host) | MAX(host.val) RANGE 5s FILL NULL |
+---------------------+-----------------------------+----------------------------------+
| 1970-01-01T00:00:00 | 5                           | 3                                |
| 1970-01-01T00:00:20 | 5                           | 5                                |
+---------------------+-----------------------------+----------------------------------+

SELECT ts, max(val) RANGE '5s' FROM host ALIGN '20s' BY (2) ORDER BY ts;

+---------------------+----------------------------------+
| ts                  | MAX(host.val) RANGE 5s FILL NULL |
+---------------------+----------------------------------+
| 1970-01-01T00:00:00 | 3                                |
| 1970-01-01T00:00:20 | 5                                |
+---------------------+----------------------------------+

-- The user explicitly specifies that the aggregation key is empty. In this case, there is no aggregation key. All data will be aggregated into a group.
-- Implement by rewrite `BY()` to `BY(1)` automatically through sqlparser. They are semantically equivalent.
SELECT ts, max(val) RANGE '5s' FROM host ALIGN '20s' BY () ORDER BY ts;

+---------------------+----------------------------------+
| ts                  | MAX(host.val) RANGE 5s FILL NULL |
+---------------------+----------------------------------+
| 1970-01-01T00:00:00 | 3                                |
| 1970-01-01T00:00:20 | 5                                |
+---------------------+----------------------------------+

SELECT ts, CAST(length(host) as INT64) + 2, max(val) RANGE '5s' FROM host ALIGN '20s' BY (CAST(length(host) as INT64) + 2) ORDER BY ts;

+---------------------+----------------------------------------+----------------------------------+
| ts                  | character_length(host.host) + Int64(2) | MAX(host.val) RANGE 5s FILL NULL |
+---------------------+----------------------------------------+----------------------------------+
| 1970-01-01T00:00:00 | 7                                      | 3                                |
| 1970-01-01T00:00:20 | 7                                      | 5                                |
+---------------------+----------------------------------------+----------------------------------+

-- Test error
-- project non-aggregation key
SELECT ts, host, max(val) RANGE '5s' FROM host ALIGN '20s' BY () ORDER BY ts;

Error: 3001(EngineExecuteQuery), DataFusion error: No field named host.host. Valid fields are "MAX(host.val) RANGE 5s FILL NULL", host.ts, "Int64(1)".

DROP TABLE host;

Affected Rows: 0

-- Test no primary key and by keyword
CREATE TABLE host (
  ts timestamp(3) time index,
  host STRING,
  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

SELECT ts, max(val) RANGE '5s' FROM host ALIGN '20s' ORDER BY ts;

+---------------------+----------------------------------+
| ts                  | MAX(host.val) RANGE 5s FILL NULL |
+---------------------+----------------------------------+
| 1970-01-01T00:00:00 | 3                                |
| 1970-01-01T00:00:20 | 5                                |
+---------------------+----------------------------------+

DROP TABLE host;

Affected Rows: 0