Help Center > > Developer Guide> Real-Time GaussDB(DWS)> Time Series Engine> Usage> Time Series Function

Time Series Function

Updated at:Aug 06, 2020 GMT+08:00

Time Series Calculation Functions

Table 1 Functions supported by time series calculation

Description

Function

Supplements the missing time values in the time window in a group. By default, values are sorted by time.

time_fill()

Performs interpolation on the missing aggregate value in the time window in a group by using the most recently seen value.

fill_last()

Performs modulo operation on the timestamp type based on any time window size.

time_window()

Sorts a given column by time series and returns the first value.

first()

Sorts a given column by time series and returns the last value.

last()

Sorts a given column by time series and returns a percentile value.

percentile_cont()

Performs interpolation on the data that is empty in each group.

fill()

Sorts a given column by time series and returns the first K tuples.

top_k()

Sorts a given column by time series and returns the last K tuples.

bottom_k()

Calculates the difference between two rows sorted by time.

delta()

Calculates the difference between the maximum value and the minimum value in a specified period.

spread()

time_fill(window_width interval, time timestamp(tz), start timestamp(tz), finish timestamp(tz))

This function is used to supplement the missing time windows in a group to make data complete. The supplemented values are sorted by time by default.

Table 2 Parameter description

Parameter

Type

Description

Required/Optional

window_width

interval

Interval of each time window

Required

time

timestamp

Time column

Required

start

timestamp

Start time

Required

finish

timestamp

End time

Required

  • The unit of window_width can be microsecond, millisecond, second, min, hour, day, week, or a positive number. The default unit is day.
  • The time_fill function needs to be used as an aggregation function. The GROUP BY clause needs to reference its own calculation result and cannot be nested with itself. The clause cannot be called multiple times in a single query, used as a lower-layer computing node, or used with the WITHIN GROUP clause.
  • The start timestamp must be smaller than the finish timestamp, and their interval must be greater than the value of window_width.
  • All parameters cannot be null. Values of start and finish must be specified.
Example:
SELECT
    time_fill(interval '1 min', time, 
              '2019-07-11 23:59:01'::timestamptz, 
              '2019-07-12 00:03:01'::timestamptz) as fill_time
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz),
            ('2019-07-12 00:00:00'::timestamptz)) v(time)
GROUP BY fill_time;

fill_last(value anyelement)

This function is used to supplement the aggregate value corresponding to the missing time window in a group. The supplement policy is to use the aggregate value of the previous time window. If the previous time window is empty, no information is supplemented.

Table 3 Parameter description

Parameter

Type

Description

Required/Optional

value

anyelement

Value to be supplemented forward

Required

  • fill_last and time_fill must be used together.
  • This function cannot be used together with window functions or the WITHIN GROUP clause.
Example:
SELECT
    time_fill(interval '1 min', time, 
              '2019-07-11 23:59:01'::timestamptz, 
              '2019-07-12 00:03:01'::timestamptz) as fill_time,
    fill_last(min(value))
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz,1),
            ('2019-07-12 00:01:00'::timestamptz,3)) v(time,value)
GROUP BY fill_time,value;

time_window(window_width interval, time timestamp(tz), origin timestamp(tz))

This function is used to perform modulo operation on the timestamp type based on any time window size.

Table 4 Parameter description

Parameter

Type

Description

Required/Optional

window_width

interval

Time window

Required

time

timestamp(tz)

Timestamp column

Required

origin

timestamp(tz)

Original timestamp

Optional

  • The unit of window_width can be microsecond, millisecond, second, min, hour, day, week, or a positive number. The default unit is day.
  • The values of window_width and time must be of the correct type and cannot be empty.
Example:
SELECT
    time_window(interval '1 min', time),
    time_window(interval '3 min', time,  '2019-07-11 00:02:05'::timestamptz)
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz),
            ('2019-07-12 00:01:01'::timestamptz)) v(time);

first(value_array anyarray, time_array timearray)

This function is used to sort a given column by time series and return the first value.

Table 5 Parameter description

Parameter

Type

Description

Required/Optional

value_array

anyarray

Array of queried column

Required

time_array

timearray

Array of time values

Required

This function cannot be directly used for a large amount of data.

Example:
SELECT
    first(array_agg(value), array_agg(time))
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz,'c','a'),
            ('2019-07-12 00:01:00'::timestamptz,'b','a'),
            ('2019-07-12 00:02:01'::timestamptz,'a','b')) v(time, value, tag)
GROUP BY tag;

last(value_array anyarray, time_array timearray)

This function is used to sort a given column by time series and return the last value.

Table 6 Parameter description

Parameter

Type

Description

Required/Optional

value_array

anyarray

Array of queried column

Required

time_array

timearray

Array of time values

Required

This function cannot be directly used for a large amount of data.

Example:
SELECT
    last(array_agg(value), array_agg(time))
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz,'c','a'),
            ('2019-07-12 00:01:00'::timestamptz,'b','a'),
            ('2019-07-12 00:02:01'::timestamptz,'a','b')) v(time, value, tag)
GROUP BY tag;

percentile_cont(percentile float)

This function is used to sort a given column by time series and return a percentile value.

Table 7 Parameter description

Parameter

Type

Description

Required/Optional

percentile

float

Specifies the percentile. The value ranges from 0 to 1. The precision value is of the floating point type. For example, 0.95 means the 95th percentile value.

Required

  • percentile is a decimal from 0 to 1. The value is of the floating point type and does not support the percent sign (%).
  • This parameter must be used together with WITHIN GROUP (ORDER BY) to specify the column to be calculated. The column must be of the numeric type.
Example:
SELECT 
     percentile_cont(0.5) WITHIN GROUP (ORDER BY value) 
FROM 
    (VALUES ('2019-07-12 00:00:01'::timestamptz,'red',1),
    ('2019-07-12 00:00:01'::timestamptz,'blue',2)) v(time,color,value); 

fill(value anyelement)

This function is used to perform interpolation on the data that is empty in each group. Different from time_fill which is used to supplement the missing time values, fill_last is used to supplement only other values of the missing time, and fill is used to supplement the null value in the existing data.

Table 8 Parameter description

Parameter

Type

Description

Required/Optional

Value

anyelement

Value to be supplemented

Required

The fill function must be used together with the over window function.

Example:
SELECT
   time, 
   CASE WHEN value is not null THEN value else fill(value) over(partition by time) END 
FROM 
   (VALUES ('2019-07-12 00:00:00'::timestamptz, 1),
           ('2019-07-12 00:00:00'::timestamptz, NULL),
           ('2019-07-12 00:02:00'::timestamptz, 3)) v(time,value);

top_k(array real[],k int)

This function is used to sort a given column by time series and return the first K tuples.

Table 9 Parameter description

Parameter

Type

Description

Required/Optional

array

real[]

Array to be input

Required

k

int

Specified first K tuples

Required

  • The value of k is a non-negative number smaller than the value of array.
  • array cannot be empty.
  • This function cannot be directly used for a large amount of data.
Example:
SELECT
     top_k(array_agg(v), 0.5),v2
FROM 
     (VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a'),(6,'a')) v(v,v2) 
GROUP BY v2;

bottom_k(array real[],k int)

This function is used to sort a given column by time series and return the last K tuples.

Table 10 Parameter description

Parameter

Type

Description

Required/Optional

array

real[]

Array to be input

Required

k

int

Specified last K tuples

Required

  • The value of k is a non-negative number smaller than the value of array.
  • array cannot be empty.
  • This function cannot be directly used for a large amount of data.
Example:
SELECT
     bottom_k(array_agg(v), 0.5),v2
FROM 
     (VALUES (1,'a'),(2,'a'),(3,'a'),(4,'a'),(5,'a'),(6,'a')) v(v,v2) 
GROUP BY v2;

delta(field numeric) over(rows 1 preceding)

Calculates the difference between two rows sorted by time.

Table 11 Parameter description

Parameter

Type

Description

Required/Option

field

Numeric

Column to be calculated

Required

  • This function is used to calculate the interpolation between two adjacent rows sorted by time to monitor indicators such as traffic and speed.
  • This function must be used together with the window function OVER(ROWS 1 PRECEDING). However, do not use the function together with OVER (PARTITION BY TAG ROWS 1 PRECEDING) or GROUP BY. Otherwise, the result may not be desirable.
Example:
SELECT
     delta(value) over (rows 1 preceding)
FROM 
     (VALUES ('2019-07-12 00:00:00'::timestamptz, 1),('2019-07-12 00:01:00'::timestamptz, 2),('2019-07-12 00:02:00'::timestamptz, 3)) v(time,value);

spread(field numeric)

Calculates the difference between the maximum value and the minimum value in a specified period.

Table 12 Parameter description

Parameter

Type

Description

Required/Option

field

Numeric

Column to be calculated

Required

  • This function is used to calculate the increment of each counter sorted by time.
  • If there are less than two tuples in each group, the returned result is 0. Do not use this function with the OVER window function.
Example:
SELECT
     SPREAD(value)
FROM 
     (VALUES ('2019-07-12 00:00:00'::timestamptz, 1),('2019-07-12 00:01:00'::timestamptz, 2),('2019-07-12 00:02:00'::timestamptz, 3)) v(time,value);

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel