Influxdata
/
docs-v2
mirror of https://github.com/influxdata/docs-v2.git
1
0
Fork 0
Code Issues Projects Releases Wiki Activity
docs-v2/content/shared/influxql-v3-reference/select.md

14 KiB
Raw Permalink Blame History

Use the SELECT statement to query data from one or more measurements. The SELECT statement requires a SELECT clause and a FROM clause.

Syntax

SELECT field_expression[, ..., field_expression_n[, tag_expression[, ..., tag_expression_n]]] FROM measurement_expression[, ..., measurement_expression_n]

SELECT clause

The SELECT clause supports several formats for identifying data to query. It requires one or more field expressions and optional tag expressions.

  • field_expression: Expression to identify one or more fields to return in query results. Can be a field key, constant, regular expression, wildcard (*), or function expression and any combination of arithmetic operators.
  • tag_expression: Expression to identify one or more tags to return in query results. Can be a tag key or constant.

Select clause behaviors

  • SELECT field_key - Returns a specific field.
  • SELECT field_key1, field_key2 - Returns two specific fields.
  • SELECT field_key, tag_key - Returns a specific field and tag.
  • SELECT * - Returns all fields and tags. See Wildcard expressions.
  • SELECT /^[t]/ - Returns all fields and tags with keys that match the regular expression. At least one field key must match the regular expression. If no field keys match the regular expression, no results are returned.

FROM clause

The FROM clause specifies the measurement or subquery to query. It requires one or more comma-delimited measurement expressions or subqueries.

measurement_expression

A measurement expression identifies a measurement to query. It can be a measurement name, fully-qualified measurement, constant, or a regular expression.

  • Measurement name: When using just the measurement name, InfluxQL assumes the default retention policy of the database specified in the query request.

    FROM measurement

  • Fully-qualified measurement: A fully qualified measurement includes a database name, retention policy name, and measurement name, each separated by a period (.). If the retention policy is not specified, InfluxQL uses the default retention policy for the specified database.

FROM database.retention_policy.measurement

-- Fully-qualified measurement with default retention policy
FROM database..measurement

[!Important]

InfluxQL retention policies

In {{< product-name >}}, retention policies are not part of the data model like they are in InfluxDB 1.x. Each {{< product-name >}} database has a retention period which defines the maximum age of data to retain in the database. To use fully-qualified measurements in InfluxQL queries, use the following naming convention when creating a database:

database_name/retention_policy

Subquery

An InfluxQL subquery is a query nested in the FROM clause of an InfluxQL query. The outer query queries results returned by the inner query (subquery).

For more information, see InfluxQL subqueries.

Notable SELECT statement behaviors

Must query at least one field

A query requires at least one field key in the SELECT clause to return data. If the SELECT clause includes only tag keys, the query returns an empty result. When using regular expressions in the SELECT clause, if regular expression matches only tag keys and no field keys, the query returns an empty result.

To return data associated with tag keys, include at least one field key in the SELECT clause.

Wildcard expressions

When using a wildcard expression (*) in the SELECT clause, the query returns all tags and fields. If a function is applied to a wildcard expression, the query returns all fields with the function applied, but does not return tags unless they are included in the SELECT clause.

Cannot include both aggregate and non-aggregate field expressions

The SELECT statement cannot include an aggregate field expression (one that uses an aggregate or selector function) and a non-aggregate field expression. For example, in the following query, an aggregate function is applied to one field, but not the other:

SELECT mean(temp), hum FROM home

This query returns an error. For more information, see error about mixing aggregate and non-aggregate queries.

Data types and casting operations

The SELECT clause supports specifying a field's type and basic casting operations with the :: syntax.

SELECT field_expression::type FROM measurement_expression

The :: syntax allows users to perform basic cast operations in queries. Currently, InfluxQL supports casting numeric field values to other numeric types. Casting to an identifier type acts as a filter on results and returns only columns of that specific identifier type along with the time column.

{{< flex >}} {{% flex-content "third" %}}

Numeric types
  • float
  • integer
  • unsigned

{{% /flex-content %}} {{% flex-content "third" %}}

Non-numeric types
  • string
  • boolean

{{% /flex-content %}} {{% flex-content "third" %}}

Identifier types
  • field
  • tag

{{% /flex-content %}} {{< /flex >}}

[!Important] InfluxQL returns no data if the query attempts to cast a numeric value to a non-numeric type and vice versa.

When casting a float value to an integer or unsigned integer, the float value is truncated at the decimal point. No rounding is performed.

SELECT statement examples

The examples below use the following sample data sets:

{{< expand-wrapper >}} {{% expand "Select all fields and tags from a measurement" %}}

SELECT * FROM home

{{% influxql/table-meta %}} Name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time co hum room temp
2022-01-01T08:00:00Z 0 35.9 Kitchen 21
2022-01-01T08:00:00Z 0 35.9 Living Room 21.1
2022-01-01T09:00:00Z 0 36.2 Kitchen 23
2022-01-01T09:00:00Z 0 35.9 Living Room 21.4
2022-01-01T10:00:00Z 0 36.1 Kitchen 22.7
2022-01-01T10:00:00Z 0 36 Living Room 21.8
... ... ... ... ...

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select specific tags and fields from a measurement" %}}

SELECT temp, hum, room FROM home

{{% influxql/table-meta %}} Name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time temp hum room
2022-01-01T08:00:00Z 21 35.9 Kitchen
2022-01-01T08:00:00Z 21.1 35.9 Living Room
2022-01-01T09:00:00Z 23 36.2 Kitchen
2022-01-01T09:00:00Z 21.4 35.9 Living Room
2022-01-01T10:00:00Z 22.7 36.1 Kitchen
2022-01-01T10:00:00Z 21.8 36 Living Room
... ... ... ...

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select all fields from a measurement" %}}

SELECT *::field FROM home

{{% influxql/table-meta %}} Name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time co hum temp
2022-01-01T08:00:00Z 0 35.9 21
2022-01-01T08:00:00Z 0 35.9 21.1
2022-01-01T09:00:00Z 0 36.2 23
2022-01-01T09:00:00Z 0 35.9 21.4
2022-01-01T10:00:00Z 0 36.1 22.7
2022-01-01T10:00:00Z 0 36 21.8
... ... ... ...

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{% expand "Select a field from a measurement and perform basic arithmetic" %}}

SELECT (temp * (9 / 5)) + 32 FROM home

{{% influxql/table-meta %}} Name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time temp
2022-01-01T08:00:00Z 69.80000000000001
2022-01-01T08:00:00Z 69.98
2022-01-01T09:00:00Z 73.4
2022-01-01T09:00:00Z 70.52
2022-01-01T10:00:00Z 72.86
2022-01-01T10:00:00Z 71.24000000000001
... ...

{{% /influxdb/custom-timestamps %}}

[!Note] Note: InfluxDB follows the standard order of operations. See InfluxQL mathematical operators for more on supported operators.

{{% /expand %}}

{{% expand "Select all data from more than one measurement" %}}

SELECT * FROM home, weather

{{% influxql/table-meta %}} Name: weather {{% /influxql/table-meta %}}

time co hum location precip room temp temp_avg temp_max temp_min wind_avg
2020-01-01T00:00:00Z Concord 0 52 66 44 3.13
2020-01-01T00:00:00Z San Francisco 0 53 59 47 14.32
2020-01-01T00:00:00Z Hayward 0 50 57 44 2.24
2020-01-02T00:00:00Z San Francisco 0 54 61 49 5.82
2020-01-02T00:00:00Z Hayward 0 51 60 44 3.8
2020-01-02T00:00:00Z Concord 0 53 66 42 3.13
... ... ... ... ... ... ... ... ... ... ...

{{% /expand %}}

{{% expand "Select all data from a fully-qualified measurement (with default retention policy)" %}}

SELECT * FROM "get-started"..home

{{% influxql/table-meta %}} Name: home {{% /influxql/table-meta %}}

{{% influxdb/custom-timestamps %}}

time co hum room temp
2022-01-01T08:00:00Z 0 35.9 Kitchen 21
2022-01-01T08:00:00Z 0 35.9 Living Room 21.1
2022-01-01T09:00:00Z 0 36.2 Kitchen 23
2022-01-01T09:00:00Z 0 35.9 Living Room 21.4
2022-01-01T10:00:00Z 0 36.1 Kitchen 22.7
2022-01-01T10:00:00Z 0 36 Living Room 21.8
... ... ... ... ...

{{% /influxdb/custom-timestamps %}} {{% /expand %}}

{{< /expand-wrapper >}}

Type-casting examples

{{< expand-wrapper >}}

{{% expand "Cast an integer field to a float" %}}

SELECT co::float FROM home

{{% /expand %}}

{{% expand "Cast a float field to an integer" %}}

SELECT temp::integer FROM home

{{% /expand %}}

{{% expand "Cast a float field to an unsigned integer" %}}

SELECT temp::unsigned FROM home

{{% /expand %}}

{{< /expand-wrapper >}}