Skip to end of metadata
Go to start of metadata

ADQL Reference:


Math expressions are field expressions that use arithmetic operators. These expressions can be used in ADQL queries in both the SELECT and WHERE clauses.

field_expression  BINARY_OPERATORS field_expression

Arithmetic Operators

Math operators are valid on integer and float data fields, constants (for example, 3.14), and on functions that return a single integer or float value, with the exception of % (modulo). The operators *, / and % have higher precedence than + and -. Parentheses can be used to control the order of operations, a unary minus (-) is also supported. 


Division of two integer types performed as integer division. Integer division by zero returns null. 

%moduloCalculates the reminder after division of two integers. Valid only for integers.
+plusAddition. Requires a space before a numeric literal.
-minusSubtraction. Requires a space before a numeric literal. Can also be used as unary_operator.

Math Expression Usage

Math expressions can be used in several ways.

SELECT regionId % 4,  count(*) FROM transactions WHERE application = "yourApp"Math expression is used as a GROUP BY term in an implicit group by query
SELECT field_name1, (field_name2 + field_name3) * 3.14  FROM transactions WHERE application = "yourApp"Math expression is used as a composite field.

SELECT sum(field_name1) / count(field_name2) FROM transactions WHERE application = "yourApp"

SELECT filter(sum(field_name1), WHERE field2 IS NOT NULL) / count(field_name3) FROM ...

Math expression used on an aggregation function.

SELECT avg(field1 + field2) FROM event_type WHERE ...

SELECT series(field1 + field2, 1)  FROM event_type WHERE ...

Math expression used within an aggregation function.

SELECT * FROM transactions WHERE (responseTime * cartValue) > 100

SELECT * FROM transactions WHERE (regionId % 3) IN (1, 2)

SELECT * FROM transactions WHERE (responseTime * cartValue) BETWEEN (100, 200)

Math expression used in a WHERE clause with comparison operators.

SELECT segments.userData.itemTitle, (segments.userData.itemPrice / 100) AS Price FROM transactions WHERE application = 'ECommerce 2.0' AND transactionName = '/product_details.xhtml' AND Price > 20

Convert a value in cents to dollars.  This query converts segments.userData.itemPrice from cents to dollars and shows results where the price is greater than $20.

SELECT transactionName, (responseTime / 1000) AS responseSeconds, userExperience, requestGUID FROM transactions WHERE application = "ECommerce 2.0" AND responseSeconds > 5

Convert a value such as average response time to seconds instead of milliseconds and view each transaction that is greater than X seconds.

Usage Notes

If any field or intermediate expression result for an expression is null, the entire expression will be null.

Math expressions in the WHERE clause are evaluated per record, not in aggregate.

Fields and metric functions can not be combined in the same expression, for example a query for the following type is not valid: 

SELECT responseTime + sum(cartValue) FROM ...

Mathematical operations that operate on every event (as opposed to on aggregation functions) will reduce query performance. Avoid statements such as:

WHERE (responseTime - 200) > 800 and use a construct such as WHERE responseTime > 1000 instead.

  • No labels