SQL multi line chart

November 3, 2009

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.

+---------+-----------------------------------------------------------------------------------+
| y_scale | chart                                                                             |
+---------+-----------------------------------------------------------------------------------+
| 1       | ****---------#######----------------------------------------*******--------###### |
| 0.88    | ----***---###-------##-----------------------------------***-------***---##------ |
| 0.75    | -------**#------------##-------------------------------**-------------*##-------- |
| 0.63    | ------##-*--------------##----------------------------*--------------##**-------- |
| 0.5     | -@@@@@@@@@@@@@@@----------#-----@@@@@@@@@@@@@@@@----**---------@@@@@@@@@@@@@@@@-- |
| 0.38    | ----#-------*--------------##----------------------*---------------#-------*----- |
| 0.25    | --##---------*---------------#--------------------*--------------##---------*---- |
| 0.13    | -#------------**--------------#-----------------**--------------#------------*--- |
| 0       | @---------------*--------------##--------------*---------------#--------------**- |
| -0.12   | -----------------*---------------#------------*--------------##-----------------* |
| -0.25   | ------------------*---------------#---------**--------------#-------------------- |
| -0.37   | -------------------**--------------#-------*---------------#--------------------- |
| -0.5    | ----------------@@@@@@@@@@@@@@@@----##----*-----@@@@@@@@@@@@@@@----------------@@ |
| -0.62   | ----------------------**--------------#-**--------------#------------------------ |
| -0.75   | ------------------------**------------**#-------------##------------------------- |
| -0.87   | --------------------------**-------***---###-------###--------------------------- |
| -1      | ----------------------------*******---------#######------------------------------ |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                               8 |
|         |     # sin(x)                                                                      |
|         |     * cos(x)                                                                      |
|         |     @ sign(tan(x))/2                                                              |
+---------+-----------------------------------------------------------------------------------+

Requirements

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:

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' AS legend
FROM tinyint_asc
LIMIT 81;
+-----------------+---------------------------------------------+------------------------------+
| ordering_column | row_values                                  | legend                       |
+-----------------+---------------------------------------------+------------------------------+
|          0.0000 | 0,1,0.0000                                  | sin(x),cos(x),sign(tan(x))/2 |
|          0.1000 | 0.099833416646828,0.99500416527803,0.5000   | sin(x),cos(x),sign(tan(x))/2 |
|          0.2000 | 0.19866933079506,0.98006657784124,0.5000    | sin(x),cos(x),sign(tan(x))/2 |
|          0.3000 | 0.29552020666134,0.95533648912561,0.5000    | sin(x),cos(x),sign(tan(x))/2 |
|          0.4000 | 0.38941834230865,0.92106099400289,0.5000    | sin(x),cos(x),sign(tan(x))/2 |
|          0.5000 | 0.4794255386042,0.87758256189037,0.5000     | sin(x),cos(x),sign(tan(x))/2 |
|          0.6000 | 0.56464247339504,0.82533561490968,0.5000    | sin(x),cos(x),sign(tan(x))/2 |
...

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:

+------------------+
| unwalked_bar     |
+------------------+
| ----Y------X---Z |
+------------------+

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.

SELECT
  * ,
  IF(tinyint_asc.value = row_value, row_value_indicator, '-') AS display_val
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
;
+-------+---------------------+-----------+---------------+---------------+-------------+
| value | row_value_indicator | row_value | min_row_value | max_row_value | display_val |
+-------+---------------------+-----------+---------------+---------------+-------------+
|     1 |                   1 |        12 |            12 |            12 | -           |
|     1 |                   2 |         5 |             5 |            12 | -           |
|     1 |                   3 |        16 |             5 |            16 | -           |
|     2 |                   1 |        12 |            12 |            12 | -           |
|     2 |                   2 |         5 |             5 |            12 | -           |
|     2 |                   3 |        16 |             5 |            16 | -           |
|     3 |                   1 |        12 |            12 |            12 | -           |
|     3 |                   2 |         5 |             5 |            12 | -           |
|     3 |                   3 |        16 |             5 |            16 | -           |
|     4 |                   1 |        12 |            12 |            12 | -           |
|     4 |                   2 |         5 |             5 |            12 | -           |
|     4 |                   3 |        16 |             5 |            16 | -           |
|     5 |                   1 |        12 |            12 |            12 | -           |
|     5 |                   2 |         5 |             5 |            12 | 2           |
|     5 |                   3 |        16 |             5 |            16 | -           |
|     6 |                   1 |        12 |            12 |            12 | -           |
|     6 |                   2 |         5 |             5 |            12 | -           |
|     6 |                   3 |        16 |             5 |            16 | -           |

This table can get very long.

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.

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:

+------------------+
| unwalked_bar     |
+------------------+
| ----2------1---3 |
+------------------+

(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!

+---------+---------------------------------------------------------------+
| y_scale | chart                                                         |
+---------+---------------------------------------------------------------+
| 4       | --------------------------------*--*------------------------- |
| 3       | -----------------------------****-*****------------------*--- |
| 3       | -----------------------------**********-----------------**-*- |
| 3       | ----------*------------------**********--------------*****-** |
| 3       | ------*--**-----------------************-*-----------******** |
| 3       | ------*******---------------****************--------********* |
| 3       | ------*******---------------******************------********* |
| 3       | ------*********-------------******************------********* |
| 3       | -----************--------*-*******************------********* |
| 2       | *----*****************--************************-**-********* |
| 2       | *----******************************************************** |
| 2       | *----******************************************************** |
| 2       | **--********************************************************* |
| 2       | ************************************************************* |
| 2       | ************************************************************* |
| 2       | ************************************************************* |
| 2       | *****************************#******************************* |
| 1       | *****************************##########*****************##*#* |
| 1       | ******#***#*****************###########**************######## |
| 1       | ******#######***************###############*********######### |
| 1       | #*****########**************##################******######### |
| 1       | #****##########*#*******##*###################******######### |
| 1       | #***######################################################### |
| 1       | #***######################################################### |
| 1       | ############################################################# |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 2009-10-22 20:00:00                       2009-10-25 08:00:00 |
|         |     # slow queries percent                                    |
|         |     * select scan percent                                     |
+---------+---------------------------------------------------------------

Next we look at the number of SELECT, INSERT, UPDATE, DELETE queries per sec - on a per hour basis.

+---------+---------------------------------------------------------------+
| y_scale | chart                                                         |
+---------+---------------------------------------------------------------+
| 135     | --##--------------------------------------------------------- |
| 130     | -###--------------------------------------------------------- |
| 125     | -###----------------------##--------------------------------- |
| 120     | -###-------------------#####--------------------------------- |
| 115     | -###-----------------#######--------------------------------- |
| 110     | -###----------------########----------------------##--------- |
| 104     | -###--------------##########--------------------####--------- |
| 99      | ####*--------#--############------------------######--------- |
| 94      | ####*--------#-#############---------------#########--------- |
| 89      | #####-----##################------------############--------# |
| 84      | ############################-----------#############--------# |
| 79      | ####%########################---####################---###### |
| 74      | ####%######################################################## |
| 69      | ####%*####################################################### |
| 64      | ####%*####################################################### |
| 59      | ##*#%*###############*####################################### |
| 54      | #**#%*#####*#*****###*****##*###########*#####*#####*#####*## |
| 49      | #***%%*#*********************##########****#*********#**##*** |
| 44      | #***%%*************************##**************************** |
| 39      | ****%%******************************************************* |
| 33      | ****%%**********************************************%******** |
| 28      | *%%*%%**********%******%%*%*%*******%***************%******** |
| 23      | %%%%o%**%**%%o%%%%%%%%%%%%%%o******%%*%%%%%%%%%%%%%%%%*****%% |
| 18      | %oooooooo%%oooooooooooooooooo%%oo%oooooooooooooooooooo%o%%ooo |
| 13      | ooooooooo%%oooooooooooooooooo%%%%%%ooooooooooooooooooo%o%%ooo |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 2009-10-22 20:00:00                       2009-10-25 08:00:00 |
|         |     # com_select per second                                   |
|         |     * com_insert per second                                   |
|         |     o com_update per second                                   |
|         |     % com_delete per second                                   |
+---------+---------------------------------------------------------------+

Here's the code to do that.

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).

I recommend using the following pager if you're on unix-like OS:

pager awk '{sub(/..$/, "")} {sub(/[ ]+$/, "")} !/^[+][-]/ && NR > 3 { print }'

The following chart is similar to the above, but now utilizes colors. It presents the number of popular DML queries per second, on an hourly basis.

Colored multi area chart

Again, comparing slow queries with full table scans:

Slow queries and full scans overlay

Slow queries and full scans overlay

Comparing number of created temporary tables with number of created disk temporary tables:

temp tables vs disk temp tables

temp tables vs disk temp tables

And, once again, the number of popular DML statements, zoomed in.

color_multi_line_chart_dml

Here's the code for colored charts.

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.

More on this in future posts.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

4 Comments to "SQL multi line chart"

  1. mysql wrote:

    Nive work! SQL charting in text mode is:
    a) cool
    b) sometimes useful
    c) slow for big data sets

  2. cscetbon wrote:

    How much time it took to do it !!! really impressive.

  3. hahmael wrote:

    OMG!, GRandioso, ingenioso!! :-)

  4. Three wishes for a new year | code.openark.org wrote:

    [...] mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]. But it makes for a poor substitution to Window Functions, and [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org