Generic, auto scaling, scaled SQL graphs

July 27, 2009

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 hard coded scaling schemes.
  • I had to rely on hard coded column names.
  • I had no y-axis legend.

I will now present an SQL query which allows for pluggable queries, which creates self, auto scaling graphs, along with y-axis scales.

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 - the steps are unimportant. I'll present a generic query, into which your own SELECT can be embedded, and which will provide you with the graph.

We'll use the same example, found in graph.sql.

Recap

Following is the table data we want to use. Values represent a sinus function.

SELECT * FROM sample_values;
+-----+----------------------+
| id  | val                  |
+-----+----------------------+
|   0 |                    1 |
|   1 |     1.09983341664683 |
|   2 |     1.19866933079506 |
|   3 |     1.29552020666134 |
|   4 |     1.38941834230865 |
|   5 |      1.4794255386042 |
|   6 |     1.56464247339504 |
|   7 |     1.64421768723769 |
|   8 |     1.71735609089952 |
|   9 |     1.78332690962748 |
...
| 246 |    0.492103409609378 |
| 247 |    0.580639083926769 |
| 248 |    0.673364873895278 |
| 249 |    0.769354294072604 |
| 250 |    0.867648249902227 |
| 251 |    0.967264620669155 |
| 252 |     1.06720807252547 |
| 253 |     1.16648000353716 |
| 254 |     1.26408852138447 |
| 255 |     1.35905835402217 |
+-----+----------------------+
256 rows in set (0.00 sec)

Step 1: Generic query

The only requirements from your query is for it to be generic. It need to provide tow columns:

  • ordering_column: A column which orders the values, e.g. an AUTO_INCREMENT, some TIMESTAMP etc.
  • value_column: The values themselves.

In the above example, the generic query will be:

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

From this point on, everything else is self-computed. The above is the only thing that needs to change if you wish to provide your own graphs.

Problem: hard coded scaling

In the following example I generate a vertical graph, but I need to hard-code the scaling:

SELECT
  ordering_column,
  CONCAT(REPEAT('-',(value_column*10+1)-1),'#',REPEAT('-',22-(value_column*10+1))) AS graph_bar
FROM
  (
  SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100
  ) AS value_select
;
+-----------------+------------------------+
| ordering_column | graph_bar              |
+-----------------+------------------------+
|               0 | ----------#----------- |
|               1 | -----------#---------- |
|               2 | ------------#--------- |
|               3 | -------------#-------- |
|               4 | --------------#------- |
|               5 | ---------------#------ |
|               6 | ----------------#----- |
|               7 | ----------------#----- |
|               8 | -----------------#---- |
|               9 | ------------------#--- |
|              10 | ------------------#--- |
|              11 | -------------------#-- |
|              12 | -------------------#-- |
|              13 | --------------------#- |
|              14 | --------------------#- |
|              15 | --------------------#- |
|              16 | --------------------#- |

Step 2: towards auto-scaling

In this step we will find the minimum/maximum values for value_column. We will as yet do nothing with these.

SELECT
  @min_value := LEAST(@min_value, value_column) AS min_value,
  @max_value := GREATEST(@max_value, value_column) AS max_value,
  ordering_column,
  CONCAT(REPEAT('-',(value_column*10+1)-1),'#',REPEAT('-',22-(value_column*10+1))) AS graph_bar
FROM
  (
  SELECT id AS ordering_column, val AS value_column FROM sample_values LIMIT 100
  ) AS value_select,
  (SELECT @min_value := 1000000) AS select_min,
  (SELECT @max_value := -1000000) AS select_max
;
+---------------------+-----------------+-----------------+------------------------+
| min_value           | max_value       | ordering_column | graph_bar              |
+---------------------+-----------------+-----------------+------------------------+
|                   1 |               1 |               0 | ----------#----------- |
|                   1 | 1.0998334166468 |               1 | -----------#---------- |
|                   1 | 1.1986693307951 |               2 | ------------#--------- |
|                   1 | 1.2955202066613 |               3 | -------------#-------- |
|                   1 | 1.3894183423086 |               4 | --------------#------- |
|                   1 | 1.4794255386042 |               5 | ---------------#------ |
|                   1 |  1.564642473395 |               6 | ----------------#----- |
|                   1 | 1.6442176872377 |               7 | ----------------#----- |
|                   1 | 1.7173560908995 |               8 | -----------------#---- |
|                   1 | 1.7833269096275 |               9 | ------------------#--- |
|                   1 | 1.8414709848079 |              10 | ------------------#--- |
|                   1 | 1.8912073600614 |              11 | -------------------#-- |
|                   1 | 1.9320390859672 |              12 | -------------------#-- |
|                   1 | 1.9635581854172 |              13 | --------------------#- |
|                   1 | 1.9854497299885 |              14 | --------------------#- |
|                   1 |  1.997494986604 |              15 | --------------------#- |
|                   1 | 1.9995736030415 |              16 | --------------------#- |
|                   1 | 1.9995736030415 |              17 | --------------------#- |
|                   1 | 1.9995736030415 |              18 | --------------------#- |
|                   1 | 1.9995736030415 |              19 | -------------------#-- |
|                   1 | 1.9995736030415 |              20 | -------------------#-- |
|                   1 | 1.9995736030415 |              21 | -------------------#-- |
|                   1 | 1.9995736030415 |              22 | ------------------#--- |
|                   1 | 1.9995736030415 |              23 | -----------------#---- |
|                   1 | 1.9995736030415 |              24 | -----------------#---- |
|                   1 | 1.9995736030415 |              25 | ----------------#----- |
|                   1 | 1.9995736030415 |              26 | ---------------#------ |
|                   1 | 1.9995736030415 |              27 | --------------#------- |
|                   1 | 1.9995736030415 |              28 | -------------#-------- |
|                   1 | 1.9995736030415 |              29 | ------------#--------- |
|                   1 | 1.9995736030415 |              30 | -----------#---------- |
|                   1 | 1.9995736030415 |              31 | ----------#----------- |
|    0.94162585657242 | 1.9995736030415 |              32 | ---------#------------ |
|    0.84225430585675 | 1.9995736030415 |              33 | --------#------------- |
|    0.74445889797317 | 1.9995736030415 |              34 | -------#-------------- |
...

I'm using ugly values for initial min/max comparison (-100000, 1000000). I'll soon get rid of them, don't worry!

Step 3: formalizing min/max values

Using another sub-query, we will isolate the minimum/maximum values, and forget about the graph for the moment.

SELECT
  @min_value,
  @max_value,
  ordering_column,
  value_column
FROM
  (
  SELECT
    @min_value := LEAST(@min_value, value_column) AS min_value,
    @max_value := GREATEST(@max_value, value_column) AS max_value,
    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 := 1000000) AS select_min,
    (SELECT @max_value := -1000000) AS select_max
  ) AS select_range
;
+---------------------+-----------------+-----------------+----------------------+
| @min_value          | @max_value      | ordering_column | value_column         |
+---------------------+-----------------+-----------------+----------------------+
| 7.6742435899169e-05 | 1.9995736030415 |               0 |                    1 |
| 7.6742435899169e-05 | 1.9995736030415 |               1 |     1.09983341664683 |
| 7.6742435899169e-05 | 1.9995736030415 |               2 |     1.19866933079506 |
| 7.6742435899169e-05 | 1.9995736030415 |               3 |     1.29552020666134 |
| 7.6742435899169e-05 | 1.9995736030415 |               4 |     1.38941834230865 |
| 7.6742435899169e-05 | 1.9995736030415 |               5 |      1.4794255386042 |
| 7.6742435899169e-05 | 1.9995736030415 |               6 |     1.56464247339504 |
| 7.6742435899169e-05 | 1.9995736030415 |               7 |     1.64421768723769 |
| 7.6742435899169e-05 | 1.9995736030415 |               8 |     1.71735609089952 |
| 7.6742435899169e-05 | 1.9995736030415 |               9 |     1.78332690962748 |
| 7.6742435899169e-05 | 1.9995736030415 |              10 |      1.8414709848079 |
| 7.6742435899169e-05 | 1.9995736030415 |              11 |     1.89120736006144 |
...

Step 4: scaling the values

In this step I'll do two things:

  • Get rid of ugly hard coded values for min/max comparison. I'll use the IFNULL function to check for initial conditions.
  • Introduce self scaling: I will assume a 21-rows high graph is desired, and will auto-scale the values to fit in. Don't worry, I'll get rid of those values later on!
SELECT
  @min_value AS min_value,
  @max_value AS max_value,
  value_column,
  @scaled_value := CONVERT((value_column-@min_value)*20/(@max_value-@min_value), UNSIGNED) AS scaled_value,
  CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@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,
    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
  ) AS select_range
;
+---------------------+-----------------+----------------------+--------------+-----------------------+
| min_value           | max_value       | value_column         | scaled_value | graph_bar             |
+---------------------+-----------------+----------------------+--------------+-----------------------+
| 7.6742435899169e-05 | 1.9995736030415 |                    1 |           10 | ----------#---------- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.09983341664683 |           11 | -----------#--------- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.19866933079506 |           12 | ------------#-------- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.29552020666134 |           13 | -------------#------- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.38941834230865 |           14 | --------------#------ |
| 7.6742435899169e-05 | 1.9995736030415 |      1.4794255386042 |           15 | ---------------#----- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.56464247339504 |           16 | ----------------#---- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.64421768723769 |           16 | ----------------#---- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.71735609089952 |           17 | -----------------#--- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.78332690962748 |           18 | ------------------#-- |
| 7.6742435899169e-05 | 1.9995736030415 |      1.8414709848079 |           18 | ------------------#-- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.89120736006144 |           19 | -------------------#- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.93203908596723 |           19 | -------------------#- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.96355818541719 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |     1.98544972998846 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |     1.99749498660405 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |      1.9995736030415 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |     1.99166481045247 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |      1.9738476308782 |           20 | --------------------# |
| 7.6742435899169e-05 | 1.9995736030415 |     1.94630008768741 |           19 | -------------------#- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.90929742682568 |           19 | -------------------#- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.86320936664887 |           19 | -------------------#- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.80849640381959 |           18 | ------------------#-- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.74570521217672 |           17 | -----------------#--- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.67546318055115 |           17 | -----------------#--- |
| 7.6742435899169e-05 | 1.9995736030415 |     1.59847214410396 |           16 | ----------------#---- |

I have now re-introduced the graph. Note that while values are in the range 0..2, the SQL query automatically scales to a 1-21 long graph bar.

Step 5: rotation

Shall we now present this as a horizontal graph?

SELECT
  GROUP_CONCAT(SUBSTRING(graph_bar, tinyint_asc.value, 1) ORDER BY ordering_column SEPARATOR '') AS `result`
FROM
  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)*20/(@max_value-@min_value), UNSIGNED) AS scaled_value,
    CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@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,
      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
    ) 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;
;
+------------------------------------------------------------------------------------------------------+
| result                                                                                               |
+------------------------------------------------------------------------------------------------------+
| -------------######---------------------------------------------------------######------------------ |
| -----------##------###---------------------------------------------------###------###--------------- |
| ---------##-----------#-------------------------------------------------#------------#-------------- |
| --------#--------------##---------------------------------------------##--------------##------------ |
| ------##-----------------#-------------------------------------------#------------------#----------- |
| -----#--------------------#-----------------------------------------#--------------------#---------- |
| ----#----------------------#---------------------------------------#----------------------#--------- |
| ---#------------------------#-------------------------------------#------------------------#-------- |
| --#--------------------------#-----------------------------------#--------------------------#------- |
| -#----------------------------#---------------------------------#----------------------------#------ |
| #------------------------------#-------------------------------#------------------------------#----- |
| --------------------------------#-----------------------------#--------------------------------#---- |
| ---------------------------------#---------------------------#----------------------------------#--- |
| ----------------------------------#-------------------------#------------------------------------#-- |
| -----------------------------------##----------------------#--------------------------------------#- |
| -------------------------------------#--------------------#----------------------------------------# |
| --------------------------------------#-----------------##------------------------------------------ |
| ---------------------------------------#---------------#-------------------------------------------- |
| ----------------------------------------##-----------##--------------------------------------------- |
| ------------------------------------------##-------##----------------------------------------------- |
| --------------------------------------------#######------------------------------------------------- |
+------------------------------------------------------------------------------------------------------+

We now have a self-scaling horizontal graph! Phew!

But I'm not satisfied yet.

Step 6: towards Y-axis scaling

We will now introduce min/max Y-scale values into the graph. Note the following:

  • I'll be using a counter to count rows.
  • I'll rely on previous knowledge of hard-coded 21 rows value (I promise, this will be fixed soon).
  • We already have min/max values. Using the two, I'll also provide the mid-value.
  • I chose to use ROUND(). This may not be the best idea when your values are very small fractions. Remove the ROUND() if it does not fit in with your values.
SELECT
  @row_number := @row_number+1,
  CASE @row_number
    WHEN 1  THEN ROUND(max_value)
    WHEN 11 THEN ROUND((max_value+min_value)/2)
    WHEN 21 THEN ROUND(min_value)
    ELSE ''
  END AS y_scale,
  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)*20/(@max_value-@min_value), UNSIGNED) AS scaled_value,
      CONCAT(REPEAT('-',@scaled_value),'#',REPEAT('-',20-@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,
        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
      ) 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
;+------------------------------+---------+------------------------------------------------------------------------------------------------------+
| @row_number := @row_number+1 | y_scale | horizontal_bar                                                                                       |
+------------------------------+---------+------------------------------------------------------------------------------------------------------+
|                            1 | 2       | -------------######---------------------------------------------------------######------------------ |
|                            2 |         | -----------##------###---------------------------------------------------###------###--------------- |
|                            3 |         | ---------##-----------#-------------------------------------------------#------------#-------------- |
|                            4 |         | --------#--------------##---------------------------------------------##--------------##------------ |
|                            5 |         | ------##-----------------#-------------------------------------------#------------------#----------- |
|                            6 |         | -----#--------------------#-----------------------------------------#--------------------#---------- |
|                            7 |         | ----#----------------------#---------------------------------------#----------------------#--------- |
|                            8 |         | ---#------------------------#-------------------------------------#------------------------#-------- |
|                            9 |         | --#--------------------------#-----------------------------------#--------------------------#------- |
|                           10 |         | -#----------------------------#---------------------------------#----------------------------#------ |
|                           11 | 1       | #------------------------------#-------------------------------#------------------------------#----- |
|                           12 |         | --------------------------------#-----------------------------#--------------------------------#---- |
|                           13 |         | ---------------------------------#---------------------------#----------------------------------#--- |
|                           14 |         | ----------------------------------#-------------------------#------------------------------------#-- |
|                           15 |         | -----------------------------------##----------------------#--------------------------------------#- |
|                           16 |         | -------------------------------------#--------------------#----------------------------------------# |
|                           17 |         | --------------------------------------#-----------------##------------------------------------------ |
|                           18 |         | ---------------------------------------#---------------#-------------------------------------------- |
|                           19 |         | ----------------------------------------##-----------##--------------------------------------------- |
|                           20 |         | ------------------------------------------##-------##----------------------------------------------- |
|                           21 | 0       | --------------------------------------------#######------------------------------------------------- |
+------------------------------+---------+------------------------------------------------------------------------------------------------------+

Well, now we're getting somewhere!

Step 7: cleaning up

In this step I'll do the following:

  • Using yet another subquery (how many do we have already?), I'll get rid of the counter column.
  • I'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.
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
  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,
          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 @graph_rows := 21) 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
;
+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 2       | -------------######---------------------------------------------------------######------------------ |
|         | -----------##------###---------------------------------------------------###------###--------------- |
|         | ---------##-----------#-------------------------------------------------#------------#-------------- |
|         | --------#--------------##---------------------------------------------##--------------##------------ |
|         | ------##-----------------#-------------------------------------------#------------------#----------- |
|         | -----#--------------------#-----------------------------------------#--------------------#---------- |
|         | ----#----------------------#---------------------------------------#----------------------#--------- |
|         | ---#------------------------#-------------------------------------#------------------------#-------- |
|         | --#--------------------------#-----------------------------------#--------------------------#------- |
|         | -#----------------------------#---------------------------------#----------------------------#------ |
| 1       | #------------------------------#-------------------------------#------------------------------#----- |
|         | --------------------------------#-----------------------------#--------------------------------#---- |
|         | ---------------------------------#---------------------------#----------------------------------#--- |
|         | ----------------------------------#-------------------------#------------------------------------#-- |
|         | -----------------------------------##----------------------#--------------------------------------#- |
|         | -------------------------------------#--------------------#----------------------------------------# |
|         | --------------------------------------#-----------------##------------------------------------------ |
|         | ---------------------------------------#---------------#-------------------------------------------- |
|         | ----------------------------------------##-----------##--------------------------------------------- |
|         | ------------------------------------------##-------##----------------------------------------------- |
| 0       | --------------------------------------------#######------------------------------------------------- |
+---------+------------------------------------------------------------------------------------------------------+

And that's as far as we go in our example.

Proof of concept

Just to prove my point, I'll present another SQL graph. This time the function is x*sin(x), which has a nice curve.

  • Take a look at the bold text in the query. This is the only thing that changes. Replace this with your generic query, and you're done!
  • I've also set the number of rows to be 11. Also find bold text in query. Change this to scale the graph!

Note how the graph automatically scales to the range [-17,16].

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
  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,
          ordering_column,
          value_column
        FROM
          (
            SELECT id AS ordering_column, SIN(id/5)*id/5 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 @graph_rows := 11) 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
;
+---------+------------------------------------------------------------------------------------------------------+
| y_scale | horizontal_bar                                                                                       |
+---------+------------------------------------------------------------------------------------------------------+
| 16      | ---------------------------------------------------------------------------------------------------# |
|         | --------------------------------------------------------------------#######-----------------------#- |
|         | ---------------------------------------##-------------------------##-------#---------------------#-- |
|         | -----------------------------------####--####--------------------#----------##------------------#--- |
|         | ------########-------------------##----------##-----------------#-------------#----------------#---- |
| -1      | ######--------#####-----------###--------------##-------------##---------------#--------------#----- |
|         | -------------------###########-------------------##----------#------------------#------------#------ |
|         | ---------------------------------------------------##------##--------------------#----------#------- |
|         | -----------------------------------------------------######-----------------------#--------#-------- |
|         | -----------------------------------------------------------------------------------##----##--------- |
| -17     | -------------------------------------------------------------------------------------####----------- |
+---------+------------------------------------------------------------------------------------------------------

Conclusion

There goes my Saturday afternoon nap.

To be done:

  • Present X-axis values.
  • Do better work with value precision.
  • Present proper titles. This is easy with session variables. I'll make a draft.

To be continued... (and concluded, I don't mean to drag this much longer).

[Continues on: Auto scaling, scaled SQL graphs concluded]

tags: ,
posted in MySQL by shlomi

« | »

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

9 Comments to "Generic, auto scaling, scaled SQL graphs"

  1. Rotating SQL graphs horizontally | code.openark.org wrote:

    [...] Generic, auto scaling, scaled SQL graphs [...]

  2. Roland Bouman wrote:

    Way cool!

    Thou art a true SQLMeister :)

  3. Jonathan wrote:

    Very impressive and cool. Good job

  4. LG wrote:

    awesome!

  5. shlomi wrote:

    Thank you, everyone

  6. Auto scaling, scaled SQL graphs concluded | code.openark.org wrote:

    [...] Generic, auto scaling, scaled SQL graphs [...]

  7. William Fencedoor wrote:

    This looks really great on my EGA monitor.

  8. shlomi wrote:

    William: I'll do my best to provide with a hardware accelerator!

  9. Log Buffer #156: a Carnival of the Vanities for DBAs | Pythian Group Blog wrote:

    [...] Noach posted a couple excellent items this week—Rotating SQL graphs horizontally, and Generic, auto scaling, scaled SQL graphs. Shlomi writes, “We all love graphs. We all love SQL hacks. We all know the SQL hack which [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org