What Are Window Functions?

A Window Function is a function that can perform calculations over a window of rows referenced from the current row in query result.

A window function is called with a OVER clause in the following syntax:

window_function(...) OVER window 

where window is one of the followings:
  (): The default window of all rows.
  (PARTITION BY exp): A set of windows grouped by unique exp values.
  name: A named window set defined the WINDOW clause.

In the following example, ROW_NUMBER() OVER () is a window function call that returns the row number of the current row in the default window of all rows sorted by help_topic_id specified at the statement level. ROW_NUMBER() OVER (ORDER BY help_category_id) is a window function call that returns the row number of the current row in the default window of all rows sorted by help_category_id specified at the window level.

SELECT help_topic_id AS TopicID, help_category_id AS CategoryID,
  ROW_NUMBER() OVER () AS RowNumByTopic, 
  ROW_NUMBER() OVER (ORDER BY help_category_id) AS RowNumByCategory
  FROM mysql.help_topic ORDER BY help_topic_id;
  -- +---------+------------+---------------+------------------+
  -- | TopicID | CategoryID | RowNumByTopic | RowNumByCategory |
  -- +---------+------------+---------------+------------------+
  -- |       0 |          1 |             1 |                1 |
  -- |       1 |          1 |             2 |                2 |
  -- |       2 |          2 |             3 |                3 |
  -- |       3 |          3 |             4 |               38 |
  -- |       4 |          4 |             5 |               97 |
  -- ...
  -- +---------+------------+---------------+------------------+

In the following example is identical to the previous one. But it uses named windows sets of w1 and w2.

SELECT help_topic_id AS TopicID, help_category_id AS CategoryID,
  ROW_NUMBER() OVER w1 AS RowNumByTopic, 
  ROW_NUMBER() OVER w2 AS RowNumByCategory
  FROM mysql.help_topic 
  WINDOW w1 AS (), w2 AS (ORDER BY help_category_id)
  ORDER BY help_topic_id;
  -- +---------+------------+---------------+------------------+
  -- | TopicID | CategoryID | RowNumByTopic | RowNumByCategory |
  -- +---------+------------+---------------+------------------+
  -- |       0 |          1 |             1 |                1 |
  -- |       1 |          1 |             2 |                2 |
  -- |       2 |          2 |             3 |                3 |
  -- |       3 |          3 |             4 |               38 |
  -- |       4 |          4 |             5 |               97 |
  -- ...
  -- +---------+------------+---------------+------------------+

In the following example uses a named windows set of w3 partitioned by a given field expression.

SELECT help_topic_id AS TopicID, help_category_id AS CategoryID, Name,
  ROW_NUMBER() OVER w3 AS RowNumInW3, 
  MIN(name) OVER w3 AS MinNameInW3
  FROM mysql.help_topic 
  WINDOW w3 AS (PARTITION BY help_category_id ORDER BY name)
  ORDER BY help_topic_id;
  -- +---------+------------+----------------+------------+----------------+
  -- | TopicID | CategoryID | Name           | RowNumInW3 | MinNameInW3    |
  -- +---------+------------+----------------+------------+----------------+
  -- |       0 |          1 | HELP_DATE      |          1 | HELP_DATE      |
  -- |       1 |          1 | HELP_VERSION   |          2 | HELP_DATE      |
  -- |       2 |          2 | AUTO_INCREMENT |          1 | AUTO_INCREMENT |
  -- |       3 |          3 | HELP COMMAND   |          4 | BINLOG         |
  -- |       4 |          4 | TRUE           |          2 | FALSE          |
  -- |       5 |          4 | FALSE          |          1 | FALSE          |
  -- |       6 |          2 | BIT            |          4 | AUTO_INCREMENT |
  -- ...
  -- +---------+------------+----------------+------------+----------------+

⇒ MySQL Functions on Result Set Windows

⇐ VARIANCE() - Synonym for VAR_POP()

⇑ MySQL Functions on Aggregation Groups

⇑⇑ MySQL Function References