使用 SQL 聚合数据

一个聚合数据的 SQL 查询包括以下子句

• * SELECT: 指定要从 measurement 输出的字段、标签和计算，或使用通配符别名 (*) 选择 measurement 中的所有字段和标签。
• * FROM: 指定要从中查询数据的 measurement。
• WHERE: 仅返回满足指定条件的数据——例如，在时间范围内、包含特定标签值或包含指定范围之外的字段值。
• GROUP BY: 对具有相同列和表达式值（例如，聚合函数结果）的数据进行分组。

了解如何将聚合操作应用于您查询的数据

• 聚合函数和选择器函数
  • 聚合函数
  • 选择器函数
• 示例聚合查询

聚合函数和选择器函数

聚合函数和选择器函数都从每个 SQL 分区或组返回单行。例如，如果您 GROUP BY room 并在 SELECT 子句中执行聚合操作，则结果将包含 room 的每个唯一值的聚合值。

聚合函数

使用聚合函数聚合每个组中指定列的值，并为每个组返回包含聚合值的单行。

查看聚合函数

基本聚合查询

SELECT AVG(co) from home

选择器函数

使用选择器函数从指定列“选择”一个值。可用的选择器函数旨在与时间序列数据一起使用。

查看选择器函数

每个选择器函数都返回一个 Rust struct（类似于 JSON 对象），表示每个组中指定列的单个时间和值。返回的时间和值取决于选择器函数中的逻辑。例如，selector_first 返回组中第一行中指定列的值。selector_max 返回组中指定列的最大值。

选择器结构体模式

从选择器函数返回的结构体具有两个属性

• time: 所选行中的 time 值
• value: 所选行中指定列的值

{time: 2023-01-01T00:00:00Z, value: 72.1}

使用选择器函数

每个选择器函数都有两个参数

• 第一个是要操作的列。
• 第二个是在选择逻辑中使用的时间列。

在您的 SELECT 语句中，执行选择器函数并使用方括号表示法引用返回的结构体的属性以填充列值

SELECT
  selector_first(temp, time)['time'] AS time,
  selector_first(temp, time)['value'] AS temp,
  room
FROM home
GROUP BY room

示例聚合查询

• 执行未分组的聚合
• 分组和聚合数据
  • 通过应用基于间隔的聚合来降采样数据
• 根据聚合值查询行

执行未分组的聚合

要聚合指定列中的所有查询值

• 在您的 SELECT 语句中使用聚合函数或选择器函数。
• 不要包含 GROUP BY 子句以使您的数据保持未分组状态。

SELECT avg(co) AS 'average co' from home

分组和聚合数据

要将聚合函数或选择器函数应用于分组数据

• 在您的 SELECT 语句中使用聚合函数或选择器函数。
• 在您的 SELECT 语句中包含要分组的列。
• 包含一个 GROUP BY 子句，其中包含要分组的列和表达式的逗号分隔列表。

使用 GROUP BY 时请记住以下几点

• GROUP BY 可以使用在 SELECT 子句中定义的列别名。
• GROUP BY 不能使用名为 time 的别名。如果您在 GROUP BY 中包含 time，它始终使用 measurement 的 time 列。

SELECT
  room,
  avg(temp) AS 'average temp'
FROM home
GROUP BY room

通过应用基于间隔的聚合来降采样数据

查询时间序列时，常见的用例是通过将聚合应用于基于时间的组来降采样数据。要将数据分组和聚合到基于时间的组中

• 在您的 SELECT 子句中

  • 使用 DATE_BIN 函数计算时间间隔，并输出一个列，其中包含最接近每行中 time 时间戳的间隔的开始时间——例如，以下子句计算从 1970-01-01T00:00:00Z 开始的两个小时间隔，并返回一个新的 time 列，其中包含最接近 home.time 的间隔的开始时间

    SELECT
      DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP) AS time
    FROM home
    ...
    

    给定一个 time 值 2023-03-09T13:00:50.000Z，输出的 time 列包含 2023-03-09T12:00:00.000Z。

  • 在指定的列上使用聚合或选择器函数。

• 在您的 GROUP BY 子句中

  • 指定 DATE_BIN(...) 列序号引用 (1)。
  • 指定在 SELECT 子句中指定且未在选择器函数中使用的其他列（例如，room）。

  SELECT
    DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP) AS time
  ...
  GROUP BY 1, room
  ...
  

  要在 GROUP BY 子句中按名称引用 DATE_BIN(...) 结果列，请在 SELECT 子句中分配一个别名，而不是 “time”——例如

  SELECT
    DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP) AS _time
  FROM home
  ...
  GROUP BY _time, room
  

• 包含一个 ORDER BY 子句，其中包含要排序的列。

以下示例检索时间间隔和房间的唯一组合及其最低、最高和平均温度。

SELECT
  DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP) AS time,
  room,
  selector_max(temp, time)['value'] AS 'max temp',
  selector_min(temp, time)['value'] AS 'min temp',
  avg(temp) AS 'average temp'
FROM home
GROUP BY 1, room
ORDER BY room, 1

SELECT
  DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP)
  AS _time,
  room,
  selector_max(temp, time)['value'] AS 'max temp',
  selector_min(temp, time)['value'] AS 'min temp',
  avg(temp) AS 'average temp'
FROM home
GROUP BY _time, room
ORDER BY room, _time

根据聚合值查询行

要根据聚合操作后的值查询数据，请包含一个 HAVING 子句，其中包含定义的谓词条件，例如值阈值。 WHERE 子句中的谓词在数据聚合之前应用。 HAVING 子句中的谓词在数据聚合之后应用。

SELECT
  room,
  avg(co) AS 'average co'
FROM home
GROUP BY room
HAVING "average co" > 5