RANGE QUERY

RANGE QUERY

查询并聚合一个给定长度的时间范围的数据是时序数据常见的一种查询模式，例如 PromQL 中的 Range selector。而 GreptimeDB 在 SQL 中支持了 Range 查询，用于将时序数据汇总为时间块，并在时间块上进行数据的聚合。Range 查询作为 SELECT 语句的一部分，可与 SQL 灵活结合，从而在 SQL 中提供更强大的时序数据查询能力。

Syntax

Range query 使用 Time Index 列作为聚合的时间线。一个合法的 Range 查询语法结构如下所示：

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 查询的步长。
- 子关键字 TO ，可选字段，指定 Range 查询对齐到的时间点，合法的 TO_OPTION 参数见TO Option 。
- 子关键字 BY ，可选字段，后接参数 (columna, columnb,..) ，描述了聚合键。详情请见BY OPTION。
参数 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，可选字段，详情请见 FILL Option。
- 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。

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 表达式的变量类型一致

以下面这张表为例

+---------------------+-------+------+
| 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 选项的结果如下：

NO FILL
FILL NULL
FILL PREV
FILL LINEAR
FILL Constant Value 6.0


> 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' 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 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 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 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 在输出上是等价的：

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' 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 的表有下面这些数据：

+---------------------+-------+------+
| 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 选项的查询结果如下：

Default to timezone
NOW
Specific Timestamp


-- 使用 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 |
+---------------------+-------+----------------------------------+


-- 如果你想要将查询范围的初始时间对齐到当前时间，
-- 可以使用 `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 |
+----------------------------+-------+----------------------------------+


-- 如果你想要将查询范围的初始时间对其到特定的时间戳，
-- 例如北京时间 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 的查询范围。

SELECT ts, host, min(val) RANGE '6h' FROM host ALIGN '1d' TO '2023-01-01T00:45:00';

+---------------------+-------+----------------------------------+
| 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 的表有以下数据：

+---------------------+-------+------+
| 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 作为聚合键：

SELECT 
    ts, 
    host, 
    min(val) RANGE '10s' 
FROM host ALIGN '5s' BY (host);

你还可以使用 BY 关键字

你还可以使用 BY 关键字声明其他列作为数据聚合的依据。比如下面这个 RANGE 查询，使用 host 列的字符串长度 length(host) 作为数据聚合的依据。

SELECT 
    ts, 
    length(host), 
    min(val) RANGE '10s' 
FROM host ALIGN '5s' BY (length(host));

得到的结果如下：

+---------------------+-----------------------------+-----------------------------------+
| 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 关键字省略，则代表着使用数据表的主键来作为数据的聚合键。

SELECT
     ts,
     min(val) RANGE '10s'
FROM host ALIGN '5s' BY ();

得到的结果如下：

+---------------------+-----------------------------------+
| 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 表达式，默认情况下，聚合函数使用时间索引列升序排列数据。

以该数据表为例：

+---------------------+-------+------+-------+
| 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 列升序排列。

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';

查询后得到

+---------------------+--------------------------------+-------------------------------+
| ts                  | FIRST_VALUE(host.val) RANGE 5s | LAST_VALUE(host.val) RANGE 5s |
+---------------------+--------------------------------+-------------------------------+
| 1970-01-01 00:00:00 |                              0 |                             2 |
+---------------------+--------------------------------+-------------------------------+

也可以自定义排序规则，比如使用 addon 排序：

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-01 00:00:00 |                                                                   2 |                                                                  0 |
+---------------------+---------------------------------------------------------------------+--------------------------------------------------------------------+

嵌套使用 Range 表达式

Range 表达式支持灵活的嵌套，可以将 Range 表达式结合各种运算，提供更强大的查询能力。

以下面这张表为例：

+---------------------+-------+------+
| 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 |
+---------------------+-------+------+

聚合函数内部和外部都支持计算：

SELECT ts, host, 2.0 * min(val * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';

运行后得到

+---------------------+-------+-----------------------------------------------------------------+
| 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 函数四舍五入

SELECT ts, host, min(round(val)) RANGE '10s' FROM host_cpu ALIGN '5s';

运行后得到

+---------------------+-------+----------------------------------------------+
| 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 |
+---------------------+-------+----------------------------------------------+

SELECT ts, host, round(min(val) RANGE '10s') FROM host_cpu ALIGN '5s';

运行后得到

+---------------------+-------+----------------------------------------------+
| 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 表达式支持分配律，下面两个表达式都是合法且等价的：

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';

运行后得到

+---------------------+-------+-------------------------------------------------------------------------------+
| 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

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 function

可以为表达式加上括号，RANGE 关键字会自动应用到括号中包含的所有聚合函数：

SELECT ts, host, (min(val * 2.0) * 2.0) RANGE '10s' FROM host_cpu ALIGN '5s';

运行后得到：

+---------------------+-------+-----------------------------------------------------------------+
| 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 查询是不合法的：

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 allowed

RANGE QUERY

RANGE QUERY

Syntax

FILL 选项

TO 选项

BY 选项

聚合函数中的 ORDER BY 选项

嵌套使用 Range 表达式

`FILL` 选项

`TO` 选项

`BY` 选项

聚合函数中的 `ORDER BY` 选项