greptimedb/tests/cases/standalone/common/range/by.result

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                  | length(host.host) | max(host.val) RANGE 5s |
+---------------------+-------------------+------------------------+
| 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 |
+---------------------+------------------------+
| 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 |
+---------------------+------------------------+
| 1970-01-01T00:00:00 | 3                      |
| 1970-01-01T00:00:20 | 5                      |
+---------------------+------------------------+

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

+---------------------+--------------------------------------------------------+------------------------+
| ts                  | arrow_cast(length(host.host),Utf8("Int64")) + Int64(2) | max(host.val) RANGE 5s |
+---------------------+--------------------------------------------------------+------------------------+
| 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), No field named host.host. Did you mean 'host.ts'?
Valid fields are "max(host.val) RANGE 5s", 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 |
+---------------------+------------------------+
| 1970-01-01T00:00:00 | 3                      |
| 1970-01-01T00:00:20 | 5                      |
+---------------------+------------------------+

DROP TABLE host;

Affected Rows: 0

CREATE TABLE grpc_latencies (
  ts TIMESTAMP TIME INDEX,
  host STRING,
  method_name STRING,
  latency DOUBLE,
  PRIMARY KEY (host, method_name)
) with('append_mode'='true');

Affected Rows: 0

INSERT INTO grpc_latencies (ts, host, method_name, latency) VALUES
  ('2024-07-11 20:00:06', 'host1', 'GetUser', 103.0),
  ('2024-07-11 20:00:06', 'host2', 'GetUser', 113.0),
  ('2024-07-11 20:00:07', 'host1', 'GetUser', 103.5),
  ('2024-07-11 20:00:07', 'host2', 'GetUser', 107.0),
  ('2024-07-11 20:00:08', 'host1', 'GetUser', 104.0),
  ('2024-07-11 20:00:08', 'host2', 'GetUser', 96.0),
  ('2024-07-11 20:00:09', 'host1', 'GetUser', 104.5),
  ('2024-07-11 20:00:09', 'host2', 'GetUser', 114.0);

Affected Rows: 8

SELECT ts, count(distinct host) RANGE '5s' as h FROM grpc_latencies ALIGN '5s' by (method_name);

+---------------------+---+
| ts                  | h |
+---------------------+---+
| 2024-07-11T20:00:05 | 2 |
+---------------------+---+

SELECT ts, count(*) RANGE '5s' as h FROM grpc_latencies ALIGN '5s' by (method_name);

+---------------------+---+
| ts                  | h |
+---------------------+---+
| 2024-07-11T20:00:05 | 8 |
+---------------------+---+

select date_bin(INTERVAL '5s', ts) as t, count(distinct host) as count from grpc_latencies group by t;

+---------------------+-------+
| t                   | count |
+---------------------+-------+
| 2024-07-11T20:00:05 | 2     |
+---------------------+-------+

DROP TABLE grpc_latencies;

Affected Rows: 0