Auto scaling, scaled SQL graphs concluded

I wasn’t sure I was to go this far. After catching breath the following have been added to Generic, auto scaling, scaled SQL graphs, and these will conclude my current hacks:

  • Displaying X-axis min/max values.
  • Support for Y-axis values precision.
  • Support for pre-defined scale range.

The addition of the above makes for presentable, usable graphs. See also sample graphs at the end of this post.

Step 8: adding X-axis values

I add minimum/maximum X-scale values to the graph. What was just ordering_column before, now turns to be the x in the y = f(x) function.

SELECT
  y_scale,
  horizontal_bar
  FROM
  (
  SELECT
    @row_number := @row_number+1,
    CASE @row_number
      WHEN 1 THEN ROUND(max_value)
      WHEN (@graph_rows+1)/2 THEN ROUND((max_value+min_value)/2)
      WHEN @graph_rows THEN ROUND(min_value)
      ELSE '~'
    END AS y_scale,
    horizontal_bar,
    @bar_length := IFNULL(@bar_length, CHAR_LENGTH(horizontal_bar))
  FROM
    (SELECT @row_number := 0) AS select_row
    INNER JOIN
    (
    SELECT
      min_value,
      max_value,
      value_column,
      GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar
    FROM
      (SELECT @row_number := 0) AS select_row,
      tinyint_asc
    INNER JOIN (
      SELECT
        ordering_column,
        @min_value AS min_value,
        @max_value AS max_value,
        value_column,
        @scaled_value := CONVERT((value_column-@min_value)*(@graph_rows-1)/(@max_value-@min_value), UNSIGNED) AS scaled_value,
        CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',(@graph_rows-1)-@scaled_value)) AS graph_bar
      FROM
        (
        SELECT
          @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,
          @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,
          @min_range := LEAST(IFNULL(@min_range, ordering_column), ordering_column) AS min_range,
          @max_range := GREATEST(IFNULL(@max_range, ordering_column), ordering_column) AS max_range,
          ordering_column,
          value_column
        FROM
          (
            SELECT id AS ordering_column, val AS value_column
            FROM sample_values LIMIT 100
          ) AS value_select,
          (SELECT @min_value := NULL) AS select_min,
          (SELECT @max_value := NULL) AS select_max,
          (SELECT @min_range := NULL) AS select_min_range,
          (SELECT @max_range := NULL) AS select_max_range,
          (SELECT @bar_length := NULL) AS select_bar_length,
          (SELECT @graph_rows := 15) AS select_graph_rows
        ) AS select_range
      ) AS select_vertical
    WHERE
      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_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(':', @bar_length-2), 'v')
UNION ALL
SELECT '', CONCAT(@min_range, REPEAT(' ', @bar_length-CHAR_LENGTH(@min_range)-CHAR_LENGTH(@max_range)), @max_range)
;
+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 2       | ------------########-------------------------------------------------------########----------------- |
| ~       | ----------##--------###-------------------------------------------------###--------###-------------- |
| ~       | -------###-------------##---------------------------------------------##--------------##------------ |
| ~       | ------#------------------##------------------------------------------#------------------##---------- |
| ~       | ----##---------------------#---------------------------------------##---------------------#--------- |
| ~       | ---#------------------------##-----------------------------------##------------------------##------- |
| ~       | -##---------------------------#---------------------------------#----------------------------#------ |
| 1       | #------------------------------##------------------------------#------------------------------#----- |
| ~       | ---------------------------------#---------------------------##--------------------------------##--- |
| ~       | ----------------------------------##------------------------#------------------------------------#-- |
| ~       | ------------------------------------#---------------------##--------------------------------------## |
| ~       | -------------------------------------##-----------------##------------------------------------------ |
| ~       | ---------------------------------------##-------------##-------------------------------------------- |
| ~       | -----------------------------------------###-------###---------------------------------------------- |
| 0       | --------------------------------------------#######------------------------------------------------- |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                                                 99 |
+---------+------------------------------------------------------------------------------------------------------+

The X-axis range is [0..99], displayed below. The code does not limit us to INTEGER. We may as well have FLOAT, DATE, TIMESTAMP etc. (examples follow).

Step 9: adding precision

The 0,1,2 Y-axis values play nicely here. But if my generic query is:

SELECT id AS ordering_column, val/3 AS value_column FROM sample_values LIMIT 100

The resulting graph looks like:

+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 1       | ------------#########------------------------------------------------------#########---------------- |
| ~       | --------####---------###-----------------------------------------------####---------###------------- |
| ~       | ------##----------------###------------------------------------------##----------------###---------- |
| ~       | ----##---------------------##-------------------------------------###---------------------##-------- |
| ~       | -###-------------------------##---------------------------------##--------------------------##------ |
| 0       | #------------------------------##-----------------------------##------------------------------##---- |
| ~       | ---------------------------------##-------------------------##----------------------------------##-- |
| ~       | -----------------------------------##---------------------##--------------------------------------## |
| ~       | -------------------------------------###----------------##------------------------------------------ |
| ~       | ----------------------------------------###---------####-------------------------------------------- |
| 0       | -------------------------------------------#########------------------------------------------------ |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                                                 99 |
+---------+------------------------------------------------------------------------------------------------------+

And the 0,0,1 values are completely inappropriate. We will now introduce precision:

SELECT
  y_scale,
  horizontal_bar
  FROM
  (
  SELECT
    @row_number := @row_number+1,
    CASE @row_number
      WHEN 1 THEN ROUND(max_value * POW(10, @value_precision))/POW(10, @value_precision)
      WHEN (@graph_rows+1)/2 THEN ROUND((max_value+min_value)*POW(10, @value_precision)/2)/POW(10, @value_precision)
      WHEN @graph_rows THEN ROUND(min_value*POW(10, @value_precision))/POW(10, @value_precision)
      ELSE '~'
    END AS y_scale,
    horizontal_bar,
    @bar_length := IFNULL(@bar_length, CHAR_LENGTH(horizontal_bar))
  FROM
    (SELECT @row_number := 0) AS select_row
    INNER JOIN
    (
    SELECT
      min_value,
      max_value,
      value_column,
      GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar
    FROM
      (SELECT @row_number := 0) AS select_row,
      tinyint_asc
    INNER JOIN (
      SELECT
        ordering_column,
        @min_value AS min_value,
        @max_value AS max_value,
        value_column,
        @scaled_value := CONVERT((value_column-@min_value)*(@graph_rows-1)/(@max_value-@min_value), UNSIGNED) AS scaled_value,
        CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',(@graph_rows-1)-@scaled_value)) AS graph_bar
      FROM
        (
        SELECT
          @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,
          @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,
          @min_range := LEAST(IFNULL(@min_range, ordering_column), ordering_column) AS min_range,
          @max_range := GREATEST(IFNULL(@max_range, ordering_column), ordering_column) AS max_range,
          ordering_column,
          value_column
        FROM
          (
            SELECT id AS ordering_column, val/3 AS value_column
            FROM sample_values LIMIT 100
          ) AS value_select,
          (SELECT @min_value := NULL) AS select_min,
          (SELECT @max_value := NULL) AS select_max,
          (SELECT @min_range := NULL) AS select_min_range,
          (SELECT @max_range := NULL) AS select_max_range,
          (SELECT @bar_length := NULL) AS select_bar_length,
          (SELECT @value_precision := 2) AS select_value_precision,
          (SELECT @graph_rows := 15) AS select_graph_rows
        ) AS select_range
      ) AS select_vertical
    WHERE
      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_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(':', @bar_length-2), 'v')
UNION ALL
SELECT '', CONCAT(@min_range, REPEAT(' ', @bar_length-CHAR_LENGTH(@min_range)-CHAR_LENGTH(@max_range)), @max_range)
;
+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 0.67    | ------------########-------------------------------------------------------########----------------- |
| ~       | ----------##--------###-------------------------------------------------###--------###-------------- |
| ~       | -------###-------------##---------------------------------------------##--------------##------------ |
| ~       | ------#------------------##------------------------------------------#------------------##---------- |
| ~       | ----##---------------------#---------------------------------------##---------------------#--------- |
| ~       | ---#------------------------##-----------------------------------##------------------------##------- |
| ~       | -##---------------------------#---------------------------------#----------------------------#------ |
| 0.33    | #------------------------------##------------------------------#------------------------------#----- |
| ~       | ---------------------------------#---------------------------##--------------------------------##--- |
| ~       | ----------------------------------##------------------------#------------------------------------#-- |
| ~       | ------------------------------------#---------------------##--------------------------------------## |
| ~       | -------------------------------------##-----------------##------------------------------------------ |
| ~       | ---------------------------------------##-------------##-------------------------------------------- |
| ~       | -----------------------------------------###-------###---------------------------------------------- |
| 0       | --------------------------------------------#######------------------------------------------------- |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                                                 99 |
+---------+------------------------------------------------------------------------------------------------------+

Watch the 0.33, 0.67 values generated here. This is much better.

Step 10: setting fixed scale range

When we compare two graphs, it’s best if they are scaled the same way. So far, our graphs have scaled automatically according to min/max values. What we add now is a way to predefine the range. For example, dealing with per-cent values, I may always wish to scale [0..100], regardless of min/max values.

The code below defines a scale of [-1, 1]. It automatically extends if the range is actually insufficient, so it’s safe to use. Let’s see the same last graph again:

SELECT
  y_scale,
  horizontal_bar
  FROM
  (
  SELECT
    @row_number := @row_number+1,
    CASE @row_number
      WHEN 1 THEN ROUND(max_value * POW(10, @value_precision))/POW(10, @value_precision)
      WHEN (@graph_rows+1)/2 THEN ROUND((max_value+min_value)*POW(10, @value_precision)/2)/POW(10, @value_precision)
      WHEN @graph_rows THEN ROUND(min_value*POW(10, @value_precision))/POW(10, @value_precision)
      ELSE '~'
    END AS y_scale,
    horizontal_bar,
    @bar_length := IFNULL(@bar_length, CHAR_LENGTH(horizontal_bar))
  FROM
    (SELECT @row_number := 0) AS select_row
    INNER JOIN
    (
    SELECT
      min_value,
      max_value,
      value_column,
      GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar
    FROM
      (SELECT @row_number := 0) AS select_row,
      tinyint_asc
    INNER JOIN (
      SELECT
        ordering_column,
        @min_value AS min_value,
        @max_value AS max_value,
        value_column,
        @scaled_value := CONVERT((value_column-@min_value)*(@graph_rows-1)/(@max_value-@min_value), UNSIGNED) AS scaled_value,
        CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',(@graph_rows-1)-@scaled_value)) AS graph_bar
      FROM
        (
        SELECT
          @min_value := LEAST(IFNULL(@min_scale_value, value_column), IFNULL(@min_value, value_column), value_column) AS min_value,
          @max_value := GREATEST(IFNULL(@max_scale_value, value_column), IFNULL(@max_value, value_column), value_column) AS max_value,
          @min_range := LEAST(IFNULL(@min_range, ordering_column), ordering_column) AS min_range,
          @max_range := GREATEST(IFNULL(@max_range, ordering_column), ordering_column) AS max_range,
          ordering_column,
          value_column
        FROM
          (
            SELECT id AS ordering_column, val/3 AS value_column FROM sample_values LIMIT 100
          ) AS value_select,
          (SELECT @min_value := NULL) AS select_min,
          (SELECT @max_value := NULL) AS select_max,
          (SELECT @min_range := NULL) AS select_min_range,
          (SELECT @max_range := NULL) AS select_max_range,
          (SELECT @bar_length := NULL) AS select_bar_length,
          (SELECT @min_scale_value := -1) AS select_min_scale,
          (SELECT @max_scale_value := 1) AS select_max_scale,
          (SELECT @value_precision := 2) AS select_value_precision,
          (SELECT @graph_rows := 15) AS select_graph_rows
        ) AS select_range
      ) AS select_vertical
    WHERE
      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_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(':', @bar_length-2), 'v')
UNION ALL
SELECT '', CONCAT(@min_range, REPEAT(' ', @bar_length-CHAR_LENGTH(@min_range)-CHAR_LENGTH(@max_range)), @max_range)
;
+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 1       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ------------########-------------------------------------------------------########----------------- |
| ~       | ------######--------#######------------------------------------------######--------#######---------- |
| ~       | -#####---------------------####---------------------------------#####---------------------####------ |
| ~       | #------------------------------#####------------------------####------------------------------####-- |
| ~       | ------------------------------------#####-------------######--------------------------------------## |
| 0       | -----------------------------------------#############---------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| ~       | ---------------------------------------------------------------------------------------------------- |
| -1      | ---------------------------------------------------------------------------------------------------- |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                                                 99 |
+---------+------------------------------------------------------------------------------------------------------+

Values are still in range [0..0.667], but are presented on a wider scale.

Showcase

Here are some nice graphs generated, to show off.

+---------+----------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                   |
+---------+----------------------------------------------------------------------------------+
| 14.17   | ----------------------------------------------------------------------###------- |
| ~       | ---------------------------------------------------------------------#---#------ |
| ~       | --------------------------------------------------------------------#-----#----- |
| ~       | -------------------------------------------------------##----------------------- |
| ~       | -----------------------------------------------------##--#---------#-------#---- |
| ~       | ----------------------------------------------------#-----#--------------------- |
| ~       | ---------------------------------------###-----------------#------#------------- |
| 7.09    | -------------------------------------##---#--------#------------------------#--- |
| ~       | ------------------------------------#------#----------------#------------------- |
| ~       | -----------------------####--------#--------#-----#--------------#-----------#-- |
| ~       | ---------------------##----#-----------------#---#-----------#------------------ |
| ~       | --------------------#-------##----#-----------------------------#--------------- |
| ~       | --------#####-----##----------#--#------------#-#-------------#---------------#- |
| ~       | ----####-----##--#-------------##----------------------------------------------# |
| 0       | ####-----------##------------------------------#---------------#---------------- |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 2009-07-25 21:43:27                                          2009-07-29 06:02:27 |
+---------+----------------------------------------------------------------------------------+
+---------+---------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                  |
+---------+---------------------------------------------------------------------------------+
| 0.99    | ##----------------------------------------------------------------------------- |
| ~       | --##--------------------------------------------------------------------------- |
| ~       | ----#-------------------------------------------------------------------------- |
| ~       | -----##------------------------------------------------------------------------ |
| ~       | -------#----------------------------------------------------------------------- |
| ~       | --------#---------------------------------------------------------------------- |
| ~       | ---------#--------------------------------------------------------------------- |
| 0.39    | ----------#-------------------------------------------------------------------- |
| ~       | -----------#------------------------------------------------------------------- |
| ~       | ------------#------------------------------------------------------------------ |
| ~       | -------------#--------------------########------------------------------------- |
| ~       | --------------#----------------###--------#####---------------################- |
| ~       | ---------------##-----------###----------------#####----######----------------# |
| ~       | -----------------##------###------------------------####----------------------- |
| -0.22   | -------------------######------------------------------------------------------ |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 2009-07-25 21:43:27                                         2009-07-29 06:02:27 |
+---------+---------------------------------------------------------------------------------+
+---------+---------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                  |
+---------+---------------------------------------------------------------------------------+
| -0      | --------------------######################------------------------------------- |
| ~       | ------------########----------------------#######--------------------------#### |
| ~       | --------####-------------------------------------#####-----------------####---- |
| ~       | ------##----------------------------------------------##------------###-------- |
| ~       | ----##--------------------------------------------------##---------#----------- |
| ~       | ---#------------------------------------------------------#------##------------ |
| ~       | --#--------------------------------------------------------#------------------- |
| -2.39   | -#----------------------------------------------------------#---#-------------- |
| ~       | ---------------------------------------------------------------#--------------- |
| ~       | #------------------------------------------------------------#----------------- |
| ~       | ------------------------------------------------------------------------------- |
| ~       | ------------------------------------------------------------------------------- |
| ~       | ------------------------------------------------------------------------------- |
| ~       | ------------------------------------------------------------------------------- |
| -4.78   | --------------------------------------------------------------#---------------- |
|         | v:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 0                                                                          3.95 |
+---------+---------------------------------------------------------------------------------+
+---------+----------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                   |
+---------+----------------------------------------------------------------------------------+
| 110.82  | ---------------------------------------------------oooooo----------------------- |
| ~       | ------------------------------------------------ooooooooooo--------------------- |
| ~       | ----------------------------------------------oooooooooooooo-------------------- |
| ~       | --------------------------------------------oooooooooooooooooo------------------ |
| ~       | ----------------------------------oooo----ooooooooooooooooooooo----------------- |
| ~       | o------------------------------ooooooooooooooooooooooooooooooooo---------------- |
| ~       | o----------------------------ooooooooooooooooooooooooooooooooooo---------------- |
| ~       | oo--------------------------ooooooooooooooooooooooooooooooooooooo--------------- |
| ~       | oo-------------------------ooooooooooooooooooooooooooooooooooooooo-------------- |
| ~       | ooo-----------------------ooooooooooooooooooooooooooooooooooooooooo------------- |
| ~       | oooo--------------------oooooooooooooooooooooooooooooooooooooooooooo------------ |
| ~       | oooo-------------------ooooooooooooooooooooooooooooooooooooooooooooo------------ |
| 85.93   | ooooo-----------------ooooooooooooooooooooooooooooooooooooooooooooooo----------- |
| ~       | oooooo---------------ooooooooooooooooooooooooooooooooooooooooooooooooo---------- |
| ~       | ooooooo-------------oooooooooooooooooooooooooooooooooooooooooooooooooo---------- |
| ~       | oooooooo-----------oooooooooooooooooooooooooooooooooooooooooooooooooooo--------- |
| ~       | ooooooooo--------ooooooooooooooooooooooooooooooooooooooooooooooooooooooo-------- |
| ~       | ooooooooooo----oooooooooooooooooooooooooooooooooooooooooooooooooooooooooo------- |
| ~       | ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo------- |
| ~       | oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo------ |
| ~       | ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo----- |
| ~       | oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo---- |
| ~       | ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo--- |
| ~       | oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo-- |
| 61.04   | oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo |
|         | v::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::v |
|         | 93                                                                           172 |
+---------+----------------------------------------------------------------------------------+

Why?

Why go for all this trouble? Isn’t this nothing but a cool hack?

Is this stuff useful? Practical?

I have some agenda with this, which I’ll be happy to share when comes into being. I think there are very practical uses for SQL-based graphs. I’m sure many readers can think of interesting uses, other than the ones I have in mind.

Conclusion

There are many more possibilities and featured which can be built into the graphs. I’m going to stop here, before this becomes too picky. I hope I have shown that some very nice graphs can be produced with SQL alone.

8 thoughts on “Auto scaling, scaled SQL graphs concluded

  1. You have gone too far, Shlomi…. TOO FAR.

    Very cool. I’ve done plenty of SQL based graphs, but never as cool or complex as yours. 🙂

    Regards,

    Jeremy

  2. @Jeremy Z. – thanks, I’m glad you like it!

    @Jeremy C. – Yes, I agree. I did not include the parts where I do some nice styling to the graph. I did catch myself in time to slap myself and go do something more useful 😀

    @Andrew – thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.