SQL Cheat Sheet Series
SQL: Window Functions
SQL Window Functions are a powerful tool for performing complex analytical queries on your data. These functions allow you to perform calculations across a set of rows that are related to the current row. Window functions are similar to aggregate functions, but instead of grouping rows into a single result, they return a result for each row in the result set.
Window functions are especially useful when you need to calculate values that depend on the ordering or grouping of your data, or when you need to perform calculations on subsets of your data without affecting the overall query result. There are several types of SQL Window Functions and they are: Aggregate Functions, Ranking Functions, Analytic Functions and Windowing Functions.
One common use case for window functions is to calculate percentiles or rankings. For example, if you have a table of employee salaries, you can use a window function to calculate the percentile rank of each salary:
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary DESC) AS percentile_rank
FROM employee_salaries;
This query uses the PERCENT_RANK() window function to calculate the percentile rank of each salary, ordered by salary in descending order. To get started with SQL Window Functions, you’ll need to know some basic commands. Here are a few essential SQL Window Functions commands to get you started:
AGGREGATE FUNCTIONS
This query uses the SUM aggregate function as a window function to calculate the running total of revenue, ordered by date. The OVER clause specifies the window definition, which in this case is the entire result set.
Example:
SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales_data;
RANKING FUNCTIONS
This query uses the RANK ranking function as a window function to assign a rank to each employee’s salary within their department, ordered by salary in descending order. The PARTITION BY clause divides the result set into partitions based on the department column.
Example:
SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employee_salaries;
ANALYTIC FUNCTIONS
This query uses the LAG analytic function as a window function to return the revenue from the previous row, ordered by date. The OVER clause specifies the window definition, which in this case is the entire result set.
Example:
SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue
FROM sales_data;
WINDOWING FUNCTIONS
This query uses the AVG aggregate function as a window function with a RANGE BETWEEN window frame to calculate the moving average of revenue for each date. The window frame specifies that the average should be calculated over the current row and the rows immediately before and after it.
Example:
SELECT date, revenue, AVG(revenue) OVER (ORDER BY date RANGE BETWEEN 1 PRECEDING 1 FOLLOWING) AS moving_avg
FROM sales_data;
In conclusion, these are just a few examples of the many types of SQL Window Functions that can be used to perform complex analytical queries on your data. Whether you need to calculate running totals, assign ranks, perform calculations on subsets of your data, or define custom window frames, there is a window function that can help you accomplish your goals. SQL Window Functions are a powerful tools for doing complex analytical queries on your data. They can allow you to perform calculations across a set of rows that are related to the current row, and are especially useful when you need to calculate values that depend on the ordering or grouping of your data without affecting the overall query result.