Window functions enable complex analytical operations that were previously difficult or impossible with standard SQL. Unlike aggregate functions that collapse groups into single values, window functions perform calculations across related rows while preserving individual row details. Major databases like Oracle, SQL Server, and DB2 support window functions, though MySQL currently lacks native support.

Consider a table storing employee information:

CREATE TABLE Employee_Data (
    Employee_Name VARCHAR2(20),
    City VARCHAR2(20),
    Employee_Age INT,
    Salary INT
);

Sample data insertion:

INSERT INTO Employee_Data VALUES('Tom','Beijing',20,3000);
INSERT INTO Employee_Data VALUES('Tim','Chengdu',21,4000);
INSERT INTO Employee_Data VALUES('Jim','Beijing',22,3500);
INSERT INTO Employee_Data VALUES('Lily','London',21,2000);
INSERT INTO Employee_Data VALUES('John','NewYork',22,1000);
INSERT INTO Employee_Data VALUES('YaoMing','Beijing',20,3000);
INSERT INTO Employee_Data VALUES('Swing','London',22,2000);
INSERT INTO Employee_Data VALUES('Guo','NewYork',20,2800);
INSERT INTO Employee_Data VALUES('YuQian','Beijing',24,8000);
INSERT INTO Employee_Data VALUES('Ketty','London',25,8500);
INSERT INTO Employee_Data VALUES('Kitty','Chengdu',25,3000);
INSERT INTO Employee_Data VALUES('Merry','Beijing',23,3500);
INSERT INTO Employee_Data VALUES('Smith','Chengdu',30,3000);
INSERT INTO Employee_Data VALUES('Bill','Beijing',25,2000);
INSERT INTO Employee_Data VALUES('Jerry','NewYork',24,3300);

To count employees earning under 5000 while showing individual records:

SELECT Employee_Name, City, Salary, 
       COUNT(*) OVER() AS LowSalaryCount
FROM Employee_Data
WHERE Salary < 5000;

The OVER() clause distinguishes window functions from aggregate functions. Empty OVER() applies the calculation to all result rows.

Parttiioning Data

PARTITION BY divides rows into groups for separate calculations:

SELECT Employee_Name, City, Employee_Age, Salary,
       COUNT(*) OVER(PARTITION BY City) AS CityEmployeeCount
FROM Employee_Data;

Multiple window functions can operate independently:

SELECT Employee_Name, City, Employee_Age, Salary,
       COUNT(*) OVER(PARTITION BY City) AS CityCount,
       COUNT(*) OVER(PARTITION BY Employee_Age) AS AgeGroupCount
FROM Employee_Data;

Ordered Window Frames

ORDER BY enables cumualtive calculations within specified ranges:

SELECT Employee_Name, City, Employee_Age, Salary,
       SUM(Salary) OVER(ORDER BY Salary) AS CumulativeSalary
FROM Employee_Data;

This defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Explicit frame specification:

SELECT Employee_Name, Salary,
       SUM(Salary) OVER(ORDER BY Salary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS SurroundingSum
FROM Employee_Data;

ROWS counts physical rows, while RANGE handles logical value groups. For employees with identical salaries, RENGE treats them as peers.

Ranking Functions

ROW_NUMBER(), RANK(), and DENSE_RANK() provide different ranking strategies:

SELECT Employee_Name, Salary, Employee_Age,
       RANK() OVER(ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRank,
       ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum
FROM Employee_Data;

• RANK(): Leaves gaps after ties
• DENSE_RANK(): No gaps after ties
• ROW_NUMBER(): Sequential numbering regardless of ties

Practical Application: Leaderboards

For ranking systems with tie-breaking:

SELECT UserID, Points, CreateTime,
       ROW_NUMBER() OVER(ORDER BY Points DESC, CreateTime ASC) AS Position
FROM ContestEntries
WHERE ContestID = '20230415';

Efficient pagination using window functions:

SELECT * FROM (
    SELECT UserID, Points, CreateTime,
           ROW_NUMBER() OVER(ORDER BY Points DESC, CreateTime ASC) AS RankPosition
    FROM ContestEntries
    WHERE ContestID = '20230415'
) RankedResults
WHERE RankPosition <= 50;