使用 SQL 窗口函数来比较时序数据中不同行之间的值。诸如 LAG 和 LEAD 这样的窗口函数，让您无需使用自连接即可访问前一行或后一行中的值，从而轻松计算随时间的变化。
比较值的常见用例包括：
- 计算当前值与前一个值之间的差值
- 计算变化率或百分比变化
- 检测显著变化或异常
- 比较特定时间间隔内的值
- 处理重置为零的计数器指标
要跨行比较值，请执行以下操作：
- 使用带有
OVER 子句的 窗口函数，例如 LAG 或 LEAD。 - 包含
PARTITION BY 子句，按标签（如 room 或 sensor_id）对数据进行分组。 - 包含
ORDER BY 子句，以定义比较顺序（通常按 time）。 - 使用算术运算符计算差值、比率或百分比变化。
比较值的示例
计算与前一个值的差值
使用 LAG 函数访问前一行中的值并计算差值。这对于检测随时间的变化很有用。
SELECT
time,
room,
temp,
temp - LAG(temp, 1) OVER (
PARTITION BY room
ORDER BY time
) AS temp_change
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time
| time | room | temp | temp_change |
|---|
| 2022-01-01T08:00:00 | 厨房 | 21.0 | NULL |
| 2022-01-01T09:00:00 | 厨房 | 23.0 | 2.0 |
| 2022-01-01T10:00:00 | 厨房 | 22.7 | -0.3 |
| 2022-01-01T08:00:00 | 客厅 | 21.1 | NULL |
| 2022-01-01T09:00:00 | 客厅 | 21.4 | 0.3 |
| 2022-01-01T10:00:00 | 客厅 | 21.8 | 0.4 |
每个分区中的第一行对于 temp_change 返回 NULL，因为没有前一个值。要使用默认值而不是 NULL，请向 LAG 提供第三个参数。
LAG(temp, 1, 0) -- Returns 0 if no previous value exists
计算百分比变化
通过将差值除以前一个值来计算当前值与前一个值之间的百分比变化。
SELECT
time,
room,
temp,
ROUND(
((temp - LAG(temp, 1) OVER (PARTITION BY room ORDER BY time)) /
LAG(temp, 1) OVER (PARTITION BY room ORDER BY time)) * 100,
2
) AS percent_change
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time
| time | room | temp | percent_change |
|---|
| 2022-01-01T08:00:00 | 厨房 | 21.0 | NULL |
| 2022-01-01T09:00:00 | 厨房 | 23.0 | 9.52 |
| 2022-01-01T10:00:00 | 厨房 | 22.7 | -1.30 |
| 2022-01-01T08:00:00 | 客厅 | 21.1 | NULL |
| 2022-01-01T09:00:00 | 客厅 | 21.4 | 1.42 |
| 2022-01-01T10:00:00 | 客厅 | 21.8 | 1.87 |
比较固定间隔的值
对于固定间隔的时序数据（如每小时的读数），请使用带有偏移量参数的 LAG 来比较 N 行之前的值。
以下查询将每个温度读数与一小时前（假设为每小时数据）的读数进行比较。
SELECT
time,
room,
temp,
LAG(temp, 1) OVER (
PARTITION BY room
ORDER BY time
) AS temp_1h_ago,
temp - LAG(temp, 1) OVER (
PARTITION BY room
ORDER BY time
) AS hourly_change
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time < '2022-01-01T12:00:00Z'
ORDER BY room, time
| time | room | temp | temp_1h_ago | hourly_change |
|---|
| 2022-01-01T08:00:00 | 厨房 | 21.0 | NULL | NULL |
| 2022-01-01T09:00:00 | 厨房 | 23.0 | 21.0 | 2.0 |
| 2022-01-01T10:00:00 | 厨房 | 22.7 | 23.0 | -0.3 |
| 2022-01-01T11:00:00 | 厨房 | 22.4 | 22.7 | -0.3 |
| 2022-01-01T08:00:00 | 客厅 | 21.1 | NULL | NULL |
| 2022-01-01T09:00:00 | 客厅 | 21.4 | 21.1 | 0.3 |
| 2022-01-01T10:00:00 | 客厅 | 21.8 | 21.4 | 0.4 |
| 2022-01-01T11:00:00 | 客厅 | 22.2 | 21.8 | 0.4 |
比较具有精确时间偏移的值
对于不规则间隔的时序数据，或当您需要与精确时间偏移（例如，正好一小时前，而不仅仅是前一行）进行比较时，请使用带有间隔算术的自连接。
SELECT
current.time,
current.room,
current.temp AS current_temp,
previous.temp AS temp_1h_ago,
current.temp - previous.temp AS hourly_diff
FROM home AS current
LEFT JOIN home AS previous
ON current.room = previous.room
AND previous.time = current.time - INTERVAL '1 hour'
WHERE
current.time >= '2022-01-01T08:00:00Z'
AND current.time < '2022-01-01T12:00:00Z'
ORDER BY current.room, current.time
| time | room | current_temp | temp_1h_ago | hourly_diff |
|---|
| 2022-01-01T08:00:00 | 厨房 | 21.0 | NULL | NULL |
| 2022-01-01T09:00:00 | 厨房 | 23.0 | 21.0 | 2.0 |
| 2022-01-01T10:00:00 | 厨房 | 22.7 | 23.0 | -0.3 |
| 2022-01-01T11:00:00 | 厨房 | 22.4 | 22.7 | -0.3 |
| 2022-01-01T08:00:00 | 客厅 | 21.1 | NULL | NULL |
| 2022-01-01T09:00:00 | 客厅 | 21.4 | 21.1 | 0.3 |
| 2022-01-01T10:00:00 | 客厅 | 21.8 | 21.4 | 0.4 |
| 2022-01-01T11:00:00 | 客厅 | 22.2 | 21.8 | 0.4 |
这种自连接方法在以下情况下有效：
- 您的数据点不以固定的间隔出现
- 您需要与特定的时间偏移进行比较，而不管上一个数据点何时发生
- 您想确保比较的是正好一小时前（或任何其他特定间隔）的值
处理计数器指标和重置
计数器指标跟踪随时间增加的累积值，例如总请求数、传输的字节数或错误数。与仪表指标（可以上升或下降）不同，计数器通常只增加，尽管它们在服务重新启动时可能会重置为零。
使用 GREATEST 和 LAG 来处理计数器重置，方法是将负差值视为零。
InfluxDB 3 SQL 和计数器指标
InfluxDB 3 SQL 不提供 Flux 的 increase() 或 InfluxQL 的 NON_NEGATIVE_DIFFERENCE() 函数的内置等效项。使用下面显示的模式来实现类似的结果。
计算非负差值（计数器速率）
计算连续计数器读数之间的增加量，将负差值（计数器重置）视为零。
SELECT
time,
host,
requests,
LAG(requests) OVER (PARTITION BY host ORDER BY time) AS prev_requests,
GREATEST(
requests - LAG(requests) OVER (PARTITION BY host ORDER BY time),
0
) AS requests_increase
FROM metrics
WHERE host = 'server1'
ORDER BY time
| time | host | requests | prev_requests | requests_increase |
|---|
| 2024-01-01T00:00:00 | server1 | 1000 | NULL | 0 |
| 2024-01-01T01:00:00 | server1 | 1250 | 1000 | 250 |
| 2024-01-01T02:00:00 | server1 | 1600 | 1250 | 350 |
| 2024-01-01T03:00:00 | server1 | 50 | 1600 | 0 |
| 2024-01-01T04:00:00 | server1 | 300 | 50 | 250 |
LAG(requests) 检索前一个计数器值，requests - LAG(requests) 计算差值，GREATEST(..., 0) 为负差值（计数器重置）返回 0。PARTITION BY host 确保仅在同一主机内进行比较。
计算累积计数器增加量
计算计数器随时间的总增加量，并处理重置。使用公用表表达式 (CTE) 先计算差值，然后将它们相加。
WITH counter_diffs AS (
SELECT
time,
host,
requests,
GREATEST(
requests - LAG(requests) OVER (PARTITION BY host ORDER BY time),
0
) AS requests_increase
FROM metrics
WHERE host = 'server1'
)
SELECT
time,
host,
requests,
SUM(requests_increase) OVER (PARTITION BY host ORDER BY time) AS cumulative_increase
FROM counter_diffs
ORDER BY time
| time | host | requests | cumulative_increase |
|---|
| 2024-01-01T00:00:00 | server1 | 1000 | 0 |
| 2024-01-01T01:00:00 | server1 | 1250 | 250 |
| 2024-01-01T02:00:00 | server1 | 1600 | 600 |
| 2024-01-01T03:00:00 | server1 | 50 | 600 |
| 2024-01-01T04:00:00 | server1 | 300 | 850 |
CTE 计算每一行的非负差值，然后 SUM(requests_increase) OVER (...) 创建一个运行总计。尽管在 03:00 发生计数器重置，但累积增加量仍在增长。
按时间间隔聚合计数器增加量
计算每个时间间隔（例如，每小时总计）的计数器总增加量。
WITH counter_diffs AS (
SELECT
DATE_BIN(INTERVAL '1 hour', time) AS time_bucket,
host,
requests,
GREATEST(
requests - LAG(requests) OVER (PARTITION BY host ORDER BY time),
0
) AS requests_increase
FROM metrics
)
SELECT
time_bucket,
host,
SUM(requests_increase) AS total_increase
FROM counter_diffs
WHERE requests_increase > 0
GROUP BY time_bucket, host
ORDER BY host, time_bucket
| time_bucket | host | total_increase |
|---|
| 2024-01-01T01:00:00 | server1 | 250 |
| 2024-01-01T02:00:00 | server1 | 350 |
| 2024-01-01T04:00:00 | server1 | 250 |
| 2024-01-01T01:00:00 | server2 | 400 |
| 2024-01-01T02:00:00 | server2 | 500 |
| 2024-01-01T03:00:00 | server2 | 300 |
| 2024-01-01T04:00:00 | server2 | 400 |
CTE 计算每一行的差值。DATE_BIN() 将每个时间戳分配到一个 1 小时的时间间隔，SUM(requests_increase) 聚合每个间隔内的所有增加量，而 WHERE requests_increase > 0 过滤掉零增加量（第一行和计数器重置）。
支持和反馈
感谢您成为我们社区的一员！我们欢迎并鼓励您对 InfluxDB 3 Core 和本文档提供反馈和错误报告。要获得支持，请使用以下资源
具有年度合同或支持合同的客户可以 联系 InfluxData 支持。
