使用SQL聚合数据

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

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

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

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

聚合和选择函数

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

聚合函数

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

查看聚合函数

基本聚合查询

SELECT AVG(co) from home

选择函数

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

查看选择函数

每个选择器函数返回一个Rust 结构体（类似于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，它始终使用测量 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）。
  • 指定其他列（例如，room），这些列在 SELECT 子句中指定且未用于选择器函数。

  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