SQL 2012 – Analytic Functions – Pt. 2

A script to demo the PERCENTILE_CONT and PERCENTILE_DISC functions in SQL Server 2012.

— Begin Script
CREATE TABLE TestTable2 (
RecordId INT NOT NULL IDENTITY(1, 1),
GroupId INT NOT NULL,
TotalAmount INT NOT NULL
)
GO

INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (1, 1)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (1, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 3)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (2, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (3, 1)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (3, 2)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (4, 3)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (4, 5)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (5, 5)
INSERT INTO TestTable2(GroupId, TotalAmount) VALUES (5, 6)

GO

SELECT GroupId,
SUM(TotalAmount) as TotalAmountSum,
COUNT(GroupId) as GroupRecordCount
FROM TestTable2
GROUP BY GroupId

SELECT DISTINCT GroupId,
PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY TotalAmount) OVER (PARTITION BY GroupId) AS PercentContValue,
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY TotalAmount) OVER (PARTITION BY GroupId) AS PercentDiscValue
FROM TestTable2
GO
— End Script

— Results

Group Id Total Amount Sum Group Record Count
1 3 2
2 7 3
3 3 2
4 8 2
5 11 2
Group Id PercentContValue PercentDiscValue
1 1.5 1
2 2 2
3 1.5 1
4 4 3
5 5.5 5

PERCENTILE_CONT – From BOL: ‘Calculates a percentile based on a continuous distribution of the column value ‘
PERCENTILE_DISC – From BOL: ‘Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset’

The input for both functions is a percent, from 0.0 to 1.0. PERCENTILE_CONT will calculate the value to match the designated percentage for each group. So in this example, using 0.5 will compute the median value.
PERCENTILE_DISC will return an actual value from the group, not a calculated value. If a value from the group doesn’t match the ‘specific percentile’ exactly (if there are an even number of values) the lower value will be returned.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: