Lindorm:SELECT

Applicable engines

The SELECT syntax is applicable to LindormTable and LindormTSDB.

Limits

The SELECT syntax in Lindorm cannot be used in join queries across multiple tables.

Syntax

SELECT [hint_clause] ( select_clause | '*' )
    FROM table_name
    [ WHERE where_clause ]
    [ GROUP BY group_by_clause ]
    [ ORDER BY ordering_clause ]
    [ LIMIT integer [OFFSET integer] ] | [LIMIT integer, integer]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  column_name
                      | term
                      | function_name '(' [ selector ( ',' selector )* ] ')'
where_clause     ::=  relation ( AND|OR relation )*
relation         ::=  column_name operator term
                      '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT? NULL
group_by_clause  ::=  column_name ( ',' column_name )*
ordering_clause  ::=  column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

hint_clause::=/*+hint_items*/
hint_items::=hint_item(','hint_item)*
hint_item::=identifier('('hint_option(','hint_option)*')')
hint_option::=expression

Parameters

SELECT

The SELECT parameter specifies the columns, constants, and function expressions returned in the query results. The AS function is used to specify the temporary alias of the column in the result set.

WHERE

The WHERE parameter is used to specify query conditions.

When you specify the WHERE parameter in the statement to query data in a wide table, note the following items:

• We recommend that you specify query conditions based on the following rules to improve query efficiency.

  • Specify only the first primary key column or the first index column of the index table as query conditions.

  • If the query conditions include a column other than the first primary key column or the first index column, you must specify a query range for the first primary key column or the first index column.

  If the query conditions do not comply with the preceding rules, you must use the /*+ _l_allow_filtering_ */ hint in the statement to forcibly execute an inefficient query. For more information, see Forcibly perform an inefficient query.

• If dynamic columns are enabled for the wide table:

  • To ensure query performance, we recommend that you include primary key columns or index columns in the query conditions specified by WHERE.

  • Query conditions specified for dynamic columns in WHERE must be hex strings.

  For more information about dynamic columns, see Dynamic columns.

GROUP BY

The GROUP BY parameter is supported by LindormTable and LindormTSDB. This parameter can be specified to group and aggregate query results.

ORDER BY

The ORDER BY parameter specifies one column or multiple columns to sort query results.

LIMIT

The LIMIT parameter is used to return query results by page. If the LIMIT parameter is followed by a single digit, the digit indicates the number of returned rows on each page. If the LIMIT parameter is followed by two digits separated by a comma (,), the first digit indicates the offset and the second digit indicates the number of returned rows on each page. LIMIT integer [OFFSET integer] clause is equivalent to the LIMIT integer, integer clause.

HINT

In LindormTable and LindormTSDB, you can use hints in statements to optimize query plans or use specific query features. For more information, see Overview.

Examples

In the following examples, the sample table sensor is created by executing the following statement described in the documentation of the CREATE TABLE and UPSERT syntaxes:

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

Perform simple queries

SELECT * FROM sensor; --Select all columns in the table.
SELECT device_id,region,humidity FROM senor;  --Select parts of columns in the table.
SELECT * FROM sensor WHERE device_id = 'F07A1260' LIMIT 20 OFFSET 5; --The LIMIT clause is equivalent to LIMIT 5,20.
SELECT count(*) AS countRow FROM sensor; --Specify an alias for the column in the result.

Perform aggregate queries

• Measure the number of all data records reported by the device F07A1260 at a specific point in time.

SELECT count(*) FROM sensor WHERE device_id = 'F07A1260' AND time = '2021-04-22 15:33:00';

• Query the highest temperature of the device F07A1260 within the specified period of time.

SELECT max(temperature) AS max_temp FROM sensor WHERE device_id = 'F07A1260' AND time >= '2021-04-22 15:33:00' AND time < '2021-04-22 15:33:20';

Perform queries based on conditions

• Query the humidity of the device whose ID is F07A1260. The device must meet one of the following conditions: 1. The region is north-cn and the temperature is higher than 15 degrees. 2. The region is south-cn, the temperature is higher than 10 degrees, and the humidity is not empty.

SELECT device_id, humidity FROM sensor WHERE ((region='north-cn' AND temperature >15) OR (region='south-cn' AND temperature >10)) 
AND device_id = 'F07A1260' AND humidity is not null;

• Query the data of the device whose ID is F07A1260 or F07A1261.

SELECT * FROM sensor WHERE device_id IN ('F07A1260', 'F07A1261');

Forcibly perform an inefficient query

SELECT /*+ _l_allow_filtering_ */ * FROM sensor WHERE humidity=20;