WINDOW Functions

This is used with The OVER() and PARTITION BY() Function.

General Syntax

WINDOW_FUNCTION() OVER (
    [PARTITION BY partition_expression]
    ORDER BY order_expression
) as new_column_name

ROW_NUMBER(): a window function or analytic function is a function which uses values from one or multiple rows to return a value for each row.
OVER(): The window function has an OVER() clause that defines the window within which the function operates (i.e. the set of rows within which the function operates). Within this function, you can specify the partitioning, ordering, and range of rows of the window using the PARTITION BY clause.
PARTITION BY <partition_expression>: Optional. Specifies how to partition the result set into groups. Rows with the same values for partition_expression will be considered as part of the same partition.

PARTITION BY vs GROUP BY

The PARTITION BY clause works similar to how The GROUP BY Clause works in aggregated queries. Each partition is then treated independnetly by the window function.

ORDER BY order_expression: Specifies the order in which the rows should be numbered within each partition. The rows are ordered based on the specified column(s) or expression(s).

Order of Execution

The expressions within the ORDER BY clause are evaluated in the order they are specified. The ORDER BY clause can include multiple expressions, and each expression is evaluated in the order they appear.

Types of Window Functions

Pasted image 20240907203201.png

Example using ROW_NUMBER():

The ROW_NUMBER() function is a window function in SQL that assigns a unique sequential integer to each row within a partition of a result set.

When combined with The OVER() and PARTITION BY() Function , we can assign a unique sequential integer to each row within a partition.
Useful for ranking purposes.

ROW_NUMBER() OVER (
SELECT 
    order_id,
    customer_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM 
    Orders;