1.3.6.1. TSDB SQL规范

TSDB 的 SQL 规范结合了 MYSQL 和INFLUXDB3, 尽量使用 MYSQL 的语法。下面对其中一些重要或特殊规范的进行介绍：

1.3.6.1.1. 大小写敏感注意点

描述

由于不同库大小写敏感不同（clickhouse 列名表名大小写敏感），对SQL有如下要求：

列名表名在不同SQL中大小写要保持一致，建议都小写

1.3.6.1.2. 删数据 DELETE

描述

DELETE 语句

语法

支持的格式如下：

DELETE TABLE table_name [WHERE expr]

警告

目前只支持clickhouse
具体转换到各库后的差异限制见文档

1.3.6.1.3. 删表 DROP

描述

DROP 语句

DROP TABLE table_name

警告

目前只支持clickhouse
具体转换到各库后的差异限制见文档

1.3.6.1.4. INSERT

描述

INSERT 查询语句, 时间字段名必须必须参考 INSERT SQL 规则

警告

时间字段名必须为time
clickhouse 时间字段精度需要为毫秒级dateTime64(3)

1.3.6.1.5. SELECT

描述

SELECT 查询语句

语法

支持的格式如下：

SELECT
column1 [AS alias1] ...
expr(columns) [AS alias3]  -- 表达式计算
FROM table_name
[WHERE condition]  -- 行过滤
[GROUP BY item1, ... ]  -- 分组
[ORDER BY item1 [ASC|DESC], ...]  -- 排序
[LIMIT [offset,] count]  -- 限制结果行数

警告

过滤的时间字段名必须为time
字段别名和字段名不能相同 - clickhouse 中where 可以识别字段别名
不能只查询tag
order by 的item 只能是time 或time 别名，或者按时间间隔分组字段
为了兼容influxdb,输入的时区为UTC,输出的时区通过时区转换来设置
对于clickhouse，输出的时区默认为东8区，可以不用时区转换
具体转换到各库后的差异限制见文档

1.3.6.1.6. 按时间间隔分组

描述

MYSQL 原生没有对按时间间隔分组的支持，使用 INFLUXBD3 的 date_bin 和 date_bin_gapfill

语法

支持的格式如下：

-- 1. 格式1, date_bin_gapfill/date_bin为第一个查询列，group by 查询列别名（不能为time）
SELECT date_bin_gapfill/date_bin(intervalExpr, time) as alias_name,
expr...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

-- 2. 格式2，date_bin_gapfill/date_bin为第一个查询列，group by 查询列id
SELECT date_bin_gapfill/date_bin(intervalExpr, time) as alias_name,
expr...
FROM table [WHERE condition]
GROUP BY 1, expr...
[ ORDER BY ... ]

-- 3. 格式3，date_bin_gapfill为第一个查询列, 使用填充函数
SELECT date_bin_gapfill(intervalExpr, time) as alias_name,
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

fill_func: locf|interpolate|coalesce

-- 4. 格式4，时区转换
SELECT date_bin_gapfill(INTERVAL 1 day, convert_tz(time, 'UTC', timezone), origin_timestamp) as alias_name,
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

-- timezone: 比如：'Asia/Shanghai'
-- origin_timestamp: 默认为Unix epoch即'1970-01-01T00:00:00+00:00'，表示时间间隔初始边界。
-- 'Asia/Shanghai' 对应'1970-01-01T00:00:00+08:00'。

警告

格式1,2 不能直接对查询列date_bin_gapfill返回的时间进行操作(date_bin 可以)，需要通过子查询实现
格式1,2 不同的时序库时间字段的格式受客户端影响不同，但对于java api 可以相同
具体转换到各库后的差异限制见文档

示例

--- 格式1：
select DATE_BIN(INTERVAL 1 day, time) as _time,
    avg(my_field) from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'and time >= '1970-01-01T00:00:00Z'
group by _time
order by _time

--- 格式2：
select DATE_BIN(INTERVAL 1 day, time) as _time,
    avg(my_field)
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
group by 1
order by 1

--- 格式3：
select date_bin_gapfill(INTERVAL 1 day, time) as _time,
    coalesce(avg(my_field),0),
    coalesce(count(my_field),0)+0.1
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
group by _time
order by _time

--- 格式4：
select date_bin_gapfill(INTERVAL 1 day, convert_tz(time, 'UTC', 'Asia/Shanghai'), '1970-01-01T00:00:00+08:00') as _time,
    coalesce(avg(my_field),0),
    coalesce(count(my_field),0)+0.1
from base_test
where my_tag='val6'
and time <= '1971-01-10T00:00:00Z'
and time >= '1971-01-01T00:00:00Z'
group by _time
order by _time

1.3.6.1.7. 时区转换

描述

转换时区函数

语法支持的时区转换格式如下：

-- 默认为UTC时间，因此源为UTC
convert_tz(expr, 'UTC', 'timezone') -- mysql 语法
convert_tz(expr, '+00:00', 'timezone') -- mysql 语法

tz(expr, 'timezone') -- INFLXUDB3 SQL 语法

警告

具体转换到各库后的差异限制见文档

示例

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <= '1970-01-03T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

1.3.6.1.8. interval 表达式

描述

时间间隔表达式

语法

interval 'num time_unit [num time_unit]...' -- influxdb3 SQL 语法
interval num time_unit -- (同influxdb3 SQL, 有扩展)

time_unit:
    nanosecond      -- mysql 语法不支持，为扩充
    |microsecond    -- mysql 语法不支持，为扩充
    |millisecond
    |second
    |minute
    |hour
    |day
    |week
    |nanoseconds    -- mysql 语法不支持，为扩充
    |microseconds   -- mysql 语法不支持，为扩充
    |milliseconds
    |seconds
    |minutes
    |hours
    |days
    |weeks
    | SECOND_MICROSECOND
    | MINUTE_MICROSECOND
    | MINUTE_SECOND
    | HOUR_MICROSECOND
    | HOUR_SECOND
    | HOUR_MINUTE
    | DAY_MICROSECOND
    | DAY_SECOND
    | DAY_MINUTE
    | DAY_HOUR

警告

具体转换到各库后的差异限制见文档

示例

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <=  now() - interval '1 weeks 1 days 9 hours 1 minutes 1 seconds'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(time, 'utc', 'Asia/Shanghai'),
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <=  now() - interval 1 days
and time >= '1970-01-01T00:00:00Z'
order by time

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <= now() - interval '1 3:20:30' day_second
and time >= '1970-01-01T00:00:00Z'
order by _time

1.3.6.1.9. 正则比较过滤

描述

正则比较过滤，用于where 中

语法

支持的正则比较 SQL格式如下：

regexp_like(expr, pattern) = true/false
regexp_like(expr, pattern)

示例

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\\Kitchen') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\(Room\)') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

-- 带斜杠
select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '/Kitchen') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

1.3.6.1.10. 转义字符 `\`

描述

TSDB 字符串中反斜杠不为转义字符

1.3.6.1.11. select_first, select_last 聚合函数

描述

select_first, select_last 同influxql 的 first, last。用于取时间范围内的第一个和最后一个字段值

语法

支持的格式如下(同influxdb3)：

-- INFLUXBD3 SQL
select_first(expr, time)['time']
select_first(expr, time)['value']
select_last(expr, time)['time']
select_last(expr, time)['value']

示例

select tz(selector_first(my_field, time)['time'], 'Asia/Shanghai') as _time,
selector_first(my_field, time)['value']
from base_test
where my_tag='val7'
and time <=  '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by time

select tz(selector_last(my_field, time)['time'], 'Asia/Shanghai') as _time,
    selector_last(my_field, time)['value']
from base_test
where my_tag='val7'
and time <=  '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by time

1.3.6.1. TSDB SQL规范

1.3.6.1.1. 大小写敏感注意点

1.3.6.1.2. 删数据 DELETE

1.3.6.1.3. 删表 DROP

1.3.6.1.4. INSERT

1.3.6.1.5. SELECT

1.3.6.1.6. 按时间间隔分组

1.3.6.1.7. 时区转换

1.3.6.1.8. interval 表达式

1.3.6.1.9. 正则比较过滤

1.3.6.1.10. 转义字符 \

1.3.6.1.11. select_first, select_last 聚合函数

1.3.6.1.10. 转义字符 `\`