使用 SQL 聚合数据

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

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

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

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

聚合函数和选择器函数

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

聚合函数

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

查看 SQL 聚合函数

基本聚合查询

SELECT AVG(co) from home

选择器函数

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

查看 SQL 选择器函数

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

选择器 struct 模式

从选择器函数返回的 struct 具有两个属性

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

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

使用选择器函数

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

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

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

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 纪元），并返回一个新的 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