SQL Analytic Functions

In SQL, an analytic function calculates results over a group of rows and gives a separate output for each row. This is distinct from an aggregate SQL function, which gives a unique output for a collection of rows. This post is all about SQL analytic functions. Let’s take a look at it in more detail.

Important: The important point here to remember is that an analytic function comprises an OVER clause, which describes a window of rows about the row being calculated. For each row, the output is calculated applying the chosen window of rows as data, probably performing aggregation.

CUME_DIST

This determines the combined order of a value within a group of values. For example, it calculates the comparable place or position of a particularized value in a group of values. The following query is describing the use of CUME-DIST.

Use Department

SELECT CarName, Model, Rate, Class,
CUME_DIST() OVER(ORDER BY Rate) as TotalPrice
FROM Vehicle;

FIRST_VALUE

As its name suggests, the first_value function gives the first value from the designated column for the data that has been ordered using the ORDER BY clause. The following query describes how it can be used.

Use Department

SELECT CarName, Model, Rate, Class,
FIRST_VALUE(CarName) OVER(ORDER BY Price) as First_Value_Result
FROM Vehicle V;

LAG

The lag function is the opposite of the lead function and is utilized to obtain the rates from earlier N rows. The column from which the rate is to be obtained and the number of rows to be balanced is defined in the lag function. The following query is used to apply the LAG function.

Use Department

SELECT CarName, Model, Rate, Class,
LAG(CarName,1) OVER(ORDER BY Rate) as Offset1,
LAG(CarName,2) OVER(ORDER BY Rate) as Offset2
FROM Vehicle V

LAST_VALUE

The last_value function is the opposite of the first value function. It gives the last value for a particular column from the aligned sequence. The following query can be used to apply the LAST_Value function.

Use Department

SELECT CarName, Model, Rate, Class,
FIRST_VALUE(CarName) OVER(PARTITION BY Type ORDER BY Rate) as First_Value
FROM Vehicle V

LEAD

The lead function is applied to obtain the rates from the subsequent N rows. The column from which the rate is to be obtained and the number of rows to be offset is defined in the lead function. The following query can be used to apply the LEAD function.

Use Department

SELECT CarName, Model, Rate, Class,
LEAD(CarName,1) OVER(ORDER BY Rate) as Offset1,
LEAD(CarName,2) OVER(ORDER BY Rate) as Offset2
FROM Vehicle V;

Analytic functions increase the capability of SQL, allowing users to perform complicated interpretations of the data besides the plain queries. These functions allow users to perform amazing analyses. Using analytic functions users can perform running analysis too. For example, the application of flow comparison to analytic function allows users to view the real applicability of complicated analysis. This output gives a resolution to a dynamic obstacle in liquid flow that formalizes the concept of movement of a fluid component. An analytic function calculates values over a collection of rows and delivers a unique result for each row. The data of aggregated rows are combined with the query output. The window functions are applied with the OVER clause.

Other useful tutorials: