Stacked Bar Chart – you can do that too in SQL

0

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<br />as table of varchar2(4000)<br />/<br />&nbsp;

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 <br />function string_table_to_string<br />( p_tbl in string_table_collection<br />) return varchar2<br />is<br />  l_str varchar2(4000):='';<br />begin<br />  for i in 1..p_tbl.count loop<br />    l_str:= l_str||p_tbl(i);<br />  end loop;<br />  return l_str;<br />end;<br />/<br /><br />

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<br /> ( select job      category      <br />   ,      job      category_orderby -- specify here the value by which the categories must be ordered<br />   ,      deptno   groupid<br />   ,      count(*) value -- the value to show in the substacks, has to be numerical; could also be sum(sal) or sysdate - min(hiredate) <br />   from   emp<br />   group<br />   by     job<br />   ,      deptno<br />   order<br />   by     job<br /> )<br /> , bar_dimensions as<br />  ( select 3 height -- the height of the bars - can be changed<br />    ,      80 width -- the width of the bars, can be changed<br />    from   dual<br />  )<br /> , fillers as<br /> ( SELECT LEVEL rn<br />   ,      substr(filler,level,1) fillcharacter<br />   FROM   ( select '+*#^@\/?%$!().,&gt;&lt;{}|~' filler<br />            from   dual<br />          ) <br />   CONNECT <br />   BY     length(filler)&gt; LEVEL<br />   ORDER <br />   BY     LEVEL ASC <br /> ) <br /> , categories as <br /> ( select distinct <br />          category      <br />   ,      category_orderby          <br />   from   chart_query<br />   order<br />   by     category_orderby<br /> )<br /> , limits as<br /> ( select 0 low_limit<br />   ,      max(sum(value)) high_limit<br />   from   chart_query<br />   group<br />   by     groupid<br /> )<br /> , bar_fillers as<br /> ( SELECT LEVEL rn<br />   ,      case level<br />          when round((height+1)/2)<br />          then '*'<br />          when height+1<br />          then '#'<br />          end marker<br />   FROM   bar_dimensions <br />   CONNECT <br />   BY     height+2 &gt; LEVEL<br />   ORDER <br />   BY     LEVEL ASC <br /> ) <br /> , category_fillers as<br /> ( select category<br />   ,      fillcharacter<br />   from   ( select category<br />            ,      rownum rn<br />            from   categories<br />          ) c<br />   ,      fillers f<br />   where  c.rn = f.rn<br /> )  <br /> , ccv2 as<br /> ( select cq.groupid<br />   ,      cq.value<br />   ,      cf.category<br />   ,      cf.fillcharacter<br />   ,      low_limit<br />   ,      high_limit<br />   ,      width<br />   ,      rpad( fillcharacter<br />               , width * (value - low_limit)/(high_limit - low_limit)<br />               , fillcharacter<br />               ) bar<br />   from   category_fillers cf<br />          inner join<br />          chart_query cq<br />          on (cq.category = cf.category)<br />          cross join<br />          limits<br />          cross join<br />          bar_dimensions<br />   order <br />   by     cq.groupid, cf.category<br /> )<br /> , bars as<br /> ( select lpad(groupid,'5')||' ' label <br />   ,      string_table_to_string (cast (collect(bar) as job_bar_collection)) bar<br />   from   ccv2<br />   group<br />   by     groupid<br /> )<br />, bar_chart as<br />(<br />  select case bf.marker<br />         when '*' -- the center of the bar, good position to print the label<br />         then label||bar<br />         when '#' -- the last line of the bar, print nothing to get spacing between the bars<br />         then '' <br />         else lpad(' ', length(label))||bar<br />         end bar<br />  from   bars<br />  ,      bar_fillers bf<br />  order<br />  by     label, bf.rn<br />  )<br />, chart_legend as<br />( select label<br />  from   (  select rpad('-', width +6 ,'-') label<br />            from   bar_dimensions<br />            union all<br />            select rpad(' ', 6)<br />                   ||rpad(low_limit, 0.33*width)<br />                   ||rpad(round(0.33 * (high_limit-low_limit)), 0.33*width)<br />                   ||rpad(round(0.66 * (high_limit-low_limit)), 0.33*width)<br />                   ||high_limit<br />            from   limits<br />                   cross join<br />                   bar_dimensions<br />            union all<br />            select null<br />            from   limits<br />            union all<br />            select 'Legend:'||chr(13)||chr(10)||'-------'<br />            from   limits<br />            union all<br />            select fillcharacter||' = '||category<br />            from   category_fillers<br />          )<br />)<br />select bar<br />from   bar_chart<br />union all<br />select label <br />from   chart_legend<br />/<br /><br /><br />

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<br /> ( select to_char(hire_date,'MONTH') category      <br />   ,      extract(month from hire_date) category_orderby<br />   ,      department_id  groupid<br />   ,      count(*) value<br />   from   employees<br />   group<br />   by     to_char(hire_date,'MONTH')<br />   ,      extract(month from hire_date)<br />   ,      department_id<br /> )<br />&nbsp;

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<br /> ( select object_type category      <br />   ,      object_type category_orderby<br />   ,      owner  groupid<br />   ,      count(*) value<br />   from   all_objects<br />   where  object_type in ('TABLE','VIEW','PACKAGE','TRIGGER', 'INDEX')<br />   and    owner  not in ('SYS','SYSTEM')<br />   group<br />   by     object_type<br />   ,      owner<br /> )<br />&nbsp;

The result looks like this in my local database:

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.