LAG and LEAD Analytical Functions in Sql Server with Real Time
Examples
Hello
folks, Today I am going to share LAG and LEAD Analytical Functions in Sql
Server. After reading this article you will be able to Understand and work with
both the Analytical Functions.
1.
LAG Function:-
In SQL Server (Transact-SQL), the LAG function is an
analytic function that lets you query more than one row in a table at a time
without having to join the table to itself. It returns values from a previous
row in the table.
Syntax
LAG(
expression [, offset [, default] ])
OVER
( [query_partition_clause ] order_by_clause )
Arguments Description:-
expression
An expression that can contain other
built-in functions, but can not contain any analytic functions.
offset
Optional. It is the physical offset from the current row in the table. If this
parameter is omitted, the default is 1.
default
Optional. It
is the value that is returned if the offset goes out of the
bounds of the table. If this parameter is omitted, the default is null.
query_partition_clause
Optional. It
is used to partition the results into groups based on one or more expressions.
order_by_clause
Optional. It
is used to order the data within each partition.
Applies
To
The LAG
function can be used in the following versions of SQL Server (Transact-SQL):
SQL Server 2014, SQL Server 2012
Example:-
Lets take an Example we have
following Student data.
And we want to know the previous mark like this.
Another Example would be like we have Customer data with their
order date so we want to know the Order Date along with Previous Order date so
we would know what was the Previous Order date.
So
now to achieve this data we will use LAG function as follow.
SELECT studentName, Mark,
LAG(Mark) OVER( ORDER BY Mark) AS PrevMark
FROM Exam order by Mark
So now it would return above result.
In this example, the LAG function will sort in
ascending order all of the Mark values in the Student table.
LAG(
expression [, offset [, default] ])
OVER
( [query_partition_clause ] order_by_clause )
Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.
2.
LEAD Function:-
Accesses data from a subsequent
row in the same result set without the use of a self-join in SQL Server 2016.
LEAD provides access to a row at a given physical offset that follows the
current row. Use this analytic function in a SELECT statement to compare values
in the current row with values in a following row.
Syntax
LEAD ( scalar_expression [ ,offset ] , [ default ]
)
OVER ( [
partition_by_clause ] order_by_clause )
Arguments Description:-
scalar_expression
The value to be returned based on the specified offset. It is an expression of
any type that returns a single (scalar) value. scalar_expression cannot
be an analytic function.
offset
The number of rows forward from the current row from which to obtain a value.
If not specified, the default is 1. offset can be a column,
subquery, or other expression that evaluates to a positive integer or can be
implicitly converted to bigint. offset cannot be a
negative value or an analytic function.
default
The value to return when scalar_expression at offset is
NULL. If a default value is not specified, NULL is returned. default can
be a column, subquery, or other expression, but it cannot be an analytic
function. default must be type-compatible with scalar_expression.
OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause divides the result set produced
by the FROM clause into partitions to which the function is applied. If not
specified, the function treats all rows of the query result set as a single
group. order_by_clause determines the order of the data before
the function is applied. When partition_by_clause is
specified, it determines the order of the data in each partition. The order_by_clause is
required.
Examples:-
Compare values between years
The query uses the LEAD function to return the
difference in sales quotas for a specific employee over subsequent years.
Notice that because there is no lead value available for the last row, the
default of zero (0) is returned.
Conclusion
In this
article I discussed how you can use LAG and LEAD analytical function to access
or query data from previous or subsequent rows without writing self-join
query. Write your queries into the comments section.
LEAD ( scalar_expression [ ,offset ] , [ default ]
)
OVER ( [
partition_by_clause ] order_by_clause )
The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. When partition_by_clause is specified, it determines the order of the data in each partition. The order_by_clause is required.
No comments:
Post a Comment