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.
We need a generic query, which returns at least these two columns: ordering_column and row_values, The latter being a comma-delimited list of values. For example, the following query will do:
Don’t worry about the @multi_line_chart_values_legend variable; it will be used later on for presenting chart legend.
Since I’ve already presented with how to create line charts in Auto scaling, scaled SQL graphs concluded, we will concentrate on how to present a single chart column, which includes multiple values.
Step 1
Let’s say we wish to present the values 12,5,16. We wish to eventually generate something like:
Where X is in position 12, Y in position 5, and Z in position 16.
We begin with having these values concatenated into one string:
SET @values := '12,5,16';
SET @num_values := CHAR_LENGTH(@values)-CHAR_LENGTH(REPLACE(@values,',',''))+1;
SELECT @num_values;
+-------------+
| @num_values |
+-------------+
| 3 |
+-------------+
The query above parses the string and correctly identified there are three tokens.
Step 2
We now move on to tokenizing the text:
SELECT
*,
SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1) AS row_value
FROM
tinyint_asc,
(SELECT @row_values := '12,5,16' AS row_values) AS sel_values,
(SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values
WHERE
tinyint_asc.value BETWEEN 1 AND @num_values
;
+-------+------------+------------+-----------+
| value | row_values | num_values | row_value |
+-------+------------+------------+-----------+
| 1 | 12,5,16 | 3 | 12 |
| 2 | 12,5,16 | 3 | 5 |
| 3 | 12,5,16 | 3 | 16 |
+-------+------------+------------+-----------+
Using a numbers table (tinyint_asc), we walk the string and tokenize it. We also maintain an indicator per value.
Step 3
We need to recognize the minimum and maximum values:
SELECT
value AS row_value_indicator,
row_value,
@min_row_value := LEAST(IFNULL(@min_row_value, row_value), row_value) AS min_row_value,
@max_row_value := GREATEST(IFNULL(@max_row_value, row_value), row_value) AS max_row_value
FROM (
SELECT
*,
SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value
FROM
tinyint_asc,
(SELECT @row_values := '12,5,16' AS row_values) AS sel_values,
(SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values,
(SELECT @min_row_value := NULL) AS sel_min_row_value,
(SELECT @max_row_value := NULL) AS sel_max_row_value
WHERE
tinyint_asc.value BETWEEN 1 AND @num_values
) sel_row_values
;
+---------------------+-----------+---------------+---------------+
| row_value_indicator | row_value | min_row_value | max_row_value |
+---------------------+-----------+---------------+---------------+
| 1 | 12 | 12 | 12 |
| 2 | 5 | 5 | 12 |
| 3 | 16 | 5 | 16 |
+---------------------+-----------+---------------+---------------+
The last line presents the correct values: 5 and 16 being min, max values respectively.
Step 4
We now iterate from min value to max value, this being the graph range, and, per row value (token in row_values column), we indicate whether there’s a pixel on the graph.
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.
SELECT
string_position,
LEFT(GROUP_CONCAT(bar_string_token ORDER BY bar_string_token DESC separator ''), 1) AS chosen_mark
FROM
(SELECT
value AS string_position,
IF(tinyint_asc.value = row_value, row_value_indicator, ' ') AS bar_string_token
FROM
tinyint_asc,
(SELECT
value AS row_value_indicator,
row_value,
@min_row_value := LEAST(IFNULL(@min_row_value, row_value), row_value) AS min_row_value,
@max_row_value := GREATEST(IFNULL(@max_row_value, row_value), row_value) AS max_row_value
FROM (
SELECT
*,
SUBSTRING_INDEX(SUBSTRING_INDEX(@row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value
FROM
tinyint_asc,
(SELECT @row_values := '12,5,16' AS row_values) AS sel_values,
(SELECT @num_values := CHAR_LENGTH(@row_values)-CHAR_LENGTH(REPLACE(@row_values,',',''))+1 AS num_values) AS sel_num_values,
(SELECT @min_row_value := NULL) AS sel_min_row_value,
(SELECT @max_row_value := NULL) AS sel_max_row_value
WHERE
tinyint_asc.value BETWEEN 1 AND @num_values
) sel_row_values
) AS sel_row_values_indicators
WHERE
tinyint_asc.value BETWEEN 1 AND @max_row_value
) AS sel_marked_row_values
GROUP BY
string_position
;
+-----------------+-------------+
| string_position | chosen_mark |
+-----------------+-------------+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | 2 |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | 1 |
| 13 | |
| 14 | |
| 15 | |
| 16 | 3 |
+-----------------+-------------+
Step 6
It’s now just a matter of using GROUP_CONCAT to turn this into the following horizontal bar:
(I know I’m mixing spaces and dashes, it’s just for display purposes).
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.
SELECT
y_scale,
horizontal_bar as chart
FROM
(
SELECT
@multi_line_chart_row_number := @multi_line_chart_row_number+1,
CASE @multi_line_chart_row_number
WHEN 1 THEN ROUND(@multi_line_chart_max_value, @multi_line_chart_value_precision)
WHEN @multi_line_chart_graph_rows THEN ROUND(@multi_line_chart_min_value, @multi_line_chart_value_precision)
ELSE ROUND(@multi_line_chart_max_value-(@multi_line_chart_max_value-@multi_line_chart_min_value)*(@multi_line_chart_row_number-1)/(@multi_line_chart_graph_rows-1), @multi_line_chart_value_precision)
END AS y_scale,
horizontal_bar,
@multi_line_chart_bar_length := IFNULL(@multi_line_chart_bar_length, CHAR_LENGTH(horizontal_bar))
FROM
(SELECT @multi_line_chart_row_number := 0) AS select_row
INNER JOIN
(
SELECT
GROUP_CONCAT(SUBSTRING(unwalked_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar
FROM
tinyint_asc
INNER JOIN (
SELECT
ordering_column,
GROUP_CONCAT(bar_string_token ORDER BY string_position SEPARATOR '') AS unwalked_bar
FROM
(SELECT
ordering_column,
string_position,
scaled_string_position,
REPLACE(LEFT(GROUP_CONCAT(bar_string_token ORDER BY bar_string_token DESC SEPARATOR ''), 1), ' ', '-') AS bar_string_token
FROM
(SELECT
ordering_column,
@multi_line_chart_scaled_string_position := CONVERT((row_value-@multi_line_chart_min_value)*(@multi_line_chart_graph_rows-1)/(@multi_line_chart_max_value-@multi_line_chart_min_value), UNSIGNED) AS scaled_string_position,
value AS string_position,
IF(tinyint_asc.value = @multi_line_chart_scaled_string_position+1, SUBSTRING(IF(@multi_line_chart_values_legend IS NULL, @multi_line_chart_graph_fallback_colors, @multi_line_chart_graph_colors), row_value_indicator, 1), ' ') AS bar_string_token
FROM
tinyint_asc,
(SELECT
ordering_column,
value AS row_value_indicator,
row_value
FROM (
SELECT
*,
@multi_line_chart_min_value := LEAST(IFNULL(@multi_line_chart_min_value, row_value), row_value) AS min_value,
@multi_line_chart_max_value := GREATEST(IFNULL(@multi_line_chart_max_value, row_value), row_value) AS max_value,
@multi_line_chart_min_range := LEAST(IFNULL(@multi_line_chart_min_range, ordering_column), ordering_column) AS min_range,
@multi_line_chart_max_range := GREATEST(IFNULL(@multi_line_chart_max_range, ordering_column), ordering_column) AS max_range
FROM
(SELECT
*,
SUBSTRING_INDEX(SUBSTRING_INDEX(row_values, ',', tinyint_asc.value), ',', -1)+0 AS row_value,
@multi_line_chart_num_values := CHAR_LENGTH(row_values)-CHAR_LENGTH(REPLACE(row_values,',',''))+1 AS num_values
FROM
(SELECT @multi_line_chart_values_legend := NULL) AS select_nullify_values_legend,
(SELECT @multi_line_chart_bar_length := NULL) AS select_nullify_bar_length,
tinyint_asc,
(
SELECT
value/10 AS ordering_column,
CONCAT(SIN(value/10), ',', COS(value/10), ',', SIGN(TAN(value/10))/2) AS row_values,
@multi_line_chart_values_legend := 'sin(x),cos(x),sign(tan(x))/2'
FROM tinyint_asc LIMIT 81
) AS sel_main_values,
(SELECT @multi_line_chart_min_value := NULL) AS select_min,
(SELECT @multi_line_chart_max_value := NULL) AS select_max,
(SELECT @multi_line_chart_min_range := NULL) AS select_min_range,
(SELECT @multi_line_chart_max_range := NULL) AS select_max_range,
(SELECT @multi_line_chart_graph_colors := '#*@%o+x;m:') AS select_graph_colors,
(SELECT @multi_line_chart_graph_fallback_colors := 'abcdefghij') AS select_graph_fallback_colors,
(SELECT @multi_line_chart_value_precision := 2) AS select_value_precision,
(SELECT @multi_line_chart_graph_rows := 17) AS select_graph_rows
) sel_counted_values_main_values
WHERE
value BETWEEN 1 AND @multi_line_chart_num_values
) sel_row_values
) AS sel_row_values_indicators
WHERE
tinyint_asc.value BETWEEN 1 AND @multi_line_chart_graph_rows
) AS sel_marked_row_values
GROUP BY
ordering_column, string_position
) AS sel_walked_bar
GROUP BY
ordering_column
) AS select_vertical
WHERE
tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(unwalked_bar)
GROUP BY
tinyint_asc.value
ORDER BY
tinyint_asc.value DESC
) AS select_horizontal
) AS select_horizontal_untitled
UNION ALL
SELECT '', CONCAT('v', REPEAT(':', @multi_line_chart_bar_length-2), 'v')
UNION ALL
SELECT '', CONCAT(@multi_line_chart_min_range, REPEAT(' ', @multi_line_chart_bar_length-CHAR_LENGTH(@multi_line_chart_min_range)-CHAR_LENGTH(@multi_line_chart_max_range)), @multi_line_chart_max_range)
UNION ALL
SELECT
'', CONCAT(' ', SUBSTRING(@multi_line_chart_graph_colors, value, 1), ' ', SUBSTRING_INDEX(SUBSTRING_INDEX(@multi_line_chart_values_legend, ',', value), ',', -1))
FROM
tinyint_asc
WHERE
value BETWEEN 1 AND @multi_line_chart_num_values
AND @multi_line_chart_values_legend IS NOT NULL
;
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!
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).
I recommend using the following pager if you’re on unix-like OS:
Some of the techinuqes used in my charting series of blogs can be used, pretty much in the same way, in order to generate Google charts. But other techniques can be used, as well.
Nive work! SQL charting in text mode is:
a) cool
b) sometimes useful
c) slow for big data sets
How much time it took to do it !!! really impressive.
OMG!, GRandioso, ingenioso!! 🙂