CODEWITHSUNDEEP
sql-server
user1044169
user1044169

Reputation: 2866

Generate histogram in SQL Server

I am using SQL Server 2012 and need to generate a histogram, conceptually similar to Google's screener

The idea is to split all the prices into 100 equally sized (based on price) buckets, and then each bucket contains a number of items priced within the bucket's min and max. NTILE didn't work -- it tried to split items equally (based on count) among buckets.

So, this is what I have so far:

select bucket, count(*) from (select cast((PERCENT_RANK() OVER(ORDER BY Price DESC)) *   100 as int) as bucket  from MyTable
where DataDate = '4/26/2012') t group by bucket

Is this a good way to produce a histogram in SQL Server 2012? Is there anything built-in SQL Server 2012 to do this task or a better way?

Thank you

Upvotes: 2

Views: 4790

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Like this perhaps:

with cte as (
  select base = 1 + u + t*3 from (
    select 0 as u union all select 1 union all select 2
  ) T1
  cross join (
    select 0 as t union all select 1 union all select 2
  ) T2
), data as (
  select * 
  from ( 
   values (1,1,2,3,3,5,7,4,2,1)
  ) data(x0,x1,x2,x3,x4,x5,x6,x7,x8,x9)
)
select cte.base
  ,case when x0>=base then 'X' else  ' ' end as x0
  ,case when x1>=base then 'X' else  ' ' end as x1
  ,case when x2>=base then 'X' else  ' ' end as x2
  ,case when x3>=base then 'X' else  ' ' end as x3
  ,case when x4>=base then 'X' else  ' ' end as x4
  ,case when x5>=base then 'X' else  ' ' end as x5
  ,case when x6>=base then 'X' else  ' ' end as x6
  ,case when x7>=base then 'X' else  ' ' end as x7
  ,case when x8>=base then 'X' else  ' ' end as x8
  ,case when x9>=base then 'X' else  ' ' end as x9
from cte
cross join data
order by base desc
;

which yields this histogram nicely:

base        x0   x1   x2   x3   x4   x5   x6   x7   x8   x9
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
9                                                         
8                                                         
7                                         X               
6                                         X               
5                                    X    X               
4                                    X    X    X          
3                          X    X    X    X    X          
2                     X    X    X    X    X    X    X     
1           X    X    X    X    X    X    X    X    X    X

Remember to pivot your data into a single row first.

For a more compact presentation, concatenate the various data columns into a single long string.

Upvotes: 3

Related Questions