An analytic function returns a value for every row, computed from a window of rows around it: a running total, a moving average, a count of bookings in the last day. Which rows belong to the window is set by the OVER clause, and its least understood part is the frame. Get the frame wrong and a running total jumps in steps or a moving average covers the wrong days, without any error.
This guide explains PARTITION BY, the default frame, ROWS, RANGE, and GROUPS frames, the EXCLUDE clause, and named windows with the WINDOW clause.
Code for This Guide
The main examples are in the examples/analytic-queries folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
function(args) over (
[partition by expr [, ...]]
[order by expr [asc | desc] [nulls first | last] [, ...]]
[{rows | range | groups}
{between frame_start and frame_end | frame_start}
[exclude {current row | group | ties | no others}]] )
frame_start, frame_end:
unbounded preceding | n preceding | current row | n following | unbounded followingPARTITION BY divides the rows into independent groups, ORDER BY orders each group, and the frame picks the rows of the window relative to the current row.
PARTITION BY Without a Frame
With only a partition, the window is the whole partition. SUM(salary) OVER (PARTITION BY department_id) puts the department's payroll on every row, without grouping the rows away, so each salary can be shown as a share of it.
Example:
select department_id, last_name, salary,
sum(salary) over (partition by department_id) as dept_payroll,
round(salary / sum(salary) over (partition by department_id) * 100, 1) as pct_of_dept
from employees
where department_id in (50, 60)
order by department_id, salary desc;Output:
DEPARTMENT_ID LAST_NAME SALARY DEPT_PAYROLL PCT_OF_DEPT
________________ ____________ _________ _______________ ______________
50 Mehta 33000 65600 50.3
50 Reddy 15500 65600 23.6
50 Williams 8800 65600 13.4
50 Schmidt 8300 65600 12.7
60 Okafor 29000 47600 60.9
60 Costa 11200 47600 23.5
60 Mensah 7400 47600 15.5
7 rows selected.The Default Frame and Its Trap
With ORDER BY and no explicit frame, the window runs from the first row of the partition to the current row and to every row that ties with it. That default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. When the ORDER BY values are unique, it gives a normal running total. When they tie, all tied rows get the same total.
Example:
select department_id, last_name, salary,
sum(salary) over (order by department_id) as default_frame,
sum(salary) over (order by department_id
rows between unbounded preceding and current row) as rows_frame
from employees
where department_id in (50, 60)
order by department_id, last_name;Output:
DEPARTMENT_ID LAST_NAME SALARY DEFAULT_FRAME ROWS_FRAME
________________ ____________ _________ ________________ _____________
50 Mehta 33000 65600 33000
50 Reddy 15500 65600 48500
50 Schmidt 8300 65600 56800
50 Williams 8800 65600 65600
60 Costa 11200 113200 76800
60 Mensah 7400 113200 84200
60 Okafor 29000 113200 113200
7 rows selected.Ordered only by department, the four employees of department 50 are peers, so the default frame gives each of them the department's whole payroll. A ROWS frame adds one row at a time instead. Because the rows within a department have no defined order, also add a unique column to the ORDER BY, such as employee_id, so the running total is repeatable.
ROWS and RANGE Frames
ROWS counts physical rows: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING is the row before, the row itself, and the row after. RANGE measures by the value of the single ORDER BY expression: RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW takes every row whose value lies within one day before the current one, however many rows that is.
Example:
select to_char(booked_at, 'YYYY-MM-DD') as day, total_amount,
sum(total_amount) over (order by booked_at rows between 1 preceding and 1 following)
as rows_3,
count(*) over (order by cast(booked_at as date)
range between interval '1' day preceding and current row) as last_24h
from bookings
where booking_id <= 8;Output:
DAY TOTAL_AMOUNT ROWS_3 LAST_24H _____________ _______________ __________ ___________ 2025-10-24 513.01 768.74 1 2025-10-26 255.73 1431.6 1 2025-10-29 662.86 1434.66 1 2025-10-29 516.07 2431.88 2 2025-10-30 1252.95 2918.28 3 2025-10-30 1149.26 3427.05 2 2025-10-30 1024.84 4364.96 3 2025-11-02 2190.86 3215.7 1 8 rows selected.
rows_3 always sums three bookings, or two at the edges. last_24h counts however many bookings fall within the previous day, from one to three here. A RANGE frame with an offset needs a single numeric or datetime ORDER BY expression, since the offset is added to or subtracted from its value.
GROUPS Frames and EXCLUDE
GROUPS counts groups of peers, rows with equal ORDER BY values. GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING takes the current row's group and the groups just below and above it. EXCLUDE then removes rows from a frame:
| Option | Removes |
|---|---|
| EXCLUDE CURRENT ROW | The current row |
| EXCLUDE GROUP | The current row and its peers |
| EXCLUDE TIES | The peers, but not the current row |
| EXCLUDE NO OTHERS | Nothing (the default) |
Example:
select rating, review_id,
count(*) over (order by rating groups between current row and current row)
as same_rating,
count(*) over (order by rating rows between unbounded preceding
and unbounded following exclude current row)
as others,
count(*) over (order by rating groups between 1 preceding and 1 following
exclude group) as neighbours
from reviews
where review_id <= 8
order by rating, review_id;Output:
RATING REVIEW_ID SAME_RATING OTHERS NEIGHBOURS
_________ ____________ ______________ _________ _____________
2 5 2 7 2
2 7 2 7 2
3 2 2 7 5
3 4 2 7 5
4 1 3 7 3
4 3 3 7 3
4 8 3 7 3
5 6 1 7 3
8 rows selected.same_rating counts the reviews with the same rating. others counts every review except the current one. neighbours counts the reviews rated one step below or above, leaving out the current rating's own group: for rating 3, the two 2s and the three 4s.
Name a Window with the WINDOW Clause
When several analytic functions share a window, the WINDOW clause defines it once with a name, after HAVING. OVER w uses it as it is, and OVER (w ROWS ...) extends it with a frame.
Example:
select last_name, hire_date, salary,
row_number() over w as seq,
sum(salary) over w as running_payroll,
avg(salary) over (w rows between 1 preceding and current row) as avg_with_previous
from employees
where department_id = 50
window w as (order by hire_date)
order by hire_date;Output:
LAST_NAME HIRE_DATE SALARY SEQ RUNNING_PAYROLL AVG_WITH_PREVIOUS ____________ ______________ _________ ______ __________________ ____________________ Mehta 01-SEP-2015 33000 1 33000 33000 Reddy 06-JUN-2016 15500 2 48500 24250 Williams 18-FEB-2019 8800 3 57300 12150 Schmidt 04-SEP-2023 8300 4 65600 8550
A window that is extended may add only what it does not already have; you cannot add a second ORDER BY to a named window that has one.
Related Guides
- Oracle SQL Query to Implement Lead/Lag Analysis for Time Series Data
- How to Write Correlated Subqueries in Oracle SQL
Conclusion
PARTITION BY restarts an analytic function for each group, ORDER BY orders the rows, and the frame chooses the rows around the current one: ROWS by count, RANGE by value, GROUPS by peer groups, with EXCLUDE to leave rows out. Remember that ORDER BY without a frame means RANGE up to the current row and its peers, write ROWS explicitly for row-by-row running totals, and name shared windows with the WINDOW clause.
