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.
Now combine this with a SQL-REST interface … who needs google-charts anymore ?