How do I create pre-downsampling rules - Lindorm - Alibaba Cloud Documentation Center

This topic describes the syntax used to create pre-downsampling rules based on which pre-downsampled data is generated.

Applicable engines and versions

The CREATE PREDOWNSAMPLE syntax is applicable only to LindormTSDB. The CREATE PREDOWNSAMPLE syntax is supported by all versions of LindormTSDB.

Syntax

create_predownsample_statement ::= CREATE PREDOWNSAMPLE time_interval
                                    AGGREGATORS '('function_identifier ( ',' function_identifier) ')'
                                    [ TTL time_interval ] ON table_identifier
time_interval                  ::= interval units

Usage notes

Parameter	Description
interval	The interval at which time series data is aggregated. The value of this parameter is a positive integer.
units	The unit of the interval. Valid values: s (seconds), m (minutes), h (hours), and d (days).
AGGREGATORS	Aggregation functions supported in pre-downsampling. Valid values: count: returns the total number of data points. first: returns the first value. last: returns the last value. min: returns the minimum value. max: returns the maximum value. sum: returns the sum of the values. Note The avg function is not supported in pre-downsampling. You can calculate the average value of pre-downsampled data by using the results of the count and sum functions. However, if data is repeatedly added and overwritten, the average value may be inaccurate.
function_identifier	The name of the aggregation function. Note You can specify one or more aggregation functions with the same interval in a single statement. You can also specify multiple aggregation functions in multiple statements.
TTL	The time to live (TTL) of the pre-downsampled data. Note If you specify different TTLs in multiple pre-downsampling rules, the largest TTL apply to all pre-downsampling rules.
table_identifier	The name of the table for which you want to configure pre-downsampling rules.

Query pre-downsampled data

By default, when you query pre-downsampled data, the original data before pre-downsampling is queried. To query only pre-downsampled data, you must add the /*+ PREDOWNSAMPLE */ hint in the query statements. For more information about pre-downsampling, see Pre-downsampling.

Examples

Create a pre-downsampling rule and set the TTL of pre-downsampled data to 90 days.
```
CREATE PREDOWNSAMPLE `1h` AGGREGATORS (`sum`, `max`) TTL `90d` ON sensor;
```

Query data that is pre-downsampled on an hourly basis.

SELECT /*+ PREDOWNSAMPLE */ SUM(temperature) FROM sensor
WHERE time >= 1619076780000 AND time <= 1619076800000
SAMPLE BY 1h;