RANGE QUERY
查询并聚合一个给定长度的时间范围的数据是时序数据常见的一种查询模式，例如 PromQL 中的 Range selector。而 GreptimeDB 在 SQL 中支持了 Range 查询，用于将时序数据汇总为时间块，并在时间块上进行数据的聚合。Range 查询作为 SELECT 语句的一部分，可与 SQL 灵活结合，从而在 SQL 中提供更强大的时序数据查询能力。
Syntax
Range query 使用 Time Index 列作为聚合的时间线。 一个合法的 Range 查询语法结构如下所示：
sql
SELECT
AGGR_FUNCTION(column1, column2,..) RANGE INTERVAL [FILL FILL_OPTION],
...
FROM table_name
[ WHERE <where_clause>]
ALIGN INTERVAL [ TO TO_OPTION ] [BY (columna, columnb,..)] [FILL FILL_OPTION]
[ ORDER BY <order_by_clause>]
[ LIMIT <limit_clause>];
INTERVAL := TIME_INTERVAL | ( INTERVAL expr )SELECT
AGGR_FUNCTION(column1, column2,..) RANGE INTERVAL [FILL FILL_OPTION],
...
FROM table_name
[ WHERE <where_clause>]
ALIGN INTERVAL [ TO TO_OPTION ] [BY (columna, columnb,..)] [FILL FILL_OPTION]
[ ORDER BY <order_by_clause>]
[ LIMIT <limit_clause>];
INTERVAL := TIME_INTERVAL | ( INTERVAL expr )- 关键字
ALIGN，必选字段，后接参数INTERVAL，ALIGN指明了 Range 查询的步长。 - 参数
INTERVAL，主要用于给出一段时间长度，有两种参数形式：- 基于
PromQL Time Durations格式的字符串（例如：3h、1h30m）。访问 Prometheus 文档 获取该格式更详细的说明。 Interval类型，使用Interval类型需要携带括号,（例如：(INTERVAL '1 year 3 hours 20 minutes')）。访问 Interval 获取该格式更详细的说明。
- 基于
AGGR_FUNCTION(column1, column2,..) RANGE INTERVAL [FILL FILL_OPTION]称为一个 Range 表达式。AGGR_FUNCTION(column1, column2,..)是一个聚合函数，代表需要聚合的表达式。- 关键字
RANGE，必选字段，后接参数INTERVAL指定了每次数据聚合的时间范围， - 关键字
FILL，可选字段，详情请见FILLOption。 - Range 表达式可与其他运算结合，实现更复杂的查询。具体见嵌套使用 Range 表达式 。
- 关键字
FILL，可以跟在一个 Range 表达式后，详情请见FILL Option 。
FILL 选项
FILL 选项指定了在某个聚合的时间片上没有数据，或者聚合字段的值为空时的数据填充方法。
它可以跟在一个 Range 表达式后，作为这个 Range 表达式的数据填充方法；也可以放在 ALIGN 后面作为所有未指定 FILL 选项的 Range 表达式的填充方法。
例如，在下面的 SQL 代码中， max(val) RANGE '10s' 范围表达式使用 FILL 选项 LINEAR，而 min(val) RANGE '10s' 没有指定 FILL 选项，它将使用在 ALIGN关键字之后指定的选项PREV。
sql
SELECT
ts,
host,
min(val) RANGE '10s',
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s' BY (host) FILL PREV;SELECT
ts,
host,
min(val) RANGE '10s',
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s' BY (host) FILL PREV;FILL 有以下几种选项：
| FILL | 描述 |
|---|---|
NULL | 直接使用 NULL 填充 |
PREV | 使用前一个点的数据填充 |
LINEAR | 使用线性插值法填充数据，如果一个整数类型使用 LINEAR 填充，则该列的变量类型会在计算的时候被隐式转换为浮点类型 |
X | 填充一个常量，该常量的数据类型必须和 Range 表达式的变量类型一致 |
以下面这张表为例
sql
+---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------++---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------+不同 FILL 选项的结果如下：
sql
> SELECT ts, host, min(val) RANGE '5s' FROM host ALIGN '5s';
+---------------------+-------+------------------------+
| ts | host | MIN(host.val) RANGE 5s |
+---------------------+-------+------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------------------------+
> SELECT ts, host, min(val) RANGE '5s' FROM host ALIGN '5s';
+---------------------+-------+------------------------+
| ts | host | MIN(host.val) RANGE 5s |
+---------------------+-------+------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------------------------+sql
> SELECT ts, host, min(val) RANGE '5s' FILL NULL FROM host ALIGN '5s';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL NULL |
+---------------------+-------+----------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | NULL |
| 1970-01-01 00:00:10 | host1 | NULL |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | NULL |
| 1970-01-01 00:00:10 | host2 | NULL |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+----------------------------------+
> SELECT ts, host, min(val) RANGE '5s' FILL NULL FROM host ALIGN '5s';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL NULL |
+---------------------+-------+----------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | NULL |
| 1970-01-01 00:00:10 | host1 | NULL |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | NULL |
| 1970-01-01 00:00:10 | host2 | NULL |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+----------------------------------+sql
> SELECT ts, host, min(val) RANGE '5s' FILL PREV FROM host ALIGN '5s';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL PREV |
+---------------------+-------+----------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 0 |
| 1970-01-01 00:00:10 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 6 |
| 1970-01-01 00:00:10 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+----------------------------------+
> SELECT ts, host, min(val) RANGE '5s' FILL PREV FROM host ALIGN '5s';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL PREV |
+---------------------+-------+----------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 0 |
| 1970-01-01 00:00:10 | host1 | 0 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 6 |
| 1970-01-01 00:00:10 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+----------------------------------+sql
> SELECT ts, host, min(val) RANGE '5s' FILL LINEAR FROM host ALIGN '5s';
+---------------------+-------+------------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL LINEAR |
+---------------------+-------+------------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 2 |
| 1970-01-01 00:00:10 | host1 | 4 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 8 |
| 1970-01-01 00:00:10 | host2 | 10 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------------------------------------+
> SELECT ts, host, min(val) RANGE '5s' FILL LINEAR FROM host ALIGN '5s';
+---------------------+-------+------------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL LINEAR |
+---------------------+-------+------------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 2 |
| 1970-01-01 00:00:10 | host1 | 4 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 8 |
| 1970-01-01 00:00:10 | host2 | 10 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+------------------------------------+sql
> SELECT ts, host, min(val) RANGE '5s' FILL 6 FROM host ALIGN '5s';
+---------------------+-------+-------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL 6 |
+---------------------+-------+-------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 6 |
| 1970-01-01 00:00:10 | host1 | 6 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 6 |
| 1970-01-01 00:00:10 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+-------------------------------+
> SELECT ts, host, min(val) RANGE '5s' FILL 6 FROM host ALIGN '5s';
+---------------------+-------+-------------------------------+
| ts | host | MIN(host.val) RANGE 5s FILL 6 |
+---------------------+-------+-------------------------------+
| 1970-01-01 00:00:00 | host1 | 0 |
| 1970-01-01 00:00:05 | host1 | 6 |
| 1970-01-01 00:00:10 | host1 | 6 |
| 1970-01-01 00:00:15 | host1 | 6 |
| 1970-01-01 00:00:00 | host2 | 6 |
| 1970-01-01 00:00:05 | host2 | 6 |
| 1970-01-01 00:00:10 | host2 | 6 |
| 1970-01-01 00:00:15 | host2 | 12 |
+---------------------+-------+-------------------------------+注意，如果存在多个 Range 表达式，只对其中的一个表达式使用了 FILL 方法的话，为了保持 SQL 输出行数的统一，其他 Range 表达式会被使用 FILL NULL 方法来填充缺失的时间片段。 所以下面两句 SQL 在输出上是等价的：
sql
SELECT
ts,
host,
min(val) RANGE '10s',
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s';SELECT
ts,
host,
min(val) RANGE '10s',
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s';sql
SELECT
ts,
host,
min(val) RANGE '10s' FILL NULL,
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s';SELECT
ts,
host,
min(val) RANGE '10s' FILL NULL,
max(val) RANGE '10s' FILL LINEAR
FROM host_cpu
ALIGN '5s';TO 选项
TO 选项的值用于组确定范围查询的初始时间点。 TO 选项、RANGE 选项和 ALIGN INTERVAL 共同决定了范围查询的时间窗口。 请参考时间范围窗口。
TO 选项的默认值为当前查询客户端的时区。如果想要设置时区，请参考 MySQL 客户端 或 PostgreSQL 客户端文档中的时区设置。其他可用的 TO 选项有：
| TO | 描述 |
|---|---|
NOW | 对齐到当前查询时间 |
Timestamp | 对齐到一个用户指定的时间戳上，支持时间戳格式 RFC3339 / ISO8601 |
假设我们有一个名为 host 的表有下面这些数据：
sql
+---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 23:00:00 | host1 | 0 |
| 2023-01-02 01:00:00 | host1 | 1 |
| 2023-01-01 23:00:00 | host2 | 2 |
| 2023-01-02 01:00:00 | host2 | 3 |
+---------------------+-------+------++---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 23:00:00 | host1 | 0 |
| 2023-01-02 01:00:00 | host1 | 1 |
| 2023-01-01 23:00:00 | host2 | 2 |
| 2023-01-02 01:00:00 | host2 | 3 |
+---------------------+-------+------+对不同的 TO 选项的查询结果如下：
sql
-- 使用 mysql 协议查询数据库时区，当前处于 UTC 时区
> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| UTC |
+-------------+
-- 如果没有指定 `TO` 选项
-- 会使用当前查询指定的时区作为初始的对齐时间
> SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-01 00:00:00 | host2 | 2 |
| 2023-01-01 00:00:00 | host1 | 0 |
| 2023-01-02 00:00:00 | host2 | 3 |
| 2023-01-02 00:00:00 | host1 | 1 |
+---------------------+-------+----------------------------------+
-- 使用 mysql 协议查询数据库时区，当前处于 UTC 时区
> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| UTC |
+-------------+
-- 如果没有指定 `TO` 选项
-- 会使用当前查询指定的时区作为初始的对齐时间
> SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-01 00:00:00 | host2 | 2 |
| 2023-01-01 00:00:00 | host1 | 0 |
| 2023-01-02 00:00:00 | host2 | 3 |
| 2023-01-02 00:00:00 | host1 | 1 |
+---------------------+-------+----------------------------------+sql
-- 如果你想要将查询范围的初始时间对齐到当前时间，
-- 可以使用 `NOW` 关键字。
-- 假如当前的时间为 `2023-01-02T09:16:40.503000`。
> SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d' TO NOW;
+----------------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+----------------------------+-------+----------------------------------+
| 2023-01-01 09:16:40.503000 | host2 | 2 |
| 2023-01-01 09:16:40.503000 | host1 | 0 |
+----------------------------+-------+----------------------------------+
-- 如果你想要将查询范围的初始时间对齐到当前时间，
-- 可以使用 `NOW` 关键字。
-- 假如当前的时间为 `2023-01-02T09:16:40.503000`。
> SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d' TO NOW;
+----------------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+----------------------------+-------+----------------------------------+
| 2023-01-01 09:16:40.503000 | host2 | 2 |
| 2023-01-01 09:16:40.503000 | host1 | 0 |
+----------------------------+-------+----------------------------------+sql
-- 如果你想要将查询范围的初始时间对其到特定的时间戳，
-- 例如北京时间 2023 年 12 月 1 日，
-- 你可以将 `TO` 选项的值设定为特定的时间戳 '2023-01-01T00:00:00+08:00'。
SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d' TO '2023-01-01T00:00:00+08:00';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-01 16:00:00 | host2 | 2 |
| 2023-01-01 16:00:00 | host1 | 0 |
+---------------------+-------+----------------------------------+
-- 如果你想要将查询范围的初始时间对其到特定的时间戳，
-- 例如北京时间 2023 年 12 月 1 日，
-- 你可以将 `TO` 选项的值设定为特定的时间戳 '2023-01-01T00:00:00+08:00'。
SELECT ts, host, min(val) RANGE '1d' FROM host ALIGN '1d' TO '2023-01-01T00:00:00+08:00';
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 1d FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-01 16:00:00 | host2 | 2 |
| 2023-01-01 16:00:00 | host1 | 0 |
+---------------------+-------+----------------------------------+如果要查询特定时间范围内的数据，也可以使用 TO 关键字指定时间戳达到目的。 例如，要查询 val 在 00:45 和 06:45 之间的每日最小值， 你可以使用 2023-01-01T00:45:00 作为 TO 选项以及指定 6h 的查询范围。
sql
SELECT ts, host, min(val) RANGE '6h' FROM host ALIGN '1d' TO '2023-01-01T00:45:00';SELECT ts, host, min(val) RANGE '6h' FROM host ALIGN '1d' TO '2023-01-01T00:45:00';sql
+---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 6h FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-02 00:45:00 | host1 | 1 |
| 2023-01-02 00:45:00 | host2 | 3 |
+---------------------+-------+----------------------------------++---------------------+-------+----------------------------------+
| ts | host | MIN(host.val) RANGE 6h FILL NULL |
+---------------------+-------+----------------------------------+
| 2023-01-02 00:45:00 | host1 | 1 |
| 2023-01-02 00:45:00 | host2 | 3 |
+---------------------+-------+----------------------------------+BY 选项
BY 选项描述聚合键。如果不指定该字段，则默认使用表的主键作为聚合键。如果表没有指定主键，则不能省略 BY 关键字。
假设我们有一个名为 host 的表有以下数据：
sql
+---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 23:00:00 | host1 | 0 |
| 2023-01-02 01:00:00 | host1 | 1 |
| 2023-01-01 23:00:00 | host2 | 2 |
| 2023-01-02 01:00:00 | host2 | 3 |
+---------------------+-------+------++---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 23:00:00 | host1 | 0 |
| 2023-01-02 01:00:00 | host1 | 1 |
| 2023-01-01 23:00:00 | host2 | 2 |
| 2023-01-02 01:00:00 | host2 | 3 |
+---------------------+-------+------+下面的 SQL 使用 host 作为聚合键：
sql
SELECT
ts,
host,
min(val) RANGE '10s'
FROM host ALIGN '5s' BY (host);SELECT
ts,
host,
min(val) RANGE '10s'
FROM host ALIGN '5s' BY (host);你还可以使用 BY 关键字
你还可以使用 BY 关键字声明其他列作为数据聚合的依据。比如下面这个 RANGE 查询，使用 host 列的字符串长度 length(host) 作为数据聚合的依据。
sql
SELECT
ts,
length(host),
min(val) RANGE '10s'
FROM host ALIGN '5s' BY (length(host));SELECT
ts,
length(host),
min(val) RANGE '10s'
FROM host ALIGN '5s' BY (length(host));得到的结果如下：
sql
+---------------------+-----------------------------+-----------------------------------+
| ts | character_length(host.host) | MIN(host.val) RANGE 10s FILL NULL |
+---------------------+-----------------------------+-----------------------------------+
| 2023-01-01 22:59:55 | 5 | 0 |
| 2023-01-01 23:00:00 | 5 | 0 |
| 2023-01-02 00:59:55 | 5 | 1 |
| 2023-01-02 01:00:00 | 5 | 1 |
+---------------------+-----------------------------+-----------------------------------++---------------------+-----------------------------+-----------------------------------+
| ts | character_length(host.host) | MIN(host.val) RANGE 10s FILL NULL |
+---------------------+-----------------------------+-----------------------------------+
| 2023-01-01 22:59:55 | 5 | 0 |
| 2023-01-01 23:00:00 | 5 | 0 |
| 2023-01-02 00:59:55 | 5 | 1 |
| 2023-01-02 01:00:00 | 5 | 1 |
+---------------------+-----------------------------+-----------------------------------+你也可以显式通过 BY () 声明不需要使用聚合键，将所有数据全部聚合到一个 group 里。但如果直接将 BY 关键字省略，则代表着使用数据表的主键来作为数据的聚合键。
sql
SELECT
ts,
min(val) RANGE '10s'
FROM host ALIGN '5s' BY ();SELECT
ts,
min(val) RANGE '10s'
FROM host ALIGN '5s' BY ();得到的结果如下：
sql
+---------------------+-----------------------------------+
| ts | MIN(host.val) RANGE 10s FILL NULL |
+---------------------+-----------------------------------+
| 2023-01-01 22:59:55 | 0 |
| 2023-01-01 23:00:00 | 0 |
| 2023-01-02 00:59:55 | 1 |
| 2023-01-02 01:00:00 | 1 |
+---------------------+-----------------------------------++---------------------+-----------------------------------+
| ts | MIN(host.val) RANGE 10s FILL NULL |
+---------------------+-----------------------------------+
| 2023-01-01 22:59:55 | 0 |
| 2023-01-01 23:00:00 | 0 |
| 2023-01-02 00:59:55 | 1 |
| 2023-01-02 01:00:00 | 1 |
+---------------------+-----------------------------------+聚合函数中的 ORDER BY 选项
Range 查询支持在聚合函数 first_value 和 last_value 中使用 order by 表达式，默认情况下，聚合函数使用时间索引列升序排列数据。
以该数据表为例：
sql
+---------------------+-------+------+-------+
| ts | host | val | addon |
+---------------------+-------+------+-------+
| 1970-01-01 00:00:00 | host1 | 0 | 3 |
| 1970-01-01 00:00:01 | host1 | 1 | 2 |
| 1970-01-01 00:00:02 | host1 | 2 | 1 |
+---------------------+-------+------+-------++---------------------+-------+------+-------+
| ts | host | val | addon |
+---------------------+-------+------+-------+
| 1970-01-01 00:00:00 | host1 | 0 | 3 |
| 1970-01-01 00:00:01 | host1 | 1 | 2 |
| 1970-01-01 00:00:02 | host1 | 2 | 1 |
+---------------------+-------+------+-------+如果不指定 order by 表达式，默认使用 ts 列升序排列。
sql
SELECT ts, first_value(val) RANGE '5s', last_value(val) RANGE '5s' FROM host ALIGN '5s';
-- 等价于
SELECT ts, first_value(val order by ts ASC) RANGE '5s', last_value(val order by ts ASC) RANGE '5s' FROM host ALIGN '5s';SELECT ts, first_value(val) RANGE '5s', last_value(val) RANGE '5s' FROM host ALIGN '5s';
-- 等价于
SELECT ts, first_value(val order by ts ASC) RANGE '5s', last_value(val order by ts ASC) RANGE '5s' FROM host ALIGN '5s';查询后得到
sql
+---------------------+--------------------------------+-------------------------------+
| ts | FIRST_VALUE(host.val) RANGE 5s | LAST_VALUE(host.val) RANGE 5s |
+---------------------+--------------------------------+-------------------------------+
| 1970-01-01 00:00:00 | 0 | 2 |
+---------------------+--------------------------------+-------------------------------++---------------------+--------------------------------+-------------------------------+
| ts | FIRST_VALUE(host.val) RANGE 5s | LAST_VALUE(host.val) RANGE 5s |
+---------------------+--------------------------------+-------------------------------+
| 1970-01-01 00:00:00 | 0 | 2 |
+---------------------+--------------------------------+-------------------------------+也可以自定义排序规则，比如使用 addon 排序：
sql
SELECT ts, first_value(val ORDER BY addon ASC) RANGE '5s', last_value(val ORDER BY addon ASC) RANGE '5s' FROM host ALIGN '5s';SELECT ts, first_value(val ORDER BY addon ASC) RANGE '5s', last_value(val ORDER BY addon ASC) RANGE '5s' FROM host ALIGN '5s';查询后得到
sql
+---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+
| 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-01 00:00:00 | 2 | 0 |
+---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------++---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+
| 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-01 00:00:00 | 2 | 0 |
+---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+嵌套使用 Range 表达式
Range 表达式支持灵活的嵌套，可以将 Range 表达式结合各种运算，提供更强大的查询能力。
以下面这张表为例：
sql
+---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 08:00:00 | host1 | 1.1 |
| 2023-01-01 08:00:05 | host1 | 2.2 |
| 2023-01-01 08:00:00 | host2 | 3.3 |
| 2023-01-01 08:00:05 | host2 | 4.4 |
+---------------------+-------+------++---------------------+-------+------+
| ts | host | val |
+---------------------+-------+------+
| 2023-01-01 08:00:00 | host1 | 1.1 |
| 2023-01-01 08:00:05 | host1 | 2.2 |
| 2023-01-01 08:00:00 | host2 | 3.3 |
| 2023-01-01 08:00:05 | host2 | 4.4 |
+---------------------+-------+------+- 聚合函数内部和外部都支持计算：
sql
SELECT ts, host, 2.0 * min(val * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';SELECT ts, host, 2.0 * min(val * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';运行后得到
sql
+---------------------+-------+-----------------------------------------------------------------+
| ts | host | Float64(2) * MIN(host_cpu.val * Float64(2)) RANGE 10s FILL NULL |
+---------------------+-------+-----------------------------------------------------------------+
| 2023-01-01 07:59:55 | host1 | 4.4 |
| 2023-01-01 07:59:55 | host2 | 13.2 |
| 2023-01-01 08:00:00 | host1 | 4.4 |
| 2023-01-01 08:00:00 | host2 | 13.2 |
| 2023-01-01 08:00:05 | host1 | 8.8 |
| 2023-01-01 08:00:05 | host2 | 17.6 |
+---------------------+-------+-----------------------------------------------------------------++---------------------+-------+-----------------------------------------------------------------+
| ts | host | Float64(2) * MIN(host_cpu.val * Float64(2)) RANGE 10s FILL NULL |
+---------------------+-------+-----------------------------------------------------------------+
| 2023-01-01 07:59:55 | host1 | 4.4 |
| 2023-01-01 07:59:55 | host2 | 13.2 |
| 2023-01-01 08:00:00 | host1 | 4.4 |
| 2023-01-01 08:00:00 | host2 | 13.2 |
| 2023-01-01 08:00:05 | host1 | 8.8 |
| 2023-01-01 08:00:05 | host2 | 17.6 |
+---------------------+-------+-----------------------------------------------------------------+- 聚合函数内部和外部都支持使用 Scalar 函数：
min(round(val)) RANGE '10s'表示对每个值先使用round函数四舍五入后再进行聚合round(min(val) RANGE '10s')表示对每个聚合完成的结果使用round函数四舍五入
sql
SELECT ts, host, min(round(val)) RANGE '10s' FROM host_cpu ALIGN '5s';SELECT ts, host, min(round(val)) RANGE '10s' FROM host_cpu ALIGN '5s';运行后得到
sql
+---------------------+-------+----------------------------------------------+
| ts | host | MIN(round(host_cpu.val)) RANGE 10s FILL NULL |
+---------------------+-------+----------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 3 |
| 2023-01-01 07:59:55 | host1 | 1 |
| 2023-01-01 08:00:00 | host2 | 3 |
| 2023-01-01 08:00:00 | host1 | 1 |
| 2023-01-01 08:00:05 | host2 | 4 |
| 2023-01-01 08:00:05 | host1 | 2 |
+---------------------+-------+----------------------------------------------++---------------------+-------+----------------------------------------------+
| ts | host | MIN(round(host_cpu.val)) RANGE 10s FILL NULL |
+---------------------+-------+----------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 3 |
| 2023-01-01 07:59:55 | host1 | 1 |
| 2023-01-01 08:00:00 | host2 | 3 |
| 2023-01-01 08:00:00 | host1 | 1 |
| 2023-01-01 08:00:05 | host2 | 4 |
| 2023-01-01 08:00:05 | host1 | 2 |
+---------------------+-------+----------------------------------------------+sql
SELECT ts, host, round(min(val) RANGE '10s') FROM host_cpu ALIGN '5s';SELECT ts, host, round(min(val) RANGE '10s') FROM host_cpu ALIGN '5s';运行后得到
sql
+---------------------+-------+----------------------------------------------+
| ts | host | round(MIN(host_cpu.val) RANGE 10s FILL NULL) |
+---------------------+-------+----------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 3 |
| 2023-01-01 07:59:55 | host1 | 1 |
| 2023-01-01 08:00:00 | host2 | 3 |
| 2023-01-01 08:00:00 | host1 | 1 |
| 2023-01-01 08:00:05 | host2 | 4 |
| 2023-01-01 08:00:05 | host1 | 2 |
+---------------------+-------+----------------------------------------------++---------------------+-------+----------------------------------------------+
| ts | host | round(MIN(host_cpu.val) RANGE 10s FILL NULL) |
+---------------------+-------+----------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 3 |
| 2023-01-01 07:59:55 | host1 | 1 |
| 2023-01-01 08:00:00 | host2 | 3 |
| 2023-01-01 08:00:00 | host1 | 1 |
| 2023-01-01 08:00:05 | host2 | 4 |
| 2023-01-01 08:00:05 | host1 | 2 |
+---------------------+-------+----------------------------------------------+- 多个 Range 表达式也可以相互计算，并且 Range 表达式支持分配律，下面两个表达式都是合法且等价的：
sql
SELECT ts, host, max(val) RANGE '10s' - min(val) RANGE '10s' FROM host_cpu ALIGN '5s';
SELECT ts, host, (max(val) - min(val)) RANGE '10s' FROM host_cpu ALIGN '5s';SELECT ts, host, max(val) RANGE '10s' - min(val) RANGE '10s' FROM host_cpu ALIGN '5s';
SELECT ts, host, (max(val) - min(val)) RANGE '10s' FROM host_cpu ALIGN '5s';运行后得到
sql
+---------------------+-------+-------------------------------------------------------------------------------+
| ts | host | MAX(host_cpu.val) RANGE 10s FILL NULL - MIN(host_cpu.val) RANGE 10s FILL NULL |
+---------------------+-------+-------------------------------------------------------------------------------+
| 2023-01-01 08:00:05 | host1 | 0 |
| 2023-01-01 08:00:05 | host2 | 0 |
| 2023-01-01 08:00:00 | host1 | 1.1 |
| 2023-01-01 08:00:00 | host2 | 1.1 |
| 2023-01-01 07:59:55 | host1 | 0 |
| 2023-01-01 07:59:55 | host2 | 0 |
+---------------------+-------+-------------------------------------------------------------------------------++---------------------+-------+-------------------------------------------------------------------------------+
| ts | host | MAX(host_cpu.val) RANGE 10s FILL NULL - MIN(host_cpu.val) RANGE 10s FILL NULL |
+---------------------+-------+-------------------------------------------------------------------------------+
| 2023-01-01 08:00:05 | host1 | 0 |
| 2023-01-01 08:00:05 | host2 | 0 |
| 2023-01-01 08:00:00 | host1 | 1.1 |
| 2023-01-01 08:00:00 | host2 | 1.1 |
| 2023-01-01 07:59:55 | host1 | 0 |
| 2023-01-01 07:59:55 | host2 | 0 |
+---------------------+-------+-------------------------------------------------------------------------------+但注意，Range 表达式修饰的范围是位于 RANGE 关键字的前一个表达式，下面的 Range 查询是不合法的，因为 RANGE 关键字修饰的是表达式 2.0，并不是表达式 min(val * 2.0) * 2.0
sql
SELECT ts, host, min(val * 2.0) * 2.0 RANGE '10s' FROM host_cpu ALIGN '5s';
ERROR 1815 (HY000): sql parser error: Can't use the RANGE keyword in Expr 2.0 without functionSELECT ts, host, min(val * 2.0) * 2.0 RANGE '10s' FROM host_cpu ALIGN '5s';
ERROR 1815 (HY000): sql parser error: Can't use the RANGE keyword in Expr 2.0 without function可以为表达式加上括号，RANGE 关键字会自动应用到括号中包含的所有聚合函数：
sql
SELECT ts, host, (min(val * 2.0) * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';SELECT ts, host, (min(val * 2.0) * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';运行后得到：
sql
+---------------------+-------+-----------------------------------------------------------------+
| ts | host | MIN(host_cpu.val * Float64(2)) RANGE 10s FILL NULL * Float64(2) |
+---------------------+-------+-----------------------------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 13.2 |
| 2023-01-01 07:59:55 | host1 | 4.4 |
| 2023-01-01 08:00:00 | host2 | 13.2 |
| 2023-01-01 08:00:00 | host1 | 4.4 |
| 2023-01-01 08:00:05 | host2 | 17.6 |
| 2023-01-01 08:00:05 | host1 | 8.8 |
+---------------------+-------+-----------------------------------------------------------------++---------------------+-------+-----------------------------------------------------------------+
| ts | host | MIN(host_cpu.val * Float64(2)) RANGE 10s FILL NULL * Float64(2) |
+---------------------+-------+-----------------------------------------------------------------+
| 2023-01-01 07:59:55 | host2 | 13.2 |
| 2023-01-01 07:59:55 | host1 | 4.4 |
| 2023-01-01 08:00:00 | host2 | 13.2 |
| 2023-01-01 08:00:00 | host1 | 4.4 |
| 2023-01-01 08:00:05 | host2 | 17.6 |
| 2023-01-01 08:00:05 | host1 | 8.8 |
+---------------------+-------+-----------------------------------------------------------------+Range 表达式不允许嵌套，嵌套的 Range 查询是不合法的：
sql
SELECT ts, host, max(min(val) RANGE '10s') RANGE '10s' FROM host_cpu ALIGN '5s';
ERROR 1815 (HY000): Range Query: Nest Range Query is not allowedSELECT ts, host, max(min(val) RANGE '10s') RANGE '10s' FROM host_cpu ALIGN '5s';
ERROR 1815 (HY000): Range Query: Nest Range Query is not allowed