Databricks SQL Series: Advanced Analytics in Databricks SQL — Using Window Functions — Part 3

A Detailed Guide on Window Functions

Synopsis

Introduction

Window functions in Databricks SQL are used for performing advanced data analytics by allowing complex calculations across sets of table rows related to the current row. These functions are essential for tasks such as running totals, moving averages, and ranking, enabling deeper insights from your data.

This blog will guide you through the fundamentals and advanced uses of window functions, illustrating their practical applications with detailed examples. By mastering these functions, data professionals can significantly enhance their analytical capabilities within Databricks SQL.

Getting Started Window Functions

• Window functions operate over a defined subset or “window” of rows in your result set, allowing for complex calculations.
• The general syntax for window functions includes specifying the function, partitioning the data, and ordering within partitions.
• Syntax: function_name(expression) OVER (PARTITION BY column_list ORDER BY column_list ROWS or RANGE clause).
• PARTITION BY divides the data into groups (partitions) where the window function will be applied separately.
• ORDER BY determines the order of rows within each partition.
• ROWS or RANGE clauses define the frame of rows relative to the current row, specifying which rows are included in the calculations.
• Window functions do not aggregate rows into a single output row; instead, they maintain the number of rows while adding the results of calculations as new columns.
• These functions are essential for tasks that require analyzing trends over a subset of data, such as calculating running totals, moving averages, and ranking rows based on specific criteria.
• Using window functions can simplify complex SQL queries by avoiding the need for multiple joins or subqueries.

Understanding and effectively utilizing window functions can significantly enhance your data analysis capabilities within Databricks SQL.

Common Window Functions in Databricks SQL

Window functions are powerful tools in SQL that allow for advanced data analysis over a set of rows related to the current row. Here, we’ll explore some of the most common window functions in Databricks SQL, providing detailed examples to illustrate their usage.

ROW_NUMBER()

Explanation: The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.

Example: Suppose we have a table employees with columns employee_id, department_id, and salary. We want to assign a unique rank to each employee within their department based on their salary.

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM
  employees;        

Explanation:

• PARTITION BY department_id creates partitions for each department.
• ORDER BY salary DESC orders employees within each department by their salary in descending order.
• ROW_NUMBER() assigns a unique rank to each employee within their department.

RANK()

Explanation: The RANK() function assigns a rank to each row within a partition of a result set. The rank of a row is one plus the number of ranks that come before it. Ties receive the same rank, causing gaps in the ranking sequence.

Example: Using the same employees table, we can assign ranks to employees within their department based on their salary.

SELECT
  employee_id,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM
  employees;        

Explanation:

• PARTITION BY department_id creates partitions for each department.
• ORDER BY salary DESC orders employees within each department by their salary in descending order.
• RANK() assigns ranks to employees, with ties receiving the same rank and gaps in the ranking sequence.

DENSE_RANK()

Explanation: The DENSE_RANK() function is similar to RANK(), but it does not leave gaps in the ranking sequence when there are ties.

Example: Using the same employees table, we can assign dense ranks to employees within their department based on their salary.

SELECT
  employee_id,
  department_id,
  salary,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM
  employees;        

Explanation:

• PARTITION BY department_id creates partitions for each department.
• ORDER BY salary DESC orders employees within each department by their salary in descending order.
• DENSE_RANK() assigns dense ranks to employees, with ties receiving the same rank and no gaps in the ranking sequence.

SUM()

Explanation: The SUM() function calculates the sum of a numeric column over a set of rows. When used as a window function, it allows for cumulative sums.

Example: Using a table sales with columns sale_date and amount, we want to calculate the cumulative sales amount.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM
  sales;        

Explanation:

• ORDER BY sale_date orders the sales by date.
• SUM(amount) calculates the cumulative sum of sales up to the current row.

AVG()

Explanation: The AVG() function calculates the average of a numeric column over a set of rows. When used as a window function, it can calculate moving averages.

Example: Using the same sales table, we can calculate a moving average of sales over the last three days.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM
  sales;        

Explanation:

• ORDER BY sale_date orders the sales by date.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines the window frame as the current row and the two preceding rows.
• AVG(amount) calculates the moving average of sales over the specified window frame.

NTILE()

Explanation: The NTILE() function divides rows within a partition into a specified number of approximately equal groups, assigning a unique group number to each row.

Example: Using a table students with columns student_id and score, we want to divide students into four quartiles based on their scores.

SELECT
  student_id,
  score,
  NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM
  students;        

Explanation:

• ORDER BY score DESC orders students by their scores in descending order.
• NTILE(4) divides the students into four quartiles, assigning a quartile number to each student.

Advanced Usage of Window Functions

Window functions in Databricks SQL offer powerful capabilities for performing complex data analysis. In this section, we’ll explore advanced use cases of window functions, including running totals, moving averages, and cumulative sums.

Running Totals

Explanation: Running totals are used to calculate a cumulative sum of values up to the current row. This is useful in financial reports and inventory management.

Example: Using a sales table with columns sale_date and amount, we can calculate the running total of sales.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM
  sales;        

Explanation:

• ORDER BY sale_date ensures the sales are ordered by date.
• SUM(amount) calculates the cumulative sum of sales up to the current row.

Moving Averages

Explanation: Moving averages smooth out short-term fluctuations and highlight longer-term trends or cycles in data. They are commonly used in stock market analysis and trend analysis.

Example: Using the sales table, we can calculate a 3-day moving average of sales.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM
  sales;        

Explanation:

• ORDER BY sale_date orders the sales by date.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a window frame including the current row and the two preceding rows.
• AVG(amount) calculates the average sales over this window frame.

Cumulative Sums

Explanation: Cumulative sums are similar to running totals but can include additional partitioning to reset the sum for different groups of data. This is useful in scenarios like calculating cumulative sales by department.

Example: Using a sales table with columns department_id, sale_date, and amount, we can calculate the cumulative sales for each department.

SELECT
  department_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY department_id
    ORDER BY sale_date
  ) AS cumulative_sales
FROM
  sales;        

Explanation:

• PARTITION BY department_id resets the sum for each department.
• ORDER BY sale_date ensures the sales are ordered by date within each department.
• SUM(amount) calculates the cumulative sum for each department up to the current row.

Ranking within Partitions

Explanation: Ranking functions like RANK(), DENSE_RANK(), and ROW_NUMBER() can be used to assign ranks to rows within each partition based on specific criteria. This is useful for identifying top performers or sorting data within groups.

Example: Using an employees table with columns department_id, employee_id, and salary, we can rank employees by salary within each department.

SELECT
  department_id,
  employee_id,
  salary,
  RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS salary_rank
FROM
  employees;        

Explanation:

• PARTITION BY department_id groups the employees by department.
• ORDER BY salary DESC ranks employees based on their salary in descending order.
• RANK() assigns a rank to each employee within their department.

Percentile Calculations

Explanation: Percentile calculations help in understanding the distribution of data. They are useful in determining the position of a particular value within a dataset.

Example: Using a students table with columns student_id and score, we can calculate the percentile rank of each student.

SELECT
  student_id,
  score,
  PERCENT_RANK() OVER (
    ORDER BY score
  ) AS percentile_rank
FROM
  students;        

Explanation:

• ORDER BY score orders the students by their score.
• PERCENT_RANK() calculates the percentile rank of each student based on their score.

Lag and Lead Functions

Explanation: Lag and lead functions allow you to access data from previous or subsequent rows in the result set. This is useful for comparing values across rows.

Example: Using a sales table with columns sale_date and amount, we can compare each day's sales to the previous day's sales.

SELECT
  sale_date,
  amount,
  LAG(amount, 1) OVER (
    ORDER BY sale_date
  ) AS previous_day_sales
FROM
  sales;        

Explanation:

• ORDER BY sale_date orders the sales by date.
• LAG(amount, 1) retrieves the sales amount from the previous row.

Practical Examples

Here are the practical examples of Databricks SQL:

Calculating Running Totals in Sales Data

• Use Case: Track cumulative sales over time for financial reporting.
• Example: Use SUM(amount) OVER (ORDER BY sale_date) to calculate the running total of sales.

Determining Employee Rankings by Performance

• Use Case: Rank employees within each department based on their performance metrics.
• Example: Use RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) to rank employees by performance within each department.

Calculating Moving Averages for Stock Prices

• Use Case: Smooth out short-term fluctuations and highlight long-term trends in stock prices.
• Example: Use AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to calculate a 3-day moving average of stock prices.

Comparing Current Sales to Previous Periods

• Use Case: Compare sales figures from the current period to previous periods to identify trends.
• Example: Use LAG(sales, 1) OVER (ORDER BY period) to compare each period's sales to the previous period.

Allocating Bonus Pools by Department

• Use Case: Distribute a fixed bonus pool among employees based on their rank within each department.
• Example: Use NTILE(5) OVER (PARTITION BY department_id ORDER BY performance_score) to divide employees into five tiers for bonus allocation.

Calculating Percentile Ranks of Student Scores

• Use Case: Determine the relative standing of students based on their exam scores.
• Example: Use PERCENT_RANK() OVER (ORDER BY score) to calculate the percentile rank of each student's score.

Analyzing Customer Retention Rates

• Use Case: Measure customer retention by comparing current customers to those from previous periods.
• Example: Use LAG(customer_id, 1) OVER (PARTITION BY customer_id ORDER BY period) to track customer retention across different periods.

Generating Sales Reports with Cumulative Metrics

• Use Case: Create detailed sales reports that include cumulative metrics for better insights.
• Example: Use SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) to generate cumulative sales metrics for each product.

Conclusion

Window functions in Databricks SQL provide a robust and flexible way to perform complex data analysis. By understanding and utilizing these functions, you can efficiently gain deeper insights and improve your data processing capabilities.

In part 4, we will explore how to integrate Databricks SQL with visualization tools to further enhance your analytical workflows and data presentation