使用 SQL 聚合数据

聚合数据的 SQL 查询包含以下子句：

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

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

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

聚合函数和选择器函数

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

聚合函数

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

查看 SQL 聚合函数

基本聚合查询

SELECT AVG(co) from home

选择器函数

使用**选择器函数**从指定列中“选择”一个值。可用的选择器函数设计用于处理时间序列数据。

查看 SQL 选择器函数

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

选择器结构体 schema

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

• 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 将不会使用该别名。GROUP BY 将使用列的原始值，而不是转换后的别名值。

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

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

查询时间序列时，一个常见的用例是通过将聚合应用于基于时间的组来对数据进行降采样。要将数据分组和聚合到基于时间的组中，请执行以下操作：

• 在您的 SELECT 子句中：

  • 使用 DATE_BIN 函数来计算时间间隔，并输出一个包含每个行中最近 time 时间戳间隔开始的时间的列——例如，以下子句计算两小时间隔（从 Unix epoch 开始），并返回一个新的 time 列，该列包含最接近 home.time 的间隔的开始时间。

    SELECT
      DATE_BIN(INTERVAL '2 hours', time) AS time
    FROM home
    ...

    给定 time 值 2022-01-01T13:00:50.000Z，输出的 time 列包含 2022-01-01T12:00:00.000Z。

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

• 在您的 GROUP BY 子句中：

  • 指定 DATE_BIN(...) 列的序数引用 (1)。这允许您按转换后的 time 值进行分组，并保留 time 列名。
  • 指定在 SELECT 子句中指定但未用于选择器函数的其他列（例如 room）。

  SELECT
    DATE_BIN(INTERVAL '2 hours', time) AS time
  ...
  GROUP BY 1, room
  ...

  要在 GROUP BY 子句中通过*名称*引用 DATE_BIN(...) 结果列，请在 SELECT 子句中为其分配一个不同于“time”的别名——例如：

  SELECT
    DATE_BIN(INTERVAL '2 hours', time) AS _time
  FROM home
  ...
  GROUP BY _time, room

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

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

SELECT
  DATE_BIN(INTERVAL '2 hours', time) 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) 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

SELECT
  DATE_BIN(INTERVAL '2 hours', time) 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

根据聚合值查询行

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

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