Thinking in SQL - Distribution Functions in SS 2012

• Download Thinking_in_SQL_-_Distribution_Functions.zip - 692 B

Introduction

A while ago I wrote an article on the select statement and in the 'what's new in 2012' section I mentioned the four new analytical functions, and said I had no real clue how they worked. Having gone on to learn, I'm going to set out to explain them today. I read a lot of explanations that were flat out confusing, so hopefully I can contribute something in terms of making this clear, because it's actually not complicated

cume_dist and percent_rank

There are four new functions which break off nicely in to pairs. In order to play with them, we're going to create a sample database with 10 entries, each with an id, a name and a birthdate. Note that there are repeated values in here.

create table birthdays
(
   id int identity,
   name varchar(50),
   birthday date
)

insert into birthdays values 
('Chris', '19690217'),
('Sarah', '19710514'),
('Bill', '19710514'),
('Hannah', '19960620'),
('Chris', '19230110'),
('Bob', '19710512'),
('Manuel', '19230114'),
('Chris', '20000601'),
('Chris', '19960620'),
('Chris', '19710514')

Now if we do a select and run these two functions, we get this:

select id, name, birthday,
cume_dist() over(order by id) as cumeDist,
PERCENT_RANK() over(order by id) as percentRank
from birthdays

gives:

So, what has this done ? The syntax for cume_dist and percent_rank is the same as for any window function, you need an order by, and you can specify a partition by as well, to create windows inside the data. We have exactly 10 values for a reason. Look at the data for the cumeDist column. The values are all id/10. CumeDist calculates the CUMulativE DISTribution of your data. In plain English, it asks, how many rows have the key value of this row, or less, as a percentage. So, 10% of the rows have an id of 1 or less, 20% have an id of 2 or less, etc. PercentRank is the PERCENTile RANKing, whch is to say, it ask 'how many rows have an id less than mine ?'. If we had 11 rows in our data, then it would be from 0-1, as you can see by running the following:

;with rankData as
(
    select id, name, birthday from birthdays
    union 
    select 11 as id, 'fred' as name, '20000101' as birthday
)

select id, name, birthday,
cume_dist() over(order by id) as cumeDist,
PERCENT_RANK() over(order by id) as percentRank
from rankData;

gives:

By using a UNION to add an extra row, we forced cume_dist to move at increments less than 1, and percent_rank to move in steps of 1.

PERCENTILE_DISC and PERCENTILE_CONT

So, what do these other two functions do ? Basically, they are the opposite question. They allow you to ask the question 'which is the first row for which cume_dist and percent_rank would return this value ? As such, the syntax is slightly different:

select id, name, birthday,
cume_dist() over(order by id) as cumeDist,
PERCENT_RANK() over(order by id) as percentRank,
PERCENTILE_DISC(.5) within group (order by id) over() as [percentileDisc .5], 
PERCENTILE_DISC(.55) within group (order by id) over() as [percentileDisc .55], 
PERCENTILE_CONT(.5) within group (order by id) over() as [percentileCont .5], 
PERCENTILE_CONT(.55) within group (order by id) over() as [percentileCont .55] 
from birthdays

Instead of specifying the column that we run over, and an optional window, we pass in a parameter ( the value to match ) and then use the 'within group' syntax to define the window that we look in for the value in question. The order by clause tells the function what value to actually return ( it will order by the id, and return the id that matches the value passed in ) and the over clause is required, even if it's empty, but can be used to define windows inside the data for the functions to run inside.

There is another wrinkle. If you run this SQL, you get the following:

Now, given the source data, you might think that percentile_disc gives rounder numbers just because cume_dist does, but if you run this:

;with rankData as
(
    select id, name, birthday from birthdays
    union 
    select 11 as id, 'fred' as name, '20000101' as birthday
)

select id, name, birthday,
cume_dist() over(order by id) as cumeDist,
PERCENT_RANK() over(order by id) as percentRank,
PERCENTILE_DISC(.5) within group (order by id) over() as [percentileDisc .5], 
PERCENTILE_DISC(.55) within group (order by id) over() as [percentileDisc .55], 
PERCENTILE_CONT(.5) within group (order by id) over() as [percentileCont .5], 
PERCENTILE_CONT(.55) within group (order by id) over() as [percentileCont .55] 
from rankData;

You get this:

This does clean up the percentile_cont values considerable, by changing the result, but it's still true that percentile_disc always gives actual values from the source data, and percentil_cont calculates values that are *between* the values in the source data.

Windowing

Here is an example that creates windows inside the data. It also shows percentile_disc over names ( I used .1 so it's obvious that the answer is the first alphabetical name ).

select id, name, birthday,
cume_dist() over(partition by name order by id) as cumeDist,
PERCENT_RANK() over(partition by name order by id) as percentRank,
PERCENTILE_DISC(.5) within group (order by id) over(partition by name) as [percentileDisc .5], 
PERCENTILE_CONT(.5) within group (order by id) over(partition by name) as [percentileCont .5], 
PERCENTILE_DISC(.1) within group (order by name) over() as [percentileDisc Name .1]
from birthdays
order by id

and the result:

One final example, this time I am grouping by birth month. Hopefully that's enough to give you some ideas, and the data set is varied enough ( or you could use your own data ) to inspire you to play further as you come to grips with how these functions can be used.

select id, name, birthday, month(birthday) as [month],
cume_dist() over(order by month(birthday)) as cumeDist,
PERCENT_RANK() over(order by month(birthday)) as percentRank,
PERCENTILE_DISC(.5) within group (order by month(birthday)) over(partition by month(birthday)) as [percentileDisc .5], 
PERCENTILE_CONT(.5) within group (order by month(birthday)) over(partition by month(birthday)) as [percentileCont .5] 
from birthdays

Which returns:

Conclusion

That basically wraps it up. Hopefully I've explained it clearly, as I said, I was confused at first because of articles I read that failed to explain clearly what these functions are used for. If in doubt, ask me, I'm happy to hear about edits I can make to make these articles clearer, or any other way I can help with SQL.