Skip to main content

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.
FeedbackOpens in a new tab