使用SQL聚合数据

一个聚合数据的SQL查询包括以下条款：

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

学习如何将聚合操作应用于您查询的数据：

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

聚合和选择器函数

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

聚合函数

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

查看聚合函数

基础聚合查询

SELECT AVG(co) from home

选择器函数

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

查看选择器函数

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

选择器结构模式

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

• 时间: 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.

  • 在指定列上使用 aggregate 或 selector 函数。

• 在你的 GROUP BY 子句中：

  • Specify the DATE_BIN(...) column ordinal reference (1).
  • Specify other columns (for example, room) that are specified in the SELECT clause and aren’t used in a selector function.

  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