Two-step aggregation

First, create an intermediate aggregation by using aggregate functions instead of calculating the final result in one step.Then, calculate the final result using analytic functions.

社区版

企业版

stats_agg

Perform linear regression analysis on two-dimensional data, such as calculating correlation coefficients and covariance. And you can also separately calculate the common statistical data of each dimension, aggregate the data into an intermediate statistical aggregation form for further calculation.

tip

Aggregation will only occur when both x and y are not empty.

stats_agg(y, x)

Options	Description
y	The y values in the dataset to be used for statistical aggregation.
x	The x values in the dataset to be used for statistical aggregation.

View stats_agg Example

Sample Data.

SELECT * FROM test_stats;
+-------------------------------+---+---+
| time                          | x | y |
+-------------------------------+---+---+
| 1970-01-01T00:00:00.000000001 | 1 | 1 |
| 1970-01-01T00:00:00.000000002 | 1 | 2 |
| 1970-01-01T00:00:00.000000003 | 1 | 3 |
| 1970-01-01T00:00:00.000000004 | 1 | 4 |
| 1970-01-01T00:00:00.000000005 | 1 | 5 |
| 1970-01-01T00:00:00.000000006 | 2 | 1 |
| 1970-01-01T00:00:00.000000007 | 2 | 2 |
| 1970-01-01T00:00:00.000000008 | 2 | 3 |
| 1970-01-01T00:00:00.000000009 | 2 | 4 |
| 1970-01-01T00:00:00.000000010 | 2 | 5 |
+-------------------------------+---+---+

Use stats_agg to aggregate results.

SELECT stats_agg(y, x) FROM test_stats;
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stats_agg(test_stats.y,test_stats.x)                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {n: 10, sx: 15.0, sx2: 2.5, sx3: -2.7755575615628914e-16, sx4: 0.6249999999999999, sy: 30.0, sy2: 20.0, sy3: -1.7763568394002505e-15, sy4: 68.0, sxy: 0.0} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above results returned a result set, with explanations for each return value respectively:

{ 
  n:   bigint, -- count 
  sx:  double, -- sum(x)- sum(x)
  sx2: double, -- sum((x-sx/n)^2) (sum of squares)
  sx3: double, -- sum((x-sx/n)^3)
  sx4: double, -- sum((x-sx/n)^4)
  sy:  double, -- sum(y)
  sy2: double, -- sum((y-sy/n)^2) (sum of squares)
  sy3: double, -- sum((y-sy/n)^3)
  sy4: double, -- sum((y-sy/n)^4)
  sxy: double, -- sum((x-sx/n)*(y-sy/n)) (sum of products) 
}

stats_agg supports the following functions for two-step aggregation

Function	Description
num_vals	Calculate the number of values in a two-dimensional statistical aggregate.
average_y, average_x	Calculate the average from a two-dimensional statistical aggregate for the dimension specified.
sum_y, sum_x	Calculate the sum from a two-dimensional statistical aggregate for the dimension specified.
stddev_samp_y, stddev_samp_x	Calculate the standard deviation of the specified dimension after two-dimensional statistical aggregation, with the method being sample.
stddev_pop_y, stddev_pop_x	Calculate the standard deviation from a two-dimensional statistical aggregate for the dimension specified, method is population.
var_samp_y, var_samp_x	Calculate the variance of the specified dimension after two-dimensional statistical aggregation, with the method being sample.
var_pop_y, var_pop_x	Calculate the variance from a two-dimensional statistical aggregate for the dimension specified, method is population.
skewness_samp_y, skewness_samp_x	Calculate the skewness value of the specified dimension after two-dimensional statistical aggregation, with the method being sample.
skewness_pop_y, skewness_pop_x	Calculate the skewness value of the specified dimension after two-dimensional statistical aggregation, method is population.
kurtosis_samp_y, kurtosis_samp_x	Calculate the kurtosis value of the specified dimension after two-dimensional statistical aggregation, with the method being sample.
kurtosis_pop_y, kurtosis_pop_x	Calculate the kurtosis value of the specified dimension after aggregating two-dimensional statistics, method is population.
correlation	Calculate the correlation after two-dimensional statistical aggregation.
covariance_samp, covariance_pop	Calculate the covariance after two-dimensional statistical aggregation.
determination_coeff	Calculate the determination coefficient after two-dimensional statistical aggregation.
slope	Calculate the slope of the linear regression line based on two-dimensional statistical aggregation.
intercept	Calculate the intercept of y after two-dimensional statistical aggregation.
x_intercept	Calculate the intercept of x after two-dimensional statistical aggregation.

View Sample

SELECT stddev_samp_x(stats_agg(y, x)) FROM test_stats;
+-----------------------------------------------------+
| stddev_samp_x(stats_agg(test_stats.y,test_stats.x)) |
+-----------------------------------------------------+
| 0.5270462766947299                                  |
+-----------------------------------------------------+

gauge_agg

Analyze Gauge data.Unlike Counter, Gauge can decrease as well as increase.

gauge_agg(time, numeric_expression)

Options	Description
numeric_expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.

View gauge_agg Example

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 supports the following functions for two-step aggregation

Function	Description
delta	Obtain the changes of Gauge within a period of time.This is a simple increment, calculated by subtracting the last seen value from the first one.
time_delta	Get the duration, subtract the time of the last Gauge from the time of the first Gauge.
rate	Calculate the ratio of Gauge changes to time changes.
first_time	Get the minimum timestamp in the Gauge.
last_time	Get the maximum timestamp in the Gauge.
first_val	Get the value corresponding to the minimum timestamp in the Gauge.
last_val	Get the value corresponding to the maximum timestamp in the Gauge.
idelta_left	Calculate the earliest instantaneous change of Gauge.This is equal to the second value minus the first value.
idelta_right	Calculate the latest instantaneous change of Gauge.This is equal to the last value minus the penultimate value.

compact_state_agg

Given a system or value that switches between discrete states, summarize the time spent in each state.For example, you can use the compact_state_agg function to track the time the system spends in error, running, or starting states.

compact_state_agg(time_expression, state)

Options	Description
time_expression	The time expression to operate.Can be a constant, column, or function, and any combination of arithmetic operators.

View compact_state_agg Example

The example dataset is as follows:

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 |
+---------------------+----------+

Aggregate using the compact_state_agg function:

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: []} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above example aggregates the state data together for further analysis, compact_state_agg supports the following two-step aggregation functions:

Function	Description
duration_in	Calculate the duration of a certain state, or calculate the duration of a certain state within a specific time period.

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 

Options	Description
state_agg_data	Result set returned by the state_agg_data function.
state	The state type of any is the same as compact_state_agg.
begin_time	Optional, specifies the start time within the specified time period.
interval_time	Optional, specifies the duration of the time period, when not specified, the time period is infinite.

View duration_in Example

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

Given a system or value that switches between discrete states, track transitions between states.

state_agg(time_expression, state)

Calculate the time spent in each state.

View state_agg Example

The example dataset is as follows:

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 |
+---------------------+----------+

Use state_agg function to aggregate:

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}]}]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above example aggregates the state data together for further analysis, state_agg supports the following two-step aggregation functions:

Function	Description
duration_in	Calculate the duration of a certain state, or calculate the duration of a certain state within a specific time period.
state_at	Statistics are in a state of affairs at a time.

duration_in

duration_in(state_agg_data, state [,begin_time, interval_time]) 

Options	Description
state_agg_data	Result set returned by the state_agg function.
state	The state type of any is the same as compact_state_agg.
begin_time	Optional, specifies the start time within the specified time period.
interval_time	Optional, specifies the duration of the time period, when not specified, the time period is infinite.

View duration_in Example

**Calculate the duration of the 'running' status.

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           |
+------------------------------------------------------------------+

Calculate the duration of the 'running' state starting from '2020-01-01 11:00:00'.

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                                      |
+----------------------------------------------------------------------------------------------+

Calculate the duration of the 'running' state starting from 2020-01-01 11:00:00 within four days.

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)

Options	Description
state_agg_data	Result set returned by the state_agg function.
time_expression	The time expression to operate.Can be a constant, column, or function, and any combination of arithmetic operators.

View state_at Example

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                                                                   |
+---------------------------------------------------------------------------+

社区版

企业版

candlestick_agg

Conduct financial asset data analysis, can obtain the opening and closing prices of stocks, as well as the lowest and highest prices.

candlestick_agg(time, price, volume)

View candlestick_agg Example

The example dataset is as follows:

CREATE TABLE IF NOT EXISTS tick(price bigint ,volume bigint);
INSERT tick(time, price, volume) VALUES('1999-12-31 00:00:00.000', 111, 444),('1999-12-31 00:00:00.005', 222, 444),('1999-12-31 00:00:00.010', 333, 222),('1999-12-31 00:00:10.015', 444, 111),('1999-12-31 00:00:10.020', 222, 555),('1999-12-31 00:10:00.025', 333, 555),('1999-12-31 00:10:00.030', 444, 333),('1999-12-31 01:00:00.035', 555, 222);
SELECT * FROM tick;
+-------------------------+-------+--------+
| time                    | price | volume |
+-------------------------+-------+--------+
| 1999-12-31T00:00:00     | 111   | 444    |
| 1999-12-31T00:00:00.005 | 222   | 444    |
| 1999-12-31T00:00:00.010 | 333   | 222    |
| 1999-12-31T00:00:10.015 | 444   | 111    |
| 1999-12-31T00:00:10.020 | 222   | 555    |
| 1999-12-31T00:10:00.025 | 333   | 555    |
| 1999-12-31T00:10:00.030 | 444   | 333    |
| 1999-12-31T01:00:00.035 | 555   | 222    |
+-------------------------+-------+--------+

Use candlestick_agg for aggregation.

SELECT candlestick_agg(time, price, volume) FROM tick;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| candlestick_agg(tick.time,tick.price,tick.volume)                                                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {open: {ts: 1999-12-31T00:00:00, val: 111.0}, close: {ts: 1999-12-31T01:00:00.035, val: 555.0}, low: {ts: 1999-12-31T00:00:00, val: 111.0}, high: {ts: 1999-12-31T01:00:00.035, val: 555.0}, volume: {vol: 2886.0, vwap: 850149.0}} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

You can extract the opening price, closing price, etc. separately in the above example.Supported functions include:

Function	Description
close	Closing price.
close_time	Closing time.
high	High price.
high_time	High price time.
low	Low price.
low_time	Low price time.
open	Opening price.
open_time	Opening time.
volume	Total cumulative.
vwap	Volume Weighted Average Price.

View example

SELECT close(candlestick_agg(time,price,volume)) AS close_price FROM tick;
+-------------+
| close_price |
+-------------+
| 555.0       |
+-------------+