Assigning row numbers
1. Assigning row numbers
Welcome in! I'm Jake, and I'll be your instructor as we journey into one of Snowflake's more advanced capabilities; window functions!2. Window functions
Window functions are used to perform calculations across a "window" of rows in a query AND return a value for each row. We'll be introduced to new functions and syntax we may not have seen before. Typically, these window functions will fall into one of two categories; ranking and aggregation. There might even be a few functions that you recognize.3. Window functions
Window functions help us to answer new types of questions. By the end of the course, we'll have the tools to find the third most popular concert at each venue, identify how item sales vary day-to-day, and monitor the rolling average of viewership.4. Traditional functions
In Snowflake, traditional functions require a `GROUP BY` clause towards the end of the query. When a function like `SUM` is used, the result is a "collapsed" set of records with one column for the grouped values and another for the sum. While useful, this results in a good bit of information being lost.5. Window functions
Window functions don't require a `GROUP BY` clause. This means that all queried records can be maintained, rather than being "collapsed" like with traditional functions. Using our previous example, the resulting sum is stored as a new field in each record. This is quite powerful, especially when we'd like to compare individual records to a summary measure.6. Assigning row numbers
The first window function that we'll use is `ROW_NUMBER`. `ROW_NUMBER` assigns a row number to each record in a result set. This syntax provide the basic framework for how we'll use window functions in this course. First, we have our call to `ROW_NUMBER`. That's followed by the `OVER` keyword. `OVER` defines the window that `ROW_NUMBER` will be applied to. Within `OVER`, we order by some field. This helps us to assign row numbers in a logical order. Finally, we alias the column. If you look closely, there's no `GROUP BY`!7. Concert attendance
Let's say we want to assign row numbers to records from the `attendance` table based on the `time_spent_minutes` column from least to greatest. After we select the `customer_id` and `event_name` fields, we call `ROW_NUMBER`. That's followed by `OVER`, where we `ORDER BY` the `time_spent_minutes` field. If we'd like, we could `ORDER BY` in descending order using `DESC`. Each record in the result set has a row number, and the output is not collapsed! While Snowflake does not sort the final output by the ranking, we'll show them sorted for clarity. Remember, the syntax for this window function is `ROW_NUMBER` `OVER` parenthesis `ORDER BY`.8. Let's practice!
Now, it's time to build your first window functions in Snowflake!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.