Window Functions in SQL Server

What are Window Functions?

Window functions are a class of SQL functions. They perform calculations across a set of table rows related to the current row. Unlike aggregate functions (e.g., SUM, COUNT), window functions do not collapse rows into a single result. Instead, they calculate values for each row while maintaining row granularity.

A window is defined by a PARTITION BY clause. This clause is used to group rows. It also includes an optional ORDER BY clause. This optional clause defines the order of the rows.

Key Components of Window Functions

1. OVER Clause:
   • Defines the window for the function.
   • Example: OVER (PARTITION BY column_name ORDER BY column_name).
2. Window Frame:
   • Specifies the subset of rows for computation.
   • Frame options:
     • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Includes all rows from the start to the current row.
     • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: Includes the current row and all following rows.

Types of Window Functions

1. Aggregate Functions as Window Functions:
   • Functions like SUM, AVG, COUNT, MAX, and MIN can be applied as window functions.
   • Example:
     • ^{SELECT employee_id, department_id, SUM(salary) OVER (PARTITION BY department_id) AS total_salary FROM employees;}
2. Ranking Functions:
   • Used for assigning ranks to rows:
     • ROW_NUMBER(): Assigns a unique sequential number.
     • RANK(): Assigns ranks with gaps for duplicates.
     • DENSE_RANK(): Similar to RANK but without gaps.
     • NTILE(n): Divides rows into n buckets.
3. Value Functions:
   • Retrieve values from other rows:
     • LEAD(): Fetches a value from a subsequent row.
     • LAG(): Fetches a value from a preceding row.
     • FIRST_VALUE(): Gets the first value in the window frame.
     • LAST_VALUE(): Gets the last value in the window frame.
4. Statistical Functions:
   • Calculate percentiles or cumulative distributions:
     • PERCENT_RANK(): Computes the relative rank as a percentage.
     • CUME_DIST(): Calculates the cumulative distribution.

Applications of Window Functions

1. Analytics and Reporting:
   • Running Totals and Cumulative Sums:
     • SELECT order_id, customer_id, SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_sum FROM orders;
   • Moving Averages:
     • SELECT order_id, AVG(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM orders;
2. Rankings:
   • Assign ranks to sales representatives based on their performance:
     • SELECT sales_rep, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank FROM sales_data;
3. Comparisons Across Rows:
   • Calculate the difference in salary between consecutive employees:
     • SELECT employee_id, salary, LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS previous_salary FROM employees;
4. Aggregations Without Collapsing Rows:
   • Determine total department salaries without collapsing individual employee records:
     • SELECT employee_id, department_id, SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary FROM employees;
5. Highlighting Extremes:
   • Identify the highest-paid employee in each department:
     • SELECT employee_id, department_id, FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_salary FROM employees;

Advantages of Window Functions

• Preserve row-level details while performing advanced analytics.
• Eliminate the need for self-joins or subqueries in many scenarios.
• Offer flexible partitioning and ordering to handle complex use cases.

Window functions are an essential tool in SQL for advanced analytics. They provide capabilities to process data across a defined range or partition. Importantly, this is done without losing row granularity.