{"id":1009,"date":"2009-07-27T12:17:16","date_gmt":"2009-07-27T10:17:16","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1009"},"modified":"2009-07-28T15:08:13","modified_gmt":"2009-07-28T13:08:13","slug":"generic-auto-scaling-scaled-sql-graphs","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/generic-auto-scaling-scaled-sql-graphs","title":{"rendered":"Generic, auto scaling, scaled SQL graphs"},"content":{"rendered":"<p>In <a href=\"http:\/\/code.openark.org\/blog\/mysql\/rotating-sql-graphs-horizontally\">Rotating SQL graphs horizontally<\/a>, I have shown how to rotate an ASCII SQL graph into horizontal position.<\/p>\n<p>I was dissatisfied with some parts of the solution, which I will show now how to fix:<\/p>\n<ul>\n<li>I had to manually scale the graph values so as to fit nicely into screen.<\/li>\n<li>I had to rely on hard coded scaling schemes.<\/li>\n<li>I had to rely on hard coded column names.<\/li>\n<li>I had no y-axis legend.<\/li>\n<\/ul>\n<p>I will now present an SQL query which allows for <em>pluggable queries<\/em>, which creates self, <em>auto scaling graphs<\/em>, along with <em>y-axis scales<\/em>.<\/p>\n<p>Using deeply nested subqueries, we will evolve a simple SELECT query into an elaborate graph. I will present the many steps required, followed by explanations and sample results. But in the end &#8211; the steps are unimportant. I&#8217;ll present a <em>generic query<\/em>, into which your own SELECT can be embedded, and which will provide you with the graph.<\/p>\n<p>We&#8217;ll use the same example, found in\u00a0<a href=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/07\/graph2.sql\">graph.sql<\/a>.<\/p>\n<h4><!--more-->Recap<\/h4>\n<p>Following is the table data we want to use. Values represent a sinus function.<\/p>\n<blockquote>\n<pre>SELECT * FROM sample_values;\r\n+-----+----------------------+\r\n| id  | val                  |\r\n+-----+----------------------+\r\n|   0 |                    1 |\r\n|   1 |     1.09983341664683 |\r\n|   2 |     1.19866933079506 |\r\n|   3 |     1.29552020666134 |\r\n|   4 |     1.38941834230865 |\r\n|   5 |      1.4794255386042 |\r\n|   6 |     1.56464247339504 |\r\n|   7 |     1.64421768723769 |\r\n|   8 |     1.71735609089952 |\r\n|   9 |     1.78332690962748 |\r\n...\r\n| 246 |    0.492103409609378 |\r\n| 247 |    0.580639083926769 |\r\n| 248 |    0.673364873895278 |\r\n| 249 |    0.769354294072604 |\r\n| 250 |    0.867648249902227 |\r\n| 251 |    0.967264620669155 |\r\n| 252 |     1.06720807252547 |\r\n| 253 |     1.16648000353716 |\r\n| 254 |     1.26408852138447 |\r\n| 255 |     1.35905835402217 |\r\n+-----+----------------------+\r\n256 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n<h4>Step 1: Generic query<\/h4>\n<p>The only requirements from your query is for it to be generic. It need to provide tow columns:<\/p>\n<ul>\n<li><strong>ordering_column<\/strong>: A column which orders the values, e.g. an <strong>AUTO_INCREMENT<\/strong>, some <strong>TIMESTAMP<\/strong> etc.<\/li>\n<li><strong>value_column<\/strong>: The values themselves.<\/li>\n<\/ul>\n<p>In the above example, the generic query will be:<\/p>\n<blockquote>\n<pre>SELECT id AS <strong>ordering_column<\/strong>, val AS <strong>value_column<\/strong> FROM sample_values LIMIT 100<\/pre>\n<\/blockquote>\n<p>From this point on, <em>everything else<\/em> is self-computed. The above is the only thing that needs to change if you wish to provide your own graphs.<\/p>\n<h4>Problem: hard coded scaling<\/h4>\n<p>In the following example I generate a vertical graph, but I need to hard-code the scaling:<\/p>\n<blockquote>\n<pre>SELECT\r\n  ordering_column,\r\n  CONCAT(REPEAT('-',(value_column*10+1)-1),'#',REPEAT('-',22-(value_column*10+1))) AS graph_bar\r\nFROM\r\n  (\r\n  SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n  ) AS value_select\r\n;\r\n+-----------------+------------------------+\r\n| ordering_column | graph_bar              |\r\n+-----------------+------------------------+\r\n|               0 | ----------#----------- |\r\n|               1 | -----------#---------- |\r\n|               2 | ------------#--------- |\r\n|               3 | -------------#-------- |\r\n|               4 | --------------#------- |\r\n|               5 | ---------------#------ |\r\n|               6 | ----------------#----- |\r\n|               7 | ----------------#----- |\r\n|               8 | -----------------#---- |\r\n|               9 | ------------------#--- |\r\n|              10 | ------------------#--- |\r\n|              11 | -------------------#-- |\r\n|              12 | -------------------#-- |\r\n|              13 | --------------------#- |\r\n|              14 | --------------------#- |\r\n|              15 | --------------------#- |\r\n|              16 | --------------------#- |<\/pre>\n<\/blockquote>\n<h4>Step 2: towards auto-scaling<\/h4>\n<p>In this step we will find the minimum\/maximum values for <strong>value_column<\/strong>. We will as yet do nothing with these.<\/p>\n<blockquote>\n<pre>SELECT\r\n  <strong>@min_value := LEAST(@min_value, value_column) AS min_value<\/strong>,\r\n  <strong>@max_value := GREATEST(@max_value, value_column) AS max_value<\/strong>,\r\n  ordering_column,\r\n  CONCAT(REPEAT('-',(value_column*10+1)-1),'#',REPEAT('-',22-(value_column*10+1))) AS graph_bar\r\nFROM\r\n  (\r\n  SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n  ) AS value_select,\r\n  <strong>(SELECT @min_value := 1000000) AS select_min<\/strong>,\r\n  <strong>(SELECT @max_value := -1000000) AS select_max<\/strong>\r\n;\r\n+---------------------+-----------------+-----------------+------------------------+\r\n| min_value           | max_value       | ordering_column | graph_bar              |\r\n+---------------------+-----------------+-----------------+------------------------+\r\n|                   1 |               1 |               0 | ----------#----------- |\r\n|                   1 | 1.0998334166468 |               1 | -----------#---------- |\r\n|                   1 | 1.1986693307951 |               2 | ------------#--------- |\r\n|                   1 | 1.2955202066613 |               3 | -------------#-------- |\r\n|                   1 | 1.3894183423086 |               4 | --------------#------- |\r\n|                   1 | 1.4794255386042 |               5 | ---------------#------ |\r\n|                   1 |  1.564642473395 |               6 | ----------------#----- |\r\n|                   1 | 1.6442176872377 |               7 | ----------------#----- |\r\n|                   1 | 1.7173560908995 |               8 | -----------------#---- |\r\n|                   1 | 1.7833269096275 |               9 | ------------------#--- |\r\n|                   1 | 1.8414709848079 |              10 | ------------------#--- |\r\n|                   1 | 1.8912073600614 |              11 | -------------------#-- |\r\n|                   1 | 1.9320390859672 |              12 | -------------------#-- |\r\n|                   1 | 1.9635581854172 |              13 | --------------------#- |\r\n|                   1 | 1.9854497299885 |              14 | --------------------#- |\r\n|                   1 |  1.997494986604 |              15 | --------------------#- |\r\n|                   1 | 1.9995736030415 |              16 | --------------------#- |\r\n|                   1 | 1.9995736030415 |              17 | --------------------#- |\r\n|                   1 | 1.9995736030415 |              18 | --------------------#- |\r\n|                   1 | 1.9995736030415 |              19 | -------------------#-- |\r\n|                   1 | 1.9995736030415 |              20 | -------------------#-- |\r\n|                   1 | 1.9995736030415 |              21 | -------------------#-- |\r\n|                   1 | 1.9995736030415 |              22 | ------------------#--- |\r\n|                   1 | 1.9995736030415 |              23 | -----------------#---- |\r\n|                   1 | 1.9995736030415 |              24 | -----------------#---- |\r\n|                   1 | 1.9995736030415 |              25 | ----------------#----- |\r\n|                   1 | 1.9995736030415 |              26 | ---------------#------ |\r\n|                   1 | 1.9995736030415 |              27 | --------------#------- |\r\n|                   1 | 1.9995736030415 |              28 | -------------#-------- |\r\n|                   1 | 1.9995736030415 |              29 | ------------#--------- |\r\n|                   1 | 1.9995736030415 |              30 | -----------#---------- |\r\n|                   1 | 1.9995736030415 |              31 | ----------#----------- |\r\n|    0.94162585657242 | 1.9995736030415 |              32 | ---------#------------ |\r\n|    0.84225430585675 | 1.9995736030415 |              33 | --------#------------- |\r\n|    0.74445889797317 | 1.9995736030415 |              34 | -------#-------------- |\r\n...<\/pre>\n<\/blockquote>\n<p>I&#8217;m using ugly values for initial min\/max comparison (-100000, 1000000). I&#8217;ll soon get rid of them, don&#8217;t worry!<\/p>\n<h4>Step 3: formalizing min\/max values<\/h4>\n<p>Using another sub-query, we will isolate the minimum\/maximum values, and forget about the graph for the moment.<\/p>\n<blockquote>\n<pre>SELECT\r\n  <strong>@min_value<\/strong>,\r\n  <strong>@max_value<\/strong>,\r\n  ordering_column,\r\n  value_column\r\nFROM\r\n  (\r\n  SELECT\r\n    @min_value := LEAST(@min_value, value_column) AS min_value,\r\n    @max_value := GREATEST(@max_value, value_column) AS max_value,\r\n    ordering_column,\r\n    value_column\r\n  FROM\r\n    (\r\n    SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n    ) AS value_select,\r\n    (SELECT @min_value := 1000000) AS select_min,\r\n    (SELECT @max_value := -1000000) AS select_max\r\n  ) AS select_range\r\n;\r\n+---------------------+-----------------+-----------------+----------------------+\r\n| @min_value          | @max_value      | ordering_column | value_column         |\r\n+---------------------+-----------------+-----------------+----------------------+\r\n| 7.6742435899169e-05 | 1.9995736030415 |               0 |                    1 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               1 |     1.09983341664683 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               2 |     1.19866933079506 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               3 |     1.29552020666134 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               4 |     1.38941834230865 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               5 |      1.4794255386042 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               6 |     1.56464247339504 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               7 |     1.64421768723769 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               8 |     1.71735609089952 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |               9 |     1.78332690962748 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |              10 |      1.8414709848079 |\r\n| 7.6742435899169e-05 | 1.9995736030415 |              11 |     1.89120736006144 |\r\n...<\/pre>\n<\/blockquote>\n<h4>Step 4: scaling the values<\/h4>\n<p>In this step I&#8217;ll do two things:<\/p>\n<ul>\n<li>Get rid of ugly hard coded values for min\/max comparison. I&#8217;ll use the <strong>IFNULL<\/strong> function to check for initial conditions.<\/li>\n<li>Introduce self scaling: I will assume a 21-rows high graph is desired, and will auto-scale the values to fit in. Don&#8217;t worry, I&#8217;ll get rid of those values later on!<\/li>\n<\/ul>\n<blockquote>\n<pre>SELECT\r\n  @min_value AS min_value,\r\n  @max_value AS max_value,\r\n  value_column,\r\n  <strong>@scaled_value := CONVERT((value_column-@min_value)*20\/(@max_value-@min_value), UNSIGNED) AS scaled_value<\/strong>,\r\n  <strong>CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@scaled_value)) AS graph_bar<\/strong>\r\nFROM\r\n  (\r\n  SELECT\r\n    @min_value := LEAST(<strong>IFNULL(@min_value, value_column)<\/strong>, value_column) AS min_value,\r\n    @max_value := GREATEST(<strong>IFNULL(@max_value, value_column)<\/strong>, value_column) AS max_value,\r\n    ordering_column,\r\n    value_column\r\n  FROM\r\n    (\r\n    SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n    ) AS value_select,\r\n    (SELECT @min_value := <strong>NULL<\/strong>) AS select_min,\r\n    (SELECT @max_value := <strong>NULL<\/strong>) AS select_max\r\n  ) AS select_range\r\n;\r\n+---------------------+-----------------+----------------------+--------------+-----------------------+\r\n| min_value           | max_value       | value_column         | scaled_value | graph_bar             |\r\n+---------------------+-----------------+----------------------+--------------+-----------------------+\r\n| 7.6742435899169e-05 | 1.9995736030415 |                    1 |           10 | ----------#---------- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.09983341664683 |           11 | -----------#--------- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.19866933079506 |           12 | ------------#-------- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.29552020666134 |           13 | -------------#------- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.38941834230865 |           14 | --------------#------ |\r\n| 7.6742435899169e-05 | 1.9995736030415 |      1.4794255386042 |           15 | ---------------#----- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.56464247339504 |           16 | ----------------#---- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.64421768723769 |           16 | ----------------#---- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.71735609089952 |           17 | -----------------#--- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.78332690962748 |           18 | ------------------#-- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |      1.8414709848079 |           18 | ------------------#-- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.89120736006144 |           19 | -------------------#- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.93203908596723 |           19 | -------------------#- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.96355818541719 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.98544972998846 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.99749498660405 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |      1.9995736030415 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.99166481045247 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |      1.9738476308782 |           20 | --------------------# |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.94630008768741 |           19 | -------------------#- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.90929742682568 |           19 | -------------------#- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.86320936664887 |           19 | -------------------#- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.80849640381959 |           18 | ------------------#-- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.74570521217672 |           17 | -----------------#--- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.67546318055115 |           17 | -----------------#--- |\r\n| 7.6742435899169e-05 | 1.9995736030415 |     1.59847214410396 |           16 | ----------------#---- |<\/pre>\n<\/blockquote>\n<p>I have now re-introduced the graph. Note that while values are in the range 0..2, the SQL query <em>automatically scales<\/em> to a 1-21 long graph bar.<\/p>\n<h4>Step 5: rotation<\/h4>\n<p>Shall we now present this as a horizontal graph?<\/p>\n<blockquote>\n<pre>SELECT\r\n  <strong>GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS `result`<\/strong>\r\nFROM\r\n  <strong>tinyint_asc<\/strong>\r\n<strong>INNER JOIN<\/strong> (\r\n  SELECT\r\n    ordering_column,\r\n    @min_value AS min_value,\r\n    @max_value AS max_value,\r\n    value_column,\r\n    @scaled_value := CONVERT((value_column-@min_value)*20\/(@max_value-@min_value), UNSIGNED) AS scaled_value,\r\n    CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@scaled_value)) AS graph_bar\r\n  FROM\r\n    (\r\n    SELECT\r\n      @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,\r\n      @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,\r\n      ordering_column,\r\n      value_column\r\n    FROM\r\n      (\r\n      SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n      ) AS value_select,\r\n      (SELECT @min_value := NULL) AS select_min,\r\n      (SELECT @max_value := NULL) AS select_max\r\n    ) AS select_range\r\n  ) AS select_vertical\r\nWHERE\r\n  <strong>tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_bar)<\/strong>\r\n<strong>GROUP BY<\/strong>\r\n  <strong>tinyint_asc.value<\/strong>\r\n<strong>ORDER BY<\/strong>\r\n  <strong>tinyint_asc.value DESC;<\/strong>\r\n;\r\n+------------------------------------------------------------------------------------------------------+\r\n| result                                                                                               |\r\n+------------------------------------------------------------------------------------------------------+\r\n| -------------######---------------------------------------------------------######------------------ |\r\n| -----------##------###---------------------------------------------------###------###--------------- |\r\n| ---------##-----------#-------------------------------------------------#------------#-------------- |\r\n| --------#--------------##---------------------------------------------##--------------##------------ |\r\n| ------##-----------------#-------------------------------------------#------------------#----------- |\r\n| -----#--------------------#-----------------------------------------#--------------------#---------- |\r\n| ----#----------------------#---------------------------------------#----------------------#--------- |\r\n| ---#------------------------#-------------------------------------#------------------------#-------- |\r\n| --#--------------------------#-----------------------------------#--------------------------#------- |\r\n| -#----------------------------#---------------------------------#----------------------------#------ |\r\n| #------------------------------#-------------------------------#------------------------------#----- |\r\n| --------------------------------#-----------------------------#--------------------------------#---- |\r\n| ---------------------------------#---------------------------#----------------------------------#--- |\r\n| ----------------------------------#-------------------------#------------------------------------#-- |\r\n| -----------------------------------##----------------------#--------------------------------------#- |\r\n| -------------------------------------#--------------------#----------------------------------------# |\r\n| --------------------------------------#-----------------##------------------------------------------ |\r\n| ---------------------------------------#---------------#-------------------------------------------- |\r\n| ----------------------------------------##-----------##--------------------------------------------- |\r\n| ------------------------------------------##-------##----------------------------------------------- |\r\n| --------------------------------------------#######------------------------------------------------- |\r\n+------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>We now have a <em>self-scaling<\/em> horizontal graph! Phew!<\/p>\n<p>But I&#8217;m not satisfied yet.<\/p>\n<h4>Step 6: towards Y-axis scaling<\/h4>\n<p>We will now introduce min\/max Y-scale values into the graph. Note the following:<\/p>\n<ul>\n<li>I&#8217;ll be using a counter to count rows.<\/li>\n<li>I&#8217;ll rely on previous knowledge of hard-coded 21 rows value (I promise, this will be fixed soon).<\/li>\n<li>We already have min\/max values. Using the two, I&#8217;ll also provide the mid-value.<\/li>\n<li>I chose to use <strong>ROUND()<\/strong>. This may not be the best idea when your values are very small fractions. Remove the<strong> ROUND()<\/strong> if it does not fit in with your values.<\/li>\n<\/ul>\n<blockquote>\n<pre>SELECT\r\n  <strong>@row_number := @row_number+1,<\/strong>\r\n  <strong>CASE @row_number<\/strong>\r\n    <strong>WHEN 1  THEN ROUND(max_value)<\/strong>\r\n    <strong>WHEN 11 THEN ROUND((max_value+min_value)\/2)<\/strong>\r\n    <strong>WHEN 21 THEN ROUND(min_value)<\/strong>\r\n    <strong>ELSE ''<\/strong>\r\n  <strong>END AS y_scale<\/strong>,\r\n  horizontal_bar\r\nFROM\r\n  <strong>(SELECT @row_number := 0) AS select_row<\/strong>\r\n  INNER JOIN\r\n  (\r\n  SELECT\r\n    min_value,\r\n    max_value,\r\n    value_column,\r\n    GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar\r\n  FROM\r\n    (SELECT @row_number := 0) AS select_row,\r\n    tinyint_asc\r\n  INNER JOIN (\r\n    SELECT\r\n      ordering_column,\r\n      @min_value AS min_value,\r\n      @max_value AS max_value,\r\n      value_column,\r\n      @scaled_value := CONVERT((value_column-@min_value)*20\/(@max_value-@min_value), UNSIGNED) AS scaled_value,\r\n      CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@scaled_value)) AS graph_bar\r\n    FROM\r\n      (\r\n      SELECT\r\n        @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,\r\n        @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,\r\n        ordering_column,\r\n        value_column\r\n      FROM\r\n        (\r\n        SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100\r\n        ) AS value_select,\r\n        (SELECT @min_value := NULL) AS select_min,\r\n        (SELECT @max_value := NULL) AS select_max\r\n      ) AS select_range\r\n    ) AS select_vertical\r\n  WHERE\r\n    tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_bar)\r\n  GROUP BY\r\n    tinyint_asc.value\r\n  ORDER BY\r\n    tinyint_asc.value DESC\r\n  ) AS select_horizontal\r\n;+------------------------------+---------+------------------------------------------------------------------------------------------------------+\r\n| @row_number := @row_number+1 | y_scale | horizontal_bar                                                                                       |\r\n+------------------------------+---------+------------------------------------------------------------------------------------------------------+\r\n|                            1 | 2       | -------------######---------------------------------------------------------######------------------ |\r\n|                            2 |         | -----------##------###---------------------------------------------------###------###--------------- |\r\n|                            3 |         | ---------##-----------#-------------------------------------------------#------------#-------------- |\r\n|                            4 |         | --------#--------------##---------------------------------------------##--------------##------------ |\r\n|                            5 |         | ------##-----------------#-------------------------------------------#------------------#----------- |\r\n|                            6 |         | -----#--------------------#-----------------------------------------#--------------------#---------- |\r\n|                            7 |         | ----#----------------------#---------------------------------------#----------------------#--------- |\r\n|                            8 |         | ---#------------------------#-------------------------------------#------------------------#-------- |\r\n|                            9 |         | --#--------------------------#-----------------------------------#--------------------------#------- |\r\n|                           10 |         | -#----------------------------#---------------------------------#----------------------------#------ |\r\n|                           11 | 1       | #------------------------------#-------------------------------#------------------------------#----- |\r\n|                           12 |         | --------------------------------#-----------------------------#--------------------------------#---- |\r\n|                           13 |         | ---------------------------------#---------------------------#----------------------------------#--- |\r\n|                           14 |         | ----------------------------------#-------------------------#------------------------------------#-- |\r\n|                           15 |         | -----------------------------------##----------------------#--------------------------------------#- |\r\n|                           16 |         | -------------------------------------#--------------------#----------------------------------------# |\r\n|                           17 |         | --------------------------------------#-----------------##------------------------------------------ |\r\n|                           18 |         | ---------------------------------------#---------------#-------------------------------------------- |\r\n|                           19 |         | ----------------------------------------##-----------##--------------------------------------------- |\r\n|                           20 |         | ------------------------------------------##-------##----------------------------------------------- |\r\n|                           21 | 0       | --------------------------------------------#######------------------------------------------------- |\r\n+------------------------------+---------+------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>Well, now we&#8217;re getting somewhere!<\/p>\n<h4>Step 7: cleaning up<\/h4>\n<p>In this step I&#8217;ll do the following:<\/p>\n<ul>\n<li>Using yet another subquery (how many do we have already?), I&#8217;ll get rid of the counter column.<\/li>\n<li>I&#8217;ll remove the 21-rows hard coding. There will only be one session variable set to this value; All calculations will scale themselves according to that value.<\/li>\n<\/ul>\n<blockquote>\n<pre>SELECT\r\n  y_scale,\r\n  horizontal_bar\r\n  FROM\r\n  (\r\n  SELECT\r\n    @row_number := @row_number+1,\r\n    CASE @row_number\r\n      WHEN 1  THEN ROUND(max_value)\r\n      WHEN <strong>(@graph_rows+1)\/2<\/strong> THEN ROUND((max_value+min_value)\/2)\r\n      WHEN <strong>@graph_rows<\/strong> THEN ROUND(min_value)\r\n      ELSE ''\r\n    END AS y_scale,\r\n    horizontal_bar\r\n  FROM\r\n    (SELECT @row_number := 0) AS select_row\r\n    INNER JOIN\r\n    (\r\n    SELECT\r\n      min_value,\r\n      max_value,\r\n      value_column,\r\n      GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar\r\n    FROM\r\n      (SELECT @row_number := 0) AS select_row,\r\n      tinyint_asc\r\n    INNER JOIN (\r\n      SELECT\r\n        ordering_column,\r\n        @min_value AS min_value,\r\n        @max_value AS max_value,\r\n        value_column,\r\n        @scaled_value := CONVERT((value_column-@min_value)*(@graph_rows-1)\/(@max_value-@min_value), UNSIGNED) AS scaled_value,\r\n        CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',(@graph_rows-1)-@scaled_value)) AS graph_bar\r\n      FROM\r\n        (\r\n        SELECT\r\n          @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,\r\n          @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,\r\n          ordering_column,\r\n          value_column\r\n        FROM\r\n          (\r\n            SELECT id AS ordering_column, val AS value_column\r\n            FROM sample_values LIMIT 100\r\n          ) AS value_select,\r\n          (SELECT @min_value := NULL) AS select_min,\r\n          (SELECT @max_value := NULL) AS select_max,\r\n          <strong>(SELECT @graph_rows := 21) AS select_graph_rows<\/strong>\r\n        ) AS select_range\r\n      ) AS select_vertical\r\n    WHERE\r\n      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_bar)\r\n    GROUP BY\r\n      tinyint_asc.value\r\n    ORDER BY\r\n      tinyint_asc.value DESC\r\n    ) AS select_horizontal\r\n  ) AS select_horizontal_untitled\r\n;\r\n+---------+------------------------------------------------------------------------------------------------------+\r\n| y_scale | horizontal_bar                                                                                       |\r\n+---------+------------------------------------------------------------------------------------------------------+\r\n| 2       | -------------######---------------------------------------------------------######------------------ |\r\n|         | -----------##------###---------------------------------------------------###------###--------------- |\r\n|         | ---------##-----------#-------------------------------------------------#------------#-------------- |\r\n|         | --------#--------------##---------------------------------------------##--------------##------------ |\r\n|         | ------##-----------------#-------------------------------------------#------------------#----------- |\r\n|         | -----#--------------------#-----------------------------------------#--------------------#---------- |\r\n|         | ----#----------------------#---------------------------------------#----------------------#--------- |\r\n|         | ---#------------------------#-------------------------------------#------------------------#-------- |\r\n|         | --#--------------------------#-----------------------------------#--------------------------#------- |\r\n|         | -#----------------------------#---------------------------------#----------------------------#------ |\r\n| 1       | #------------------------------#-------------------------------#------------------------------#----- |\r\n|         | --------------------------------#-----------------------------#--------------------------------#---- |\r\n|         | ---------------------------------#---------------------------#----------------------------------#--- |\r\n|         | ----------------------------------#-------------------------#------------------------------------#-- |\r\n|         | -----------------------------------##----------------------#--------------------------------------#- |\r\n|         | -------------------------------------#--------------------#----------------------------------------# |\r\n|         | --------------------------------------#-----------------##------------------------------------------ |\r\n|         | ---------------------------------------#---------------#-------------------------------------------- |\r\n|         | ----------------------------------------##-----------##--------------------------------------------- |\r\n|         | ------------------------------------------##-------##----------------------------------------------- |\r\n| 0       | --------------------------------------------#######------------------------------------------------- |\r\n+---------+------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n<p>And that&#8217;s as far as we go in our example.<\/p>\n<h4>Proof of concept<\/h4>\n<p>Just to prove my point, I&#8217;ll present another SQL graph. This time the function is <strong>x*sin(x)<\/strong>, which has a nice curve.<\/p>\n<ul>\n<li>Take a look at the <strong>bold text<\/strong> in the query. This is <em>the only thing<\/em> that changes. Replace this with your generic query, and you&#8217;re done!<\/li>\n<li>I&#8217;ve also set the number of rows to be <strong>11<\/strong>. Also find bold text in query. Change this to scale the graph!<\/li>\n<\/ul>\n<p>Note how the graph <em>automatically<\/em> scales to the range [-17,16].<\/p>\n<blockquote>\n<pre>SELECT\r\n  y_scale,\r\n  horizontal_bar\r\n  FROM\r\n  (\r\n  SELECT\r\n    @row_number := @row_number+1,\r\n    CASE @row_number\r\n      WHEN 1  THEN ROUND(max_value)\r\n      WHEN (@graph_rows+1)\/2 THEN ROUND((max_value+min_value)\/2)\r\n      WHEN @graph_rows THEN ROUND(min_value)\r\n      ELSE ''\r\n    END AS y_scale,\r\n    horizontal_bar\r\n  FROM\r\n    (SELECT @row_number := 0) AS select_row\r\n    INNER JOIN\r\n    (\r\n    SELECT\r\n      min_value,\r\n      max_value,\r\n      value_column,\r\n      GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS horizontal_bar\r\n    FROM\r\n      (SELECT @row_number := 0) AS select_row,\r\n      tinyint_asc\r\n    INNER JOIN (\r\n      SELECT\r\n        ordering_column,\r\n        @min_value AS min_value,\r\n        @max_value AS max_value,\r\n        value_column,\r\n        @scaled_value := CONVERT((value_column-@min_value)*(@graph_rows-1)\/(@max_value-@min_value), UNSIGNED) AS scaled_value,\r\n        CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',(@graph_rows-1)-@scaled_value)) AS graph_bar\r\n      FROM\r\n        (\r\n        SELECT\r\n          @min_value := LEAST(IFNULL(@min_value, value_column), value_column) AS min_value,\r\n          @max_value := GREATEST(IFNULL(@max_value, value_column), value_column) AS max_value,\r\n          ordering_column,\r\n          value_column\r\n        FROM\r\n          (\r\n            <strong>SELECT id AS ordering_column, SIN(id\/5)*id\/5 AS value_column FROM sample_values LIMIT 100<\/strong>\r\n          ) AS value_select,\r\n          (SELECT @min_value := NULL) AS select_min,\r\n          (SELECT @max_value := NULL) AS select_max,\r\n          (SELECT @graph_rows := <strong>11<\/strong>) AS select_graph_rows\r\n        ) AS select_range\r\n      ) AS select_vertical\r\n    WHERE\r\n      tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(graph_bar)\r\n    GROUP BY\r\n      tinyint_asc.value\r\n    ORDER BY\r\n      tinyint_asc.value DESC\r\n    ) AS select_horizontal\r\n  ) AS select_horizontal_untitled\r\n;\r\n+---------+------------------------------------------------------------------------------------------------------+\r\n| y_scale | horizontal_bar                                                                                       |\r\n+---------+------------------------------------------------------------------------------------------------------+\r\n| 16      | ---------------------------------------------------------------------------------------------------# |\r\n|         | --------------------------------------------------------------------#######-----------------------#- |\r\n|         | ---------------------------------------##-------------------------##-------#---------------------#-- |\r\n|         | -----------------------------------####--####--------------------#----------##------------------#--- |\r\n|         | ------########-------------------##----------##-----------------#-------------#----------------#---- |\r\n| -1      | ######--------#####-----------###--------------##-------------##---------------#--------------#----- |\r\n|         | -------------------###########-------------------##----------#------------------#------------#------ |\r\n|         | ---------------------------------------------------##------##--------------------#----------#------- |\r\n|         | -----------------------------------------------------######-----------------------#--------#-------- |\r\n|         | -----------------------------------------------------------------------------------##----##--------- |\r\n| -17     | -------------------------------------------------------------------------------------####----------- |\r\n+---------+------------------------------------------------------------------------------------------------------<\/pre>\n<\/blockquote>\n<h4>Conclusion<\/h4>\n<p>There goes my Saturday afternoon nap.<\/p>\n<p>To be done:<\/p>\n<ul>\n<li>Present X-axis values.<\/li>\n<li>Do better work with value precision.<\/li>\n<li>Present proper titles. This is easy with session variables. I&#8217;ll make a draft.<\/li>\n<\/ul>\n<p>To be continued&#8230; (and concluded, I don&#8217;t mean to drag this much longer).<\/p>\n<p>[Continues on: <a href=\"http:\/\/code.openark.org\/blog\/mysql\/auto-scaling-scaled-sql-graphs-concluded\">Auto scaling, scaled SQL graphs concluded<\/a>]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Rotating SQL graphs horizontally, I have shown how to rotate an ASCII SQL graph into horizontal position. I was dissatisfied with some parts of the solution, which I will show now how to fix: I had to manually scale the graph values so as to fit nicely into screen. I had to rely on [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[41,21],"class_list":["post-1009","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-gh","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1009","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1009"}],"version-history":[{"count":43,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1009\/revisions"}],"predecessor-version":[{"id":1073,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1009\/revisions\/1073"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1009"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1009"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1009"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}