Analytical Functions In Sql in SQL
? Analytical Functions in SQL (Window Functions)
Analytical functions (also called window functions) perform calculations across a set of table rows that are related to the current row, without collapsing rows into a single result (like aggregate functions do).
? Key Characteristics
Use the
OVER()clauseRetain row-level detail
Can partition and order results
? Common Analytical Functions
| Function | Description |
|---|---|
ROW_NUMBER() | Assigns a unique row number within a partition |
RANK() | Ranks rows with gaps for ties |
DENSE_RANK() | Ranks rows without gaps |
NTILE(n) | Divides rows into n buckets |
LEAD() | Gets the value from the next row |
LAG() | Gets the value from the previous row |
SUM() | Running total |
AVG() | Running average |
FIRST_VALUE() | Gets the first value in the window |
LAST_VALUE() | Gets the last value in the window |
? Syntax
function_name(column) OVER ( [PARTITION BY col1] [ORDER BY col2])? Example Table: sales
| id | employee | month | sales |
|---|---|---|---|
| 1 | Alice | Jan | 1000 |
| 2 | Alice | Feb | 1200 |
| 3 | Bob | Jan | 800 |
| 4 | Bob | Feb | 900 |
? 1. ROW_NUMBER()
SELECT employee, month, sales, ROW_NUMBER() OVER (PARTITION BY employee ORDER BY month) AS row_numFROM sales;? 2. RANK()
SELECT employee, month, sales, RANK() OVER (PARTITION BY employee ORDER BY sales DESC) AS sales_rankFROM sales;? 3. LEAD() and LAG()
SELECT employee, month, sales, LAG(sales) OVER (PARTITION BY employee ORDER BY month) AS prev_sales, LEAD(sales) OVER (PARTITION BY employee ORDER BY month) AS next_salesFROM sales;? 4. Running Total
SELECT employee, month, sales, SUM(sales) OVER (PARTITION BY employee ORDER BY month) AS running_totalFROM sales;? Use Cases
Ranking students by score within each class
Getting previous/next transactions for an account
Calculating running totals or moving averages
First and last order for each customer
? Supported In:
? PostgreSQL
? MySQL 8+
? SQL Server
? Oracle
? SQLite (partial support)
Would you like real-life scenarios or practice exercises with analytical functions?
