Percentile in SQL Server

Percentile_cont and Percentile_disc in SQL Server

Percentile_cont and Percentile_disc

Look at the following set of SQL statements:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 The

Run Result:

Name score per_rnk percont0_5 percont0_6 percont0_7 percont0_75 perdist0_5 perdist0_6 perdist0_7 perdist0_75

Leewhoeeuniversity 10 0 30 34 38 40 30 30 40 40

Leewhoeeuniversity 20 0.25 30 34 38 40 30 30 40 40

Leewhoeeuniversity 30 0.5 30 34 38 40 30 30 40 40

Leewhoeeuniversity 40 0.75 30 34 38 40 30 30 40 40

Leewhoeeuniversity 50 1 30 34 38 40 30 30 40 40

DePaul 60 0 80 84 88 90 80 80 90 90

DePaul 70 0.25 80 84 88 90 80 80 90 90

DePaul 80 0.5 80 84 88 90 80 80 90 90

DePaul 90 0.75 80 84 88 90 80 80 90 90

DePaul 100 1 80 84 88 90 80 80 90 90

Simple understanding, Percent_rank previously introduced, score percent, Percentile_cont and Percentile_disc is based on the percentage of the corresponding score. But the difference is that when a percentage does not have a corresponding score, Percentile_cont calculates a new value based on the percentage deviation, which may not exist in score. Percentile_disc the score value in favor of the nearest percentage, so this value must exist in score.

If Percentile_cont (0.6) corresponds to a value of 34, the deviation value I guess this is calculated by: percentages 75% and 50%percentile_cont are calculated by 40 and 30 respectively. Then the equation: (40-30)/(0.75-0.5) = new deviation value /0.6-0.5. The deviation value of Percentile_cont (0.6) on 50% = 4. Therefore, Percentile_cont (0.6) corresponds to Percentile_cont (0.5) +4=34.

Then look at Percentile_disc (0.6), directly to the nearest 0.5 calculated, Percentile_disc value taken over.