Time Series Database:General syntax of functions

Combine multiple functions in a SELECT statement

Syntax

SELECT <function>(), <function>() FROM_clause [...]

Separate multiple functions with commas. This syntax applies to all InfluxQL functions except TOP() and BOTTOM(), which cannot be combined with other functions in the same SELECT statement.

Calculate the mean and median field values in one query

> SELECT MEAN("water_level"), MEDIAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                  mean               median
----                  ----               ------
1970-01-01T00:00:00Z  4.442107025822522  4.124

This query returns the average and median of the water_level field key.

Calculate the mode of two field keys in one query

> SELECT MODE("water_level"), MODE("level description") FROM "h2o_feet"

name: h2o_feet
time                  mode  mode_1
----                  ----  ------
1970-01-01T00:00:00Z  2.69  between 3 and 6 feet

This query returns the most frequent value for each field key. The water_level result appears in the mode column; the level description result appears in mode_1. Output column names must be unique, so the system automatically renames the second mode column to mode_1. To set custom column names, see Rename output field keys.

Calculate the minimum and maximum field values in one query

> SELECT MIN("water_level"), MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                  min    max
----                  ---    ---
1970-01-01T00:00:00Z  -0.61  9.964

This query returns the smallest and largest values of the water_level field key.

The result shows the null timestamp 1970-01-01T00:00:00Z. MIN() and MAX() are selector functions. When a SELECT statement contains a single selector function, a specific timestamp is returned. When it contains two selector functions, each returns a different timestamp, and the system overwrites both with the null timestamp. The individual timestamps are:

>  SELECT MIN("water_level") FROM "h2o_feet"

name: h2o_feet
time                  min
----                  ---
2015-08-29T14:30:00Z  -0.61

>  SELECT MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                  max
----                  -----
2015-08-29T07:24:00Z  9.964

Rename output field keys

Syntax

SELECT <function>() AS <field_key> [...]

By default, functions return results under a field key that matches the function name. Include an AS clause to specify a custom output field key name.

Rename a single output field key

> SELECT MEAN("water_level") AS "dream_name" FROM "h2o_feet"

name: h2o_feet
time                  dream_name
----                  ----------
1970-01-01T00:00:00Z  4.442107025822522

Without AS, the output field key defaults to mean.

> SELECT MEAN("water_level") FROM "h2o_feet"

name: h2o_feet
time                  mean
----                  ----
1970-01-01T00:00:00Z  4.442107025822522

Rename output field keys for multiple functions

> SELECT MEDIAN("water_level") AS "med_wat", MODE("water_level") AS "mode_wat" FROM "h2o_feet"

name: h2o_feet
time                  med_wat  mode_wat
----                  -------  --------
1970-01-01T00:00:00Z  4.124    2.69

Without AS, the output field keys default to median and mode.

> SELECT MEDIAN("water_level"), MODE("water_level") FROM "h2o_feet"

name: h2o_feet
time                  median  mode
----                  ------  ----
1970-01-01T00:00:00Z  4.124   2.69

Handle empty time intervals in GROUP BY time() queries

Queries that combine InfluxQL functions with a GROUP BY time() clause return null values for time intervals in which no data is reported. To replace null values with a different fill value, add a fill() clause at the end of the GROUP BY clause.