oracle sql analytics functions - ghdrako/doc_snipets GitHub Wiki

avg (<..>) over ()
avg (<..>) over (partition by <..>)

Within a partition, it is possible to specify a window. The window determines which range of rows in the current partition are available to perform the function upon. Analytic functions are always performed from the perspective of the current row. Window sizes can be based on either a physical number of rows or a logical interval, such as a numeric value or time.

Currently, there are three options to define the windowing clause. Oracle Database 21c introduced the groups window.

• rows specify the number of rows relative to the current row. This can be either forward or backward from the current row.
• range specifies the range of values relative to the current row. The number of rows depends on the value of the current row. The sort key must allow for addition or subtraction operations, like numeric, date, or interval data types.
• groups are where the data is divided based on the ordered values. Ties are part of the same group.

The offset specified refers to the preceding or following group. Because the windowing clause relies on an ordered result set, it is required to include an order by in the analytic clause. The windowing clause defaults to range unbounded preceding and current row when an order by is present.

Note When you want to know the last value in the partition, instead of the current last row in the window, specify unbounded following. this expands the window from the current row to the last row.

Since Oracle Database 21c, it is possible to define a reusable windowing clause.

select ltm.lap
, ltm.milliseconds
, sum (ltm.milliseconds) over win as running_total
, first_value (ltm.lap) over win as win_start
, last_value (ltm.lap) over win as win_end
from f1data.laptimes ltm
join f1data.races rcs
on rcs.raceid = ltm.raceid
where rcs.race_date between trunc (sysdate, 'yy') and sysdate
and rcs.raceid = 1074 -- Bahrain Grand Prix
and ltm.lap between 1 and 3
and ltm.driverid = 844 -- Charles Leclerc
window win as (partition by ltm.driverid
order by ltm.lap)
order by ltm.lap
, ltm.milliseconds
/