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. 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. 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. 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.
