As per Books online, Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.
SQL SERVER 2012 introduced LEAD function as an ANALYTICAL FUNCTION, with the help of that we can get simply the next Nth row value in an order. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead () function.
In other words, we can say, this function will return columns values from subsequent rows in the same result set without the use of a self-join. With the help of this function, we can 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)
NOTE: - The partition by clause is optional, the order by clause is required.
DESCRIPTION OF LEAD () CLAUSES:-
· SCALAR_EXPRESSION:- is the value to be returned – this will normally be a column, but it can also be a subquery or any other expression that results in a single value.
· OFFSET: - is the number of following (LEAD) rows from the current row from which to obtain a value, and if not specified it will default to 1.
· DEFALUT: - is the value to use if the value at the offset is NULL, and if not specified it will default to NULL.
EXAMPLE WITHOUT “PARTITION BY” CLAUSE:-
DECLARE @tbl TABLE (id INT IDENTITY
, name VARCHAR (10)
)
INSERT INTO @tbl (name) VALUES ('A')
INSERT INTO @tbl (name) VALUES ('B')
INSERT INTO @tbl (name) VALUES ('C')
SELECT * FROM @tbl
Result will be:-
id
|
name
|
1
|
A
|
2
|
B
|
3
|
C
|
· HAVING ONLY SCALAR EXPRESSION AND ORDER BY CLAUSE
SELECT *
, LEAD( name ) OVER (ORDER BY id)
FROM @tbl
Result will be :-
id
|
name
|
LEAD EXAMPLE
|
1
|
A
|
B
|
2
|
B
|
C
|
3
|
C
|
NULL
|
· HAVING OFFSET VALUE (WITHOUT DEFAULT) WITH SCALAR EXPRESSION AND ORDER BY CLAUSE
SELECT *
, LEAD ( name, 2 ) OVER (ORDER BY id)
FROM @tbl
Result will be :-
id
|
name
|
LEAD EXAMPLE
|
1
|
A
|
C
|
2
|
B
|
NULL
|
3
|
C
|
NULL
|
· HAVING OFFSET VALUE HAVING DEFAULT WITH SCALAR EXPRESSION AND ORDER BY CLAUSE
SELECT *
, LEAD( name, 2, '[LEAD]' ) OVER (ORDER BY id)
FROM @tbl
Result will be:-
id
|
name
|
(No column name)
|
1
|
A
|
C
|
2
|
B
|
[LEAD]
|
3
|
C
|
[LEAD]
|
EXAMPLE WITHOUT “PARTITION BY” CLAUSE:-
DECLARE @tbl TABLE (id INT
, name VARCHAR (10)
)
INSERT INTO @tbl VALUES (1, 'A')
INSERT INTO @tbl VALUES (1, 'B')
INSERT INTO @tbl VALUES (1, 'C')
INSERT INTO @tbl VALUES (2, 'P')
INSERT INTO @tbl VALUES (2, 'P')
INSERT INTO @tbl VALUES (2, 'Q')
INSERT INTO @tbl VALUES (2, 'R')
SELECT * FROM @tbl
Result will be:-
id
|
name
|
1
|
A
|
1
|
B
|
1
|
C
|
2
|
P
|
2
|
P
|
2
|
Q
|
2
|
R
|
· HAVING ONLY SCALAR EXPRESSION AND ORDER BY CLAUSE
SELECT *
, LEAD (name) OVER (PARTITION BY id ORDER BY id)
FROM @tbl
Result will be:-
id
|
name
|
LEAD EXAMPLE
|
1
|
A
|
B
|
1
|
B
|
C
|
1
|
C
|
NULL
|
2
|
P
|
P
|
2
|
P
|
Q
|
2
|
Q
|
R
|
2
|
R
|
NULL
|
· HAVING OFFSET VALUE (WITHOUT DEFAULT) WITH SCALAR EXPRESSION AND ORDER BY CLAUSE
SELECT *
, LEAD (name, 2) OVER (PARTITION BY id ORDER BY id)
FROM @tbl
Result will be:-
id
|
name
|
LEAD EXAMPLE
|
1
|
A
|
C
|
1
|
B
|
NULL
|
1
|
C
|
NULL
|
2
|
P
|
Q
|
2
|
P
|
R
|
2
|
Q
|
NULL
|
2
|
R
|
NULL
|
SELECT *
, LEAD (name, 2, '[LEAD]’) OVER (PARTITION BY id ORDER BY id)
FROM @tbl
Result will be:-
id
|
name
|
LEAD EXAMPLE
|
1
|
A
|
C
|
1
|
B
|
[LEAD]
|
1
|
C
|
[LEAD]
|
2
|
P
|
Q
|
2
|
P
|
R
|
2
|
Q
|
[LEAD]
|
2
|
R
|
[LEAD]
|
No comments:
Post a Comment