One of the most important features which make dealing with complex computations easy are window functions. The article goes in depth about the window functions in Mariadb (concept, syntax and use cases).
A window function in MariaDB allows users to perform calculations on a specific set of rows known as a "window." Unlike traditional aggregate functions that collapse rows into a single result, window functions maintain the individual row structure while performing calculations across a defined window frame.
SYNTAX OF WINDOW FUNCTIONS
To use window functions in MariaDB, the OVER() clause is employed in conjunction with the desired function. The OVER() clause specifies the window frame and partitioning criteria. The syntax for window functions in MariaDB is as follows:
function_name() OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [{ASC | DESC}]]
[frame_clause]
)
PARTITIONING AND ORDER BY IN WINDOW FUNCTIONS
Partitioning allows the data to be divided into distinct groups based on one or more columns. By using the PARTITION BY clause within the window function, users can perform calculations separately for each partition. Ordering, on the other hand, allows rows to be arranged in a specific sequence within each partition. This is accomplished using the ORDER BY clause in conjunction with the window function.
FRAME CLAUSES
Frame clauses define the window frame, i.e., the subset of rows used in the calculation. They specify the boundaries of the window in relation to the current row. MariaDB supports three types of frame clauses: ROWS, RANGE, and GROUPS. Each has its own significance and usage scenarios.
The article is continued in next part which discusses about currently available window functions, their use cases and performance. link_part2
Top comments (0)