After learning the fundamentals of SQL such as SELECT and GROUP BY, more complicated calculations will come into view such as calculating a running sale or ranking employees by department. Here come window functions. They make calculations in a stringent group of rows pertaining to the current row, but do not collapse. This tutorial will familiarize you with functions of windows easily.
What Exactly is a Window Function?
A window function calculates a value across a set of rows, referred to as a window or window frame, at its center. Aggregate functions (such as SUM(), AVG(), COUNT()) are aggregated with the use of a GROUP BY: window functions, however, do not store the original rows collapsed. They instead give out a value in each row depending on the given window.
Suppose you have a list of sales in a month. A simple SUM() function would provide you with a single number: the total amount of sales per the entire period. Another such operation is the window operation which may compute running total, that is, the sales at the close of each month, and in front of the sales figure of that particular month, there is the total sales at the end of that month. The old rows are kept, only that some more, closer to calculation, data are added to them.
This ability has been incredibly convenient in doing tasks such as:
- Calculating moving averages.
- Ranking items within categories.
- Finding the percentage of a total for each row.
- Comparing a row's value to the average of its group.
The Key Components of a Window Function
To understand how window functions work, you need to be familiar with a few key concepts that define the "window" over which the function operates.
The OVER() Clause: Defining the Window
A window function starts with the OVER() clause. It is what makes it different to a normal aggregate function. The OVER () clause instructs the database on how to support the calculation by grouping the rows. An empty OVER() clause denotes that the window contains all the rows contained in the result set. One such example would be to calculate the average salary of the whole firm and place the same figure beside the salary of every employee.
PARTITION BY: Creating Sub-Groups
It is not always desirable to carry out a calculation on the full dataset. You need to make smaller individual windows, depending on some criteria. That is the effect of PARTITION BY. It is similar to GROUP BY, only that it splits the rows into partitions but it does not couple of rows together. Each partition is then subjected to window use separately.
To illustrate, the following query can be used to calculate the average salary of employees in each department using one table (having employees of various departments), i.e. PARTITION by department: Average salary of Sales team would be calculated and displayed among all sales employees, and the same thing would be done with the Engineering team.
ORDER BY: Sorting Rows Within the Window
Although PARTITION BY generates the groups, the rows contained in each partition are sorted by ORDER BY. This is important on functions that require the succession of the rows such as the one that make calculation of rankings or running totals. As an example, to rank employees based on their date of hire you would get ORDER BY hire date. The order defines the process through which the function manipulates the rows.
ROWS/RANGE: Specifying the Window Frame
To control further, one can specify a frame of a window that is with reference to the current row. This defines the rows in the partition to be incorporated in the calculation. You can define a frame, e.g. the current row, two rows before it, to compute a three-month moving average. This provides you with finer control over the precise set of rows to be used in any particular calculation.
Common Types of Window Functions
The most common analytical functions of windows can be designed into three broad classifications based on analytical applications.
Aggregate Window Functions
That is all the aggregate functions stripped of their old form, and given a new form. Combined with the OVER() clause, such functions as SUM, AVG, COUNT, MIN and MAX become window functions.
Running Totals
Sum cumulative values by using SUM with an order by clause. This is ideal in monitoring some improvement with time such as the revenue accrued on a daily basis accumulating throughout the month.
Group Averages
AVG() can be used with PARTITION by to provide comparison of the value of an individual row and the average of the group. An example would be to look at the relationship between the test score of each student to class average.
Total Count
Check the count of items in each partition with COUNT (). An example is that you can place the number of persons in a department beside the profile of an employee.
Ranking Window Functions
Ranking functions serve to issue a ranking of all rows in a partition with regards to a given order. This comes in very handy in developing leader boards or finding the best performers.
RANK()
This is a function which assigns a rank to every row. In case there are two rows with identical values, they receive similar rank and a void is created in the sequence. As an illustration, when two salespersons are tied at the 2nd position both are assigned 2 nd rank and the 4 th position at the next rank is assigned 5 th rank.
DENSE_RANK()
This is similar to RANK but does not leave out gaps in the ranking order. The rank of the two salespeople in the above case would be 2, and the succeeding case would be 3.
ROW_NUMBER()
In this role, a row in the partition is given a distinct number, a sequence number, starting with number 1. They will be assigned different numbers even in case there are tied rows.
NTILE(n)
This feature splits the rows into a given quantity of groups (or tiles) and assigns group numbers to rows. As will be illustrated in the example below, NTILE(4) would consider the data in sections of four (where row 1 is assigned 1, 2 3 or 4).
Value Window Functions
Value functions allow you to access data from other rows within your window. This is useful for comparing values across rows.
LAG()
This function allows you to access data from a previous row in the partition. For example, you could use LAG() to compare this month's sales to last month's sales.
LEAD()
This is the opposite of LAG(). It lets you access data from a following row. This could be used to calculate the difference between the current order date and the next one.
FIRST_VALUE() and LAST_VALUE()
These functions return the value of the first or last row in the ordered partition, respectively. This can be helpful for finding the initial or final value in a series.
Conclusion
Mastering SQL window functions unlocks powerful analytical capabilities. They enable complex in-database calculations and comparisons, leading to cleaner, efficient queries. Forget complicated subqueries or external tools; let SQL do the work. Start with basic aggregates like SUM() and AVG(), then explore ranking functions. Understanding window functions significantly elevates your SQL expertise, tackling data challenges more effectively.
