Skip to end of metadata
Go to start of metadata

ADQL Reference:


Functions are used as arguments in the SELECT clause. ADQL provides several types of functions as follows:

  • Metric functions 
  • Bucketing functions
  • Functions that modify other functions

The functions are described in the tables below. Many functions are only valid for numeric fields. Valid field types for each function are listed in the table.

Note: In the Controller Analytics search UI, the query box in the data tab is used for adding WHERE clause differentiation. When you perform the search, the results of metric functions are shown in the visualization tab under the table widget.

Metric Functions

Metric functions perform a calculation on a set of values for a field and return a result, such as a count or a sum or a set of results, such as percentile. Metric functions can only be in the SELECT clause. A metric function should always be preceded by a field_name unless it is the only field. If the fields in the query are not in this order, some visualizations do not work.

FunctionDescriptionValid Field TypeADQL Query Examples

Returns the average value for a numeric field.

integer or floating point

SELECT avg(btdata.estimatedtime) FROM browser_records WHERE appkey = 'your_appkey'


Returns a count of events. Only one field can be specified. count() ignores null values, such as those resulting from integer division by zero or empty data fields. The form count(*) is also valid.

Can be used to find data such as the top 10 IP addresses hitting an application.


SELECT count(orderID) AS 'Sales' FROM logs WHERE method='GET'

The following query returns the top 10 ip addresses hitting an application.

SELECT ip, count(*) FROM web_session_records WHERE appkey = "yourApp" LIMIT 10


Returns a count of the number of unique values recorded for a field.

This function returns accurate results up to 3000 unique values, beyond that value the results are approximate due to performance optimizations. Note: The maximum accuracy is reduced if the function is used following a bucketing function or group by term.


SELECT distinctcount(field_name) FROM logs


Returns the maximum recorded value of a numeric field. Only one field can be specified.

integer, floating point

SELECT max(numeric_field_name) FROM transactions


Returns the minimum recorded value of a numeric field. Only one field can be specified. 

integer, floating point

SELECT min(numeric_field_name) FROM transactions

stdev(numeric_field_name)Returns the population standard deviation of a numeric field. This value cannot be used to sort or filter resultsinteger, floating pointSELECT stdev(numeric_field_name)FROM transactions

Returns the sum of the values of a numeric field. Only one field can be specified.

integer, floating point

SELECT sum(numeric_field_name) FROM transactions

percentile(numeric_field_name, percent)

Returns the specified percentile values. Multiple percentiles can be specified. The percentile must be between 0 and 100.

integer, floating point

SELECT percentile(responseTime, 50, 75, 90, 95) FROM transactions


Returns statistics for a field, including count, min, max, average, sum.

integer, floating point

SELECT stats(numeric_fieldName) FROM transactions WHERE application='yourApp'

Bucketing Functions

Bucketing functions operate on a field and yield a single value or range of values that are grouped together for another aggregation (such as a series of counts).
FunctionDescriptionValid Field TypeADQL Query Examples
range(field_name, n, (n1, n2), (n4, n5) and so on)

This bucketing function returns aggregations bucketed into ranges. The parameters can be a single value, x, or pairs of values "x,y".

Pairs of values (x, y) define an explicit bucket from x, inclusive, to y, exclusive. Single values x define a range from the previous argument if it is also a single value, inclusive, to x, exclusive. A single value appearing immediately after the field defines a bucket starting at negative infinity, a single value at the end of the parameter list defines a bucket ending at positive infinity.

A single value surrounded on both sides by pairs of values is ignored.

integer, floating point, and dates

This range query:

SELECT range(segments.transactionTime, 0, (10, 20), 30, (40, 50), 60, 70) FROM transactions

Results in the following buckets:

[-Inf, 0), [10, 20), [40, 50), [60, 70), [70, +Inf)

series(field_name, interval_size)

This bucketing function generates a series of buckets based on the provided interval.  If there are no values in a specific bucket, it is omitted from the results.

Date units up to weeks are supported. For date fields, use intervals such as:

  • '1m' for minutes
  • '1h' for hours
  • '1w' for weeks

For numeric fields, specify the interval as an integer.

Use the time range selector in the upper right to get a larger data set in your results.

The series function operates also with extended bounds. Extended bound forces the histogram aggregation to start building intervals from a specific minimum value and expands the interval up to a given maximum value. The series function operates with the interval value alone as well as with both the interval value and the extended bounds. Extended bounds argument does not operate as a filtering bucket. If the minimum value of extended bounds is higher than the values extracted from the events, the events will still dictate what the first bucket is. This rule also applies to the maximum value of extended bounds and the last bucket.

Series supports the following keyword arguments:

offset: Changes the start value of each series from 0 by the specified positive (+) or negative offset (-) duration, such as 1h for an hour, or 1d for a day. The values of alignStart and alignEnd align the given buckets to the start and end of the extended bounds interval respectively. The function must have extended bounds specified to use these special values. Defaults to 0. Accepts both integer and string values.

rangeStyleBucketKeys: Converts the start and end values in a numeric series to "bucketStart - bucketEnd" format as a string type. For example, "0-10", "10-20", and so on. This output this argument returns is similar to what returned by the range function. The series function must be acting on a numeric type to use this keyword argument. Defaults to false. Accepts only boolean value.

strictEndpoints: Adds a filter on the query starting at the minimum extended bound and extending to the maximum extended bound. This ensures no buckets outside of the extended bounds are present in the results. Defaults to false. Accepts only boolean value.

integer, floating point, and dates

SELECT series(metrics.`Application Server Time (ms)`,10), count(*) FROM browser_records WHERE appkey='yourAppKey'

SELECT series(eventTimestamp, '1h'), count(*) FROM transactions WHERE application='yourAppName'


SELECT series(responseTime, 1, 1, 10, offset="alignStart"), count(*) FROM transactions

SELECT series(responseTime, 100, 1, 1000, rangeStyleBucketKeys=true), count(*) FROM transactions

SELECT series(responseTime, 5, rangeStyleBucketKeys=true), count(*) FROM transactions

SELECT series(responseTime, 10, 5, 50, strictEndpoints=true), count(*) FROM transactions

Functions That Modify Other Functions

FunctionDescriptionADQL Query Examples

filter(metric_function (field_expression), ...) [WHERE] condition_expression)

This function applies a filter to the input of a single metric function and enables computation of aggregations for specific subsets of data. The metric function must be one of the following that returns a single value.

 {count | avg | max | min | sum | distinctcount}

The second argument has the same syntax as a WHERE clause and optionally begins with the WHERE keyword, such as:

SELECT filter(avg(responseTime), WHERE responseTime > 1) FROM transactions WHERE application = "Travel"

filter(sum(numeric_field_name), numeric_field_name > 100)

SELECT 100.0 * filter(count(*), field_name = "value") / count(*) as "%" FROM transactions WHERE application = "yourApp" AND transactionName = "yourValue"


  • No labels