How to generate a histogram using SQL
March 6th, 2006 By Eddie Awad
To generate an output like the following, where each employee in a department is represented by a “+”:
DEPARTMENT_NAME EMPLOYEES ------------------------------ ---------------------------------------------- Administration + Accounting ++ Purchasing ++++++ Human Resources + IT +++++ Public Relations + Executive +++ Shipping +++++++++++++++++++++++++++++++++++++++++++++ Sales ++++++++++++++++++++++++++++++++++ Finance ++++++ Marketing ++
Use the following query:
select d.department_name,
lpad('+',count(*),'+') as employees
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
Note that you can also use rpad instead of lpad.
March 9th, 2006 at 1:21 am
Hi Eddie,
your example shows very good how a Frequency Histogram works. Do you have an example(sql) / idea how to generate a height based histogram with your pseudo graphical output?
from the Oracle Server Performance Guide :
Frequency Histograms
In a frequency histogram, each value of the column corresponds to a single bucket of
the histogram. Each bucket contains the number of occurrences of that single value.
Frequency histograms are automatically created instead of height-balanced histograms
when the number of distinct values is less than or equal to the number of histogram
buckets specified. Frequency histograms can be viewed using the *TAB_HISTOGRAMS
tables, as shown in Example 14–2.
Height-Balanced Histograms
In a height-balanced histogram, the column values are divided into bands so that each
band contains approximately the same number of rows. The useful information that
the histogram provides is where in the range of values the endpoints fall.
Greetings
Karl
March 9th, 2006 at 9:10 pm
Well, an example of the data behind a height-balanced histogram (from the HR schema in my XE database):
How do you imagine the result set above be represented graphically?
March 9th, 2006 at 10:12 pm
Sorry for confusion. I thought about an example out of the HR Data to generate a Height based Histogram. For example your Frequncy histogram has 11 Buckets - the departments. If there would be thousands of departments you would need to aggregate that with fewer buckets. I will try an example out of the HR-Schema with the same data when i have time to do this.
Background is that the understanding of these tow historgram type would increase with these examples.
Greetings
Karl
March 12th, 2006 at 8:01 pm
You can derive a height based histogram easily enough with analytics. However I don’t see a useful way of displaying that graphically.