All Products
Search
Document Center

Lindorm:CREATE CONTINUOUS QUERY

Last Updated:Aug 24, 2022

This topic describes the SQL syntax that is used to create a continuous query.

Syntax

create_cq_statement ::= CREATE CONTINUOUS QUERY [database_name.] cq_name WITH (  cq_attribute_statement ) AS insert_select_statement

cq_attribute_statement ::= INTERVAL 'interval_string'

Parameters

Parameter

Description

database_name

The name of the database in which you want to perform a continuous query on objects. If you do not specify this parameter, the name of the database that you use is specified.

The specified name must be an SQL constant or an SQL identifier.

cq_name

The name of the continuous query.

interval_string

The time interval at which the continuous query performs calculation. The specified value can be accurate to the second. The specified value must be in the following format:

%d%h%m%s. For example, the value 1h30s specifies that your continuous query performs calculation at a time interval of 1 hour and 30 seconds, and the value 1d specifies that your continuous query performs calculation at a time interval of 1 day.

The following list describes d, h, m, and s:

  • d: days

  • h: hours

  • m: minutes

  • s: seconds

Notice

  1. For information about the syntax of insert_select_statement, see SQL syntax for writing data.

  2. When you specify insert_select_statement, you do not need to specify a time range for your query. If you do not specify a time range for your query, LindormTSDB performs calculation during the interval when you run the query.

Example

The following sample code provides an example on how to create a continuous query that calculates average temperature and average humidity based on the data of the sensor table in the db_sensor database at an interval of one day and inserts the average values into the sensor table in the db_sensor_year database:

CREATE CONTINUOUS QUERY `db_sensor`.`my_cq` WITH (INTERVAL='1d') AS 
INSERT INTO `db_sensor_year`.`sensor` 
SELECT MEAN(`temperature`) AS `temperature`, 
       MEAN(`humidity`) AS `humidity`, `device_id`, `region` 
FROM  `db_sensor`.`sensor`;