0 
0 With the series on SQL based Charts, I can now safely commit any crime in the firm knowledge that an insanity plea will undoubtedly be accepted. After spending way too much time on pretty nonsensical exercises like the Gauge and Stacked Bart Chart in SQL, I had decided not to do it again. But then, out of the blue, an urgent request reached me all the way from Norway: can you please do a Hi-Lo chart?
Never one to turn down anyone seeking help, I got into my SQL coding outfit and produced the following query, that produces graphically challenged results such as:
This type of chart is used to quickly give an impression of the range of a certain parameter – where are the extremes, what is the average – per category. In this example we see the Salary, per Department. The graphical layout makes it clear that Department 10 has the fat cats: it has the highest extreme as well as highest average (the asterisk) and is the only department to have an higher than average average.
We can use the same query
with almost no modifications to plot the Salary per Job:
Or show the hiredate plotted as High-Low chart.
Note: there are about 10 lines of PL/SQL involved, all to create the markers (1000, 2200,…).The rest of it is pure SQL, not even Analytical Functions or other advanced, far fetched features.
The required PL/SQL is this piece:
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; /
Which requires the following nested table definition:
create or replace type string_table_collection as table of varchar2(4000) /
And then it’s all SQL from here on.
The query is largely driven from the chart-query in line view:
with chart_query as ( select deptno label , max(sal) hi , min(sal) lo , sum(sal) summary , count(sal) rec_count , avg(sal) average from emp group by deptno )
It is this one that returns the data required by the query parts that actually draw the graph. Through the limits and chart_dimensions queries, we can do some layout settings like the width of the chart, the value range for the x-axis etc.:
, limits as ( select min(lo) overall_lo , max(hi) overall_hi , sum(summary)/sum(rec_count) overall_avg , sum(rec_count) overall_sum , max(length(label))+1 label_length from chart_query ) , chart_dimensions as ( select (overall_avg - x_min)/(x_max - x_min) * width axis_pos , x_min , x_max , width , marker_count from ( select least(1000, 100*trunc(overall_lo/100)) x_min , greatest(7000, 100*(1+trunc(overall_hi/100))) x_max , 80 width , overall_avg , 6 marker_count from limits ) )
.
Resources
Download: hilo_chart.txt
Related posts:
Good Citizenship – Have Client Applications register themselves with the database 
Oracle releases Raptor – free tool for Database Development (SQL, PL/SQL, database object browsing) Find the number of descendant nodes in a SQL query – how many employees work (indirectly) for me? And what do they earn on average? Building a Route Planner with Oracle SQL – Finding the quickest route in a graph Bloom Filters, Hierarchical Profiling, Synopses & One pass distinct sampling, jumping the GAPP … – feast for DBAs (considered harmful..)
Thank you for your article.I know more
Why use a separate function (string_table_to_string) and type (string_table_collection)? You can use something like the following SQL to do the same.
Anton
( select markers
from ( select marker markers
, rn
from marker_values
, chart_dimensions
model
dimension by (row_number() over (order by marker_value desc) rn)
measures (rpad(marker_value, width /(marker_count-1) ) marker)
rules ( marker[any] order by rn desc = marker[cv()+1] || marker[cv()] )
)
where rn = 1
)