Help Center > > Developer Guide

Time Series Function

Updated at: Jul 14, 2021 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 in forward search mode.

fill_last()

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

fill_first()

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

fill_avg()

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()

Returns N random values for a given column.

sample()

Returns the value with the highest occurrence frequency for a given column. If multiple values have the same frequency, this function returns the smallest value among these values.

mode()

Returns the trapezoidal area integral for a given column at a given time.

integral()

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 latest timestamp. If all the previous timestamps are empty, no timestamp 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;

fill_first(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 next timestamp. If all subsequent timestamps are empty, no timestamp is supplemented.

Table 4 Parameter description

Parameter

Type

Description

Required/Optional

value

anyelement

Value to be supplemented backward

Required

  • fill_first 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_first(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;

fill_avg(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 search for the latest aggregate value bidirectionally. If all previous timestamps are empty, the subsequent timestamp is used for supplement. If all subsequent timestamp is empty, the previous timestamp is used for supplement. If neither of the timestamps is empty, the average value of the two timestamps is used for supplement. If both of the timestamps are empty, the error "hash table content fill error" is reported.

Table 5 Parameter description

Parameter

Type

Description

Required/Optional

value

anyelement

Supplemented value of the average value

Required

  • fill_avg and time_fill must be used together.
  • This function cannot be used together with window functions or the WITHIN GROUP clause.
  • The parameter type of fill_avg must be int2, int4, int8, or NUMERIC.
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_avg(min(value))
FROM
    (VALUES ('2019-07-12 00:00:00'::timestamptz, 'red', 1),
            ('2019-07-12 00:01:00'::timestamptz,'blue', 3)) v(time,color, value)
GROUP BY fill_time,color;

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 6 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 7 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 8 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 9 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 each group of empty data and supplement the empty values in the existing data.

Table 10 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 11 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 12 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)

Calculates the difference between two rows sorted by time.

Table 13 Parameter description

Parameter

Type

Description

Required/Optional

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.
  • The delta window function needs to be used together with the over window function. In addition, the rows statement in the over statement does not change the result of the delta function. For example, the results returned by delta(value) over(order by time rows 1 preceding) and delta(value) over(order by time rows 3 preceding) are the same.
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 14 Parameter description

Parameter

Type

Description

Required/Optional

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);

sample(value_array anyarray, N int)

Returns N random values for a given column.

Table 15 Parameter description

Parameter

Type

Description

Required/Optional

value_array

anyarray

Array of queried column

Required

N

int

Number of random samples

Required

  • The value of N must be a positive number and the length cannot be greater than that of value_array.
  • This function cannot be used together with the over clause.
  • You are advised not to use this function when a large amount of data exists because it takes a long time.
Example:
SELECT
    sample(array_agg(value), 1)
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);

mode() within group (order by value anyelement)

Returns the value with the highest occurrence frequency for a given column. If multiple values have the same frequency, this function returns the smallest value among these values.

Table 16 Parameter description

Parameter

Type

Description

Required/Optional

value

anyelement

Queried column

Required

  • This function must be used together with the within group function. If the within group statement does not exist, an error is reported. This function parameter is placed after order by of the group.
  • This function cannot be used together with the over clause.
Example:
SELECT
    mode() within group (order by 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);

integral(time_array timestamptz[], value_array double precision[], unit interval)

Returns the trapezoidal area integral for a given column at a given time.

Table 17 Parameter description

Parameter

Type

Description

Required/Optional

time_array

timestamptz[]

Queried time array

Required

value_array

double precision[]

Array of queried column

Required

unit

interval

Time unit

Required

  • The length of the time array must be the same as that of the value array. Otherwise, an error is reported.
  • This function cannot be used together with the over clause.
  • You are advised not to use this function when a large amount of data exists because it takes a long time.
Example:
SELECT
    integral(array_agg(time), array_agg(value), interval '1 second')
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);

proc_add_partition

Description: Adds partitions.

Parameters: relationname name and boundaries_interval interval

Return type: void

proc_drop_partition

Description: Deletes partitions.

Parameters: relationname name, older_than interval

Return type: void

add_job_class_depend

Description: Adds the dependency of a scheduled task on the relation.

Parameters: oid, oid

Return type: void

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