Time Series Database:General syntax of functions

Specify multiple functions in a SELECT statement

Syntax

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

Description

Separate multiple functions in a SELECT statement with commas (,). The syntax applies to all the InfluxQL functions except TOP() and BOTTOM() functions. TOP() and BOTTOM() functions cannot be used in combination with other functions in a SELECT statement.

Example 1: Calculate the average and median of the field values by using a query

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

name: h2o_feet
time                  mean               median
--------------
1970-01-01T00:00:00Z4.4421070258225224.124

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

Example 2: Obtain the most frequent field values of two field keys by using a query

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

name: h2o_feet
time                  mode  mode_1
--------------
1970-01-01T00:00:00Z2.69  between 3 and 6 feet

This query returns the most frequent field value of the water_level field key and the most frequent field value of the level description field key. The result for the water_level field key is displayed in the mode column, and the result for the level description field is displayed in the mode_1 column. The name of each output column must be unique in the query results. Therefore, the second column mode is renamed mode_1.

For more information about how to specify names for output columns, see Rename output field keys.

Example 3: Obtain the largest and the smallest field values by using a query

> SELECT MIN("water_level"), MAX("water_level")[...]

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

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

Note: This query returns the 1970-01-01T00:00:00Z timestamp. This timestamp is a null timestamp in TSDB for InfluxDB®. MIN() and MAX() are selector functions. If a SELECT statement contains only one selector function, a specific timestamp is returned. The MIN() and MAX() functions return two different timestamps, as shown in the following example. Therefore, the system overwrites the two timestamps with a null timestamp.

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

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

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

name: h2o_feet
time                  max
-------
2015-08-29T07:24:00Z9.964<---Timestamp2

Rename output field keys

Syntax

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

Description

By default, the query results are displayed in the related sections of the field keys that are specified in functions. You can use an AS clause to specify the name of an output field key in your query.

Example 1: Specify an output field key

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

name: h2o_feet
time                  dream_name
--------------
1970-01-01T00:00:00Z4.442107025822522

This query returns the average field value of the water_level field key and renames the output field key dream_name. If no AS clauses are included, this query returns mean as the output field key.

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

name: h2o_feet
time                  mean
--------
1970-01-01T00:00:00Z4.442107025822522

Example 2: Specify 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:00Z4.1242.69

This query returns the median of the field values for the water_level field key and the most frequently occurring field value of the water_level field key. This query renames the output field keys and returns med_wat and mode_wat as the output field keys. If no AS clauses are included, this query returns median and mode as the output field keys.

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

name: h2o_feet
time                  median  mode
--------------
1970-01-01T00:00:00Z4.1242.69

Change the return values for time intervals in which no data is reported

By default, queries that contain InfluxQL functions and GROUP BY time() clauses return null values. This occurs when no data is reported in the related time intervals. If you do not want to obtain null values, add the GROUP BY function to the end of the fill() clause in your query.