SQL: Exploring Basic Window Functions
SQL Window functions are analytic functions. Read this article to explore some of these functions with detailed examples.
Join the DZone community and get the full member experience.
Join For FreeSQL Window functions are analytic functions. The window function performs calculations like SUM, COUNT, MIN, MAX, AVG on the set of rows, and this set of rows is called a "Window". Window functions can be categorized into 3 categories:
Aggregate Functions

SUM, COUNT, MIN, MAX, AVG 
Ranking Functions 
RANK, DENSE_RANK, ROW_NUMBER, NTILE

Value Functions 
LAG, LEAD, FIRST_VALUE, LAST_VALUE

Window functions perform calculations at the single row level, unlike GROUP BY, which performs calculations after grouping rows and providing the consolidated results.
OVER(): The aggregate functions are applied after creating a window (set of rows). These windows are created using the "OVER()" clause. The window is created using the PARTITION BY clause and ordering is done using the ORDER BY clause, both PARTITION BY and ORDER BY should be passed in the OVER() function.
OVER(ORDER BY ‘date’ DESC)
OVER(PARTITION BY ‘city’)
OVER(PARTITION BY ‘city’ ORDER BY ‘date’ DESC)
PARTITION BY: When no PARTITION BY clause is used, the entire table is considered as one single partition/window and the aggregation and grouping will be done on the entire table, so the PARTITION BY clause is used to create the partitions by columns.
Exploring OVER(): When we use the empty OVER () function the entire table is considered as one window and the window functions like aggregate function or ranking function will be performed on the entire window. Consider a table named "Orders":
Find the amount sum of total orders placed in the year 2020 using the OVER () function.
Q: SELECT name, city, SUM(amount) OVER() FROM ORDERS WHERE YEAR(orderdate) = 2020
Upon looking at the Orders table, we can see that the total order amount for the city of Austin is 1210 and for the city of Irving is 200. Here the SUM is 1741 for the entire year 2020 for all cities.
Using ORDER BY clause in OVER (): Usage of ORDER BY clause inside the OVER () function yields different results depending on the column on which order is performed.
ORDER BY "amount" DESC:
Q: SELECT name, city, SUM(amount) OVER(ORDER BY amount DESC) AS TOTAL FROM ORDERS
WHERE YEAR(orderdate) = 2020
Since ordering is done in descending order of amount, first the city with the maximum amount is considered. Then add the amount from "Dallas" city, and so on.
AUSTIN (1210) + DALLAS (300) + IRVING (200) + ATLANTA (20) + DALLAS (11) = 1741.
Similarly, if we execute ORDER BY on orderdate, the result will be different.
Q: SELECT name, city, SUM(amount) OVER(ORDER BY orderdate DESC) AS TOTAL FROM ORDERS
WHERE YEAR(orderdate) = 2020
Instead of SUM, RANK, or Other, the window function can be used. In the above query, to see the Ranking of cities placed orders in descending order of dates:
Q: SELECT name, city, RANK() OVER(ORDER BY orderdate DESC) AS RANK FROM ORDERS
WHERE YEAR(orderdate) = 2020
In case the ranking of columns are the same, for example, if the orderdate of 2 cities are the same, then the RANK function has a specific behavior.
Austin and Dallas's order has the same Date, and executing the same query will yield the below result:
Both Austin and Dallas now have rank 3, in the case of “tie” RANK function increment the next row with 2 leaving the gap between 3 & 5. That’s where the DENSE_RANK function comes handy and the next value after a tie will properly be incremented by 1, leaving no gaps, if we execute the query with DENSE_RANK, yields.
Q: SELECT name, city, DENSE_RANK() OVER(ORDER BY orderdate DESC) AS RANK FROM ORDERS
WHERE YEAR(orderdate) = 2020
Since we have used DENSE_RANK, the next value after the tied value is properly incremented by 1, leaving no gaps.
Note: RANK and DENSE_RANK function never work without ORDER BY clause.
Q: SELECT name, city, DENSE_RANK() OVER() AS RANK FROM ORDERS
WHERE YEAR(orderdate) = 2020
Query results in ERROR, “The function 'DENSE_RANK' must have an OVER clause with ORDER BY.”
PARTITION BY: When PARTITION BY clause is used within OVER(), it divides the results into a set of partitions. The window function when used with OVER(PARTITION BY ‘XXX’) is applied to each row/partition separately.
Q. SELECT name, city, SUM(amount) OVER(PARTITION BY city) AS TOTAL
FROM Orders
We have created the partition based on "city". Each row will represent a separate partition and the SUM is applied on each row. The above query will yield in:
PARTITION BY can be combined with ORDER BY with OVER() Function. In that case, the flow of execution is:
 First, the PARTITION BY creates the Partitions, then the SUM() function is applied on the partitions.
2. The ORDER BY clause sorts the rows within a Partition.
LEAD and LAG: with the help of the LEAD function, we can access data of the next row and further rows, whereas the LAG function helps in accessing data from the previous rows. These functions are extremely helpful in comparing the values from the previous and next rows. In Our example of the "Orders" table:
Q: SELECT name, city, LEAD(amount) OVER(PARTITION BY city ORDER BY amount ASC) AS next_amount_value FROM Orders
The above query is partitioned by city, which is why "Atlanta"/"Austin"/"Irving" do not have next rows. Therefore, the ‘next_amount_value’ is null. City "Dallas" has 3 rows.
Row No.7 from the "Orders" table screenshot has amount 300 customer name "Mick":
The result of window function query returns:
This is the next value of 300, which is why "next_amount_value" in front of Customer with Name "Mick" is showing 400.
Similarly, "Sam" is having value:
But the window function query returns 1000 which is the next LEAD value of 400:
The LAG() function returns the previous value of the current value,
Q: SELECT name, city, LAG(amount) OVER(PARTITION BY city ORDER BY amount ASC) AS next_amount_value FROM Orders
"Sam" is having a value of 400:
But its previous row value is 300:
NOTE: LEAD() / LAG() function never works without ORDER BY clause.
Opinions expressed by DZone contributors are their own.
Comments