I have
previously written about how analytic functions are used in grouped queries.
They can also be used in window functions. The functions get applied to a
window of rows. This window is defined by the OVER clause. Unlike GROUP BY,
which transforms multiple inputs rows into a single output row, window
functions produce a one-for-one input to output row relationship.
The window
is defined with respect to the current row as the input is traversed. The rows
that will be subjected to the analytic function are in a window frame. The
frame is defined by units and an extent. Units can be rows or a range. The
extent can be things such as “unbounded preceding” (all prior rows) or current
row.
If you want
to rank the results, you need an ORDER BY clause. For example, you can use the
ROW_NUMBER function. It will give you a unique index into the frame. Another
similar function is the RANK function, in which ties get the same number. After
a tie, other numbers are skipped by RANK.
The DENSE_RANK
function will give you the same number for ties like RANK. However DENSE_RANK
will not skip over any index values, even in the case of a tie. The NTILE
function allows you to arrange the rows in a window in batches. The number of
batches is determined by the parameter you pass to the NTILE fuction.
There are functions which give you a value with
respect to the window frame. LAG with give you the next record. LEAD will give
you the prior record. FIRST_VALUE and LAST_VALUE work as their names suggest
within the frame.
