Functions and Expressions

Time Series Calculation Functions

delta(field numeric)

Calculates the difference between two rows sorted by time.

• 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.

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.

• This function is used to calculate the increment of each counter sorted by time.
• If there are fewer than two tuples in each group, the returned result is 0. Do not use this function with the OVER window function.

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

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.

• 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.

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

value_of_percentile(column float, percentile float, compression float)

Returns percentile values for a specified column in ascending order. It result is an approximation of percentile_cont, but the function achieves better performance than percentile_cont.

percentile_of_value(column float, percentilevalue float, compression float)

Returns percentiles in ascending order for a given column. This function is the inverse function of value_of_percentile.

first(column1, column2)

Aggregate Functions Compare the values of column2 in a group, find the minimum value, and output the value of column1.

Example (the table definition and data in the time_fill expression is used):

Obtain the first idle value in time order in each group based on scope_name.

1
2
3
4
5
6

select first(idle, time_string) from dcs_cpu group by scope_name;
first
-------
1
3
(2 rows)

last(column1, column2)

Aggregate Functions Compare the values of column2 in a group, find the maximum value, and output the corresponding value of column1.

Example (the table definition and data in the time_fill expression is used):

Obtain the last idle value in time order in each group based on scope_name.

1
2
3
4
5
6

select last(idle, time_string) from dcs_cpu group by scope_name;
last
------
2
3
(2 rows)

get_timeline_count_internal(schema_name text, rel_name text)

Obtains the number of rows in the tag table of the time series table on the current DN. This function can be used only on DNs.

Example:

Create a table and insert data.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE IF NOT EXISTS CPU(
scope_name text TSTag,
server_ip text TSTag,
group_path text TSTag,
time timestamptz TSTime,
idle numeric TSField
) with (orientation=TIMESERIES) distribute by hash(scope_name);
insert into CPU values('dcxtataetaeta','10.145.255.33','saetataetaeta','2020-04-07 17:12:09+08',  60639);
insert into CPU values('wrhtataetaeta','10.145.255.33','saetataetaeta','2020-04-07 17:12:09+08',  53311);
insert into CPU values('saetataetaeta','10.145.255.33','saetataetaeta','2020-04-07 17:12:09+08',  27101);
insert into CPU values('saetataetaeta','10.145.255.33','saetataetaeta','2020-04-07 17:12:09+08',  48005);

After data is transferred from the delta table to CU, connect to the DN and execute the following function:

1
2
3
4
5

select get_timeline_count_internal('public', 'cpu');
get_timeline_count_internal
-----------------------------
2
(1 row)

get_timeline_count(relname regclass)

Obtains the number of rows in the tag table of the time series table on each DN. This function can be used only on CNs.

Example:

Table creation and data import are the same as those of the get_timeline_count_internal function. Connect to the CN and execute the function:

1
2
3
4
5
6

select get_timeline_count('cpu');
get_timeline_count
--------------------
(dn_1,2)
(dn_2,1)
(2 rows)

gs_clean_tag_relation(tagOid oid)

This function is used to delete useless data in the row corresponding to a tagid in a tag table. Data in the primary table is cleared during automatic partition elimination. However, if a tag table is used for a long time, there may be some obsolete data in it You can invoke this function to clear the row data in the tag table to improve the utilization rate of the tag table. The returned value is the number of rows that are successfully deleted from the tag table.

Parameter description

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TABLE IF NOT EXISTS CPU(
scope_name text TSTag,
server_ip text TSTag,
group_path text TSTag,
time timestamptz TSTime,
idle numeric TSField,
system numeric TSField,
util numeric TSField,
vcpu_num numeric TSField,
guest numeric TSField,
iowait numeric TSField,
users numeric TSField) with (orientation=TIMESERIES) distribute by hash(scope_name);
SELECT oid FROM PG_CLASS WHERE relname='cpu';
  oid
-------
 19099
(1 row)
SELECT gs_clean_tag_relation(19099);
 gs_clean_tag_relation
-----------------------
                     0
(1 row)

ts_table_part_policy_pgjob_to_pgtask(schemaName text, tableName text)

This function is used to migrate partition management tasks of a time series table. It is used only when the time series table is upgraded along with the cluster upgrade from 8.1.1 to 8.1.3. In version 8.1.1, the partition management tasks of time series tables are in the pg_jobs table, while in version 8.1.3, these tasks are in the pg_task table. Therefore, during the cluster upgrade from version 8.1.1 to version 8.1.3, the partition management tasks need to be migrated from pg_jobs to pg_task. This function migrates only the time series table partition management tasks. After the migration is complete, the status of the original tasks in pg_jobs table are changed to broken.

Example:

CALL ts_table_part_policy_pgjob_to_pgtask('public','cpu1');
WARNING:  The job on pg_jobs is migrated to pg_task, and the original job is broken, the job what is call proc_drop_partition('public.cpu1', interval '7 d'); , the job interval is interval '1 day'.
WARNING:  The job on pg_jobs is migrated to pg_task, and the original job is broken, the job what is call proc_add_partition('public.cpu1', interval '1 d'); , the job interval is interval '1 day'.
ts_table_part_policy_pgjob_to_pgtask

----------------------------------

(1 row)

proc_part_policy_pgjob_to_pgtask()

This function is used only when the time series table is upgraded along with the cluster upgrade from version 8.1.1 to version 8.1.3. It is used to migrate the partition management tasks of all time series tables in the 8.1.1 version database. This function traverses all time series tables in the database and checks whether the partition management tasks of a time series table are migrated. If the tasks are not migrated, the ts_table_part_policy_pgjob_to_pgtask function is invoked to migrate them. If the migration fails, the entire system is rolled back.

Example:

CALL proc_part_policy_pgjob_to_pgtask();
NOTICE:  find table, name is cpu1, namespace is public.
WARNING:  The job on pg_jobs is migrated to pg_task, and the original job is broken, the job what is call proc_drop_partition('public.cpu1', interval '7 d'); , the job interval is interval '1 day'.
CONTEXT:  SQL statement "call ts_table_part_policy_pgjob_to_pgtask('public', 'cpu1');"
PL/pgSQL function proc_part_policy_pgjob_to_pgtask() line 17 at EXECUTE statement
WARNING:  The job on pg_jobs is migrated to pg_task, and the original job is broken, the job what is call proc_add_partition('public.cpu1', interval '1 d'); , the job interval is interval '1 day'.
CONTEXT:  SQL statement "call ts_table_part_policy_pgjob_to_pgtask('public', 'cpu1');"
PL/pgSQL function proc_part_policy_pgjob_to_pgtask() line 17 at EXECUTE statement
NOTICE:  find table, name is cpu2, namespace is public.
WARNING:  The job on pg_jobs is migrated to pg_task, and the original job is broken, the job what is call proc_add_partition('public.cpu2', interval '1 d'); , the job interval is interval '1 day'.
CONTEXT:  SQL statement "call ts_table_part_policy_pgjob_to_pgtask('public', 'cpu2');"
PL/pgSQL function proc_part_policy_pgjob_to_pgtask() line 17 at EXECUTE statement
proc_part_policy_pgjob_to_pgtask
--------------------------------------

(1 row)

print_sql_part_policy_pgjob_to_pgtask()

This function is used only when the time series tables are upgraded along with the cluster upgrade from version 8.1.1 to version 8.1.3. This function is used to print SQL statements that can be used to migrate the partition management tasks of a time series table. The migration granularity of proc_part_policy_pgjob_to_pgtask function is at the database level. But you can manually execute the statements printed by the proc_part_policy_pgjob_to_pgtask function to implement the table-level migration granularity.

Example:

CALL print_sql_part_policy_pgjob_to_pgtask();
call ts_table_part_policy_pgjob_to_pgtask('public', 'cpu1');
call ts_table_part_policy_pgjob_to_pgtask('public', 'cpu2');
print_sql_part_policy_pgjob_to_pgtask
---------------------------------------

(1 row)