These functions are categorized into types of "Window Functions".
Window Functions were first introduced in SQL Server 2005 and greatly expanded upon since SQL Server 2012 product release.
SQL Server 2012+ now includes three types of window functions: Ranking, Aggregate, Analytic.
Code Example of T-SQL Analytic Functions:
Create & Populate a table with data:
Create Table #TBL (League varchar(100), Team varchar(100), Salary int)
Insert into #TBL Values ('National League', 'Los Angeles Dodgers', 272000000)
Insert into #TBL Values ('American League', 'New York Yankees', 219000000)
Insert into #TBL Values ('American League','Boston Red Sox', 191000000)
Insert into #TBL Values ('American League','Detroit Tigers', 191000000)
Insert into #TBL Values ('National League','San Francisco Giants', 191000000)
Insert into #TBL Values ('National League','Washington Nationals', 174000000)
Insert into #TBL Values ('National League','Los Angeles Angels', 164000000)
Insert into #TBL Values ('American League','Texas Rangers', 142000000)
Insert into #TBL Values ('National League', 'Philadelphia Phillies', 135000000)
Insert into #TBL Values ('American League', 'Toronto Blue Jays', 121000000)
Query using Analytic Functions:
SELECT
League,
Team,
Salary,
FIRST_VALUE(Salary) OVER(PARTITION BY League ORDER BY Salary DESC) AS FirstValue,
LAST_VALUE(Salary) OVER(PARTITION BY League ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue,
LEAD(Salary) OVER(PARTITION BY League ORDER BY Salary DESC) AS LeadValue,
LAG(Salary) OVER(PARTITION BY League ORDER BY Salary DESC) AS LagValue
FROM
#TBL;
Drop Table #TBL
Image 1: FIRST_VALUE(), LAST_VALUE()
Image 2: LEAD(), LAG()
Note:
SQL Server 2012+ added eight Analytic functions and all require the OVER clause. Included are: LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC.
|
|
|
|
|
|