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 <br />function string_table_to_string<br />( p_tbl in string_table_collection<br />) return varchar2<br />is<br /> l_str varchar2(4000):='';<br />begin<br /> for i in 1..p_tbl.count loop<br /> l_str:= l_str||p_tbl(i);<br /> end loop;<br /> return l_str;<br />end;<br />/<br />
Which requires the following nested table definition:
create or replace type string_table_collection<br />as table of varchar2(4000)<br />/<br /><br />
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<br />( select deptno label<br /> , max(sal) hi<br /> , min(sal) lo<br /> , sum(sal) summary<br /> , count(sal) rec_count<br /> , avg(sal) average<br /> from emp<br /> group<br /> by deptno<br />)<br />
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<br />( select min(lo) overall_lo<br /> , max(hi) overall_hi<br /> , sum(summary)/sum(rec_count) overall_avg<br /> , sum(rec_count) overall_sum<br /> , max(length(label))+1 label_length<br /> from chart_query<br />)<br />, chart_dimensions as<br />( select (overall_avg - x_min)/(x_max - x_min) * width axis_pos<br /> , x_min<br /> , x_max<br /> , width<br /> , marker_count<br /> from ( select least(1000, 100*trunc(overall_lo/100)) x_min <br /> , greatest(7000, 100*(1+trunc(overall_hi/100))) x_max <br /> , 80 width<br /> , overall_avg<br /> , 6 marker_count<br /> from limits<br /> )<br />) <br />
- Gauge charts in SQL or How to produce the world's ugliest speedometers
- Creating a Gantt-chart in SQL
- Stacked Bar Chart – you can do that too in SQL
- SVG, XSLT and JSP/JSTL: Generating Digital Gauge or Speedometer Chart based on dynamic data
- Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines)