A Redshift Window function is a highly effective method to write queries for analytics, dashboards and reports. Window functions are a bit on the higher end of sql skills; however, I promise you, once you understand how it works, your life will not be the same. If you are new to Redshift, you can start by reading our post on the key Redshift differences.
A window in redshift is nothing more than a partition on your data. A window function takes the input data, partitions it and calculates a value for every row in the partition. The value thus calculated is based on the function you choose operating on all the rows within each partition.
To use redshift window functions effectively, you need to understand three key concepts: Partition, Order and Frames.
Let’s look at what they mean.
The table below lists all 25 Redshift window functions with description and syntax in alphabetic order. An important fact, Redshift only supports two types of window functions: aggregate and ranking.
So, what’s the difference between the two you ask ?
Redshift aggregate functions returns a single output value for all your input data in a single column, if your data is not partitioned. If the data is partitioned, you get an output value for every partition. Most common examples of redshift aggregate functions are count, min, max, sum.
A rank function on the other hand, returns the position (numeric sequence starting at 1) of a value with respect to other values in a column. If your data is partitioned, then the ranking sequence starts at 1 for every partition. Redshift has 5 ranking functions: dense_rank, ntile, percent_rank, rank and row_number.
Do you know what the different data types in Redshift are, and when to use them ?
Redshift has 14 different datatypes. Read more in data types to make you the office hero.