This article describes how to use the T-SQL Analytic functions
in SQL Server database.
The Analytic functions perform a calculation on aggregate value based on a group of rows.
The SQL Server
analytic functions are: CUME_DIST, FIRST_VALUE, LAST_VALUE, LAG, LEAD, PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC.
CUME_DIST
SQL Server CUME_DIST
function calculates the relative position of a specified value in a group of values.
select c.*,
CUME_DIST () OVER (ORDER BY c.price) AS CumeDist
from courses c;
FIRST_VALUE
SQL Server FIRST_VALUE
function returns the first value in an ordered set of values.
select c.*,
FIRST_VALUE(c.name) OVER (ORDER BY c.price ASC) AS FirstValue_Asc
from courses c order by c.price;
select c.*,
FIRST_VALUE(c.name) OVER (ORDER BY c.price DESC) AS FirstValue_Asc
from courses c order by c.price DESC;
LAST_VALUE
SQL Server LAST_VALUE
function returns the last value in an ordered set of values.
select c.price, c.name, rc.id as reg_id,
FIRST_VALUE(c.name) OVER (ORDER BY c.price) AS FirstValue,
LAST_VALUE(c.name) OVER (ORDER BY c.price) AS LastValue
from courses c, register_course rc
where c.id=rc.course_id
order by rc.id;
LAG
SQL Server LAG
function returns data from a previous row in the same result. First data value is null.
select c.*,
LAG(c.name) OVER (ORDER BY price) AS PrevCourse
from courses c;
LEAD
SQL Server LEAD
function returns data from a next row in the same result. Last data value is null.
select c.*,
LEAD(c.name) OVER (ORDER BY price) AS NextCourse
from courses c;
PERCENT_RANK
SQL Server PERCENT_RANK
function calculates the relative rank of a row within a group of rows.
select c.*,
PERCENT_RANK() OVER (ORDER BY c.price ) AS PctRank
from courses c;
PERCENTILE_CONT
SQL Server PERCENTILE_CONT
function calculates a percentile based on a continuous distribution of the column value.
select distinct c.*,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c.id)
OVER (PARTITION BY c.price) AS PercentCount
from courses c, register_course rc
where c.id=rc.course_id;
PERCENTILE_DISC
SQL Server PERCENTILE_DISC
function calculates a specific percentile for sorted values in an entire rowset.
select distinct c.*,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY c.id)
OVER (PARTITION BY c.price) AS PercentDisc
from courses c, register_course rc
where c.id=rc.course_id;