All Products
Search

This Product

    Time Series Database:Aggregate functions

    Document Center

    Time Series Database:Aggregate functions

    Last Updated:Sep 27, 2023

    This topic describes the syntax and parameters of aggregate functions. It also provides multiple examples.

    COUNT()

    Returns the number of non-null field values.

    Syntax

    SELECT COUNT([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Syntax for nested structures

    SELECT COUNT(DISTINCT([*|<field_key>|/<regular_expression>/]))[...]

    Description

    COUNT(field_key): returns the number of non-null field values for the specified field key.

    COUNT(/regular_expression/): returns the number of non-null field values for each field key that matches the specified regular expression.

    COUNT(*): returns the number of non-null field values for each field key in the specified measurement.

    The COUNT() function supports field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

    Example 1: Calculate the number of non-null field values for the specified field key

    SELECT COUNT("water_level") FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z15258

    This query returns the number of non-null field values for the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the number of non-null field values for each field key in the specified measurement

    SELECT COUNT(*) FROM "h2o_feet"

name: h2o_feet
time                   count_level description   count_water_level
--------------------------------------------
1970-01-01T00:00:00Z1525815258

    This query returns the number of non-null field values for each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: Calculate the number of non-null field values for each field key that matches the specified regular expression

    SELECT COUNT(/water/) FROM "h2o_feet"

name: h2o_feet
time                   count_water_level
---------------------
1970-01-01T00:00:00Z15258

    This query returns the number of non-null field values for each field key that contains water in the h2o_feet measurement.

    Example 4: Calculate the number of non-null field values for the specified field key in each time interval by using a query that includes multiple clauses

    SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(200) LIMIT 7 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   count
---------
2015-08-17T23:48:00Z200
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:36:00Z2
2015-08-18T00:48:00Z2

    This query returns the number of non-null field values for the water_level field key in each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 200. This query limits the number of returned points to 7, and limits the number of returned series to 1.

    Example 5: Calculate the number of unique field values for the specified field key

    SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z4

    This query returns the number of unique field values for the level description field key in the level description measurement.

    FAQ

    For time intervals in which no data is reported, most InfluxQL functions return null values. If you do not want to obtain null values, use the fill() function. The fill(<fill_option>) function replaces each null value with the value of the fill_option parameter. If no data is reported during some time intervals, the COUNT() function returns 0. If you use the fill(<fill_option>) function in the query, the function replaces 0 with the value of the fill_option parameter.

    Examples

    > SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-09-18T21:24:00Z' AND time <='2015-09-18T21:54:00Z' GROUP BY time(12m)

name: h2o_feet
time                   count
---------
2015-09-18T21:24:00Z2
2015-09-18T21:36:00Z2
2015-09-18T21:48:00Z0

> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-09-18T21:24:00Z' AND time <='2015-09-18T21:54:00Z' GROUP BY time(12m) fill(800000)

name: h2o_feet
time                   count
---------
2015-09-18T21:24:00Z2
2015-09-18T21:36:00Z2
2015-09-18T21:48:00Z800000

    The first query in the preceding code block does not use the fill() function. In the query result, 0 is returned for the last time interval. This is because no data is reported during the last time interval. The second query uses the fill(800000) function. In this query, no data is reported during the last interval. For the last time interval, the fill(800000) function returns 800000. If you do not use the fill() function, 0 is returned for the last time interval.

    DISTINCT()

    Returns the unique field values of the specified field keys.

    Syntax

    SELECT DISTINCT([*|<field_key>|/<regular_expression>/]) FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Syntax for nested structures

    SELECT COUNT(DISTINCT([*|<field_key>|/<regular_expression>/]))[...]

    Description

    DISTINCT(field_key): returns the unique field values of specified field key.

    DISTINCT(/regular_expression/): returns the unique field values of each field key that matches the specified regular expression.

    DISTINCT(*): returns the unique field values of each field key in the specified measurement.

    The DISTINCT() function supports field values of all data types. In InfluxQL, the DISTINCT() function can be nested in the COUNT() function.

    Example 1: List the unique field values of the specified field key

    SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
------------
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

    This query returns the unique field values of the level description field key in the h2o_feet measurement.

    Example 2: List the unique field values of each field key in the specified measurement

    SELECT DISTINCT(*) FROM "h2o_feet"

name: h2o_feet
time                   distinct_level description   distinct_water_level
--------------------------------------------------
1970-01-01T00:00:00Z   between 6 and 9 feet         8.12
1970-01-01T00:00:00Z   between 3 and 6 feet         8.005
1970-01-01T00:00:00Z   at or greater than 9 feet    7.887
1970-01-01T00:00:00Z   below 3 feet                 7.762
[...]

    This query returns the unique field values of each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: List the unique field values of each field key that matches the specified regular expression

    SELECT DISTINCT(/description/) FROM "h2o_feet"

name: h2o_feet
time                   distinct_level description
------------------------------
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

    This query returns the unique field values of each field key that contains description in the h2o_feet measurement.

    Example 4: List the unique field values of the specified field key by using a query that includes multiple clauses

    >  SELECT DISTINCT("level description") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   distinct
------------
2015-08-18T00:00:00Z   between 6 and 9 feet
2015-08-18T00:12:00Z   between 6 and 9 feet
2015-08-18T00:24:00Z   between 6 and 9 feet
2015-08-18T00:36:00Z   between 6 and 9 feet
2015-08-18T00:48:00Z   between 6 and 9 feet

    This query returns the unique field values of the level description field key. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. This query limits the number of returned series to 1.

    Example 5: Calculate the number of unique field values for the specified field key

    SELECT COUNT(DISTINCT("level description")) FROM "h2o_feet"

name: h2o_feet
time                   count
---------
1970-01-01T00:00:00Z4

    This query returns the unique field values of the level description field key in the h2o_feet measurement.

    FAQ

    If you use the DISTINCT() function in a DISTINCT() clause, TSDB for InfluxDB® may overwrite some points in the target measurement. In most cases, the DISTINCT() function returns multiple field values that have the same timestamp. TSDB for InfluxDB® considers the points that have the same timestamp in the same series as duplicate points, and uses the latest points to overwrite the duplicate points in the target measurement.

    Examples

    The first query in the following code block uses the DISTINCT() function and returns four field values. Note: The four field values have the same timestamp. The second query uses an INTO clause and writes the returned field values to the distincts measurement. The last query returns all the data in the distincts measurement. The four initial points are considered as duplicate points because these points belong to the same series and have the same timestamp. Therefore, the last query returns only one point. If duplicate points exist, TSDB for InfluxDB® uses the latest point to overwrite the previous points. 

    SELECT DISTINCT("level description") FROM "h2o_feet"

name: h2o_feet
time                   distinct
------------
1970-01-01T00:00:00Z   below 3 feet
1970-01-01T00:00:00Z   between 6 and 9 feet
1970-01-01T00:00:00Z   between 3 and 6 feet
1970-01-01T00:00:00Z   at or greater than 9 feet

SELECT DISTINCT("level description") INTO "distincts" FROM "h2o_feet"

name: result
time                   written
-----------
1970-01-01T00:00:00Z4

SELECT * FROM "distincts"

name: distincts
time                   distinct
------------
1970-01-01T00:00:00Z   at or greater than 9 feet

    INTEGRAL()

    Returns the area of the section that resides between the field value curve and the x-axis. The area indicates the integral of the field values.

    Syntax

    SELECT INTEGRAL([*|<field_key>|/<regular_expression>/][,<unit>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    TSDB for InfluxDB® calculates the areas of the sections that reside between the field value curves and the x-axis. Then, TSDB for InfluxDB® converts the results into the total areas based on the time granularity that is specified by the unit parameter. The value of the unit parameter is an integer that is suffixed by a time unit. This parameter is optional. If you do not specify the unit parameter in your query, the default value 1s is specified for the unit parameter.

    INTEGRAL(field_key): returns the area of the section that resides between the field value curve and the x-axis for each field key in the specified measurement.

    INTEGRAL(/regular_expression/): returns the area of the section that resides between the field value curve and the x-axis for each field key that matches the specified regular expression.

    INTEGRAL(*): returns the area of the section that resides between the field value curve and the x-axis for each field key in the specified measurement.

    The INTEGRAL() function cannot be nested in the fill() function. The INTEGRAL() function supports INT64 and FLOAT64 field values.

    Example 1: Calculate the integral of the field values for the specified field key

    SELECT INTEGRAL("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral
------------
1970-01-01T00:00:00Z3732.66

    This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-second interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the integral of the field values for the specified field key and specify the unit parameter

    SELECT INTEGRAL("water_level",1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral
------------
1970-01-01T00:00:00Z62.211

    This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-second interval. The field value curve is created for the water_level field key in the h2o_feet measurement.

    Example 3: Calculate the integral of the field values for each field key in the specified measurement and specify the unit parameter

    SELECT INTEGRAL(*,1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral_water_level
------------------------
1970-01-01T00:00:00Z62.211

    This query returns the area of the section that resides between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

    Example 4: Calculate the integral of the field values for each field key that matches the specified regular expression and specify the unit parameter

    SELECT INTEGRAL(/water/,1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z'

name: h2o_feet
time                 integral_water_level
------------------------
1970-01-01T00:00:00Z62.211

    This query returns the area of the section that resides between the field value curve and the x-axis for each specified field key based on a 1-minute interval. The specified field key contains water and stores numeric values in the h2o_feet measurement.

    Example 5: Calculate the integral of the field values for the specified field key by using a query that includes multiple clauses

    SELECT INTEGRAL("water_level",1m) FROM "h2o_feet" WHERE "location"='santa_monica' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m) LIMIT 1

name: h2o_feet
time                 integral
------------
2015-08-18T00:00:00Z24.972

    This query returns the area of the section that resides between the field value curve and the x-axis based on a 1-minute interval for the water_level field key in the h2o_feet measurement. The specified time range is from 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval. The query limits the number of returned points to 1.

    MEAN()

    Returns the average field values of the specified field keys.

    Syntax

    SELECT MEAN([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    MEAN(field_key): returns the average field value of a specified field key.

    MEAN(/regular_expression/): returns the average field value of each field key that matches the specified regular expression.

    MEAN(*): returns the average field value of each field key in the specified measurement.

    The MEAN() function supports INT64 and FLOAT64 field values.

    Example 1: Calculate the average field value of the specified field key

    SELECT MEAN("water_level") FROM "h2o_feet"

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

    This query returns the average field value of the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the average field value of each field key in the specified measurement

    SELECT MEAN(*) FROM "h2o_feet"

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

    This query returns the average field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

    Example 3: Calculate the average field value of each field key that matches the specified regular expression

    SELECT MEAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mean_water_level
--------------------
1970-01-01T00:00:00Z4.442107025822523

    This query returns the average field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Calculate the average field value of the specified field key for each time interval by using a query that includes multiple clauses

    SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 7 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   mean
--------
2015-08-17T23:48:00Z9.01
2015-08-18T00:00:00Z8.0625
2015-08-18T00:12:00Z7.8245
2015-08-18T00:24:00Z7.5675
2015-08-18T00:36:00Z7.303
2015-08-18T00:48:00Z7.046

    This query returns the average field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01 This query limits the number of returned points to 7, and limits the number of returned series to 1.

    MEDIAN()

    Returns the median in a set of sorted field values.

    Syntax

    SELECT MEDIAN([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    MEDIAN(field_key): returns the median of the field values for the specified field key.

    MEDIAN(/regular_expression/): returns the median of the field values for each field key that matches the specified regular expression.

    MEDIAN(*): returns the median of the field values for each field key in the specified measurement.

    The MEDIAN() function supports INT64 and FLOAT64 field values.

    Note

    The MEDIAN() function is similar to the PERCENTILE(field_key, 50) function unless the specified field key has an even number of field values. In this case, the MEDIAN() function returns the average of the two middle field values.

    Example 1: Calculate the median of the field values for the specified field key

    SELECT MEDIAN("water_level") FROM "h2o_feet"

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

    This query returns the median of the field values for the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the median of the field values for each field key in the specified measurement

    SELECT MEDIAN(*) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----------------------
1970-01-01T00:00:00Z4.124

    This query returns the median of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

    Example 3: Calculate the median of the field values for each field key that matches the specified regular expression

    SELECT MEDIAN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   median_water_level
----------------------
1970-01-01T00:00:00Z4.124

    This query returns the median of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Calculate the median of the field values for the specified field key in each time interval by using a query that includes multiple clauses

    SELECT MEDIAN("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(700) LIMIT 7 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   median
----------
2015-08-17T23:48:00Z700
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003
2015-08-18T00:36:00Z2.0620000000000003
2015-08-18T00:48:00Z700

    This query returns the median of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 700. This query limits the number of returned points to 7, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

    MODE()

    Returns the most frequently occurring fields values.

    Syntax

    SELECT MODE([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    MODE(field_key): returns the most frequently occurring field values of the specified field key.

    MODE(/regular_expression/): returns the most frequently occurring field value of each field key that matches the specified regular expression.

    MODE(*): returns the most frequently occurring field value of each field key in the specified measurement.

    The MODE() function supports field values of all data types.

    Note

    If two or more field values have the maximum number of occurrences and they are interrelated, the MODE() function returns the field value that is attached with the earliest timestamp.

    Example 1: Obtain the most frequently occurring field value of the specified field key

    SELECT MODE("level description") FROM "h2o_feet"

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

    This query returns the most frequently occurring field value of the level description field key in the h2o_feet measurement.

    Example 2: Obtain the most frequently occurring field value of each field key in the specified measurement

    SELECT MODE(*) FROM "h2o_feet"

name: h2o_feet
time                   mode_level description   mode_water_level
------------------------------------------
1970-01-01T00:00:00Z   between 3 and 6 feet     2.69

    This query returns the most frequent field value of each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: Obtain the most frequently occurring field value of each field key that matches the specified regular expression

    SELECT MODE(/water/) FROM "h2o_feet"

name: h2o_feet
time                   mode_water_level
--------------------
1970-01-01T00:00:00Z2.69

    This query returns the most frequent field value of each field key that contains water in the h2o_feet measurement.

    Example 4: Obtain the most frequently occurring field value of the specified field key for each time interval by using a query that includes multiple clauses

    SELECT MODE("level description") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* LIMIT 3 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   mode
--------
2015-08-17T23:48:00Z
2015-08-18T00:00:00Z   below 3 feet
2015-08-18T00:12:00Z   below 3 feet

    This query returns the most frequent field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

    SPREAD()

    Returns the difference between the maximum and the minimum field values of the specified field keys.

    Syntax

    SELECT SPREAD([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    SPREAD(field_key): returns the difference between the maximum and the minimum field values of the specified field key.

    SPREAD(/regular_expression/): returns the difference between the maximum and the minimum field values of each field key that matches the specified regular expression.

    SPREAD(*): returns the difference between the maximum and the minimum field values of each field key in the specified measurement.

    The SPREAD() function supports INT64 and FLOAT64 field values.

    Example 1: Calculate the difference between the maximum and the minimum field values of the specified field key 

    SELECT SPREAD("water_level") FROM "h2o_feet"

name: h2o_feet
time                   spread
----------
1970-01-01T00:00:00Z10.574

    This query returns the difference between the maximum and the minimum field values of the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the difference between the maximum and the minimum field values of each field key in the specified measurement

    SELECT SPREAD(*) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----------------------
1970-01-01T00:00:00Z10.574

    This query returns the difference between the maximum and the minimum field values of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

    Example 3: Calculate the difference between the maximum and the minimum field values of each field key that matches the specified regular expression

    SELECT SPREAD(/water/) FROM "h2o_feet"

name: h2o_feet
time                   spread_water_level
----------------------
1970-01-01T00:00:00Z10.574

    This query returns the difference between the maximum and the minimum field values of each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Calculate the difference between the maximum and the minimum field values of the specified field key for each time interval by using a query that includes multiple clauses

    SELECT SPREAD("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18) LIMIT 3 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   spread
----------
2015-08-17T23:48:00Z18
2015-08-18T00:00:00Z0.052000000000000046
2015-08-18T00:12:00Z0.09799999999999986

    This query returns the difference between the maximum and the minimum field values of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18. The query limits the number of returned points to 3, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

    STDDEV()

    Returns the standard deviation of the specified field values.

    Syntax

    SELECT STDDEV([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    STDDEV(field_key): returns the standard deviation of the field values for the specified field key.

    STDDEV(/regular_expression/): returns the standard deviation of the field values for each field key that matches the specified regular expression.

    STDDEV(*): returns the standard deviation of the field values for each field key in the specified measurement.

    The STDDEV() function supports INT64 or FLOAT64 field values.

    Example 1: Calculate the standard deviation of the field values for the specified field key

    SELECT STDDEV("water_level") FROM "h2o_feet"

name: h2o_feet
time                   stddev
----------
1970-01-01T00:00:00Z2.279144584196141

    This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the standard deviation of the field values for each field key in the specified measurement

    SELECT STDDEV(*) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----------------------
1970-01-01T00:00:00Z2.279144584196141

    This query returns the standard deviation of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

    Example 3: Calculate the standard deviation of the field values for each field key that matches the specified regular expression

    SELECT STDDEV(/water/) FROM "h2o_feet"

name: h2o_feet
time                   stddev_water_level
----------------------
1970-01-01T00:00:00Z2.279144584196141

    This query returns the standard deviation of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Calculate the standard deviation of the field values for the specified field key in each time interval by using a query that includes multiple clauses

    SELECT STDDEV("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 2 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   stddev
----------
2015-08-17T23:48:00Z18000
2015-08-18T00:00:00Z0.03676955262170051

    This query returns the standard deviation of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 2, and limits the number of returned series to 1. The series offset for the query results is set to 1, which indicates that no query results are returned for the first series.

    SUM()

    Returns the sum of the specified field values.

    Syntax

    SELECT SUM([*|<field_key>|/<regular_expression>/])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]

    Description

    SUM(field_key): returns the sum of the field values for the specified field key.

    SUM(/regular_expression/): returns the sum of the field values for each field key that matches the specified regular expression.

    SUM(*): returns the sum of the field values for each field key in the specified measurement.

    The SUM() function supports INT64 and FLOAT64 field values.

    Example 1: Calculate the sum of the field values for the specified field key

    SELECT SUM("water_level") FROM "h2o_feet"

name: h2o_feet
time                   sum
-------
1970-01-01T00:00:00Z67777.66900000004

    This query returns the sum of the field values for the water_level field key in the h2o_feet measurement.

    Example 2: Calculate the sum of the field values for each field key in the specified measurement

    SELECT SUM(*) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
-------------------
1970-01-01T00:00:00Z67777.66900000004

    This query returns the sum of the field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the numeric water_level field.

    Example 3: Calculate the sum of the field values for each field key that matches the specified regular expression

    SELECT SUM(/water/) FROM "h2o_feet"

name: h2o_feet
time                   sum_water_level
-------------------
1970-01-01T00:00:00Z67777.66900000004

    This query returns the sum of the field values for each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Calculate the sum of the field values for the specified field key in each time interval by using a query that includes multiple clauses

    SELECT SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-17T23:48:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(12m),* fill(18000) LIMIT 4 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   sum
-------
2015-08-17T23:48:00Z18000
2015-08-18T00:00:00Z16.125
2015-08-18T00:12:00Z15.649
2015-08-18T00:24:00Z15.135

    This query returns the sum of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 18000. The query limits the number of returned points to 4, and limits the number of returned series to 1.