Sign up
Sign in
Sign up
Sign in
Member-only story
Julia Kho
Follow
Towards Data Science
--
26
Share
This article is a guide on advanced window functions for data analysis in SQL. This is definitely a must know for data scientists and analysts. I will first introduce what window functions are, why you should use them, and the 3 types of window functions. Next, I will go through real-life examples to show how each of these functions are used.
Window functions were first introduced to standard SQL in 2003. Per the PostgresSQL documentation:
“A window function performs a calculation across a set of table rows that are somehow related to the current row…Behind the scenes, the window function is able to access more than just the current row of the query result.”
Window functions are similar to the aggregation done in the GROUP BY clause. However, rows are not grouped into a single row, each row retains their separate identity. That is, a window function may return a single value for each row. Here’s a good visualization of what I mean by that.
Notice how the GROUP BY aggregation on the left hand side of the picture groups the three rows into one single row. The window function on the right hand side of the picture is able to output each row with an aggregation value. This may save you from having to do a join after the GROUP BY.
Here’s a quick example to give you a taste of what a window function does.
Let’s say we have some salary data and we want to find to create a column that gives us the average salary for each job title.
--
--
26
Towards Data Science
Julia is an analytics professional who loves to write easy to understand Python and data science articles for beginners
Help
Status
About
Careers
Blog
Privacy
Terms
Text to speech
Teams

source