Overview
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.
| Operator | Name | Description |
|---|
| * | asterisk | Multiplication |
| / | slash | Division of two integer types performed as integer division. Integer division by zero returns null. |
| % | modulo | Calculates the reminder after the division of two integers. Valid only for integers. |
| + | plus | Addition. Requires a space before a numeric literal. |
| - | minus | Subtraction. 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.
| Query | Result |
|---|
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.