Documentation

Operate on columns

Use the following common queries to operate on columns:

These examples use NOAA water sample data.

Find and count unique values in a column

Find and count the number of unique values in a specified column. The following examples find and count unique locations where data was collected.

Find unique values

This query:

  • Uses group() to ungroup data and return results in a single table.
  • Uses keep() and unique() to return unique values in the specified column.
from(bucket: "noaa")
    |> range(start: -30d)
    |> group()
    |> keep(columns: ["location"])
    |> unique(column: "location")

Example results

location
coyote_creek
santa_monica

Count unique values

This query:

  • Uses group() to ungroup data and return results in a single table.
  • Uses keep(), unique(), and then count() to count the number of unique values.
from(bucket: "noaa")
    |> group()
    |> unique(column: "location")
    |> count(column: "location")

Example results

location
2

Recalculate the _values column

To recalculate the _value column, use the with operator in map() to overwrite the existing _value column.

The following query:

  • Uses filter() to filter the average_temperature measurement.
  • Uses map() to convert Fahrenheit temperature values into Celsius.

from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with _value: (float(v: r._value) - 32.0) * 5.0 / 9.0} ))
_field _measurement _start _stop _time location _value
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:00:00Z coyote_creek 27.77777777777778
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:06:00Z coyote_creek 22.77777777777778
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:12:00Z coyote_creek 30
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:18:00Z coyote_creek 31.666666666666668
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:24:00Z coyote_creek 25
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:30:00Z coyote_creek 21.11111111111111
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:36:00Z coyote_creek 28.88888888888889
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:42:00Z coyote_creek 24.444444444444443
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:48:00Z coyote_creek 29.444444444444443
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T00:54:00Z coyote_creek 26.666666666666668
degrees average_temperature 1920-03-05T22:10:01Z 2020-03-05T22:10:01Z 2019-08-17T01:00:00Z coyote_creek 21.11111111111111
••• ••• ••• ••• ••• ••• •••

Calculate a new column

To use values in a row to calculate and add a new column, use map(). This example below converts temperature from Fahrenheit to Celsius and maps the Celsius value to a new celsius column.

The following query:

  • Uses filter() to filter the average_temperature measurement.
  • Uses map() to create a new column calculated from existing values in each row.
from(bucket: "noaa")
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> range(start: -30d)
    |> map(fn: (r) => ({r with celsius: (r._value - 32.0) * 5.0 / 9.0}))

Example results

_start _stop _field _measurement location _time _value celsius
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:00:00Z 82 27.78
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:06:00Z 73 22.78
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:12:00Z 86 30.00
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:18:00Z 89 31.67
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:24:00Z 77 25.00
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:30:00Z 70 21.11
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:36:00Z 84 28.89
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:42:00Z 76 24.44
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:48:00Z 85 29.44
1920-03-05T22:10:01Z 2020-03-05T22:10:01Z degrees average_temperature coyote_creek 2019-08-17T00:54:00Z 80 26.67
••• ••• ••• ••• ••• ••• ••• •••

Select your region

Upgrade to InfluxDB Cloud or InfluxDB 2.0!

InfluxDB Cloud and InfluxDB OSS 2.0 ready for production.