SQL 也能搞复杂时序查询？- 使用 SQL 在 GreptimeDB 上做 Range 查询

查询并聚合一个「给定长度的时间范围的数据」，是时序数据中常见的一种查询模式。例如 PromQL 中的 Range selector，就原生地支持了这种时序查询。但对于通用的数据库查询语言 SQL ，这类时序查询很难通过原生的 SQL 完成，所以我们在 GreptimeDB 中引入了扩展的 SQL Range 查询语法，将时序查询能力与 SQL 高度灵活的表达能力相结合，实现了 SQL 对时序数据查询的原生支持。

✨ 让数据动起来！

使用 SQL 在 GreptimeDB 上实现动态 Range 查询：https://greptime.cn/playground

🔧 点击链接加入我们的 Playground，立即体验👆

示例

我们用一个例子来介绍 Range 查询。下面这张表 temperature 记录了不同城市在不同时间的气温：

我们想查询北京从 2023 年 5 月 2 日（时间戳为 1682985600000 ）以前的：

每日的日平均气温；
每日的周平均气温；
如果某天的数据点缺失，则以前后两天平均气温均值作为这天的平均气温。

我们首先从 PromQL 的视角来看如何去写这句查询，对于两条查询，都需要以天为步长进行查询。对于日平均气温，需要向前聚合一天的数据，对于周平均气温，需要每次向前聚合一周的数据，并求出平均值。另外我们需要使用 @ 将查询时间对齐到时间戳 1682985600000。

最后的查询为：

sql

-- 日平均气温
avg_over_time(temperature{city="beijing"}[1d] @ 1682985600000) step=1d

-- 周平均气温
avg_over_time(temperature{city="beijing"}[7d] @ 1682985600000) step=1d

但上面的查询存在一些问题。PromQL 强调的是做数据的查询，但不能很好地处理查询中缺失数据点的情况，也就是如何对查询的数据作平滑。PromQL 只是存在 Lookback delta 的机制（具体见：https://promlabs.com/blog/2020/07/02/selecting-data-in-promql/#lookback-delta），用一些旧的数据来代替缺失的数据点。这样的默认行为在某些情况下并不是用户想要的。因为 Lookback delta 的机制的存在，会导致聚合的数据携带一些旧值。在默认情况下，PromQL 很难做到数据精确性的控制。并且针对我们上面提出的数据平滑要求，PromQL 也没有很好的办法完成。

如果从传统的 SQL 出发，因为 SQL 中不存在 Lookback delta 的机制，我们可以精确地控制我们数据选择和聚合的范围。所见即所得，进行比较精确的查询。

上述查询本质上是以天为单位，对每天和每周的数据做聚合。针对日平均气温，我们可以利用 date_trunc 这一 scalar 函数，该函数能够将时间截断到某一个精度上，我们使用这个函数将时间截断到天这一单位，最后再以天为单位对数据进行聚合，就能得到我们想要的结果：

sql

-- 日平均气温
SELECT
    day,
    avg(temp),
FROM (
    SELECT
        date_trunc('day', ts) as day
        temp,
    FROM
        temperature
    WHERE
        city="beijing" and ts < 1682985600000
)
GROUP BY day;

这样也基本满足了我们的需求，但是这种查询问题在于：

写起来非常繁琐，需要写子查询；
应用范围有限，用这种办法只能求出日平均气温，没法求出周平均气温。因为 SQL 中的聚合要求每一条数据只能属于一个 group。但是在时序查询中，如果每次采样的时间是一星期，步长是一天的话，一条数据必然会被多个 group 使用，针对这种查询传统 SQL 无能为力；
还是没办法解决空白数据填充的问题。

在上面讨论之后，我们需要思考：这种查询本质上是时序查询，但是我们所使用的 SQL——虽然有非常灵活的表达能力，但却不是专门为时序数据库设计的。我们需要一些新的 SQL 扩展语法，在 SQL 中来简单的描述这种时序查询。一些时序数据库如 InfluxDB 提供了 group by time 语法， QuestDB 提供了 Sample By 语法，这些实现为我们的 Range 查询提供了思路。下面介绍如何使用 GreptimeDB 提供的 Range 语法来进行上述查询。

sql

-- 日平均气温
SELECT
    ts,
    avg(temp) RANGE '1d' FILL LINEAR,
FROM
    temperature
WHERE
    city="beijing" and ts < 1682985600000
ALIGN '1d';

-- 周平均气温
SELECT
    ts,
    avg(temp) RANGE '7d' FILL LINEAR,
FROM
    temperature
WHERE
    city="beijing" and ts < 1682985600000
ALIGN '1d';

我们为一个 SELECT 语句引入了一个关键字 ALIGN，代表每次时序查询的步长，ALIGN 会将时间对齐到日历上。在聚合函数后引入了一个 RANGE 关键词，代表在每次数据聚合的范围，FILL LINEAR 表示数据点为空的时候的数据填充方式，即以数据平均值填充。通过这样的方式，我们比较轻松的完成了上文所提出的几个要求。

通过 Range 查询，我们可以很优雅的在 SQL 中表达时序查询。弥补了 SQL 在时序查询上描述能力不足的问题，并且可以结合 SQL 强大的表达能力，实现更加复杂的数据查询功能。

Range 查询还有更为灵活的使用方式，具体使用参见文档：
https://docs.greptime.com/reference/sql/range

实现逻辑

基于 Range 的时序数据查询本质上是对数据的聚合，不同于 SQL 中的 GROUP BY，这类聚合依照时间线有着独特的聚合方式，所以我们仍能够通过基本的聚合算法实现 Range 查询。

GreptimeDB 使用 DataFusion 作为 SQL Planner。我们需要将 Range 查询的逻辑抽象成一个查询计划，与 SQL 查询的其他部分一起，最后生成完整的 SQL 查询计划并执行查询。我们并不想直接修改 DataFusion 的代码。因为如果单独 fork 并维护一份 DataFusion 的代码，由于 DataFusion 快速变化的代码库，会花很多时间在上游代码的同步上。所以我们选择了另外一种方式。即先让 DataFusion 自己生成最初的查询计划，我们再修改 DataFusion 生成的查询计划，插入我们自己的 Range 查询计划，并生成最后的查询计划。

整个 Range 实现的过程采用经典的 hash 聚合算法，为每个采样的时间窗口留一个 hash 桶，将所有待采样的数据放到对应的 hash 桶中。与传统聚合算法不一样的是，时序数据的聚合可能存在数据的交集，比如查询每日的周平均气温。对应到算法上，也就是一条数据很可能属于多个 hash 桶。这是与传统 hash 聚合算法不一样的地方。

总结

通过 GreptimeDB 在 SQL 中提供的 RANGE 查询语法扩展，并结合 SQL 语言本身强大的表达能力。我们可以在 GreptimeDB 中对时序数据进行更加简洁、优雅、高效的分析查询，并避免了一些 PromQL在数据查询中不足。用户可以灵活的使用 RANGE 查询，在 GreptimeDB 中解锁更多时序数据分析查询的新方式。