SELECT statement-阿里云帮助中心

Use a SELECT statement to query data from one or more measurements in TSDB for InfluxDB®.

Syntax

SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

Every SELECT statement requires a SELECT clause and a FROM clause.

SELECT clause

The SELECT clause defines what data to return. The following formats are supported:

Format	Returns
`SELECT *`	All fields and tags
`SELECT "<field_key>"`	A single field
`SELECT "<field_key>","<field_key>"`	Multiple fields
`SELECT "<field_key>","<tag_key>"`	A field and a tag. At least one field key is required when selecting tags.
`SELECT "<field_key>"::field,"<tag_key>"::tag`	A field and a tag, with explicit identifier types. Use `::[field \| tag]` to distinguish between a field key and tag key that share the same name.

The SELECT clause also supports arithmetic operators, functions, conversion operators, and regular expressions.

Common issue: querying only tag keys returns no data

SELECT must include at least one field key to return data. If the SELECT clause contains only tag keys, the query returns an empty response — this is a result of how TSDB stores data.

-- Returns no data: tag key only
> SELECT "location" FROM "h2o_feet"
>

To retrieve tag values, include at least one field key:

> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3

name: h2o_feet
time                   water_level  location
-----------------------
2015-08-18T00:00:00Z   8.12         coyote_creek
2015-08-18T00:00:00Z   2.064        santa_monica
[...]
2015-09-18T21:36:00Z   5.066        santa_monica
2015-09-18T21:42:00Z   4.938        santa_monica

FROM clause

The FROM clause specifies which measurements to query. The following formats are supported:

Format	Description
`FROM <measurement_name>`	Queries a single measurement. Uses the database set by `USE` (CLI) or the `db` parameter (HTTP API), with the default retention policy.
`FROM <measurement_name>,<measurement_name>`	Queries multiple measurements. Separate measurement names with commas.
`FROM <database_name>.<retention_policy_name>.<measurement_name>`	Queries a fully qualified measurement, specifying both the database and retention policy explicitly.
`FROM <database_name>..<measurement_name>`	Queries a measurement in a specified database using the default retention policy. The `..` notation indicates the default retention policy.

The FROM clause also supports regular expressions.

Identifier quoting

Enclose an identifier in double quotation marks (") if it:

Contains characters outside [A-z,0-9,_]
Starts with a digit
Is an InfluxQL keyword

As a best practice, use double quotation marks for all identifiers.

The quoting syntax for identifiers in InfluxQL differs from the InfluxDB line protocol.

Examples

Select all fields and tags from a single measurement

> SELECT * FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

CLI: Run USE NOAA_water_database before the query. The measurement uses the database specified by USE and the default retention policy.
HTTP API: Set the db parameter to NOAA_water_database. Omit rp to use the default retention policy.

Select specific fields and tags from a single measurement

> SELECT "level description","location","water_level" FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

This returns the level description field, water_level field, and location tag. Because the query includes a tag key (location), it must also include at least one field key.

Select specific fields and tags with explicit identifier types

> SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The ::[field | tag] syntax explicitly declares the type of each identifier. Use this syntax when a field key and tag key share the same name; it is optional otherwise.

Select all fields from a single measurement

> SELECT *::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      water_level
2015-08-18T00:00:00Z   below 3 feet           2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   4.938

Combining * with ::field returns all fields but excludes tags.

Select a field and perform basic arithmetic

> SELECT ("water_level"*2)+4 FROM "h2o_feet"

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   20.24
2015-08-18T00:00:00Z   8.128
[...]
2015-09-18T21:36:00Z   14.132
2015-09-18T21:42:00Z   13.876

The query multiplies each value in water_level by 2 and then adds 4.

Note

TSDB For InfluxDB® follows the standard order of arithmetic operations. For more information, see InfluxQL mathematical operators.

Select all data from multiple measurements

> SELECT * FROM "h2o_feet","h2o_pH"

name: h2o_feet
--------------
time                   level description      location       pH   water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938

name: h2o_pH
------------
time                   level description   location       pH   water_level
2015-08-18T00:00:00Z                       santa_monica   6
2015-08-18T00:00:00Z                       coyote_creek   7
[...]
2015-09-18T21:36:00Z                       santa_monica   8
2015-09-18T21:42:00Z                       santa_monica   7

Separate multiple measurement names with commas. Results are grouped by measurement.

Select all data from a fully qualified measurement

> SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

Specifying <database>.<retention_policy>.<measurement> queries data from h2o_feet in the NOAA_water_database database using the autogen retention policy. Use this approach to avoid setting the database and retention policy separately:

CLI: No need to run USE or set a default retention policy.
HTTP API: No need to set the db or rp parameters.

Select all data from a measurement using the default retention policy

> SELECT * FROM "NOAA_water_database".."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The .. notation selects the default retention policy for the specified database. This queries h2o_feet from NOAA_water_database without specifying a retention policy name:

CLI: No need to run USE.
HTTP API: No need to set the db parameter.

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