Window Functions for Statistical Analysis

This chapter provides tutorial notes on Window Functions. Topics include introduction of window functions; OVER clause to identify window functions; PARTITION BY clause to form multiple windows; ROWS and RANGE clauses to specify moving frames; WINDOW clause to name windows.

These sections are omitted from this Web preview version. To view the full content, see information on how to obtain the full version this book.

What Is Window Function

List of Window Functions

Order of Rows in Windows and Output

"PARTITION BY ..." for Multiple Windows

"ROWS|RANGE ..." for Moving Frames in Windows

"WINDOW ..." to Define Named Windows

Takeaways:

• A window function is a function that can perform calculations over a window (or range) of rows dynamically defined and referenced by the current row in output rows.
• Some window functions are extended from aggregate functions like "COUNT() OVER ...", "SUM(...) OVER ...", "AVG(...) OVER ...", MIN(...) OVER ...", "MAX(...) OVER ...", etc.
• Some other window functions take values from other output rows relative to the current row or window boundary rows like "ROW_NUMBER(...) OVER ...", "FIRST_VALUE(...) OVER ...", "LAST_VALUE(...) OVER ... ", "NTH_VALUE(...) OVER ...", etc.
• Some other window functions calculate statistical information like "RANK() OVER ...", "DENSE_RANK() OVER ...", "CUME_DIST() OVER ...", etc.
• "PARTITION BY ..." clause can be used in window functions to divided output rows into multiple windows.
• "SORT BY ..." clause can be used in window function to re-arrange the order of output rows within each window.
• "ROWS ..." clause can be used in window functions to create moving frames using row counts as parameters.
• "RANGE ..." clause can be used in window functions to create moving frames using value differences of a given expression.
• "WINDOWS ..." clause can be used at the SELECT statement level to define named windows to be used by window functions.

Table of Contents