In my series of useless queries and my attempt to produce any Chart-style in ASAP (as plain as possible) SQL, I would now like to show my Stacked Bar Chart, for the moment in a 2D display. In a Stacked Bar Chart, you see a bar per group – for example department – and in the par different ‘zones’ (piles) per category – for example Job. This next picture shows an example of Stacked Bar Chart that displays per department the number of employees in every Job:
In this article I will show how, with a very straightforward and generic query-structure, your queries too can be turned in cool, visually appealing bar charts. And I cheat just a tiny
little bit in the sense that I need just one PL/SQL function that concattenates Strings in a nested table structure into a single string.
In preparation, I need a Nested Table Type:
create or replace type string_table_collection as table of varchar2(4000) /
and a PL/SQL function that takes the type as input and returns a VARCHAR2 as result, with the concattenated value of all strings in the nested table:
create or replace
function string_table_to_string
( p_tbl in string_table_collection
) return varchar2
is
l_str varchar2(4000):='';
begin
for i in 1..p_tbl.count loop
l_str:= l_str||p_tbl(i);
end loop;
return l_str;
end;
/
Using these building blocks, the query for stacked bar chart is as follows. Note that only the first in line view – called Chart_Query – needs to be modifiedfor your own data set, the rest is generic.
with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select job category
, job category_orderby -- specify here the value by which the categories must be ordered
, deptno groupid
, count(*) value -- the value to show in the substacks, has to be numerical; could also be sum(sal) or sysdate - min(hiredate)
from emp
group
by job
, deptno
order
by job
)
, bar_dimensions as
( select 3 height -- the height of the bars - can be changed
, 80 width -- the width of the bars, can be changed
from dual
)
, fillers as
( SELECT LEVEL rn
, substr(filler,level,1) fillcharacter
FROM ( select '+*#^@\/?%$!().,><{}|~' filler
from dual
)
CONNECT
BY length(filler)> LEVEL
ORDER
BY LEVEL ASC
)
, categories as
( select distinct
category
, category_orderby
from chart_query
order
by category_orderby
)
, limits as
( select 0 low_limit
, max(sum(value)) high_limit
from chart_query
group
by groupid
)
, bar_fillers as
( SELECT LEVEL rn
, case level
when round((height+1)/2)
then '*'
when height+1
then '#'
end marker
FROM bar_dimensions
CONNECT
BY height+2 > LEVEL
ORDER
BY LEVEL ASC
)
, category_fillers as
( select category
, fillcharacter
from ( select category
, rownum rn
from categories
) c
, fillers f
where c.rn = f.rn
)
, ccv2 as
( select cq.groupid
, cq.value
, cf.category
, cf.fillcharacter
, low_limit
, high_limit
, width
, rpad( fillcharacter
, width * (value - low_limit)/(high_limit - low_limit)
, fillcharacter
) bar
from category_fillers cf
inner join
chart_query cq
on (cq.category = cf.category)
cross join
limits
cross join
bar_dimensions
order
by cq.groupid, cf.category
)
, bars as
( select lpad(groupid,'5')||' ' label
, string_table_to_string (cast (collect(bar) as job_bar_collection)) bar
from ccv2
group
by groupid
)
, bar_chart as
(
select case bf.marker
when '*' -- the center of the bar, good position to print the label
then label||bar
when '#' -- the last line of the bar, print nothing to get spacing between the bars
then ''
else lpad(' ', length(label))||bar
end bar
from bars
, bar_fillers bf
order
by label, bf.rn
)
, chart_legend as
( select label
from ( select rpad('-', width +6 ,'-') label
from bar_dimensions
union all
select rpad(' ', 6)
||rpad(low_limit, 0.33*width)
||rpad(round(0.33 * (high_limit-low_limit)), 0.33*width)
||rpad(round(0.66 * (high_limit-low_limit)), 0.33*width)
||high_limit
from limits
cross join
bar_dimensions
union all
select null
from limits
union all
select 'Legend:'||chr(13)||chr(10)||'-------'
from limits
union all
select fillcharacter||' = '||category
from category_fillers
)
)
select bar
from bar_chart
union all
select label
from chart_legend
/
The query is probably somewhat more fragmented than is optimal. To understand what is happening, this is fortunate. The first in-line view – chart_query – is the one that provides the data model for the chart. Here you can plug in your own query, to turn it into a chart as well. The groupid column indicates bars (here department), the categories are the piles within the pars (here the job) and the value specifies the numerical value that determines the height of the pile (here the number of employees in the job in the department).
The second view, bar_dimensions, is used to specify the height or thickness end the width (or length) of the bars. If you have many groups, you may want to use a smaller height, if you have but a few, you can use thicker bars.
The fillers view provides the characters used to fill the various piles or stacks in the bar. Currently the view returns 21 different filler characters; if you have more categories than that, you probably should use a different technology to create charts than SQL.
The categories view returns the list distincty category values. Limits determines the upper and lower limits, to properly scale the bars. Bar_fillers is used to provide a set of rows to join the bar width to turn a single bar row into as many rows as the bar-thickness or height requires. Category_fillers associates all categories (jobs for example) with one of the filler characters (^%!@# etc.).
With all these underlying inline-views in place, the real work is done in three consecutive steps (which may be merged together). First (ccv2), all bars are retrieved, associated with a filler-character and the bar-dimensions. At this stage, we have individual bars, not only for all groups but also for each category within the group
. The bars view aggregates the
bars from ccv2 per group, using the COLLECT operator to aggregate the bars into a string_table_collection and then calls our PL/SQL function string_table_to_string to collapse the collection into a single string again.
By joining the single-row bar per group with the bar_fillers view, we bring the bars to their proper thickness (height) and add spacing (an empty line between the bars). This happens in bar_chart. The view chart_legend creates the x-axis as well as legend to indicate how the filler characters are related to the categories.
Finally bar_chart and chart_legend are unioned together, and presto: our chart!
Another example: number employees hired per hire-month per department
With this elaborate query at our disposal and the promise that only the chart_query needs to be adopted to chart-challenge at hand, let’s create a chart for the EMPLOYEES table in the HR schema, displaying for each department (group) the number of employees hired in each month of the year. All we need to do is write the chart_query, which is simple enough:
with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category ( select to_char(hire_date,'MONTH') category , extract(month from hire_date) category_orderby , department_id groupid , count(*) value from employees group by to_char(hire_date,'MONTH') , extract(month from hire_date) , department_id )
Then plug this chart-query into the overall Stacked Bar Chart query, perhaps adjust the bar height, and run it.
Charting the count of instances of Object Types per Schema
Another example of the Stacked Bar Chart would be this utility for DBAs that shows the number of instances of various object types per schema, filtered for specific object types and specific schemas.
with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select object_type category
, object_type category_orderby
, owner groupid
, count(*) value
from all_objects
where object_type in ('TABLE','VIEW','PACKAGE','TRIGGER', 'INDEX')
and owner not in ('SYS','SYSTEM')
group
by object_type
, owner
)
The result looks like this in my local database:
