基于时间间隔聚合平均数据的AWS TimeStream SQL实现问询

需求说明 #

我有一张存储日期与数值的表，需要实现：

• 支持用户选择平均计算的时间间隔（比如按月、按天）
• 支持用户指定数据返回的时间范围（比如2022年1月1日至2022年3月1日）
• 输出对应时间区间的平均值表格

目前已能筛选指定时间范围内的数据，但不清楚如何按日期区间分组计算平均值，尝试过循环思路但不熟悉SQL语法，试过用BIN函数但没得到完整方案。

解决方案（基于AWS Timestream SQL） #

AWS Timestream的SQL不需要写循环，直接用BIN()函数对时间字段分组，配合聚合函数即可实现需求，以下是完整实现步骤：

1. 核心SQL模板 #

SELECT
  -- 生成易读的区间标签（比如"2022-01-01 至 2022-02-01"）
  CONCAT(
    DATE_TRUNC('${interval}', BIN(time, '${interval}')),
    ' 至 ',
    DATE_TRUNC('${interval}', BIN(time, '${interval}') + INTERVAL '1' ${interval})
  ) AS time_interval,
  -- 计算区间平均值，保留2位小数
  ROUND(AVG(value), 2) AS avg_value,
  -- 用于排序的分组起始时间
  BIN(time, '${interval}') AS binned_start_time
FROM myTable
WHERE
  measure_name = 'myMeasureName'
  -- 匹配用户指定的时间范围
  AND time >= TIMESTAMP '${start_date}'
  AND time <= TIMESTAMP '${end_date}'
-- 按分组后的时间字段聚合，不能用聚合结果作为分组依据
GROUP BY BIN(time, '${interval}')
-- 按时间区间顺序排序
ORDER BY binned_start_time ASC

2. 关键语法说明 #

• BIN(time, interval)：Timestream核心分组函数，将时间字段按指定间隔（如1d天、1mo月）分组，返回每个分组的起始时间
• DATE_TRUNC(interval, timestamp)：格式化时间，让区间标签更直观
• CONCAT()：拼接字符串生成清晰的区间描述
• 变量替换：${interval}、${start_date}、${end_date}对应Grafana中配置的用户可交互变量

3. Grafana变量配置（实现用户交互） #

在Grafana中设置3个变量，让用户可以直观选择：

• interval：下拉选择，选项包括1h小时、1d天、1mo月等，对应Timestream的时间间隔格式
• start_date：日期选择器，用于选择时间范围起始点
• end_date：日期选择器，用于选择时间范围结束点

4. 示例：按月平均（2022-01-01至2022-03-01） #

替换变量后的SQL如下，执行后会返回2022年1月、2月两个区间的平均值：

SELECT
  CONCAT(
    DATE_TRUNC('month', BIN(time, '1mo')),
    ' 至 ',
    DATE_TRUNC('month', BIN(time, '1mo') + INTERVAL '1' month)
  ) AS time_interval,
  ROUND(AVG(value), 2) AS avg_value,
  BIN(time, '1mo') AS binned_start_time
FROM myTable
WHERE
  measure_name = 'myMeasureName'
  AND time >= TIMESTAMP '2022-01-01 00:00:00'
  AND time <= TIMESTAMP '2022-03-01 23:59:59'
GROUP BY BIN(time, '1mo')
ORDER BY binned_start_time ASC

5. 纠正之前的错误尝试 #

你之前的SQL错误在于GROUP BY avg_value, BIN(time,1d)，聚合函数的结果（avg_value）不能放在GROUP BY里，正确的分组字段应该是BIN(time, interval)。

内容的提问来源于stack exchange，提问作者wyt