Window functions are a type of SQL function that perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not collapse rows; they preserve the original row structure while providing additional insights.<\/p>\n\n\n\n
PARTITION BY<\/code> and ORDER BY<\/code> clauses.<\/li>\n<\/ul>\n<\/li>\n\n\n\n- Partitioning<\/strong>\n
\nPARTITION BY<\/code> divides the dataset into subsets (partitions) and the window function is applied within each subset.<\/li>\n\n\n\n- Example:
PARTITION BY department<\/code> calculates results per department.<\/li>\n<\/ul>\n<\/li>\n\n\n\n- Ordering<\/strong>\n
\nORDER BY<\/code> defines the sequence of rows within each partition for calculation.<\/li>\n\n\n\n- Useful for functions like ranking or running totals.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
\n\n\n\nCommon Window Functions<\/h3>\n\n\n\n\n- ROW_NUMBER()<\/strong>\n
\n- Assigns a unique sequential number to rows within a partition.<\/li>\n\n\n\n
- Example: Identify top-performing employees per department.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- RANK()<\/strong>\n
\n- Assigns a rank to each row within a partition, with gaps for ties.<\/li>\n\n\n\n
- Useful for competitive ranking scenarios.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- DENSE_RANK()<\/strong>\n
\n- Similar to
RANK()<\/code> but without gaps for ties.<\/li>\n\n\n\n- Example: Assign continuous ranks to sales figures.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- SUM() OVER()<\/strong>\n
\n- Calculates cumulative sums without collapsing rows.<\/li>\n\n\n\n
- Example: Running total of sales.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- AVG() OVER()<\/strong>\n
\n- Calculates the average of a column over a window.<\/li>\n\n\n\n
- Example: Average salary per department.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- LEAD() and LAG()<\/strong>\n
\nLEAD()<\/code> accesses data from the next row in the window.<\/li>\n\n\n\nLAG()<\/code> accesses data from the previous row.<\/li>\n\n\n\n- Useful for comparisons and trend analysis.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
\n\n\n\nSyntax Example<\/h3>\n\n\n\nSELECT
employee_id,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS cumulative_salary,
LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS prev_salary
FROM
employees;<\/pre>\n\n\n\n
\n\n\n\nBest Practices<\/h3>\n\n\n\n\n- Always define a clear window using
PARTITION BY<\/code> and ORDER BY<\/code> to avoid unexpected results.<\/li>\n\n\n\n- Use window functions when you need row-level insights along with aggregate calculations.<\/li>\n\n\n\n
- Avoid overusing window functions on very large datasets as they can impact performance.<\/li>\n<\/ul>\n\n\n\n
\n\n\n\nUse Cases<\/h3>\n\n\n\n\n- Ranking top sales performers by region.<\/li>\n\n\n\n
- Calculating running totals or cumulative averages.<\/li>\n\n\n\n
- Comparing current row values with previous or next rows.<\/li>\n\n\n\n
- Detecting trends and patterns within partitions of data.<\/li>\n<\/ul>\n\n\n