Mathematical
Mathematical operators
Addition
Sums two numbers.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
1 + 1 AS add_example1,
1 + 2 + 3 AS add_example2,
1.1 + 2.2 AS add_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
2
6
3.3000000000000003
Subtraction
Subtracts one number from another.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
1 - 1 AS sub_example1,
1 - 2 - 3 AS sub_example2,
2.2 - 1.1 AS sub_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
0
-4
1.1
Negation
Negates the value of a number.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
- 1 AS neg_example1,
- (- 1) AS neg_example2,
- 0 AS neg_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
-1
1
0
Multiplication
Multiplies two numbers.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
1 * 0 AS mult_example1,
1 * 2 * 3 AS mult_example2,
1.1 * 2.2 AS mult_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
0
6
2.4200000000000004
Division
Divides one number with another.
Note that when dividing integers, the result is truncated, meaning that only the integer portion of the result is returned if the numbers do not divide evenly.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
4 / 2 AS div_example1,
4 / 2 / 2 AS div_example2,
3 / 4 AS div_example3,
2.2 / 3.3 AS div_example4
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
2
1
0
0.6666666666666667
Integer division
Divides one integer from another.
Inputs that are not integers are truncated and then divided.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
4 _/ 3 as div_example1,
4.1 _/ 3.1 as div_example2,
4.1 / 3.1 as div_example3,
4.8 _/ 3.8 as div_example4,
4.8 / 3.8 as div_example5,
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
1
1
1.3225,806451612903
1
1.263157894736842
Modulus
Computes the modulus (remainder) from dividing one number with another.
Examples
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
4 % 3 AS mod_example1,
10 % 4 % 2 AS mod_example2,
5.5 % 2.2 AS mod_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
1
0
1.0999999999999996
Power
Raises one number to the power of another.
This operator has the same functionality as POW
and POWER
.
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
2 ^ 2 AS pow_example1,
3.3 ^ 1.1 AS pow_example2
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
4
3.718479005997499
Example with sample data
The following example uses Upsolver sample data that is available to you through Upsolver.
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
orderid AS order_id,
ROUND(nettotal / (1 + taxrate), 2) AS pretax_total,
taxrate,
nettotal
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 3;
Query result
NzwdlehpJ9
349.98
0.12
391.98
6nDo5vqL3B
741.95
0.12
830.98
mqEu3Y96pE
1157.74
0.12
1296.67
Last updated