TimescaleDB API 参考超函数状态跟踪

state_agg()函数

工具包TimescaleDB 工具包函数在 Timescale 社区版中可用。它们会自动包含在 Timescale 中，但必须为自托管的 TimescaleDB 单独安装。点击了解更多。

介绍

给定一个在离散状态之间切换的系统或值，跟踪状态之间的转换。例如，您可以使用 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

将状态数据聚合到状态聚合中以跟踪状态转换。与 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 epoch

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 epoch

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 参考
超函数
使用超函数简化数据分析
下采样函数
下采样函数的 API 参考
days_in_month()
计算给定 timestamptz 的月份中的天数
month_normalize()
根据月份的天数标准化每月指标