Understanding SQL: Getting Started with Window Functions | by Rob Taylor, PhD | Sep, 2023


Get more from your aggregations by using SQL window functions

Photo by Components AI on Unsplash

Introduction

When it comes to aggregating data in SQL, window functions provide greater flexibility than aggregations used in conjunction with GROUP BY clauses. While both approaches indeed perform similar functions, window functions differ by virtue of how the outputs are structured. Specifically, window functions apply operations across a set of related rows, where the relation is determined by some grouping, or partition, of the table rows. And, unlike their non-window counterparts that collapse rows into a single output row, all rows retain their separate identities and are present in the output table.

This behaviour is quite unlike your usual run of the mill aggregation and can greatly extend your analytical toolbox beyond simple summary statistics. For example, window functions allows us to compute running sums, moving averages, and even statistical measures like z-scores.

In this post we’re going to take a look at the anatomy and basic functionality of SQL window functions. The focus here is somewhat rudimentary, so if you haven’t come across window functions, or have limited experience using them, this will hopefully be of some interest to you.

For this post we’ll be using some high level summary data about the FIFA World Cup competitions between 1930 and 2022. The placings and statistics were sourced from Wikipedia and is made available under the Creative Commons Attribution Share-Alike license (CC-BY-SA). The data and related information can be found here. For the purpose of this blog I imported the table into my own PostgresSQL database, but if you want to follow along you can grab a copy of the table from my Git repository. In my database this table is called world_cup_placings and an output is shown below:

|year|start_date|end_date|host_country |first_place |second_place  |third_place  |fourth_place|total_teams|matches_played|total_goals|total_attendance|
|----|----------|--------|-------------|------------|--------------|-------------|------------|-----------|--------------|-----------|----------------|
|1930|13/07/30 |30/07/30|Uruguay |Uruguay |Argentina |United…



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*