OFFSET and SOFFSET clauses - Time Series Database - Alibaba Cloud Documentation Center

The OFFSET clause excludes first N points from the query results. The SOFFSET clause excludes first N series from the query results.

OFFSET clause

The OFFSET <N> clause excludes the first N points from the query results.

Syntax

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]

Description

N indicates that the first N points are excluded from the query results. If you need to use the LIMIT clause in your query, you must include the LIMIT clause in your query and place the LIMIT clause before the OFFSET clause. If a query has the OFFSET clause but does not have the LIMIT clause, inconsistent query results may be returned.

Notes: If the WHERE clause includes a time range, TSDB for InfluxDB® returns no results. If the OFFSET clause is used, TSDB for InfluxDB® may return the points whose timestamps fall out of the time range.

Examples

Specify first N points to be excluded from the query results

> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3
name: h2o_feet
time                   water_level   location
----                   -----------   --------
2015-08-18T00:06:00Z   2.116         santa_monica
2015-08-18T00:12:00Z   7.887         coyote_creek
2015-08-18T00:12:00Z   2.028         santa_monica

The query returns the fourth, fifth, and sixth points from the h2o_feet measurement. If the preceding statement does not include the OFFSET 3 clause, the query returns the first, second, and third points from the measurement.

Specify first N points to be excluded from the query results, and include multiple clauses

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1
name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:00:00Z   8.0625

The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.

The SELECT clause includes an InfluxQL function.
The FROM clause specifies a measurement.
The WHERE clause specifies the time range.
The GROUP BY clause groups query results based on all the tags and a 12-minute interval. The asterisk (*) represents all the tags.
The ORDER BY time DESC clause returns query results based on timestamps in descending order.
The LIMIT 2 clause limits the number of returned points to two.
The OFFSET 2 clause excludes the first two average values from the query results.
The SLIMIT 1 clause limits the number of series returned to one.

If the preceding statement does not include the OFFSET 2 clause, the query returns the first two average values of the query results.

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:36:00Z   7.303
2015-08-18T00:24:00Z   7.5675

SOFFSET clause

The SOFFSET<N> clause excludes the first N series from the query results.

Syntax

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(time_interval)] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] SLIMIT_clause SOFFSET <N>

Description

N indicates that the first N series are excluded from the query results. If you need to use the SLIMIT clause in your query, you must include the SLIMIT clause in your query and place the SLIMIT clause before the SOFFSET clause. If a query has the SOFFSET clause but does not have the SLIMIT clause, inconsistent query results may be returned.

Notes: If N is greater than the total number of series, TSDB for InfluxDB® returns no results.

Examples

Specify first N series to be excluded from the query results

> SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1
name: h2o_feet
tags: location=santa_monica
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
[...]
2015-09-18T21:36:00Z   5.066
2015-09-18T21:42:00Z   4.938

The query returns the data for the series that are associated with the h2o_feet measurement and the location = santa_monica tag. If the preceding statement does not include the SOFFSET 1 clause, the query returns the data for the series associated with the h2o_feet measurement and the location = coyote_creek tag.

Specify first N series to be excluded from the query results, and include multiple clauses

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1
name: h2o_feet
tags: location=santa_monica
time                   mean
----                   ----
2015-08-18T00:12:00Z   2.077
2015-08-18T00:00:00Z   2.09

The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.

The SELECT clause includes an InfluxQL function.
The FROM clause specifies a measurement.
The WHERE clause specifies the time range.
The GROUP BY clause groups query results based on all the tags and a 12-minute interval. The asterisk (*) represents all the tags.
The ORDER BY time DESC clause returns query results based on timestamps in descending order.
The LIMIT 2 clause limits the number of returned points to two.
The OFFSET 2 clause excludes the first two average values from the query results.
The SLIMIT 1 clause limits the number of series returned to one.
The SOFFSET 1 clause excludes the first one series from the query results.

If the preceding statement does not include the SOFFSET 1 clause, the query returns the result of the other series.

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:00:00Z   8.0625

InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.