How do SQL partitions work?

SQL partitions are a feature used with window functions to group rows for calculations while retaining individual row details. When you apply a partition using the PARTITION BY clause, you divide the dataset into subsets based on specified columns. Unlike GROUP BY, which collapses rows into aggregated summaries, partitions allow you to perform computations (like sums, averages, or rankings) within each group without merging the rows. This makes partitions ideal for tasks where you need to compare or analyze data within subgroups but still require access to the original row-level information.

For example, consider a sales table with columns employee_id, sale_date, and amount. To calculate the total sales for each employee over time without losing individual transaction details, you could write:

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

Here, PARTITION BY employee_id groups rows by each employee. The SUM(amount) computes a cumulative total (running_total) for each employee’s sales, ordered by date. Each row retains its original data but includes the employee-specific running total. Similarly, functions like RANK() or ROW_NUMBER() can be used with partitions to assign rankings within groups, such as identifying top-performing sales per region or month.

Partitions are useful in scenarios like calculating moving averages, ranking results within categories, or comparing individual rows to group averages. They simplify queries that would otherwise require complex self-joins or subqueries. For instance, instead of writing multiple subqueries to compute department-specific averages, a single window function with PARTITION BY department can achieve this efficiently. Performance can be optimized by indexing partition columns, as the database must sort and process each subset. However, excessive partitioning on large datasets may impact speed, so balancing granularity and efficiency is key. Overall, partitions provide a flexible way to perform context-aware calculations while preserving data granularity.