挖掘机驾驶宝典

Appearance

InfluxDB 3 SQL 语法 ​

InfluxDB 3 使用基于 Apache Arrow DataFusion 的 SQL 查询时序数据。它支持常见的过滤、聚合、子查询、CTE、JOIN 和 Window Function，并增加了时间窗口、Selector 和缺失值填充等时序能力。

这里的 SQL 指 InfluxDB 3 SQL。InfluxDB 1.x 主要使用 InfluxQL，InfluxDB 2.x 常见 Flux，三者不能混用。InfluxDB 3 SQL 也不是完整的 MySQL/PostgreSQL 方言：通常用 SQL 查询数据，用 Line Protocol 写入数据，不应默认支持关系数据库中的 INSERT、UPDATE、DELETE、事务和任意 DDL。

贯穿示例的数据模型 ​

以下示例使用 sensor_data 表保存设备采样：

列角色类型示例含义
timeTimestampTimestamp2026-08-20T08:00:00Z采样时间
siteTagStringhefei站点
device_idTagStringsensor01设备标识
temperatureFieldFloat6426.3温度
humidityFieldFloat6461.5湿度百分比
statusFieldStringnormal设备状态
onlineFieldBooleantrue是否在线

使用 Line Protocol 写入一组可供后续 SQL 查询的数据：

text
sensor_data,site=hefei,device_id=sensor01 temperature=26.3,humidity=61.5,status="normal",online=true 1787212800
sensor_data,site=hefei,device_id=sensor02 temperature=27.1,humidity=58.2,status="normal",online=true 1787212800
sensor_data,site=hefei,device_id=sensor01 temperature=27.0,humidity=60.8,status="normal",online=true 1787213100
sensor_data,site=hefei,device_id=sensor02 temperature=31.6,humidity=57.4,status="warning",online=true 1787213100
sensor_data,site=shanghai,device_id=sensor03 temperature=25.2,humidity=65.1,status="normal",online=true 1787213100
sensor_data,site=hefei,device_id=sensor01 temperature=27.5,humidity=60.1,status="normal",online=true 1787213400
sensor_data,site=hefei,device_id=sensor02 temperature=32.4,humidity=56.9,status="warning",online=false 1787213400

时间戳使用秒精度，写入时应显式指定精度：

bash
influxdb3 write \
  --database iot \
  --token "$INFLUXDB3_AUTH_TOKEN" \
  --precision s \
  --file sensor-data.lp

同一列的 Field 类型应保持稳定。例如第一次写入的 temperature=26.3 是 Float，后续不要改写成字符串 temperature="27.0"。

查看表和字段 ​

先查看当前 Database 中有哪些表：

sql
SHOW TABLES;

查看表的列名、数据类型和 Tag/Field 信息：

sql
SHOW COLUMNS IN sensor_data;

InfluxDB 3 通常在首次写入时根据 Line Protocol 自动创建表和 Schema，因此排查查询错误时，应先确认实际表名、列名和类型。

基础查询 ​

查询最近一小时的数据：

sql
SELECT
    time,
    site,
    device_id,
    temperature,
    humidity,
    status,
    online
FROM sensor_data
WHERE time >= now() - INTERVAL '1 hour'
ORDER BY time DESC
LIMIT 100;

基本执行顺序可以按以下思路理解：

text
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

SELECT * 适合临时查看数据，Dashboard 和应用查询更适合显式列出字段，减少无用数据传输并避免 Schema 扩展影响结果。

别名与计算列 ​

sql
SELECT
    time AS collected_at,
    device_id,
    temperature AS temperature_c,
    temperature * 9.0 / 5.0 + 32.0 AS temperature_f
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day';

字符串使用单引号。表名或列名若包含特殊字符、大小写或 SQL 保留字，需要使用双引号；Schema 设计时更推荐小写下划线命名，避免频繁引用。

sql
SELECT "device-id", "temperature"
FROM "sensor-data";

条件过滤 ​

比较与逻辑条件 ​

sql
SELECT time, device_id, temperature, status
FROM sensor_data
WHERE time >= now() - INTERVAL '24 hours'
  AND site = 'hefei'
  AND temperature >= 30.0
  AND online = true;

常用条件：

需求写法
相等、不等=, <>, !=
大小比较>, >=, <, <=
多条件AND, OR, NOT
范围BETWEEN ... AND ...
集合IN (...), NOT IN (...)
模式匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
sql
SELECT time, device_id, temperature
FROM sensor_data
WHERE time BETWEEN
        '2026-08-20T08:00:00Z'::TIMESTAMP
        AND '2026-08-20T09:00:00Z'::TIMESTAMP
  AND site IN ('hefei', 'shanghai')
  AND device_id LIKE 'sensor%';

BETWEEN 两端都包含。连续翻页或相邻时间段查询时，通常使用左闭右开的时间范围，避免边界点重复：

sql
WHERE time >= '2026-08-20T08:00:00Z'::TIMESTAMP
  AND time <  '2026-08-20T09:00:00Z'::TIMESTAMP

NULL 判断 ​

不同 Point 可以缺少某个 Field，查询结果中会表现为 NULL。NULL 不能使用 = NULL 判断：

sql
SELECT time, device_id, humidity
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
  AND humidity IS NOT NULL;

使用 COALESCE 为显示结果提供默认值：

sql
SELECT
    time,
    device_id,
    COALESCE(status, 'unknown') AS status
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day';

默认值只改变查询结果，不会回写原始 Point。

排序、去重与分页 ​

sql
SELECT time, device_id, temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
ORDER BY time DESC, device_id ASC
LIMIT 20 OFFSET 40;

没有 ORDER BY 时，结果顺序不保证稳定。大 OFFSET 会扫描并丢弃前面的结果；持续翻页更适合记录上一页最后一个时间点，再查询更早数据：

sql
SELECT time, device_id, temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '7 days'
  AND time < '2026-08-20T08:10:00Z'::TIMESTAMP
ORDER BY time DESC
LIMIT 20;

查询出现过的站点：

sql
SELECT DISTINCT site
FROM sensor_data
WHERE time >= now() - INTERVAL '30 days'
ORDER BY site;

时序数据量通常很大，即使只查询 Tag 的不同值，也应尽量指定合理的时间范围。

聚合与分组 ​

按站点汇总最近一天的指标：

sql
SELECT
    site,
    count(*) AS point_count,
    avg(temperature) AS avg_temperature,
    min(temperature) AS min_temperature,
    max(temperature) AS max_temperature,
    sum(humidity) AS humidity_sum
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
GROUP BY site
ORDER BY site;

常用聚合函数包括 count、avg、sum、min 和 max。count(*) 统计行数，count(humidity) 只统计 humidity 非空的行。

使用 HAVING 过滤分组后的结果：

sql
SELECT
    device_id,
    avg(temperature) AS avg_temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
GROUP BY device_id
HAVING avg(temperature) >= 30.0
ORDER BY avg_temperature DESC;

WHERE 在聚合前过滤原始 Point，HAVING 在聚合后过滤分组，二者作用阶段不同。

条件聚合 ​

sql
SELECT
    site,
    count(*) AS total_count,
    sum(CASE WHEN status = 'warning' THEN 1 ELSE 0 END) AS warning_count,
    sum(CASE WHEN online = false THEN 1 ELSE 0 END) AS offline_count
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
GROUP BY site;

CASE 也可用于生成状态标签：

sql
SELECT
    time,
    device_id,
    temperature,
    CASE
        WHEN temperature >= 35 THEN 'critical'
        WHEN temperature >= 30 THEN 'warning'
        ELSE 'normal'
    END AS temperature_level
FROM sensor_data
WHERE time >= now() - INTERVAL '1 hour';

时间窗口聚合 ​

时序查询通常不直接返回数百万个原始 Point，而是按时间窗口降采样。date_bin 将时间对齐到固定窗口：

sql
SELECT
    date_bin(INTERVAL '5 minutes', time) AS window_start,
    device_id,
    avg(temperature) AS avg_temperature,
    max(temperature) AS max_temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '24 hours'
  AND site = 'hefei'
GROUP BY 1, device_id
ORDER BY 1, device_id;

GROUP BY 1 表示按 SELECT 列表中的第一项分组，即 window_start。显式写出表达式更直观，但会重复较长的 date_bin(...)。

可以提供第三个参数控制窗口对齐原点：

sql
SELECT
    date_bin(
        INTERVAL '1 hour',
        time,
        '2026-01-01T00:00:00Z'::TIMESTAMP
    ) AS window_start,
    avg(temperature) AS avg_temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
GROUP BY 1
ORDER BY 1;

窗口结果的时间通常表示窗口起点，不代表窗口内某个原始 Point 的实际采样时间。

查询每台设备的最新值 ​

只使用全表 ORDER BY time DESC LIMIT 1 只能得到整个结果集的一条数据，不能得到每台设备各自的最新数据。可使用时序 Selector：

sql
SELECT
    device_id,
    selector_last(temperature, time)['time'] AS last_time,
    selector_last(temperature, time)['value'] AS last_temperature
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
GROUP BY device_id
ORDER BY device_id;

Selector 返回包含 time 和 value 的结构体，因此使用 ['time']、['value'] 取出属性。常用函数如下：

函数含义
selector_first(value, time)选择时间最早的值及其时间
selector_last(value, time)选择时间最晚的值及其时间
selector_min(value, time)选择最小值及该值所在时间
selector_max(value, time)选择最大值及该值所在时间

同一查询对多个 Field 分别调用 selector_last 时，各 Field 的最新非空值可能来自不同 Point。需要返回“最新一整行”时，可使用窗口函数为每台设备编号：

sql
WITH ranked AS (
    SELECT
        time,
        site,
        device_id,
        temperature,
        humidity,
        status,
        online,
        row_number() OVER (
            PARTITION BY device_id
            ORDER BY time DESC
        ) AS row_num
    FROM sensor_data
    WHERE time >= now() - INTERVAL '1 day'
)
SELECT
    time,
    site,
    device_id,
    temperature,
    humidity,
    status,
    online
FROM ranked
WHERE row_num = 1
ORDER BY device_id;

时间下界既控制扫描成本，也决定“最新”的搜索范围；业务需要查全历史最新值时，应结合数据保留范围和 Last Value Cache 等能力设计。

窗口函数 ​

窗口函数在保留每个 Point 的同时，计算它与同组其他 Point 的关系。

与上一个值比较 ​

sql
SELECT
    time,
    device_id,
    temperature,
    lag(temperature) OVER (
        PARTITION BY device_id
        ORDER BY time
    ) AS previous_temperature,
    temperature - lag(temperature) OVER (
        PARTITION BY device_id
        ORDER BY time
    ) AS temperature_change
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
ORDER BY device_id, time;

PARTITION BY device_id 表示每台设备独立计算，ORDER BY time 决定“上一条”的顺序。每组第一行没有上一条数据，因此 lag 返回 NULL。

移动平均 ​

sql
SELECT
    time,
    device_id,
    temperature,
    avg(temperature) OVER (
        PARTITION BY device_id
        ORDER BY time
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3_points
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day'
ORDER BY device_id, time;

这是按最近 3 个 Point 计算移动平均，不等于最近 3 分钟。采样不均匀时，应先按固定时间窗口聚合，再计算窗口函数。

累计值 ​

sql
SELECT
    time,
    device_id,
    humidity,
    sum(humidity) OVER (
        PARTITION BY device_id
        ORDER BY time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_humidity
FROM sensor_data
WHERE time >= now() - INTERVAL '1 day';

累计值是否有业务意义取决于 Field 含义。瞬时温度不适合简单累计，流量增量、耗电量增量等数据更常使用累计计算。

缺失时间窗口填充 ​

普通 date_bin 只返回存在数据的窗口。绘制连续曲线时，可使用 date_bin_gapfill 生成缺失窗口，再选择填充策略。

线性插值 ​

sql
SELECT
    date_bin_gapfill(INTERVAL '5 minutes', time) AS window_start,
    device_id,
    interpolate(avg(temperature)) AS temperature
FROM sensor_data
WHERE time >= '2026-08-20T08:00:00Z'::TIMESTAMP
  AND time <= '2026-08-20T09:00:00Z'::TIMESTAMP
GROUP BY 1, device_id
ORDER BY device_id, 1;

使用上一个已知值 ​

sql
SELECT
    date_bin_gapfill(INTERVAL '5 minutes', time) AS window_start,
    device_id,
    locf(avg(temperature)) AS temperature
FROM sensor_data
WHERE time >= '2026-08-20T08:00:00Z'::TIMESTAMP
  AND time <= '2026-08-20T09:00:00Z'::TIMESTAMP
GROUP BY 1, device_id
ORDER BY device_id, 1;

interpolate 适合可以合理线性变化的数值；locf 表示 Last Observation Carried Forward，适合短时间内保持上一状态的指标。离线期间是否允许补值必须由业务语义决定，补出的值不是实际采样值。

Gap Fill 查询必须给出明确的时间上下界，否则无法确定需要生成多少个窗口。

子查询与 CTE ​

子查询适合先聚合再过滤：

sql
SELECT device_id, avg_temperature
FROM (
    SELECT
        device_id,
        avg(temperature) AS avg_temperature
    FROM sensor_data
    WHERE time >= now() - INTERVAL '1 day'
    GROUP BY device_id
) AS device_summary
WHERE avg_temperature >= 30.0
ORDER BY avg_temperature DESC;

CTE 使用 WITH 为中间结果命名，复杂查询更易阅读：

sql
WITH hourly AS (
    SELECT
        date_bin(INTERVAL '1 hour', time) AS hour,
        site,
        avg(temperature) AS avg_temperature
    FROM sensor_data
    WHERE time >= now() - INTERVAL '7 days'
    GROUP BY 1, site
)
SELECT hour, site, avg_temperature
FROM hourly
WHERE avg_temperature >= 30.0
ORDER BY hour, site;

CTE 主要组织查询结构，不代表中间结果一定被物化或缓存。

JOIN 与时间对齐 ​

假设另一个 power_data 表保存同一批设备的功率：

text
power_data,site=hefei,device_id=sensor01 watts=120.5 1787212800
power_data,site=hefei,device_id=sensor02 watts=135.2 1787212800

按设备和完全相同的时间戳关联：

sql
SELECT
    s.time,
    s.device_id,
    s.temperature,
    p.watts
FROM sensor_data AS s
INNER JOIN power_data AS p
    ON s.device_id = p.device_id
   AND s.time = p.time
WHERE s.time >= now() - INTERVAL '1 day'
  AND p.time >= now() - INTERVAL '1 day';

真实采集时间往往相差数秒，直接使用 s.time = p.time 可能匹配不到。可以先把两侧聚合到相同窗口再 JOIN：

sql
WITH sensor_5m AS (
    SELECT
        date_bin(INTERVAL '5 minutes', time) AS window_start,
        device_id,
        avg(temperature) AS avg_temperature
    FROM sensor_data
    WHERE time >= now() - INTERVAL '1 day'
    GROUP BY 1, device_id
),
power_5m AS (
    SELECT
        date_bin(INTERVAL '5 minutes', time) AS window_start,
        device_id,
        avg(watts) AS avg_watts
    FROM power_data
    WHERE time >= now() - INTERVAL '1 day'
    GROUP BY 1, device_id
)
SELECT
    s.window_start,
    s.device_id,
    s.avg_temperature,
    p.avg_watts
FROM sensor_5m AS s
LEFT JOIN power_5m AS p
    ON s.device_id = p.device_id
   AND s.window_start = p.window_start
ORDER BY s.window_start, s.device_id;

JOIN 两侧都应限制时间范围。没有时间约束或关联键不唯一时，结果行数可能急剧膨胀。设备名称、组织关系等频繁变化的业务主数据通常仍由关系数据库维护，不宜因为 SQL 支持 JOIN 就照搬 OLTP 模型。

类型转换与时间计算 ​

使用 CAST 或 :: 转换类型：

sql
SELECT
    CAST(temperature AS BIGINT) AS rounded_temperature,
    humidity::DOUBLE AS humidity_value,
    '2026-08-20T08:00:00Z'::TIMESTAMP AS start_time;

常见时间表达式：

sql
SELECT
    now() AS current_time,
    now() - INTERVAL '15 minutes' AS fifteen_minutes_ago,
    time + INTERVAL '8 hours' AS display_time_utc8
FROM sensor_data
WHERE time >= now() - INTERVAL '1 hour'
LIMIT 1;

数据库中的时间应统一按绝对时间存储和过滤。需要显示本地时间时，优先在展示层转换时区；直接加 8 小时只是固定偏移示例，不能处理夏令时规则。

参数化查询 ​

应用程序不应把用户输入直接拼进 SQL。InfluxDB 3 SQL 支持命名参数，具体绑定方式由 HTTP API 或客户端库决定：

sql
SELECT
    time,
    device_id,
    temperature
FROM sensor_data
WHERE time >= $start_time
  AND time < $end_time
  AND site = $site
  AND temperature >= $min_temperature
ORDER BY time;

参数只能替代值，不能直接替代表名、列名或排序方向。动态标识符应在程序中通过允许列表选择。

常见错误 ​

没有时间范围 ​

sql
-- 不推荐：可能扫描全部历史数据
SELECT avg(temperature)
FROM sensor_data;
sql
-- 推荐：限制业务真正需要的范围
SELECT avg(temperature)
FROM sensor_data
WHERE time >= now() - INTERVAL '24 hours';

把 Tag 和 Field 当成完全相同的列 ​

SQL 查询时二者都表现为列，但写入和 Schema 设计意义不同：Tag 用于描述和分组，Field 保存观测值。不能因为都能出现在 WHERE 中，就忽略它们对写入 Schema、基数和查询代价的影响。

使用 MySQL 专有语法 ​

sql
-- MySQL 风格，不能假定 InfluxDB 3 支持
SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00')
FROM sensor_data;

时间聚合应使用 InfluxDB 3 SQL 支持的 date_bin、date_bin_gapfill、Interval 和时间函数。

用 SQL 修改单行 ​

InfluxDB 的核心路径是追加时序 Point，不是按主键频繁更新业务行。写入使用 Line Protocol；更正同一时间点的数据、删除历史范围和保留策略都具有产品版本相关语义，不能照搬 MySQL 的行级 DML。

混用 SQL、InfluxQL 和 Flux ​

目标语言时间窗口示例
InfluxDB 3 SQLdate_bin(INTERVAL '5 minutes', time)
InfluxQLGROUP BY time(5m)
FluxaggregateWindow(every: 5m, fn: mean)

报语法错误时，先确认连接的产品版本、查询端点和查询语言，而不是只修改函数名称。

参考资料 ​