TimescaleDB API 参考超级函数状态跟踪

state_agg() 函数

ToolkitTimescaleDB Toolkit 函数在 Timescale 社区版中可用。它们已自动包含在 Timescale 中，但对于自托管的 TimescaleDB 必须单独安装。点击了解更多。

Timescale Cloud：性能、扩展、企业级

自托管产品

MST

简介

给定一个在离散状态之间切换的系统或值，可以跟踪状态之间的转换。例如，您可以使用 state_agg 来创建状态转换的状态，或者计算状态的持续时间。state_agg 扩展了 compact_state_agg 的功能。

state_agg 旨在处理相对较少数量的状态。在状态在行之间大多不同的数据集中，其性能可能不佳。

因为 state_agg 跟踪更多信息，所以它比 compact_state_agg 使用更多的内存。如果您想最大限度地减少内存使用，并且不需要查询状态转换的时间戳，请考虑改用 compact_state_agg。

相关超级函数组

此组中的函数

聚合

state_agg
将状态数据聚合为状态聚合以进行进一步分析

访问器

duration_in
根据状态聚合计算在给定状态中花费的总时间
interpolated_duration_in
根据状态聚合计算在给定状态中花费的总时间，并在时间桶边界处插值
interpolated_state_periods
从状态聚合中获取与给定状态对应的时间段，并在时间桶边界处插值
interpolated_state_timeline
从状态聚合中获取所有状态的状态，并在时间桶边界处插值
into_values
将状态聚合展开为一组行，显示每个状态的持续时间
state_at
确定给定时间的状态
state_periods
从状态聚合中获取与给定状态对应的时间段
state_timeline
从状态聚合中获取所有状态的状态

合并

rollup
合并多个状态聚合

函数详情

state_agg(
  ts TIMESTAMPTZ,
  value {TEXT | BIGINT}
) RETURNS StateAgg

将状态数据聚合为状态聚合以跟踪状态转换。与只存储持续时间的 compact_state_agg 不同，state_agg 还存储状态转换的时间戳。

必需参数
名称类型描述
tsTIMESTAMPTZ与每个状态读取关联的时间戳
valueTEXT, BIGINT该时间点的状态
返回
列类型描述
aggStateAgg一个对象，存储每个状态中花费的时间段，包括状态转换的时间戳
示例

创建一个状态聚合以跟踪某些设备的状态

SELECT state_agg(time, status) FROM devices;
duration_in(
  agg StateAgg,
  state {TEXT | BIGINT}
  [, start TIMESTAMPTZ]
  [, interval INTERVAL]
) RETURNS INTERVAL

给定一个状态聚合，计算在某个状态中花费的总时间。如果需要在时间桶边界处插值缺失值，请使用 interpolated_duration_in。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
stateTEXT, BIGINT要查询的状态
可选参数
名称类型描述
startTIMESTAMPTZ如果指定，则只返回此时间之后的状态时间。
intervalINTERVAL如果指定，则只返回从开始时间到时间间隔结束的状态时间。
返回
列类型描述
duration_inINTERVAL在给定状态中花费的时间。显示为 days（天）、hh:mm:ss（时:分:秒）或两者的组合。
示例

创建一个测试表，跟踪系统在 starting（启动中）、running（运行中）和 error（错误）状态之间切换的时间。查询该表以获取在 running 状态中花费的时间。

如果您希望以秒为单位查看结果，请从返回结果中 EXTRACT 纪元时间。

SET timezone TO 'UTC';
CREATE TABLE states(time TIMESTAMPTZ, state TEXT);
INSERT INTO states VALUES
  ('1-1-2020 10:00', 'starting'),
  ('1-1-2020 10:30', 'running'),
  ('1-3-2020 16:00', 'error'),
  ('1-3-2020 18:30', 'starting'),
  ('1-3-2020 19:30', 'running'),
  ('1-5-2020 12:00', 'stopping');


SELECT duration_in(
  state_agg(time, state),
  'running'
) FROM states;
duration_in  
---------------
3 days 22:00:00
interpolated_duration_in(
  agg StateAgg,
  state {TEXT | BIGINT},
  start TIMESTAMPTZ,
  interval INTERVAL
  [, prev StateAgg]
) RETURNS DOUBLE PRECISION

计算在给定状态中的总持续时间。与 duration_in 不同，您可以在覆盖多个时间桶的多个状态聚合中使用此函数。时间桶边界处的任何缺失值都将从相邻的状态聚合中插值。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
stateTEXT, BIGINT要查询的状态
startTIMESTAMPTZ要计算的时间间隔的开始
intervalINTERVAL要计算的时间间隔的长度
可选参数
名称类型描述
prevStateAgg来自前一个时间间隔的状态聚合，用于在 start 处插值。如果为 NULL，则 aggregate 中的第一个时间戳将用作时间间隔的开始。
返回
列类型描述
interpolated_duration_inINTERVAL在查询状态中花费的总时间。显示为 days（天）、hh:mm:ss（时:分:秒）或两者的组合。
示例

创建一个测试表，跟踪系统在 starting（启动中）、running（运行中）和 error（错误）状态之间切换的时间。查询该表以获取在 running 状态中花费的时间。使用 LAG 和 LEAD 获取相邻聚合以进行插值。如果您希望以秒为单位查看结果，请从返回结果中 EXTRACT 纪元时间。

SELECT 
  time,
  interpolated_duration_in(
    agg,
    'running',
    time,
    '1 day',
    LAG(agg) OVER (ORDER BY time)
) FROM (
  SELECT
    time_bucket('1 day', time) as time,
    state_agg(time, state) as agg
  FROM
    states
  GROUP BY time_bucket('1 day', time)
) s;
time                    | interpolated_duration_in 
------------------------+--------------------------
2020-01-01 00:00:00+00  | 13:30:00
2020-01-02 00:00:00+00  | 16:00:00
2020-01-03 00:00:00+00  | 04:30:00
2020-01-04 00:00:00+00  | 12:00:00
interpolated_state_periods(
  agg StateAgg,
  state [TEXT | BIGINT],
  start TIMESTAMPTZ,
  interval INTERVAL,
  [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

给定一个状态聚合和特定状态，列出系统处于该状态的时间段。时间段由开始时间和结束时间定义。

与 state_periods 不同，您可以在覆盖不同时间桶的多个状态聚合中使用此函数。时间桶边界处的任何缺失值都将从相邻的状态聚合中插值。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
stateTEXT, BIGINT要查询的状态
startTIMESTAMPTZ要计算的时间间隔的开始
intervalINTERVAL要计算的时间间隔的长度
可选参数
名称类型描述
prevStateAgg来自前一个时间间隔的状态聚合，用于在 start 处插值。如果为 NULL，则 aggregate 中的第一个时间戳将用作时间间隔的开始。
返回
列类型描述
start_timeTIMESTAMPTZ状态开始时间（包含）
end_timeTIMESTAMPTZ状态结束时间（不包含）
示例

给定按 1 分钟间隔分桶的状态聚合，在桶边界处插值状态，并列出与状态 OK 对应的所有时间段。为了执行插值，使用 LAG 和 LEAD 函数获取前一个和后一个状态聚合。

SELECT
    bucket,
    (interpolated_state_periods(
        summary,
        'OK',
        bucket,
        '15 min',
        LAG(summary) OVER (ORDER by bucket)
    )).*
FROM (
    SELECT
        time_bucket('1 min'::interval, ts) AS bucket,
        state_agg(ts, state) AS summary
    FROM states_test
    GROUP BY time_bucket('1 min'::interval, ts)
) t;
bucket         |       start_time       |        end_time
------------------------+------------------------+------------------------
 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00
 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00
interpolated_state_timeline(
    agg StateAgg,
    start TIMESTAMPTZ,
    interval INTERVAL,
    [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)


interpolated_state_int_timeline(
    agg StateAgg,
    start TIMESTAMPTZ,
    interval INTERVAL,
    [, prev StateAgg]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

获取所有状态的状态，显示每次进入和退出状态的时间。

与 state_timeline 不同，您可以在覆盖不同时间桶的多个状态聚合中使用此函数。时间桶边界处的任何缺失值都将从相邻的状态聚合中插值。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
startTIMESTAMPTZ要计算的时间间隔的开始
intervalINTERVAL要计算的时间间隔的长度
可选参数
名称类型描述
prevStateAgg来自前一个时间间隔的状态聚合，用于在 start 处插值。如果为 NULL，则 aggregate 中的第一个时间戳将用作时间间隔的开始。
返回
列类型描述
stateTEXT, BIGINT在状态聚合中找到的状态
start_timeTIMESTAMPTZ状态开始时间（包含）
end_timeTIMESTAMPTZ状态结束时间（不包含）
示例

给定按 1 分钟间隔分桶的状态聚合，在桶边界处插值状态，并获取所有状态的历史记录。

为了执行插值，使用 LAG 和 LEAD 函数获取前一个和后一个状态聚合。

SELECT
    bucket,
    (interpolated_state_timeline(
        summary,
        bucket,
        '15 min',
        LAG(summary) OVER (ORDER by bucket)
    )).*
FROM (
    SELECT
        time_bucket('1 min'::interval, ts) AS bucket,
        state_agg(ts, state) AS summary
    FROM states_test
    GROUP BY time_bucket('1 min'::interval, ts)
) t;
bucket         | state |       start_time       |        end_time
------------------------+-------+------------------------+------------------------
 2020-01-01 00:00:00+00 | START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00
 2020-01-01 00:00:00+00 | OK    | 2020-01-01 00:00:11+00 | 2020-01-01 00:15:00+00
 2020-01-01 00:01:00+00 | ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00
 2020-01-01 00:01:00+00 | OK    | 2020-01-01 00:01:03+00 | 2020-01-01 00:16:00+00
 2020-01-01 00:02:00+00 | STOP  | 2020-01-01 00:02:00+00 | 2020-01-01 00:17:00+00
into_values(
  agg StateAgg
) RETURNS (TEXT, INTERVAL)


into_int_values(
  agg StateAgg
) RETURNS (INT, INTERVAL)

将状态聚合解包为一组包含两列的行，显示每个状态的持续时间。默认情况下，列名为 state 和 duration。您可以按照重命名表相同的方法重命名它们。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
返回
列类型描述
stateTEXT, BIGINT在状态聚合中找到的状态
durationINTERVAL在该状态中花费的总时间
示例

从表 states_test 创建一个状态聚合。时间列名为 time，state 列包含对应于系统不同状态的文本值。使用 into_values 显示状态聚合中的数据。

SELECT state, duration FROM into_values(
  (SELECT state_agg(time, state) FROM states_test)
);
state | duration
------+----------
ERROR | 00:00:03
OK    | 00:01:46
START | 00:00:11
state_at(
  agg StateAgg,
  ts TIMESTAMPTZ
) RETURNS TEXT


state_at_int(
  agg StateAgg,
  ts TIMESTAMPTZ
) RETURNS BIGINT

给定一个状态聚合，确定给定时间的状态。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
tsTIMESTAMPTZ获取状态的时间。
返回
列类型描述
stateTEXT, BIGINT给定时间的状态。
示例

创建一个状态聚合并确定特定时间的状态

SELECT state_at(
  (SELECT state_agg(ts, state) FROM states_test),
  '2020-01-01 00:00:05+00'
);
state_at
----------
START
state_periods(
    agg StateAgg,
    state [TEXT | BIGINT]
) RETURNS (TIMESTAMPTZ, TIMESTAMPTZ)

给定一个状态聚合和特定状态，列出系统处于该状态的时间段。时间段由开始时间和结束时间定义。

如果您有多个状态聚合，并且需要在时间间隔边界处插值状态，请使用 interpolated_state_periods。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合。
stateTEXT, BIGINT要获取数据的目标状态。
返回
列类型描述
start_timeTIMESTAMPTZ状态开始时间（包含）
end_timeTIMESTAMPTZ状态结束时间（不包含）
示例

创建一个状态聚合并列出所有与状态 OK 对应的时间段

SELECT start_time, end_time FROM state_periods(
  (SELECT state_agg(ts, state) FROM states_test),
  'OK',
);
start_time       |        end_time
------------------------+------------------------
 2020-01-01 00:00:11+00 | 2020-01-01 00:01:00+00
 2020-01-01 00:01:03+00 | 2020-01-01 00:02:00+00
state_timeline(
    agg StateAgg
) RETURNS (TEXT, TIMESTAMPTZ, TIMESTAMPTZ)


state_int_timeline(
    agg StateAgg
) RETURNS (BIGINT, TIMESTAMPTZ, TIMESTAMPTZ)

获取所有状态的状态，显示每次进入和退出状态的时间。

如果您有多个状态聚合，并且需要在时间间隔边界处插值状态，请使用 interpolated_state_timeline。

必需参数
名称类型描述
aggStateAgg从中获取状态的聚合
返回
列类型描述
stateTEXT, BIGINT在状态聚合中找到的状态
start_timeTIMESTAMPTZ状态开始时间（包含）
end_timeTIMESTAMPTZ状态结束时间（不包含）
示例

从状态聚合中获取状态历史记录

SELECT state, start_time, end_time
  FROM state_timeline(
    (SELECT state_agg(ts, state) FROM states_test)
  );
state |       start_time       |        end_time
-------+------------------------+------------------------
 START | 2020-01-01 00:00:00+00 | 2020-01-01 00:00:11+00
 OK    | 2020-01-01 00:00:11+00 | 2020-01-01 00:01:00+00
 ERROR | 2020-01-01 00:01:00+00 | 2020-01-01 00:01:03+00
 OK    | 2020-01-01 00:01:03+00 | 2020-01-01 00:02:00+00
 STOP  | 2020-01-01 00:02:00+00 | 2020-01-01 00:02:00+00
rollup(
    agg StateAgg
) RETURNS StateAgg

将多个状态聚合合并为一个状态聚合。例如，您可以使用 rollup 将 15 分钟桶中的状态聚合合并到每日桶中。

必需参数
名称类型描述
aggStateAgg使用 state_agg 创建的状态聚合
返回
列类型描述
aggStateAgg结合输入状态聚合的新状态聚合
示例

合并多个状态聚合并计算在 START 状态中花费的持续时间

WITH buckets AS (SELECT
    time_bucket('1 minute', ts) as dt,
    state_agg(ts, state) AS sa
FROM states_test
GROUP BY time_bucket('1 minute', ts))
SELECT duration_in(
    'START',
    rollup(buckets.sa)
)
FROM buckets;

关键词

状态跟踪超级函数工具包

此页面有问题？报告问题 或在 GitHub 上编辑此页面。

上一页compact_state_agg下一页heartbeat_agg

相关内容

heartbeat_agg()
heartbeat_agg() 函数的 API 参考
compact_state_agg()
compact_state_agg() 函数的 API 参考
超级函数
Timescale Cloud 中可用的超级函数的完整列表，包含必需参数、返回值和完整使用示例
降采样函数
降采样函数的 API 参考
days_in_month()
根据 timestamptz 计算月份中的天数
month_normalize()
根据月份天数标准化月度指标