Collections:
Window Functions
What Are Window Functions?
✍: FYIcenter.com
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()
2024-05-15, 329🔥, 0💬
Popular Posts:
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...