Exploring a World Without SQL Window Functions
What if SQL did not have window functions? Can we even imagine such a world? Window functions are, after all, the heart and soul of SQL’s advanced capabilities. They allow us to perform complex calculations that would be impossible (or at least, highly cumbersome) otherwise.
However, for the sake of this discussion, let’s imagine a world where SQL does not have window functions. What would we miss? What challenges would we face? And how would we attempt to overcome them?
Why Window Functions?
Before diving into a world without window functions, let’s quickly recap why they are so important. Window functions perform a calculation across a set of table rows that are somehow related to the current row. They are like regular SQL aggregate functions (COUNT(), SUM(), AVG(), etc.) but they do not cause rows to become grouped into a single output row like the traditional aggregations.
Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and many more. They are extremely useful for tasks such as:
- Comparing current data with historical data (using the LAG/LEAD functions)
- Ranking results within a specific category (using RANK/DENSE_RANK)
- Creating running totals or moving averages (using SUM/AVG with an appropriate window frame)
The World Without Window Functions
Now, let’s consider what our SQL lives would look like without these handy tools:
1. Complex Subqueries:
Without window functions, we would likely end up writing complex subqueries and self-joins to solve problems like calculating running totals or retrieving lag/lead information. This would not only make our queries harder to read and maintain but also impact performance significantly.
2. Limited Aggregation:
Traditional aggregation functions group rows, returning a single result row for each group. Without window functions, we wouldn’t be able to retain our individual row data alongside these aggregate calculations. This would limit the complexity and depth of analysis we could perform within a single query.
3. Increased Computation Time:
Calculations that are straightforward with window functions would be more computationally expensive without them. Imagine needing to calculate a moving average without the ability to define a frame for your aggregation — the number of computations needed would increase dramatically!
The Workarounds
Without window functions, we’d have to get creative. Here’s how we might approach some common use cases.
We will use this table in the following examples:
Row Numbering:
In the absence of ROW_NUMBER(), we could use a self-join on the table. The joined table would include rows with a lower or equal id, and then we’d count the number of id’s to simulate the row number.
/* With WINDOW Function */
SELECT
sale_id,
sale_amount,
ROW_NUMBER() OVER(ORDER BY sale_id) AS row_num
FROM WIND_DATA;
/* Without WINDOW Function */
SELECT
s1.sale_id,
s1.sale_amount,
COUNT(s1.sale_id) AS row_num
FROM WIND_DATA AS s1
JOIN WIND_DATA AS s2 ON s1.sale_id >= s2.sale_id
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY s1.sale_id;Lead/Lag Analysis:
Without LAG() or LEAD(), we would again have to rely on self-joins. For example, to get the previous value (LAG), we could join the table on itself where the main table’s row id is 1 less than the joined table’s row id.
/* With WINDOW Function */
SELECT
sale_id,
sale_amount,
LAG(sale_amount) OVER(ORDER BY sale_id) AS prev_sale_amount
FROM WIND_DATA
ORDER BY sale_id;
/* Without WINDOW Function */
SELECT
s1.sale_id,
s1.sale_amount,
s2.sale_amount AS prev_sale_amount
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2
ON s1.sale_id = s2.sale_id + 1
ORDER BY s1.sale_id;Running Total
Calculating a running total without a window function would be cumbersome, requiring a self-join to all previous rows and then summing up the column value.
/* With WINDOW Function */
SELECT
sale_id,
sale_amount,
SUM(sale_amount) OVER(ORDER BY sale_id) AS running_total
FROM WIND_DATA;
/* Without WINDOW Function */
SELECT
s1.sale_id,
s1.sale_amount,
SUM(s2.sale_amount) AS running_total
FROM WIND_DATA AS s1
JOIN WIND_DATA AS s2
ON s1.sale_id >= s2.sale_id
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY s1.sale_id;Ranking — RANK()
/* With WINDOW Function */
SELECT
sale_id,
sale_amount,
RANK() OVER(ORDER BY sale_amount DESC) AS sale_rank
FROM WIND_DATA;
/* Without WINDOW Function */
SELECT
s1.sale_id,
s1.sale_amount,
1+ COUNT(s2.sale_amount) AS sale_rank
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2
ON s1.sale_amount < s2.sale_amount
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY sale_rank;Ranking — DENSE_RANK()
/* With WINDOW Function */
SELECT
sale_id,
sale_amount,
DENSE_RANK() OVER(ORDER BY sale_amount DESC) AS sale_rank
FROM WIND_DATA;
/* Without WINDOW Function */
SELECT
s1.sale_id,
s1.sale_amount,
1+ COUNT(DISTINCT s2.sale_amount) AS sale_rank
FROM WIND_DATA AS s1
LEFT JOIN WIND_DATA AS s2
ON s1.sale_amount < s2.sale_amount
GROUP BY s1.sale_id, s1.sale_amount
ORDER BY sale_rank;And what if we want to use ROW_NUMBER(), RANK() and DENSE_RANK() in one query? The answer is — Correlated Subquery.
SQL DDL
You can use the following DDL statement to create the table used in the above examples. And to play with queries for free and without downloading anything, visit db-fiddle.
-- Create a new table named 'sales'
CREATE TABLE WIND_DATA (
sale_id INT PRIMARY KEY,
sale_amount DECIMAL(5, 2)
);
-- Insert some data into the 'sales' table
INSERT INTO WIND_DATA (sale_id, sale_amount)
VALUES
(1, 200.00),
(2, 500.00),
(3, 200.00),
(4, 800.00),
(5, 500.00),
(6, 300.00),
(7, 900.00),
(8, 300.00),
(9, 900.00),
(10, 800.00);A world without SQL window functions would indeed be a daunting one. We would be left with complex subqueries and self-joins, leading to less readable and less performant queries. It’s clear that window functions are a crucial part of SQL, allowing us to perform complex analyses efficiently. So, let’s take a moment to appreciate the power of window functions and the sophistication they bring to our SQL queries!
Thanks for reading this article. If you found it interesting, consider giving it some claps 👏 to show your support. If you have any questions or comments, feel free to leave them below.
You can also follow me on Linkedin.
