Redshift Window Function: A powerful way to query better

window depicting redhisft windows function

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.

  • Partition: This involves diving the data into smaller subsets. To form a visual, think horizontal slice
  • Ordering: Once you have your data partitioned, you assign a logical sequence to the partition based on a field in your data
  • Frames: In certain scenarios, you might need to further limit the data in your partition. You do this using frames.

Redshift Window function list

redshift window function

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.

Key takeaways from this post

  • Not all queries require using advanced window functions. If you are new to writing SQL, try using the basic functions such as COUNT, MIN, MAX and ROW_NUMBER.  
  • Window functions are limited to SELECT and ORDER BY. For instance, you cannot use a window function in a GROUP BY , SORT or WHERE clause. If you do need to, then consider writing nested SQL queries.

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.

Amazon Redshift helpful links

Redshift SQL Commands

Link to Redshift sql commands with syntax and examples from the official redshift documentation, listed in alphabetical order

Redshift Window Function Documentation

Link to the latest Window Function documentation from the Database Developers Guide

Service Offerings by Obstkel

Get to know the AWS Cloud Services offered by Obstkel

Building a Data Warehouse

This is an excellent blog post from AWS by Stephen Borg on Building a Data Warehouse using Amazon Redshift

Email us at : info@obstkel.com

Copyright 2021 © OBSTKEL LLC. All rights Reserved.