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
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:

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.