跳到主要内容
版本:2.4.x

两步聚合

首先,通过使用聚合函数创建一个中间聚合,而不是在一步中计算最终结果。然后,使用分析函数计算最终结果。

gauge_agg

分析 Gauge数据。与 Counter 不同,Gauge可以减少也可以增加。

gauge_agg(time, numeric_expression)
选项描述
numeric_expression要操作的数字表达式。可以是常量、列或函数,以及算术运算符的任意组合。
查看 gauge_agg 示例
SELECT gauge_agg(time, pressure) FROM air GROUP BY date_trunc('month', time);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gauge_agg(air.time,air.pressure) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {first: {ts: 2023-03-01T00:00:00, val: 54.0}, second: {ts: 2023-03-01T00:00:00, val: 59.0}, penultimate: {ts: 2023-03-14T16:00:00, val: 55.0}, last: {ts: 2023-03-14T16:00:00, val: 80.0}, num_elements: 13122} |
| {first: {ts: 2023-02-01T00:00:00, val: 60.0}, second: {ts: 2023-02-01T00:00:00, val: 54.0}, penultimate: {ts: 2023-02-28T23:57:00, val: 74.0}, last: {ts: 2023-02-28T23:57:00, val: 59.0}, num_elements: 26880} |
| {first: {ts: 2023-01-14T16:00:00, val: 63.0}, second: {ts: 2023-01-14T16:00:00, val: 68.0}, penultimate: {ts: 2023-01-31T23:57:00, val: 54.0}, last: {ts: 2023-01-31T23:57:00, val: 77.0}, num_elements: 16640} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

gauge_agg 支持以下二次聚合的函数

函数描述
delta获取一段时间内Gauge的变化。这是简单的增量,通过从第一个值减去最后一个看到的值来计算。
time_delta获取持续时间,最后一个 Gauge 的时间减去第一个 Gauge 的时间。
rate计算 Gauge 变化和时间变化的比率。
idelta_left计算 Gauge 最早的瞬时变化。这等于第二个值减去第一个值。
idelta_right计算 Gauge 最晚的瞬时变化。这等于最后一个值值减去倒数第二个值。

compact_state_agg

给定一个在离散状态之间切换的系统或值,汇总每个状态所花费的时间。例如,您可以使用compact_state_agg函数来跟踪系统在errorrunningstarting状态下花费的时间。

compact_state_agg(time_expression, state)
选项描述
time_expression要操作的时间表达式。可以是常量、列或函数,以及算术运算符的任意组合。
查看 compact_state_agg 示例

示例数据集如下:

CREATE TABLE states(state STRING);
INSERT INTO states VALUES ('2020-01-01 10:00:00', 'starting'),('2020-01-01 10:30:00', 'running'),('2020-01-03 16:00:00', 'error'),('2020-01-03 18:30:00', 'starting'),('2020-01-03 19:30:00', 'running'),('2020-01-05 12:00:00', 'stopping');
SELECT * FROM states;
+---------------------+----------+
| time | state |
+---------------------+----------+
| 2020-01-01T10:00:00 | starting |
| 2020-01-01T10:30:00 | running |
| 2020-01-03T16:00:00 | error |
| 2020-01-03T18:30:00 | starting |
| 2020-01-03T19:30:00 | running |
| 2020-01-05T12:00:00 | stopping |
+---------------------+----------+

使用 compact_state_agg 函数聚合:

SELECT compact_state_agg(time, state) FROM states;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| compact_state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}, {state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}, {state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}], state_periods: []} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

以上示例将状态数据汇总在一起,以便进一步分析,compact_state_agg 支持如下二次聚合函数:

函数描述
duration_in统计某个状态的持续时间,或统计某个状态在某个时间段内的持续时间。

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 
选项描述
state_agg_datastate_agg_data 函数返回的结果集。
stateany 与 compact_state_agg 的 state 类型相同。
begin_time可选,指定时间段内的开始时间。
interval_time可选,指定时间段的持续时间,不指定时,时间段为无穷大。
查看 duration_in 示例
SELECT duration_in(compact_state_agg(time, state), 'running') FROM states;
+--------------------------------------------------------------------------+
| duration_in(compact_state_agg(states.time,states.state),Utf8("running")) |
+--------------------------------------------------------------------------+
| 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs |
+--------------------------------------------------------------------------+

state_agg

给定一个在离散状态之间切换的系统或值,跟踪状态之间的转换。

state_agg(time_expression, state)

统计每个状态所花费的时间。

查看 state_agg 示例

示例数据集如下:

CREATE TABLE states(state STRING);
INSERT INTO states VALUES('2020-01-01 10:00:00', 'starting'),('2020-01-01 10:30:00', 'running'),('2020-01-03 16:00:00', 'error'),('2020-01-03 18:30:00', 'starting'),('2020-01-03 19:30:00', 'running'),('2020-01-05 12:00:00', 'stopping');
SELECT * FROM states;
+---------------------+----------+
| time | state |
+---------------------+----------+
| 2020-01-01T10:00:00 | starting |
| 2020-01-01T10:30:00 | running |
| 2020-01-03T16:00:00 | error |
| 2020-01-03T18:30:00 | starting |
| 2020-01-03T19:30:00 | running |
| 2020-01-05T12:00:00 | stopping |
+---------------------+----------+

使用 state_agg 函数聚合:

SELECT state_agg(time, state) FROM states;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| state_agg(states.time,states.state) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {state_duration: [{state: running, duration: 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs}, {state: error, duration: 0 years 0 mons 0 days 2 hours 30 mins 0.000000000 secs}, {state: stopping, duration: 0 years 0 mons 0 days 0 hours 0 mins 0.000000000 secs}, {state: starting, duration: 0 years 0 mons 0 days 1 hours 30 mins 0.000000000 secs}], state_periods: [{state: running, periods: [{start_time: 2020-01-01T10:30:00, end_time: 2020-01-03T16:00:00}, {start_time: 2020-01-03T19:30:00, end_time: 2020-01-05T12:00:00}]}, {state: starting, periods: [{start_time: 2020-01-01T10:00:00, end_time: 2020-01-01T10:30:00}, {start_time: 2020-01-03T18:30:00, end_time: 2020-01-03T19:30:00}]}, {state: error, periods: [{start_time: 2020-01-03T16:00:00, end_time: 2020-01-03T18:30:00}]}]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

以上示例将状态数据汇总在一起,以便进一步分析,state_agg 支持如下二次聚合函数:

函数描述
duration_in统计某个状态的持续时间,或统计某个状态在某个时间段内的持续时间。
state_at统计一时刻所处的状态。

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 
选项描述
state_agg_datastate_agg 函数返回的结果集。
stateanycompact_state_aggstate 类型相同。
begin_time可选,指定时间段内的开始时间。
interval_time可选,指定时间段的持续时间,不指定时,时间段为无穷大。
查看 duration_in 示例

**统计 'running' 状态的持续时间。

SELECT duration_in(state_agg(time, state), 'running') FROM states;
+------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running")) |
+------------------------------------------------------------------+
| 0 years 0 mons 3 days 22 hours 0 mins 0.000000000 secs |
+------------------------------------------------------------------+

统计从 '2020-01-01 11:00:00' 开始 'running' 状态的持续时间。

SELECT duration_in(state_agg(time, state), 'running', Timestamp '2020-01-01 11:00:00') FROM states;
+----------------------------------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running"),Utf8("2020-01-01 11:00:00")) |
+----------------------------------------------------------------------------------------------+
| 0 years 0 mons 3 days 21 hours 30 mins 0.000000000 secs |
+----------------------------------------------------------------------------------------------+

统计 从2020-01-01 11:00:00 开始的四天内 'running' 状态的持续时间。

SELECT duration_in(state_agg(time, state), 'running', Timestamp '2020-01-01 11:00:00', interval '4 day') FROM states;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| duration_in(state_agg(states.time,states.state),Utf8("running"),Utf8("2020-01-01 11:00:00"),IntervalMonthDayNano("73786976294838206464")) |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 0 years 0 mons 3 days 20 hours 30 mins 0.000000000 secs |
+-------------------------------------------------------------------------------------------------------------------------------------------+

state_at

state_at(state_agg_data, time_expression)
选项描述
state_agg_datastate_agg 函数返回的结果集。
time_expression要操作的时间表达式。可以是常量、列或函数,以及算术运算符的任意组合。
查看 state_at 示例
SELECT state_at(state_agg(time, state), Timestamp '2020-01-01 10:30:00') FROM states;
+---------------------------------------------------------------------------+
| state_at(state_agg(states.time,states.state),Utf8("2020-01-01 10:30:00")) |
+---------------------------------------------------------------------------+
| running |
+---------------------------------------------------------------------------+