Pie Charts in SQL – how pathetic can you get?

0 0

I have written a number of posts on alternative, far fetched usages of Table Functions. My all-time high thusfar was a post titled Table FUNctions: select a graph in SQL . Now, I think I have reached another level in irrelevancy – though I have to admit it gives me great personal satisfaction: this post tells you how to create pie-charts using SQL queries. Be warned: it is utterly useless. But great fun for the twisted minds I know some of you have too! Think Table Functions, some basic maths and too much fantasy.

The story is really quite simple: you pass a query that selects labels and values as a cursor to a table function –table( cursor (select label, value from table))
In SQL*Plus, we execute these commands:

set linesize 150
set pagesize 500
set long 200

select substr(column_value,1,150) "Piechart Salaries per Job"
from table( pie_graph
            ( cursor(select job, sum(sal) from emp group by job)
            , 'Salaries per Job'
            ,30
            , 0.15
            )
          )
/

The function pie_graph that is invoked here looks as follows:

function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table

It relies on three Types being defined in the database:

  create type string_table is table of varchar2(2000);
create type values_tbl is table of number;
create type fixed_string_table as table of char(150);

Another example of using this function to create pie-charts:

select substr(column_value,1,150) "Piechart Objects per User"
from table( pie_graph
            ( cursor(select owner, count(*) from all_objects where owner not in ('PUBLIC','SYS') group by owner)
            , 'Objects per user'
            ,25
            , 0.15
            )
          )

The complete source code can be downloaded at the end of this article. It looks as follows (suggestions for improvements are very welcome!):

function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table
is
   /* required collection types

      create type string_table is table of varchar2(2000);
      create type values_tbl is table of number;
      create type fixed_string_table as table of char(150);


    select substr(column_value,1,150) "Piechart Salaries per Job"
    from table( pie_graph
                ( cursor(select job, sum(sal) from emp group by job)
                , 'Salaries per Job'
                ,30
                , 0.2
                )
              )
/

    */

   c_full_circle float := 2*3.14159;
   l_radius integer:=nvl(p_radius, 40);

   retval fixed_string_table:= fixed_string_table();
   l_labels string_table;
   l_values values_tbl;
   l_string char(1200);
   l_sum number:=0;
   l_sqr_radius integer:= power(l_radius,2);
   l_dial float:=0;
   l_percentage float;
   l_index integer;
   l_x integer;
   l_others integer:=0;
   l_others_label varchar2(1000):= ': ';
   l_others_value number:=0;

   procedure xy(x integer, y integer, marker char default '*')
   is
   l_string char(1200);
   begin
      l_string:= retval( l_radius - y+1);

      l_string :=  substr( l_string, 1 ,2*(l_radius + x))
                  ||marker
                  ||substr(l_string,2*(l_radius + x)+2)
            ;
      retval( l_radius - y+1):= substr(l_string,1,130);
   end xy;

   procedure create_piesegment
   ( p_percentage in float
   , p_marker in varchar2
   , p_label in varchar2)
   is
   begin
       -- move the dial half the distance (or angle) for this value
       -- to first print the Indicator (A, B, C etc.) for this value
       l_dial:= l_dial + 0.5 *p_percentage * c_full_circle;

       -- display the indicator in this pie-segment
        xy( round(6/10*l_radius*cos(l_dial))
          , round(6/10*l_radius*sin(l_dial))
          , p_marker);

       -- move the dial the other half of the distance (or angle) for this value
       l_dial:= l_dial + 0.5*p_percentage * c_full_circle;
       -- in 10 steps, draw the border for this segment
       for i in 0..10 loop
          -- print a dot at i/10 of the line from the center to the perimeter
          xy( round(i/10*l_radius*cos(l_dial))
            , round(i/10*l_radius*sin(l_dial))
            ,'.');
       end loop;
       -- extend the collection to return to add another line to the legend
       retval.extend;
       -- write the legend line for the current value (something like: A - 500 (23 %))
       retval(retval.last):= p_label;
  end;

begin
   -- retrieve all values and labels from the refcursor into local collections
   fetch p_src bulk collect into l_labels, l_values;
   close p_src;
   -- iterate through the values to calculate the sum of all values
   l_index:= l_values.first;
   loop
     l_sum:= l_sum + l_values(l_index);
     l_index:= l_values.next(l_index);
     if l_index is null then exit; end if;
   end loop;

   -- initialize the collection of fixed length strings to return
   for y in 1..2*l_radius+2 loop
     retval.extend;
     retval(y):= rpad(' ',140);
  end loop;
  retval(1):= p_title;
   -- draw center
   xy(0,0,'x');
   -- draw the outline of the chart
   for y in 0..l_radius  loop
      if (y mod 2 = 0)  -- to get a stepsize of 2 : every other line a marker
      then
        l_x:=  round(sqrt( l_sqr_radius - power(y,2)));
        xy(l_x,y); -- noon-3 o'clock
        xy(-l_x,y); -- 9 to 12
        xy(l_x,-y); -- 3 to 6
        xy(-l_x,-y); -- 6 to 9
      end if;
   end loop;

   -- draw the initial line, from the center horizontally to the right (3 o'clock)
   for i in 0..10 loop
      xy( round(i/10*l_radius*cos(l_dial))
        , round(i/10*l_radius*sin(l_dial))
        ,'.');
   end loop;

   -- iterate through the values and labels to draw all pie-segments
   l_index:= l_values.first;
   loop
     -- calculate the percentage for the current value
     l_percentage:= l_values(l_index)/l_sum;
     if l_percentage > p_threshold
     then
       create_piesegment
       ( p_percentage => l_percentage
       , p_marker => chr(ascii('A')+l_index-1-l_others)
       , p_label => chr(ascii('A')+l_index-1-l_others)||' = '||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %)'
       );
     else -- under threshold, update others
       l_others:= l_others +1;
       l_others_value:= l_others_value + l_values(l_index);
       l_others_label:= l_others_label||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %);';
     end if;
     l_index:= l_values.next(l_index);
     if l_index is null
     then
       exit;
     end if;
   end loop; -- over all values and labels
   if l_others_value <> 0
   then
     -- process the others
     l_percentage:= l_others_value/l_sum;
     create_piesegment
     ( p_percentage => l_percentage
     , p_marker => '#'
     , p_label => '# = Others '||l_others_value||' ('||round(l_percentage*1000)/10||' %)'||l_others_label||' - '
     );
   end if; -- l_others_value <>0
   return retval;
end;

Download the pie_graph function

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

lucas.jellema@amis.nl

https://www.amis.nl/

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases Oracle 10gR2 – Conditional PL/SQL Compilation Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table Oracle Open World / Java One Preview bij AMIS