Arithmetic Expressions
Arithmetic expressions perform arithmetic operations on numerical values.
Supported Arithmetic Operations
Tamr supports arithmetic expressions for numerical values that use the operators +
, -
, *
, %
, and /
, as well as unary negation (that is, negative numbers are identified by -
before the value).
To keep the data in the numerical format, you may need to use a data type conversion function such as to_int
, to_double
, or to_long
.
The Remainder Operator and the pmod() Function
The %
operator returns the remainder of the division of the dividend by the divisor. As in Java, it ignores negative divisors. It allows positive and negative dividends.
For example, for a positive dividend 19, the result of 19%5
is 4. For a negative dividend of -19, the result of -19%5
is -4.
In addition to the %
operator, Tamr supports the pmod() function. pmod()
uses the following formula:
for numbers x and n, (x % n + n) % n
.
pmod()
differs in behavior from the %
operator in that it returns only positive remainders, even in cases when the dividend is a negative number. Specifically:
- The
%
operator implements the standard Javamodulo
function which returns negative results if the dividend is negative, as in-19%5 is -4
- In Tamr Core, the
pmod()
function returns strictly positive results when given any input (positive or negative dividend).
For example, for the pair of values -19,5 the %
operator in -19%5
returns -4, but the pmod(-19,5)
function calculates and returns the positive remainder of 1. Specifically, -19%5
is -4, -4+5=1 and 1%5 is 1.
Examples of the % Operator
The following query uses the %
operator:
SELECT dividend % divisor AS result
It produces the following results for these values:
-19 % 5 = -4
22 % 5 = 2
5 % 3 = 2
5%-3 = 2 (negative divisor is ignored)
Note: You cannot divide by zero and therefore x%0
is null.
Updated about 2 years ago