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;
AWSOME!!!!! First time in my life I have seen something like this 🙂
Good Work 🙂
Simply Great!!!This shows your great hold on Oracle.
I am not sure whether this is divine or devilish! Hats off to you 🙂
He, couldn’t you also build in some nice text adventures, underneath those nice graphics 😉
Cool and nerdy. This calls for colors 😉
Have you seen and appreciated the strict MVC architecture? The query itself is the Controller that instructs the Model to produce some data (that is the inner query passed in the first parameter as CURSOR expression while the VIEW, completely shielded from MODEL specifics is only concerned with producing a pretty picture.
Wow! That beats the ol’ bargraph any day…
SQL> column Bar format a20
SQL> select rownum, lpad(‘>’, rownum, ‘-‘) Bar from all_objects where rownum < 7 2 / ROWNUM BAR ---------- -------------------- 1 >
2 ->
3 –>
4 —>
5 —->
6 —–>
6 rows selected.
Something like poke 781,X:poke 782,Y:poke 783,0:sys 65520:Print”*” you mean? 😉 Ok ok i admit, that’s for the Commodore, never did any ZX81 coding
hmmm it gives me those warm ZX-81 memories 🙂
LOL! oh.. my.. god.. 😀 Great stuff
How about animation? 😉