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.
That is both crazy and awesome. I like it. 🙂
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
This is brilliant
@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!