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

Time Series Function

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 95^th 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/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.
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/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);

Parent topic: Usage

Last Article: Partition Management

Next Article: Support and Constraints

Did this article solve your problem?

Thank you for your score！Your feedback would help us improve the website.

Products

Compute

Application

Dedicated Cloud

Storage

Management & Deployment

Migration

Network

Enterprise Intelligence

Video

Database

Edge Cloud Services

DevCloud

Security

Cloud Communications

Internet of Things

Solutions

Industry-Specific Solutions

General-Purpose Solutions

Security

DevOps

Enterprise Intelligence

Essential Platform

Big Data

Visual Cognition

Speech and Semantics

Support

Help Center

Customer Services

Developers

Console

语言 - Language

中国站 - 简体中文

中国站 - English

International - 简体中文

International - English

Help Center

Time Series Function

Time Series Calculation Functions

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

fill_last(value anyelement)

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

first(value_array anyarray, time_array timearray)

last(value_array anyarray, time_array timearray)

percentile_cont(percentile float)

fill(value anyelement)

top_k(array real[],k int)

bottom_k(array real[],k int)

delta(field numeric) over(rows 1 preceding)

spread(field numeric)