# 使用 SQL 查询数据

EMQX Tables 支持使用 SQL 筛选、排序和聚合时序数据。
本页以[快速开始](./emqx_tables_quick_start.md)创建的 `machine_metrics` 表为例；执行前，请先创建表并写入示例数据。

在**数据查询**页面选择 `public` 数据库，每次执行一个代码块中的一条 SQL。数据库查询与 Broker 规则 SQL 不同，不要把 MQTT 主题表达式直接用于数据库查询。

## 示例数据

后续查询继续使用 `machine_metrics` 表。下表列出一组用于理解查询的参考数据，其中包含 3 轮设备数据，每轮包含 3 台设备：

| 设备 | 生产线 | 第 1 轮温度 | 第 2 轮温度 | 第 3 轮温度 |
| --- | --- | ---: | ---: | ---: |
| `machine_001` | `line_a` | 36.5 | 52.3 | 82.1 |
| `machine_002` | `line_a` | 34.2 | 38.6 | 41.7 |
| `machine_003` | `line_b` | 28.7 | 31.4 | 47.8 |

每轮数据至少间隔 1 分钟。`ts` 为 Broker 接收消息的时间，因此实际时间戳取决于消息发布时间。如果已完成快速开始，`machine_metrics` 表中已经包含至少一条记录。表中也可能包含此前或之后写入的数据，因此实际查询结果以表中的数据为准。

如需观察筛选和聚合效果，可以参照[快速开始中的发布步骤](./emqx_tables_quick_start.md#发布-mqtt-消息)，修改 Payload 中的 `machine_id`、`production_line` 和 `temperature`，为不同设备发布多条测试消息。

## 基本查询

使用 `SELECT` 查看表中的全部字段：

```sql
SELECT * FROM machine_metrics;
```

只查询所需字段，可以减少结果体积：

```sql
SELECT ts, machine_id, temperature
FROM machine_metrics;
```

统计记录总数：

```sql
SELECT COUNT(*) AS record_count
FROM machine_metrics;
```

`record_count` 表示 `machine_metrics` 表中的实际记录总数。

计算平均温度：

```sql
SELECT AVG(temperature) AS avg_temperature
FROM machine_metrics;
```

`avg_temperature` 表示表中所有非空温度值的平均值。

## 限制返回行数

时序数据可能持续增长，探索数据时建议使用 `LIMIT` 限制结果数量：

```sql
SELECT * FROM machine_metrics
ORDER BY ts DESC
LIMIT 10;
```

这条语句返回按时间降序排列的前 10 条记录。只使用 `LIMIT` 而不指定排序，不能保证返回的是最新数据。

## 筛选数据

使用 `WHERE` 按设备筛选：

```sql
SELECT * FROM machine_metrics
WHERE machine_id = 'machine_001';
```

组合多个条件：

```sql
SELECT ts, machine_id, temperature
FROM machine_metrics
WHERE production_line = 'line_a'
  AND temperature > 35
ORDER BY ts DESC;
```

查询结果只包含 `line_a` 生产线上温度大于 `35` 的记录，并按时间从新到旧排列。

### 按时间筛选

使用 `NOW()` 查询最近 1 天的数据：

```sql
SELECT * FROM machine_metrics
WHERE ts >= NOW() - INTERVAL 1 DAY
  AND ts < NOW()
ORDER BY ts ASC;
```

使用左闭右开的条件可避免相邻时间区间重复统计边界上的记录。

### 时间精度和时区

本示例的 `ts` 使用 `TIMESTAMP(3)`，即毫秒精度。通过客户端写入整数时间戳时，必须确认单位与目标字段及驱动要求一致，不能将秒值当作毫秒值。

查询示例使用带 `Z` 的时间字符串表示 UTC。也可以使用带明确时区偏移的时间，例如 `2026-09-11T08:00:00+08:00`。避免混用没有时区信息的字符串和不同单位的整数时间戳。

### 函数

可以在 SQL 中使用聚合、日期时间等函数。
函数名、参数和返回类型以 EMQX Tables 支持的 SQL 为准；PostgreSQL 协议兼容不意味着所有 PostgreSQL 函数和扩展都可用。

## 排序

使用 `ORDER BY` 指定结果顺序：

```sql
SELECT ts, machine_id, temperature
FROM machine_metrics
ORDER BY ts ASC;
```

按温度降序显示前 10 条记录：

```sql
SELECT ts, machine_id, temperature
FROM machine_metrics
ORDER BY temperature DESC
LIMIT 10;
```

## CASE 表达式

使用 `CASE` 根据温度生成业务状态：

```sql
SELECT
    ts,
    machine_id,
    temperature,
    CASE
        WHEN temperature >= 80 THEN 'high'
        WHEN temperature >= 50 THEN 'warning'
        ELSE 'normal'
    END AS temperature_level
FROM machine_metrics;
```

这些阈值仅用于演示，应根据业务设备的实际规则调整。

查询结果会根据每条记录的温度增加 `temperature_level` 字段：温度不低于 `80` 时为 `high`，不低于 `50` 且低于 `80` 时为 `warning`，其余为 `normal`。

## 按设备或生产线聚合

使用 `GROUP BY` 按设备统计记录数和温度：

```sql
SELECT
    machine_id,
    COUNT(*) AS sample_count,
    AVG(temperature) AS avg_temperature,
    MAX(temperature) AS max_temperature
FROM machine_metrics
GROUP BY machine_id
ORDER BY machine_id;
```

查询结果中每台设备对应一行。`sample_count`、`avg_temperature` 和 `max_temperature` 分别表示该设备的记录数、平均温度和最高温度。

按生产线统计记录数、平均温度和最高温度：

```sql
SELECT
    production_line,
    COUNT(*) AS sample_count,
    AVG(temperature) AS avg_temperature,
    MAX(temperature) AS max_temperature
FROM machine_metrics
GROUP BY production_line
ORDER BY production_line;
```

查询结果中每条生产线对应一行，并显示该生产线的记录数、平均温度和最高温度。

## 查询设备的最新记录

先按设备筛选，再按时间倒序取一条记录：

```sql
SELECT * FROM machine_metrics
WHERE machine_id = 'machine_001'
ORDER BY ts DESC
LIMIT 1;
```

该查询返回 `machine_001` 时间戳最新的一条记录。如果之后继续写入该设备的数据，查询结果也会随之变化。

如果只需要各设备的最近上报时间，可以使用：

```sql
SELECT machine_id, MAX(ts) AS latest_ts
FROM machine_metrics
GROUP BY machine_id;
```

`MAX(ts)` 返回最新时间，不会自动返回该时间对应的其他字段值。

## 按时间窗口聚合

使用 `date_bin` 将时间分入固定窗口，再计算窗口内的统计值：

```sql
SELECT
    date_bin('1 minute', ts) AS window_start,
    machine_id,
    AVG(temperature) AS avg_temperature
FROM machine_metrics
WHERE ts >= NOW() - INTERVAL 1 DAY
GROUP BY window_start, machine_id
ORDER BY window_start, machine_id;
```

该查询按设备分别计算每个有数据的 1 分钟窗口内的平均温度。`window_start` 和平均值取决于消息的实际发布时间；如果每台设备在一个窗口内只有一条记录，平均值等于该记录的温度。

### 时间窗口和对齐

`'1 minute'` 表示窗口大小。`date_bin` 默认以 UTC Unix Epoch 为对齐起点；需要按北京时间零点对齐每天的窗口时，可以显式指定起点：

```sql
SELECT
    date_bin('1 day', ts, '2026-09-01T00:00:00+08:00') AS day_start,
    COUNT(*) AS sample_count
FROM machine_metrics
GROUP BY day_start
ORDER BY day_start;
```

### 缺失窗口

`date_bin` 不会自动补齐没有记录的窗口，也不会把缺失温度当作零。需要补齐窗口时，应明确有限的查询时间范围和填充值的业务含义，再使用 `date_bin_gapfill`，并按需配合插值或填充函数。相关语法参见[时间窗口与插值说明](https://docs.datalayers.cn/datalayers/latest/sql-reference/gap_fill.html)。

## 表名和字段名

Datalayers 的数据库名、表名和字段名区分大小写。示例统一使用小写字母和下划线命名，避免保留字、空格和特殊符号。引用已有表时，应使用与建表时一致的名称和大小写；可从数据表面板复制名称，减少拼写错误。

## 后续步骤

- [在数据查询中查看结果](./emqx_tables_data_explorer.md)
- [使用 PostgreSQL 客户端查询](./integration/emqx_tables_with_postgresql.md)
- [使用 DBeaver 查询](./integration/emqx_tables_with_dbeaver.md)