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, 426🔥, 0💬
Popular Posts:
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...