Rotating SQL graphs horizontally

July 24, 2009

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]

tags: ,
posted in MySQL by shlomi

« | »

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

10 Comments to "Rotating SQL graphs horizontally"

  1. Emil wrote:

    Wow. That's amongst the coolest things I've ever seen done in SQL. :)

  2. shlomi wrote:

    Emil,
    Thank you. Turns out a sleepless night is all that's required.

  3. Ruturaj wrote:

    hey,
    thats marvellous I never wondered if somebody would go to lengths for building a graphs off a dB. Whats next? an ASCII text charting library :) ?

  4. Giuseppe Maxia wrote:

    Shlomi,
    once again, hats off to you!

    I posted it in the Librarian,
    http://dev.mysql.com/librarian/?tag_search=6808

    Several other posts of yours should be there. Probably you should add them yourself.

    Cheers

    Giuseppe

  5. Roland Bouman wrote:

    Hi!

    well done Shlomi, very cool hack :)

  6. David Holoboff wrote:

    This is indeed one of the coolest and practical queries out there. Kudos!

  7. Dups wrote:

    Well done indeed!

  8. Generic, auto scaling, scaled SQL graphs | code.openark.org wrote:

    [...] Rotating SQL graphs horizontally [...]

  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 [...]

  10. SQL: selecting top N records per group | code.openark.org wrote:

    [...] Examples for string walking (described in the excellent SQL Cookbook) can be found here, here and here. We’ll be using a numbers table: a simple table which lists ascending integer numbers. For [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org