SQL Window Functions

• Overview of Window Functions – Functions that specify a per-row "window frame" for calculating aggregates and ranking.
• AVG – A window function that assigns the average of the values in the field column for rows within the specified window frame to all rows in that window frame.
• COUNT – A window function that assigns a number to each row in the specified window frame starting at 1.
• CUME_DIST() – A window function that assigns the cumulative distribution value for all rows within the specified window frame.
• DENSE_RANK() – A window function that assigns a rank to each row within the same window frame, starting with one.
• FIRST_VALUE – A window function that assigns the first value of the field column within the window frame to each of the other values in that column.
• LAG – A window function that assigns the value of the field column for the row that is offset rows before the given row within the specified window frame.
• LAST_VALUE – A window function that assigns the last value of the field column within the window frame to each of the other values in that column.
• LEAD – A window function that assigns the value of the field column for the row that is offset rows after the given row within the specified window frame.
• MAX – A window function that assigns the maximum value of the field column within the specified window frame to all rows in that window frame.
• MIN – A window function that assigns the minimum value of the field column within the specified window frame to all rows in that window frame.
• NTH_VALUE – A window function that assigns the value of the field column for row number n within the specified window frame to all rows in the window frame.
• NTILE – A window function that splits the row within the specified window frame into num-groups number of groups that each have a roughly equal number of elements.
• PERCENT_RANK() – A window function that assigns a ranking as a fractional number between 0 and 1 (inclusive) to each row within the same window frame.
• RANK() – A window function that assigns a rank to each row within the same window frame, starting with one.
• ROW_NUMBER() – A window function that assigns a unique sequential integer to each row within the same window frame, starting with one.
• SUM – A window function that assigns the sum of the values of the field column within the specified window frame to all rows in that window frame.