Window Functions in SQL

 

What is Window Function and How to use in Analytics

What is Window Functions?

• Window functions operate on a set of rows and return a single value for each row.
• The term window describes the set of rows on which the function operates. 
• A window function uses values from the rows in a window.
• When you use a window function in a query, define the window using the OVER() clause. 
• The OVER() clause differentiates window functions from other analytical functions.
•  A query can include multiple window functions with the same/different window.

Window Functions Syntax

window_function_name(expression) 

    OVER (

        [partition_defintion]

        [order_definition]

        [frame_definition]

    )

• Specify the window function name followed by an expression.
• Specify the OVER clause which has three possible elements: 
• Partition definition,
• Order definition,
• Frame definition.

The opening and closing parentheses after the OVER clause are mandatory, even with no expression

Window Functions (Partition By)

• The opening and closing parentheses after the OVER clause are mandatory, even with no expression
• The partition_clause breaks up the rows into chunks or partitions. 
• Two partitions are separated by a partition boundary.
• The window function is performed within partitions and re-initialized when crossing the partition boundary.
• You can specify one or more expressions in the PARTITION BY clause. 
• Multiple expressions are separated by commas.

Window functions (Order By)

• The ORDER BY clause specifies how the rows are ordered within a partition.
• It is possible to order data within a partition on multiple keys, each key is specified by an expression. 
• Multiple expressions are also separated by commas.
• Similar to the PARTITION BY clause, the ORDER BY clause is also supported by all the window functions.  However, it only makes sense to use the ORDER BY clause for order-sensitive window functions.

Window functions (frame)

• A frame is a subset of the current partition.
• A window frame is used to specify how many rows around the current row the window should include.
• A frame is defined with respect to the current row, which allows a frame to move within a partition.
• The frame unit specifies the type of relationship between the current row and frame rows.
• The frame unit can be ROWS or RANGE. The offsets of the current row and frame rows are the row numbers, if the frame unit is ROWS and row values the frame unit is RANGE.
• The frame_start and frame_between define the frame boundary.
• To define a window frame, you use one of the following syntaxes:

• { RANGE | ROWS } frame_start
• { RANGE | ROWS } BETWEEN frame_start AND frame_end 
• The frame_start can take one of the following options:
• N PRECEDING
• UNBOUNDED PRECEDING
• CURRENT ROW
• The frame_end can take one of the following options:
• CURRENT ROW
• UNBOUNDED FOLLOWING
• N FOLLOWING

• Here is the meaning of each option:
• UNBOUNDED PRECEDING: the frame starts at the first row of the partition.
• N PRECEDING: the frame starts at Nth rows before the current row.
• CURRENT ROW: is the current row that is being processed.
• UNBOUNDED FOLLOWING: the frame ends at the final row of the partition.
• M FOLLOWING: the frame ends at the Mth row after the current row.
• By default, window functions use the below option:
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Note:
• That RANGE must use only with UNBOUNDED or CURRENT ROWS option

Window functions supported since?

• MYSQL : version 8/0 onwards
• MS SQL Server: version 2012 onwards

Why I need window function ?

• It allows us to solve query problems in new, easier ways, and with better performance

Definition:

• Window functions, also called analytics functions, are powerful SQL functions that will take you from a beginner SQL user to an intermediate SQL user. 
• What makes window functions different is that it does not group the result set. The number of rows in the output is the same as the number of rows in the input.

SQL CODE FOR DEMO

We are using SQL Server, you can use same code with MYSQL, Postgresql, Oracle, PySQL also

/************Create data Set******************/

CREATE TABLE sales(

    sales_employee VARCHAR(50) , fiscal_year INT ,

    sale DECIMAL(14,2)

);

 

INSERT INTO sales(sales_employee,fiscal_year,sale)

VALUES

('Bob',2016,100), ('Bob',2017,150), ('Bob',2018,200),

('Alice',2016,150), ('Alice',2017,100), ('Alice',2018,200),

('John',2016,200), ('John',2017,150), ('John',2018,250);

Select * from sales

/******************************/

Group By vs Windows function?

/************Group BY******************/

SELECT sum(sale) sum_sale , fiscal_year FROM sales

group by fiscal_year;

/************Window Functions******************/

SELECT fiscal_year, sales_employee, sale, 

SUM(sale) OVER (PARTITION BY fiscal_year) total_sales

FROM  sales; 

/******************************/

/********Window Functions with frames*********/

SELECT fiscal_year, sales_employee, sale,

SUM(sale) OVER (PARTITION BY fiscal_year) total_sales,

SUM(sale) OVER (PARTITION BY fiscal_year ORDER BY fiscal_year 

ROWS BETWEEN 1 PRECEDING AND 1FOLLOWING

) total_sales_year

FROM sales

/*******************************************/

/********Window Functions with frames*********/

SELECT fiscal_year, sales_employee, sale,

SUM(sale) OVER (PARTITION BY fiscal_year) total_sales,

SUM(sale) OVER (PARTITION BY fiscal_year ORDER BY fiscal_year 

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING

) total_sales_year

FROM sales

/*******************************************/