Time for another charting SQL query. I wish to present a single-query generated multi-line/area chart. I'll walk through some of the steps towards making this happen. By the end of this post I'll present some real-data charts, area charts and colored charts.

Step 5

We now group the marks per graph-row value. If there's no mark, we present with an empty space. If only one mark is present, we display that mark. If two lines collide on that point, we need to decide which mark to use. In the following example, we choose by order of appearance in the row_values column. Another way (used later on) is to choose the higher value.

Putting it all together

From this point on, we use the techniques shown on Auto scaling, scaled SQL graphs concluded to generate the complete chart. For the curious, here's what the complete query looks like. The text in bold is the only thing that needs to change; replace this with your own query.

Area charts

Sinus functions curves go nicely with ASCII art. But your everyday chart won't have such nice curves. There is some erratic behavior to, say, your number of queries per hour. It doesn't display well in the above graph.

Which is why we can choose to present area graphs: not only display the line, but also the area beneath it.

This leads to the problem of hiding: higher values can hide the lower values display. So we need to adjust the above logic and make sure we always bring to front the lower values.

The following example shows the relation between the percentage of slow queries (per hour) and the percentage of full table scans (of total queries, per hour). It's really fun to see the unmistakeable connection!

Colored charts

Time to add colors! The previous charts can be easily improved by sending the terminal escape characters to instruct initiating color display (thanks to TheVoo for providing the idea and implementation for that on SQL pie chart).