Arithmetic operators are used to generate numeric output from one or more input expressions.
The input expressions must be numeric (fixed-point or floating point), except in the following cases:
The unary operator + can take a number string, but the string is implicitly converted to its corresponding numeric value.
The binary operator - can be applied to DATE expressions.
Operator
Syntax
Description
+ (unary)
+a
Returns a, which causes implicit conversion of a to a numeric value. If a is a string, but the string can’t be converted to a numeric value, an error is returned.
+
a + b
Adds two numeric expressions (a and b).
- (unary)
-a
Negates the input numeric expression.
-
a - b
Subtracts one numeric expression (b) from another (a).
-
a - b
Subtracts one date expression (b) from another (a). The result is an integer number of days. Subtraction is the only arithmetic operation allowed on DATE expressions.
*
a * b
Multiplies two numeric expressions (a and b).
/
a / b
Divides one numeric expression (a) by another (b). For functions that return 0 when dividing by 0 or NULL, see DIV0 and DIV0NULL.
%
a % b
Computes the modulo of numeric expression a per b. For more information, see MOD.
The scale and precision of the output of an arithmetic operation depends on the scale and precision of the input.
Snowflake uses calculations to preserve scale and precision in the numeric output generated by various arithmetic operations (multiplication, division, and so on). The following descriptions are used in this section:
Number of digits (L) to the left of the decimal point in a numeric value.
Number of digits (S) to the right of the decimal point in a numeric value.
Total number of digits (P) in a numeric value, calculated as the sum of its leading digits and scale (that is, P = L + S). Note that precision in Snowflake is always limited to 38.
Also:
Fixed-point data types (NUMBER, DECIMAL, and so on) utilize precision and scale. For example, for the DECIMAL(8,2) data type, precision is 8, scale is 2, and the number of leading digits is 6.
Floating-point data types (FLOAT, DOUBLE, REAL, and so on) utilize 8-byte doubles.
For outputs, note that these are maximum number of digits; the actual number of digits for any given output might be less.
Multiplication¶When performing multiplication:
The number of leading digits in the output is the sum of the leading digits in both inputs.
Snowflake minimizes potential overflow (due to chained multiplication) by adding the number of digits in the scale of both inputs, up to a maximum threshold of 12 digits, unless either of the inputs has a scale larger than 12, in which case the larger input scale is used as the output scale.
In other words, assuming a multiplication operation with two inputs (L1.S1 and L2.S2), the maximum number of digits in the output are calculated as follows:
- Leading digits:
L = L1 + L2- Scale:
S = min(S1 + S2, max(S1, S2, 12))- Precision:
P = L + S
Note
Snowflake performs integer multiplication for numeric values, so intermediate results might cause some overflow; however, the final output won’t overflow.
Examples¶SELECT 10.01 n1, 1.1 n2, n1 * n2;
Copy
+-------+-----+---------+ | N1 | N2 | N1 * N2 | |-------+-----+---------| | 10.01 | 1.1 | 11.011 | +-------+-----+---------+
SELECT 10.001 n1, .001 n2, n1 * n2;
Copy
+--------+-------+----------+ | I1 | I2 | I1 * I2 | |--------+-------+----------| | 10.001 | 0.001 | 0.010001 | +--------+-------+----------+
SELECT .1 n1, .0000000000001 n2, n1 * n2;
Copy
+-----+-----------------+-----------------+ | N1 | N2 | N1 * N2 | |-----+-----------------+-----------------| | 0.1 | 0.0000000000001 | 0.0000000000000 | +-----+-----------------+-----------------+Division¶
When performing division:
The number of leading digits in the output is the sum of the leading digits of the numerator and the scale of the denominator.
Snowflake minimizes potential overflow in the output (due to chained division) and loss of scale by adding 6 digits to the scale of the numerator, up to a maximum threshold of 12 digits, unless the scale of the numerator is larger than 12, in which case the numerator scale is used as the output scale.
In other words, assuming a division operation with numerator L1.S1 and denominator L2.S2, the maximum number of digits in the output are calculated as follows:
- Leading digits:
L = L1 + S2- Scale:
S = max(S1, min(S1 + 6, 12))- Precision:
P = L + S
If the result of the division operation exceeds the output scale, Snowflake rounds the output (rather than truncating the output).
Note
Similar to multiplication, intermediate division results might cause some overflow; however, the final output won’t overflow.
Examples¶SELECT 2 n1, 7 n2, n1 / n2;
Copy
+----+----+----------+ | N1 | N2 | N1 / N2 | |----+----+----------| | 2 | 7 | 0.285714 | +----+----+----------+
SELECT 10.1 n1, 2.1 n2, n1 / n2;
Copy
+------+-----+-----------+ | N1 | N2 | N1 / N2 | |------+-----+-----------| | 10.1 | 2.1 | 4.8095238 | +------+-----+-----------+
SELECT 10.001 n1, .001 n2, n1 / n2;
Copy
+--------+-------+-----------------+ | N1 | N2 | N1 / N2 | |--------+-------+-----------------| | 10.001 | 0.001 | 10001.000000000 | +--------+-------+-----------------+
SELECT .1 n1, .0000000000001 n2, n1 / n2;
Copy
+-----+-----------------+-----------------------+ | N1 | N2 | N1 / N2 | |-----+-----------------+-----------------------| | 0.1 | 0.0000000000001 | 1000000000000.0000000 | +-----+-----------------+-----------------------+Addition and subtraction¶
For addition or subtraction:
The number of leading digits in the output is the largest number of leading digits of the inputs plus 1 (to preserve carried values).
The scale for the output is the largest scale of the inputs.
In other words, assuming an addition or subtraction operation has two inputs (L1.S1 and L2.S2), the maximum number of digits in the output are calculated as follows:
Other N-ary operations¶
- Leading digits:
L = max(L1, L2) + 1- Scale:
S = max(S1, S2)- Precision:
P = L + S
For all other arithmetic operations with more than one numeric input, such as modulo (a % b or MOD):
The number of leading digits in the output is the largest number of leading digits of the inputs.
The scale for the output is the largest scale of the inputs.
In other words, assuming an n-ary operation with inputs L1.S1, L2.S2, etc., the maximum number of digits in the output are calculated as follows:
Unary operations¶
- Leading digits:
L = max(L1, L2, ...)- Scale:
S = max(S1, S2, ...)- Precision:
P = L + S
Unary arithmetic operations have the same output precision and scale as the input precision and scale, except for ROUND, which allows explicitly specifying the output scale.
Bitwise operations¶The list of supported bitwise arithmetic operations is available at Conditional expression functions.
Note
For numeric values, bitwise operations only operate on the leading digits in the input. The output always has a scale of zero.
For binary bitwise operations, the output has the same number of leading digits as the maximum leading digits in the input.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4