{"id":984,"date":"2009-07-24T06:00:12","date_gmt":"2009-07-24T04:00:12","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=984"},"modified":"2009-07-27T12:53:50","modified_gmt":"2009-07-27T10:53:50","slug":"rotating-sql-graphs-horizontally","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/rotating-sql-graphs-horizontally","title":{"rendered":"Rotating SQL graphs horizontally"},"content":{"rendered":"

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).<\/p>\n

But we all love horizontal<\/em> graphs, not vertical ones. We are used to the X axis being horizontal, Y being vertical. Not vice versa.<\/p>\n

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.<\/p>\n

A vertical graph example<\/h4>\n

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<\/a>.<\/p>\n

<\/p>\n

\n
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
The common trick for displaying a graph is something like:<\/p>\n
\n
SELECT\r\n  id,\r\n  CONCAT(REPEAT('-',(val*10+1)-1),'#',REPEAT('-',22-(val*10+1))) AS bar\r\nFROM\r\n  sample_values\r\nLIMIT 100\r\n+----+------------------------+\r\n| id | 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 | --------------------#- |\r\n| 17 | --------------------#- |\r\n| 18 | --------------------#- |\r\n| 19 | -------------------#-- |\r\n| 20 | -------------------#-- |\r\n| 21 | -------------------#-- |\r\n| 22 | ------------------#--- |\r\n| 23 | -----------------#---- |\r\n| 24 | -----------------#---- |\r\n| 25 | ----------------#----- |\r\n| 26 | ---------------#------ |\r\n| 27 | --------------#------- |\r\n...\r\n+----+------------------------+\r\n100 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\n
In the above I’ve explicitly stretched values for them to be presentable.<\/p><\/blockquote>\n
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?<\/p>\n
Rotating the graph<\/h4>\n
We will combine the String Walking technique with String Unwalking<\/a>. What we need and assume is as follows:<\/p>\n