Introduction
A SQL Window function that performs a calculation across a set of rows related to the current row, while preserving the individual rows in the result.
Unlike aggregate functions, which group rows into a single output, window functions return a value for each row based on defined window, using the 'OVER()' clause to specify the partitioning and ordering of the rows within that window.
The name window in SQL is essentially a group of rows or observations within a table or result set. Depending on how the query is structured, multiple windows within the same table might be defined.
Types of Window Functions
Window functions are classified into several types based on their purpose and functionality.
Aggregate Window Function
Aggregate window functions allow you to perform calculations such as finding totals, averages, or counts - across a specific set of rows also know as a Window. This returns result for each individual row in your dataset. These functions combine the power of regular aggregate functions such as SUM, AVG, COUNT etc., with the flexibility of window functions.
AVERAGE() Window Function
Suppose we want to find the average years of experience and specialty, without losing any individual rows.
Query;
Result;
- AVG(grade) OVER(PARTITION BY subject); calculates the average years of experience. The PARTITION BY specialty groups the rows by doctor specialty and calculates the average within each group.
- Every row keeps its individual data, but now each row shows the average years of experience for that specialty, making comparison easy for individual specialty against the average years of experience.
MIN() and MAX() Window Function
- MIN() - This functions returns the smallest value in a set of rows. In the context of window functions, it finds the minimum value within a specified "window" without reducing the total number of rows in the result set.
- MAX() - The function returns the largest value in a set of rows. In this context, it finds the maximum value within a specified window.
Suppose we want to find the highest and lowest number of years of experience within the doctors table.
Query
Result
- MIN(doctor_experience_years) OVER(PARTITION BY doctor_specialty) calculates minimum years of experience for each doctor in the department. Our data only contains one doctor per department.
- MAX(doctor_experience_years) OVER(PARTITION BY doctor_specialty) calculates maximum years of experience for each doctor in the department. Our data only contains one doctor per department.
COUNT() Window Function
This function is used to count the number of rows within a specified window of rows, without collapsing the result set. It is similar to the aggregate function COUNT(), but in this case, it counts rows within each partition while retaining each row in the output.
Query
Result
Ranking Window Function
These functions assign a rank or position to each row within a specific set of rows. They are used to order rows and then assign a numerical rank based on that order. They don't group rows together, instead, they allow each row to retain its data while also showing its rank relative to others.
ROW_NUMBER() Window Function
This function is used to assign a unique, sequential number to each row within a partition of a result set. It resets the numbering for each partition and increments by 1 for every row within that partition or result set.
Suppose we have a table of doctors with their specialty and years of experience, and we want to assign a unique row number to each doctor within their specialty, ordered by years of experience.
Query
Result
**RANK() Window Function
This function assigns a rank to each row within a partition of a result set, based on the values in specified column. The rows with equal values are given the same rank, but this causes gaps in the ranking subsequent rows.
Query
Result
- The PARTITION BY Specialty ranks the doctors within each specialty.
- Ordering by years of experience ranks the doctors within the same specialty in descending order of years of experience.
DENSE_RANK() Window Function
This function is used to rank rows within a result set or partition without skipping any ranks, even when there are ties. Unlike the RANK() function, which skips ranks for tied values, DENSE_RANK() assigns the same rank to tied rows but does not leave gaps in the ranking sequence.
PERCENT_RANK() Window Function
This function calculates the relative rank of a row as a percentage between 0 and 1 within its result set or partition. It is used to find the percentile ranking of a row compared to other rows.
It is a useful function for determining the relative position of a row in a dataset, expressed as a percentage. It is particularly helpful when analyzing the distribution of values across a dataset.
NTILE() Window Function
It divides rows into a specified number of roughly equal-sized groups or buckets, assigning each row a "bucket number". This is useful for distributing data evenly into groups, like quartiles or percentiles.
VALUE() Window Function
This function refers to specific window functions that retrieve values from certain positions within a window frame of rows.
• LAG(): Accesses data from a previous row at a specified physical offset before the current row.
• LEAD(): Accesses data from a subsequent row at a specified physical offset after the current row.
• FIRST_VALUE(): Returns the value of the specified expression from the very first row in the window frame.
• LAST_VALUE(): Returns the value of the specified expression from the last row in the window frame.
• NTH_VALUE(): Retrieves the value from an exact user-defined row position within the window partition.
LEAD() Window Function
This function allows access of data from the following (next0 row relative to the current row in the result set. It helps in comparing a row with future rows, making it useful for operations like calculating differences over time.
LAG() Window Function
The function is the opposite of LEAD(). It allows you to access data from a preceding row relative to the current row. It's useful for comparing the current row with past values.
FIRST_VALUE() Window Function
Both FIRST_VALUE and LAST_VALUE functions are used to retrieve the first or last value in a window of ordered rows. These functions are useful for accessing the first or last data point within a partition or an entire dataset. Useful for retrieving the earliest event in time series, such as the first sale, the first payment, etc.
LAST_VALUE() Window Function
The LAST_VALUE() function returns the last value in the window frame based on the specified ordering. Useful for retrieving the most recent data, like the last transaction or the most recent sale.
Nth_VALUE() Window Function
This function allows you to retrieve the value from a specific row, based on its position, within a window or partition. It is useful when you need to access not just the first or last value, but any specific row in the dataset.
It returns the value of the N-th row within the ordered window.
Conclusion
Window functions are essential functions for data analysis while maintaining individual rows. By mastering aggregate, ranking, and value functions, you can perform calculations, compare records, and uncover meaningful patterns in your datasets.
The key is to understand how OVER(), PARTITION BY, and ORDER BY work together.
Top comments (0)