Rotating SQL graphs horizontally

We all love graphs. We all love SQL hacks. We all know the SQL hack which displays a character-based graph (example follows for those unfamiliar).

But we all love horizontal graphs, not vertical ones. We are used to the X axis being horizontal, Y being vertical. Not vice versa.

In this post I’ll present a SQL hack which rotates a vertical graph to horizontal. In fact, the technique shown will rotate any ‘textual image’; but graphs are a nice example.

A vertical graph example

What’s prettier than a sinus curve? I have prepared a simple table that will serve nicely, and can be found in graph_tables.sql.

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)

The common trick for displaying a graph is something like:

SELECT
  id,
  CONCAT(REPEAT('-',(val*10+1)-1),'#',REPEAT('-',22-(val*10+1))) AS bar
FROM
  sample_values
LIMIT 100
+----+------------------------+
| id | bar                    |
+----+------------------------+
|  0 | ----------#----------- |
|  1 | -----------#---------- |
|  2 | ------------#--------- |
|  3 | -------------#-------- |
|  4 | --------------#------- |
|  5 | ---------------#------ |
|  6 | ----------------#----- |
|  7 | ----------------#----- |
|  8 | -----------------#---- |
|  9 | ------------------#--- |
| 10 | ------------------#--- |
| 11 | -------------------#-- |
| 12 | -------------------#-- |
| 13 | --------------------#- |
| 14 | --------------------#- |
| 15 | --------------------#- |
| 16 | --------------------#- |
| 17 | --------------------#- |
| 18 | --------------------#- |
| 19 | -------------------#-- |
| 20 | -------------------#-- |
| 21 | -------------------#-- |
| 22 | ------------------#--- |
| 23 | -----------------#---- |
| 24 | -----------------#---- |
| 25 | ----------------#----- |
| 26 | ---------------#------ |
| 27 | --------------#------- |
...
+----+------------------------+
100 rows in set (0.00 sec)

In the above I’ve explicitly stretched values for them to be presentable.

So that’s the vertical graph. It’s not easy to read, plus you need to scroll down on your terminal to find out what’s going on. Can we rotate it?

Rotating the graph

We will combine the String Walking technique with String Unwalking. What we need and assume is as follows:

• The above graph pads the bars up to some fixed length.
• Said length is not too high, so as to fit nicely within our screen.
• We have an integers table, used for string walking.

The idea is to iterate the textual column character by character from end to start (done by string walking), and convert each such character column to row (done by string unwalking).

SELECT
  GROUP_CONCAT(SUBSTRING(bar,tinyint_asc.value,1) ORDER BY id SEPARATOR '') AS `Sinus graph`
FROM
  tinyint_asc
INNER JOIN (
  SELECT
    id,
    CONCAT(REPEAT('-',(val*10+1)-1),'#',REPEAT('-',22-(val*10+1))) AS bar
  FROM
    sample_values
  LIMIT 100) sel_graph
WHERE
  tinyint_asc.value BETWEEN 1 AND CHAR_LENGTH(bar)
GROUP BY
  tinyint_asc.value
ORDER BY
  tinyint_asc.value DESC;

+------------------------------------------------------------------------------------------------------+
| Sinus graph                                                                                          |
+------------------------------------------------------------------------------------------------------+
| ---------------------------------------------------------------------------------------------------- |
| -------------######---------------------------------------------------------######------------------ |
| -----------##------###---------------------------------------------------###------###--------------- |
| ---------##-----------#-------------------------------------------------#------------#-------------- |
| --------#--------------##---------------------------------------------##--------------##------------ |
| ------##-----------------#-------------------------------------------#------------------#----------- |
| -----#--------------------#-----------------------------------------#--------------------#---------- |
| ----#----------------------#---------------------------------------#----------------------#--------- |
| ---#------------------------#-------------------------------------#------------------------#-------- |
| --#--------------------------#-----------------------------------#--------------------------#------- |
| -#----------------------------#---------------------------------#----------------------------#------ |
| #------------------------------#-------------------------------#------------------------------#----- |
| --------------------------------#-----------------------------#--------------------------------#---- |
| ---------------------------------#---------------------------#----------------------------------#--- |
| ----------------------------------#-------------------------#------------------------------------#-- |
| -----------------------------------##----------------------#--------------------------------------#- |
| -------------------------------------#--------------------#----------------------------------------# |
| --------------------------------------#-----------------##------------------------------------------ |
| ---------------------------------------#---------------#-------------------------------------------- |
| ----------------------------------------##-----------##--------------------------------------------- |
| ------------------------------------------##-------##----------------------------------------------- |
| --------------------------------------------#######------------------------------------------------- |
+------------------------------------------------------------------------------------------------------+

Now that’s much prettier, isn’t it?

Crazy stuff challenge

For those eager to improve upon this, I raise a few challenges (no prize but fame, though). Use the given data (graph_tables.sql), to produce:

• (Vertical) values on the X-axis for all graph points.
• Min/max values for Y-axis, on separate column.
• As I understand it, full, in place, values for Y-axis is not possible; please prove me wrong!
• Other crazy stuff?

Please add solutions in comments!

[Continues on: Generic, auto scaling, scaled SQL graphs]