An issue that came up in this answer:
Say, I have a large range of numerical values (doubles in this case), say from 0.0 - 50,000.0, in a column x. They are not evenly distributed in this range.
Now I want to build partitions to group them. Obviously a GROUP BY x is useless as it will typically group for each and every value. So I will have to define some intervals to group over - in my case, these intervals can be equally wide.
So I guess I will have to build categories by rounding x to them, say by
-- group by interval of ]x - 50, x + 50] select round(x / 100.0, 0) * 100.0 as xRounded, count(*) from MyTable group by xRounded order by xRounded
This will group by intervals of 100 integers, each.
In order to sum up extrem intervals, I could use a further case expression, say
select case when xRounded <= 1000.0 then 1000.0 when xRounded >= 20000.0 then 20000.0 else xRounded end as xRounded2, count(*) from (select round(x / 100.0, 0) * 100.0 as xRounded from MyTable) S group by xRounded2 order by xRounded2
Is there a better way to do so?
I'm sure there are better ways to do this but you could create a join with a table for your partitions. The below shows an example for ranges of 100 from 0 to 5000 using the sa_rowgenerator to create the partitions (and generate test data).
BEGIN //create data SELECT RAND()*5000 as myVal INTO #myTemp FROM sa_rowgenerator(0,3000); //sort data to view distribution SELECT srg.row_num as lower_bound, srg.row_num + 100 as upper_bound, count(myVal) as cnt FROM sa_rowgenerator(0, 4900, 100) srg, #myTemp mt WHERE mt.myVal >= lower_bound AND mt.myVal < upper_bound GROUP BY lower_bound ORDER BY lower_bound; END go