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

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

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

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

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

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

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

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

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