SQL Window Functions Techniques

Introduction to SQL Window Functions

SQL window functions are a powerful feature in SQL that allow users to perform calculations across a set of rows that are related to the current row. Unlike regular subqueries, window functions operate on partitions of data, enabling advanced analytics such as ranking, aggregation, and trend analysis. They are particularly useful for scenarios where you need to compare rows within a dataset or compute moving averages.

Basic Syntax and Concepts

Window functions are defined using the OVER() clause, which specifies the partitioning and ordering logic. The general syntax is:

SELECT column, WINDOW_FUNCTION()
FROM table
OVER (
  [PARTITION BY column1, column2]
  [ORDER BY column3]
  [ROWS/ROWS BETWEEN ...]
);

The OVER() clause can include parameters like ROWS BETWEEN, RANGE BETWEEN, or GROUPING, which define the scope of the calculation.

Common Window Functions

1. ROW_NUMBER()

Assigns a unique sequential number to rows within a partition.

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

2. RANK()

Assigns a rank to rows, with ties receiving the same rank.

SELECT 
  RANK() OVER (ORDER BY salary DESC) AS rank,
  employee, salary
FROM employees;

3. DENSE_RANK()

Similar to RANK(), but ties receive consecutive ranks.

SELECT 
  DENSE_RANK() OVER (ORDER BY salary DESC) AS rank,
  employee, salary
FROM employees;

4. COUNT()

Returns the number of rows in a partition.

SELECT 
  COUNT(*) OVER (PARTITION BY department) AS total_rows,
  department, count
FROM employees;

5. SUM() and AVG()

Compute aggregate values across a partition.

SELECT 
  department, 
  SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

6. MIN() and MAX()

Retrieve the minimum or maximum value in a partition.

SELECT 
  department, 
  MIN(salary) OVER (PARTITION BY department) AS min_salary
FROM employees;

7. FIRST_VALUE() and LAST_VALUE()

Access the first or last value in a partition.

SELECT 
  department, 
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS first_salary
FROM employees;

Advanced Techniques

1. Partitioning with PARTITION BY

Define a partition based on specific columns.

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

2. WITH Clause for Complex Queries

Combine multiple window functions in a single query.

WITH ranked_employees AS (
  SELECT 
    employee, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM ranked_employees
WHERE rank <= 10;

3. Handling NULL Values

Use NULL to exclude rows with missing data.

SELECT 
  department, 
  SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees
WHERE salary IS NOT NULL;

4. Ordering with ORDER BY

Specify the order of results within a partition.

SELECT 
  employee, 
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Practical Examples

Example 1: Ranking Employees by Salary

SELECT 
  employee, 
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

This query ranks employees by their salary in descending order, with ties receiving the same rank.

Example 2: Calculating Monthly Sales

SELECT 
  month, 
  SUM(sales) OVER (ORDER BY month) AS cumulative_sales
FROM sales
GROUP BY month;

This query computes a running total of sales by month.

Example 3: Identifying Top Performers

SELECT 
  employee, 
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees
WHERE rank <= 10;

This query returns the top 10 highest-paid employees.

Conclusion

SQL window functions provide a flexible way to perform complex calculations on datasets. By leveraging OVER() clauses, users can rank rows, compute aggregates, and analyze trends with minimal code. Techniques like partitioning, ordering, and handling NULL values enhance the functionality of window functions. Whether for simple ranking or advanced analytics, window functions offer powerful tools for data manipulation and insight.