Window functions and analytic functions are advanced SQL features that enable you to perform calculations across a set of rows related to the current row. They offer powerful tools for data analysis and reporting by allowing you to compute aggregated values, rankings, and other insights without changing the structure of your result set.
Window Functions: A window function performs calculations across a set of table rows related to the current row, within a defined window or partition. This partition can be based on columns or conditions, such as grouping by a specific column. Window functions use the OVER clause to define the partition and ordering of rows.
Some common window functions include:
ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition.RANK(),DENSE_RANK(): Assigns a ranking to rows based on specified criteria.SUM(),AVG(),MIN(),MAX(): Computes aggregate values over a partition.
Example:
SELECT employee_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_per_dept
FROM employees;
Analytic Functions: Analytic functions are a subset of window functions that provide additional analytical capabilities. They allow you to perform more complex calculations involving ranking, percentiles, and moving averages, among other things. Analytic functions use the same OVER clause to define the window or partition.
Some common analytic functions include:
PERCENTILE_CONT(),PERCENTILE_DISC(): Compute percentiles within a partition.LAG(),LEAD(): Access values from previous or following rows.NTILE(): Divide rows into equally sized groups.
Example:
SELECT order_id, order_date, total_amount,
LAG(total_amount) OVER (ORDER BY order_date) AS prev_order_amount
FROM orders;
Both window functions and analytic functions are extremely useful for performing complex calculations that involve comparisons and aggregations across related rows. They can provide valuable insights into data trends, rankings, and patterns without the need for subqueries or temporary tables. These features are supported by most modern SQL database systems.
