Documentation

InfluxDB OSS

v1

Calculate percentages with Flux

This page documents an earlier version of InfluxDB OSS. InfluxDB OSS v2 is the latest stable version. See the equivalent InfluxDB v2 documentation: Calculate percentages with Flux.

Calculating percentages from queried data is a common use case for time series data. To calculate a percentage in Flux, operands must be in each row. Use map() to re-map values in the row and calculate a percentage.

To calculate percentages

  1. Use from(), range() and filter() to query operands.
  2. Use pivot() or join() to align operand values into rows.
  3. Use map() to divide the numerator operand value by the denominator operand value and multiply by 100.

The following examples use pivot() to align operands into rows because pivot() works in most cases and is more performant than join(). See Pivot vs join.

from(bucket: "db/rp")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "m1" and r._field =~ /field[1-2]/ )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r.field1 / r.field2 * 100.0 }))

GPU monitoring example

The following example queries data from the gpu-monitor bucket and calculates the percentage of GPU memory used over time. Data includes the following:

  • gpu measurement
  • mem_used field: used GPU memory in bytes
  • mem_total field: total GPU memory in bytes

Query mem_used and mem_total fields

from(bucket: "gpu-monitor")
  |> range(start: 2020-01-01T00:00:00Z)
  |> filter(fn: (r) => r._measurement == "gpu" and r._field =~ /mem_/)
Returns the following stream of tables:
_time _measurement _field _value
2020-01-01T00:00:00Z gpu mem_used 2517924577
2020-01-01T00:00:10Z gpu mem_used 2695091978
2020-01-01T00:00:20Z gpu mem_used 2576980377
2020-01-01T00:00:30Z gpu mem_used 3006477107
2020-01-01T00:00:40Z gpu mem_used 3543348019
2020-01-01T00:00:50Z gpu mem_used 4402341478

_time _measurement _field _value
2020-01-01T00:00:00Z gpu mem_total 8589934592
2020-01-01T00:00:10Z gpu mem_total 8589934592
2020-01-01T00:00:20Z gpu mem_total 8589934592
2020-01-01T00:00:30Z gpu mem_total 8589934592
2020-01-01T00:00:40Z gpu mem_total 8589934592
2020-01-01T00:00:50Z gpu mem_total 8589934592

Pivot fields into columns

Use pivot() to pivot the mem_used and mem_total fields into columns. Output includes mem_used and mem_total columns with values for each corresponding _time.

// ...
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
Returns the following:
_time _measurement mem_used mem_total
2020-01-01T00:00:00Z gpu 2517924577 8589934592
2020-01-01T00:00:10Z gpu 2695091978 8589934592
2020-01-01T00:00:20Z gpu 2576980377 8589934592
2020-01-01T00:00:30Z gpu 3006477107 8589934592
2020-01-01T00:00:40Z gpu 3543348019 8589934592
2020-01-01T00:00:50Z gpu 4402341478 8589934592

Map new values

Each row now contains the values necessary to calculate a percentage. Use map() to re-map values in each row. Divide mem_used by mem_total and multiply by 100 to return the percentage.

To return a precise float percentage value that includes decimal points, the example below casts integer field values to floats and multiplies by a float value (100.0).

// ...
  |> map(fn: (r) => ({
    _time: r._time,
    _measurement: r._measurement,
    _field: "mem_used_percent",
    _value: float(v: r.mem_used) / float(v: r.mem_total) * 100.0
  }))
Query results:
_time _measurement _field _value
2020-01-01T00:00:00Z gpu mem_used_percent 29.31
2020-01-01T00:00:10Z gpu mem_used_percent 31.37
2020-01-01T00:00:20Z gpu mem_used_percent 30.00
2020-01-01T00:00:30Z gpu mem_used_percent 35.00
2020-01-01T00:00:40Z gpu mem_used_percent 41.25
2020-01-01T00:00:50Z gpu mem_used_percent 51.25

Full query

from(bucket: "gpu-monitor")
  |> range(start: 2020-01-01T00:00:00Z)
  |> filter(fn: (r) => r._measurement == "gpu" and r._field =~ /mem_/ )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
    _time: r._time,
    _measurement: r._measurement,
    _field: "mem_used_percent",
    _value: float(v: r.mem_used) / float(v: r.mem_total) * 100.0
  }))

Examples

Calculate percentages using multiple fields

from(bucket: "db/rp")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "example-measurement")
  |> filter(fn: (r) =>
    r._field == "used_system" or
    r._field == "used_user" or
    r._field == "total"
  )
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with
    _value: float(v: r.used_system + r.used_user) / float(v: r.total) * 100.0
  }))

Calculate percentages using multiple measurements

  1. Ensure measurements are in the same bucket.
  2. Use filter() to include data from both measurements.
  3. Use group() to ungroup data and return a single table.
  4. Use pivot() to pivot fields into columns.
  5. Use map() to re-map rows and perform the percentage calculation.
from(bucket: "db/rp")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    (r._measurement == "m1" or r._measurement == "m2") and
    (r._field == "field1" or r._field == "field2")    
  )
  |> group()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with  _value: r.field1 / r.field2 * 100.0 }))

Calculate percentages using multiple data sources

import "sql"
import "influxdata/influxdb/secrets"

pgUser = secrets.get(key: "POSTGRES_USER")
pgPass = secrets.get(key: "POSTGRES_PASSWORD")
pgHost = secrets.get(key: "POSTGRES_HOST")

t1 = sql.from(
  driverName: "postgres",
  dataSourceName: "postgresql://${pgUser}:${pgPass}@${pgHost}",
  query:"SELECT id, name, available FROM exampleTable"
)

t2 = from(bucket: "db/rp")
  |> range(start: -1h)
  |> filter(fn: (r) =>
    r._measurement == "example-measurement" and
    r._field == "example-field"
  )

join(tables: {t1: t1, t2: t2}, on: ["id"])
  |> map(fn: (r) => ({ r with _value: r._value_t2 / r.available_t1 * 100.0 }))

Was this page helpful?

Thank you for your feedback!

© 2024 InfluxData, Inc.

What is your InfluxDB OSS URL?

Customize your InfluxDB OSS URL and we’ll update code examples for you.

Default
Custom

For more information, see InfluxDB OSS URLs.

Thank you for your feedback!

Let us know what we can do better:

Thank you!

No thanks

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements

InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered