Numeric calculations

Data Types and Functions in Snowflake

Jake Roach

Field Data Engineer

Numeric calculations

Three sorts of numeric calculations and manipulations.

Comparing numeric values

Comparison opreators help us compare or evaluate multiple values

=, are two values equal?
!=, are two values not equal?
<, is one value less than another?
>, is one value greater than another?
<= less than or equal to
>=, greater than or equal to

Return true or false!

        1 = 1,   -- true

        1 != 1,   -- false

        1 < 2,    -- true

        1 > 2,    -- false

        1 <= 2,    -- true

        2 >= 2    -- true

               ...

WHERE 1 = 1   -- Filter records

Arithmetic in Snowflake

SELECT

    <#> + <#>,        -- 2 + 2  ->  4
    <field> - <#>,    -- 4 - 1  ->  3
    <field> * <#>,    -- 3 * 2  ->  6
    <field> / <#>,    -- 9 / 3  ->  3

                ...

Can be performed between a permutation of # and field

Arithmetic operators allow us to perform "math" with numeric values

+, addition
-, subtraction
*, multiplication
/, division

Arithmetic

SELECT
    student_name, exam_score,


    exam_score + 10 AS add_points,      -- Add 10 points to each student's grade

    exam_score * curve AS curved,       -- Curve the grade by 10%

    exam_score / 2 AS weighted          -- Reduce the weight of the test


FROM STUDENTS.grades;

              student_name  |   exam_score   |  add_points  |  curved  |  weighted  
             -------------- | -------------- | ------------ | -------- | ----------
               Ryan         |       78       |      88      |   85.8   |     39
               Tatiana      |       89       |      99      |   97.9   |    44.5
               Pankaj       |       74       |      84      |   81.4   |     37

Aggregation functions generate summary data

An illustration of GROUP BY aggregation records.

Aggregation functions

 SELECT

     <1>,


     SUM(<field>),  -- Returns the total of a column

     AVG(<field>)   -- Finds the average value of a column


 FROM ...


 GROUP BY <1>;

Must GROUP BY non-aggregated fields!

GROUP BY ALL

Aggregation functions

SELECT
    exam_name,

    SUM(correct_answers) AS total_correct_answers,      -- Total # correct
    AVG(exam_score) AS avg_exam_score,                  -- Average exam score


    ROUND(AVG(exam_score), 1) AS rounded_exam_score     -- ROUND(<value>, <n>)


FROM STUDENTS.grades
GROUP BY exam_name;  -- GROUP BY to aggregate records, otherwise error

ROUND() takes a value to round, and the number of digits to keep after the decimal point

Aggregation functions

   exam_name    |  total_correct_answers  |  avg_exam_score  |  rounded_avg_exam_score 
  ------------- | ----------------------- | ---------------- | ------------------------ 
   Calculus I   |           871           |      89.11111    |           89.1     
   Biology      |           776           |      87.47777    |           87.5
   English III  |           541           |      91.33333    |           91.3
   Python       |          1179           |      92.78787    |           92.8
   Finance      |           349           |      96.14156    |           96.1

Values were generated using:

SUM(correct_answers)
AVG(exam_score)
ROUND(AVG(exam_score), 2)

Let's practice!

Data Types and Functions in Snowflake