ADQL Reference:

Your Rating:
Results:
PatheticBadOKGoodOutstanding!
12 rates

Overview

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.

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 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
avg(numeric_field_name)

Returns the average value for a numeric field.

integer, floating point, datetime

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

count(field_name)

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.

all

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

distinctcount(field_name)

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.

The maximum accuracy is reduced if the function is used following a bucketing function. The nested distinctcount queries trade accuracy for saving memory because both high-cardinality set and higher precision consume more memory.

For example, SELECT transactionName, distinctcount(userId) FROM transactions returns less accurate result than `SELECT distinctcount(userId) FROM transactions.

all

SELECT distinctcount(field_name) FROM logs

max(numeric_field_name)

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

integer, floating point, datetime

SELECT max(numeric_field_name) FROM transactions

SELECT nodeName, max(eventtimestamp) FROM logs

min(numeric_field_name)

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

integer, floating point, datetime

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
sum(numeric_field_name)

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

integer, floating point, datetime

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, datetime

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

stats(numeric_field_name)

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

integer, floating point, datetime

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

totalResultCount()Returns the total count of events. Does not accept a field.

SELECT transactionName, count(*), count(*)/totalresultcount() FROM transactions

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)

series(field_name, interval_size, lower_extended_bound, upper_extended_bound)

This bucketing function generates a series of buckets based on the provided interval. If there are no values in a specific bucket and no extended bounds specified, 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.

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 alignStart and alignEnd align buckets to the start and end of the extended bounds arguments 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. Extended bound forces the histogram aggregation to start building intervals on a specific minimum value and expands the interval up to a given maximum 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, offset=1), count(*) FROM transactions

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

SELECT series(responseTime, 2, 1, 5, strictEndpoints=true, rangeStyleBucketKeys=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