Lindorm:SELECT

SELECT clause

The SELECT clause specifies the columns, constants, and function expressions to output in a query. Use the AS keyword to define a temporary alias for a column in the result set.

WHERE clause

The WHERE clause specifies query conditions.

Consider the following when using the WHERE clause to query data in a wide table:

• To avoid inefficient queries, define query conditions based on the following rules:

  • Use only the first primary key column of the table or the first index column of the index table as a query condition.

  • If the query conditions include a column other than the first primary key column or the first index column, define an upper and lower bound for the first primary key column of the table or the first index column of the index table.

  If the query conditions do not follow these rules, you must use /*+ _l_allow_filtering_ */ to force the inefficient query to run. For an example, see Force an inefficient query.

• If dynamic columns are enabled for the wide table:

  • To ensure query performance, include primary key or index columns in the WHERE clause.

  • The conditions for dynamic columns in the WHERE clause must be HexStrings.

  For more information about dynamic columns, see Dynamic columns.

GROUP BY clause

LindormTable and LindormTSDB support the GROUP BY clause for grouping and aggregation. Note that spatiotemporal functions do not support the GROUP BY clause.

ORDER BY clause

Specifies one or more columns to sort the result set of a query.

LIMIT clause

The LIMIT clause is used for paged queries. If LIMIT is followed by a single number, the number indicates the number of rows to return. If LIMIT is followed by two numbers separated by a comma (,), such as LIMIT n, m, the statement skips n rows and returns m rows of data. [ LIMIT integer [OFFSET integer] is equivalent to [LIMIT integer, integer].

HINT

LindormTable and LindormTSDB support hints to optimize query plans or enable specific query features. For more information, see HINT overview.

The examples in this topic are based on the sample statements from the CREATE TABLE and UPSERT topics.

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

Simple queries

• Query the sensor table.

  SELECT * FROM sensor; --Select all columns.
  SELECT device_id,region,humidity FROM senor;  --Select specific columns.
  SELECT * FROM sensor WHERE device_id = 'F07A1260' LIMIT 20 OFFSET 5; --Equivalent to LIMIT 5,20.
  SELECT count(*) AS countRow FROM sensor; --Define an alias.

• Call spatiotemporal functions to perform simple queries. For more information about the categories and descriptions of spatiotemporal functions, see Spatiotemporal functions.

  SELECT id, ST_AsText(g) FROM gps_data;
  SELECT id, ST_Buffer(g, 1.0) AS buffer FROM gps_data;

Aggregate queries

• Count the total number of data records reported by the device with the ID 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 maximum temperature of the device with the ID F07A1260 within a 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';

Conditional queries

• Query the humidity of the device with the ID F07A1260 where the region is 'north-cn' and the temperature is higher than 15 degrees, or the region is 'south-cn' and the temperature is higher than 10 degrees, and the humidity is not null.

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 data for devices with the ID F07A1260 or F07A1261.

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

• Call spatiotemporal functions to perform conditional queries. Efficient execution is supported for the following three spatial conditions: ST_Contains, ST_DWithin, and ST_DWithinSphere.

  • Use the ST_Contains function to query data that corresponds to points within a given spatial range.

    SELECT id FROM gps_data WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), g);

  • Use the ST_DWithin function to query data that corresponds to points within a specified planar distance from a centroid. Unit: degree.

    SELECT id FROM gps_data WHERE ST_DWithin(ST_GeomFromText('POINT(0 0)'), g, 100);

  • Use the ST_DWithinSphere function to query data that corresponds to points within a specified spherical distance from a centroid. Unit: meters.

    SELECT id FROM gps_data WHERE ST_DWithinSphere(ST_GeomFromText('POINT(0 0)'), g, 100);

Force an inefficient query

When LindormTable identifies a SELECT statement as an inefficient query, LindormTable does not execute the query by default and throws an exception. This prevents the query from affecting the performance and stability of Lindorm.

To skip the inefficient query check and force the query to run, add /*+ _l_allow_filtering_ */ to the query statement.

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