SQL Server : Segregate data into dynamic buckets

Question

Please help me with a SQL Server Query that can bucket data dynamically into ranges.

Here is my source data:

Value
=======
45

33.5
33.1
33
32.8

25.3
25.2
25.1
25

21.3
21.2
21.1
20.9

12.3
12.2
12.15
12.1
12
11.8

Expected output:

Value  Rank
=============
45        1
(mean value in this range is 45)

33.5      2
33.1      2
33        2
32.8      2
(mean value is 33.1 - any value in the range (-10%) 29.79 to 36.41 (+10%) should be given a rank of 2)

25.3      3
25.2      3
25.1      3
25        3

21.3      4
21.2      4
21.1      4
20.9      4

12.3      5
12.2      5
12.15     5
12.1      5
12        5
11.8      5

DENSE, RANK and NTILE does not seem to give me a ranking like this. The range is dynamic and not known earlier. Any help highly appreciated.

The bucketing rule is:

Each bucket contains a data set with 10% variation from the mean value

Answer 1

Here's one way:

select val, dense_rank() over (order by cast(val/10 as int) desc) ntile 
from yourtable

Use dense_rank but specify your buckets in the order by clause. (I'm assuming this is how it works for your sample data)

Answer 2

First convert the value to a number having 2 decimal places.
Then, use a CASE expression for doing FLOOR or ROUND function based on the first number after decimal point.
Then use DENSE_RANK function for giving rank based on the rounded value.

Query

select z.[Value], dense_rank() over(order by z.[val_rounded] desc) as [Rank] from(
    select t.[Value],
    case when substring(t.[Value2], charindex('.', t.[Value2], 1) + 1, 1) > 5 
    then round(t.[Value], 0) else floor(t.[Value]) end as [val_rounded] from(
        select [Value], cast((cast([Value]as decimal(6, 2))) as varchar(50)) as [Value2]
        from [your_table_name]
    )t
)z;

Demo