Made to order: Eirik’s Hi-Lo Chart in SQL – extending the SQL Chart Palette (Pie, Stacked Bar Chart, Gauge, Gantt,…)

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