SQL

Mando Iwanaga
Nov 3 · 4 min read

Window Functions

Hey Readers! This blog will cover what I’ve learned with Window Functions… Getting into a little more advanced SQL! 🤓

I will be using the Chinook database as example data.

What are Window Functions?

The easiest way to understand window functions, for me at least, was to compare it to Aggregate Functions (SUM, COUNT, AVG, MIN, MAX). “Aggregate functions calculate a set of rows and return a single output row.” For example:

SELECT *,
SUM(Quantity) AS total_quantity
FROM InvoiceLine;

As you can see the aggregate function SUM caused the query to output a single row, which is the very first row from InvoiceLine, and the total_quantity.

Now with Window Functions, “it also calculates across a set of rows, but they don’t cause rows to become grouped into a single output.”

SELECT *,
SUM(Quantity) OVER() AS total_quantity
FROM InvoiceLine
LIMIT 10;

This photo helped me understand window functions better:

photo from: https://www.sqlitetutorial.net/sqlite-window-functions/

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Basic Syntax for Window Functions

WINDOW_FUNCTION(Column/Expression) OVER(PARTITION BY
ORDER BY)

OVER is what designates it as a Window Function, and computes across rows without grouping.

PARTITION BY is used to define the window. It allows us to specify how we want to aggregate the rows.

ORDER BY does the same thing as in a regular query. Specifies how we want to order the output data.

WITHOUT Window Function and PARTITION BY:

SELECT InvoiceId,
ROUND(SUM(UnitPrice), 2) AS total_price
FROM InvoiceLine
GROUP BY InvoiceId
LIMIT 10;

WITH Window Function and PARTITION BY :

SELECT InvoiceId,
SUM(UnitPrice) OVER(PARTITION BY InvoiceId) AS total_price
FROM InvoiceLine
LIMIT 10;

Using a Window Function and PARTITION BY , we can return all the rows in the InvoiceLine table and their corresponding total_price .

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Types of Window Functions

photo from: https://www.sqlitetutorial.net/sqlite-window-functions/

Let’s say we wanted to find out the monthly sales and percent change in sales each month. For this, we can use the LAG( ) window function.

For this query, I will create a “view” using the WITH clause. This view acts like a table in which I can query from. It makes queries cleaner and easier to understand. More info on that here.

WITH monthly_sales AS (
SELECT strftime('%m', InvoiceDate) AS month,
ROUND(SUM(Total)) AS total_sales
FROM Invoice
GROUP BY month
ORDER BY month
)
SELECT month,
total_sales,
ROUND(((total_sales/LAG(total_sales, 1)
OVER(ORDER BY month)) - 1) * 100, 2) AS percent_change
FROM monthly_sales;

I am able to use LAG( ) here to obtain the value from 1 row back from the current row. Pretty neat huh!

Thanks for reading! Refer to my repo for source code.

References

Mando Iwanaga

Written by

Data Fanatic

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade