Perform calculations across a set of rows related to the current row.
Perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. Essential for running totals, rankings, moving averages, and row comparisons. Not supported by MySQL before 8.0.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0 | Added in MySQL 8.0 |
| PostgreSQL | Native | 8.4 | Supported since PG 8.4 |
| SQL Server | Native | 2005 | Since SQL Server 2005 |
| Oracle | Native | 8i | Since Oracle 8i |
| SQLite | Native | 3.25.0 | Added in SQLite 3.25.0 |
Details
Includes ranking, aggregate, and value functions like LEAD and LAG.
Standard Syntax
SELECT column1,
AVG(column3) OVER (PARTITION BY column1 ORDER BY column2) AS avg_val
FROM table_name;
Version Support
MySQL: Native since 8.0
PostgreSQL: Native since 8.4
SQL Server: Native since 2005
Oracle: Native since 8i
SQLite: Native since 3.25.0
Per-Database Syntax & Notes
MySQL Native syntax
Added in MySQL 8.0
SELECT col1,
ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS rn
FROM tbl;
PostgreSQL Native syntax
Supported since PG 8.4
SELECT col1,
RANK() OVER (PARTITION BY col1 ORDER BY col2) AS rnk
FROM tbl;
SQL Server Native syntax
Since SQL Server 2005
SELECT col1,
LAG(col2) OVER (PARTITION BY col1 ORDER BY col2) AS prev_val
FROM tbl;
Oracle Native syntax
Since Oracle 8i
SELECT col1,
FIRST_VALUE(col2) OVER (PARTITION BY col1 ORDER BY col2) AS first_val
FROM tbl;
SQLite Native syntax
Added in SQLite 3.25.0
SELECT col1,
SUM(col2) OVER (PARTITION BY col1) AS total
FROM tbl;