Who Needs Graphics? Create Charts in SQL

Tweet

SQL pie chartThis is one of the most impressive demonstrations of SQL I’ve seen. Shlomi Noach, a database consultant from Israel, has developed a single SQL query that analyzes a table and generates a pie chart in ASCII art.

Shlomi’s blog post, SQL pie chart, provides a full step-by-step analysis of how it was achieved. In summary:

  1. Values from a single table are examined and accumulating totals are calculated.
  2. From the accumulating totals, accumulating ratios are calculated in a 0 to 1 range and 0 to 2 * PI range (radians in a whole circle).
  3. A co-ordinates table is then created. This starts at zero in the top-left and increases in appropriate values to a maximum size in the bottom right.
  4. The co-ordinates table is used to generate a table of degrees in relation to the center of the table.
  5. The original table data is then mapped on to the degrees table, assigned an ASCII character for each ‘color’, and anything outside the circle is set to a space.
  6. Finally, a legend is added.
+----------------------------------------------------------------------------------------------+
| pie chart                                                                                    |
+----------------------------------------------------------------------------------------------+
|                                                                                              |
|                                   ;;;;;;;;;;;;;;;;;;;;;;;;;                                  |
|                          oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                         |
|                    ooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                   |
|                oooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;               |
|             oooooooooooooooooooooooo                     ;;;;;;;;;;;;;;;;;;;;;###            |
|           oooooooooooooooooooo                                 ;;;;;;;;;###########          |
|         oooooooooooooooooo                                         ##################        |
|       ooooooooooooooooo                                               #################      |
|      ooooooooooooooooo                                                 #################     |
|      oooooooooooooooo                                                   ################     |
|     oooooooooooooooo                                                     ################    |
|      oooooooooooooooo                                                   ::::::::::::::::     |
|      ooooooooooooooooo                                                 :::::::::::::::::     |
|       ooooooooooooooooo                                               :::::::::::::::::      |
|         oooooooooooooo::::                                         ::::::::::::::::::        |
|           ooooooo:::::::::::::                                 ::::::::::::::::::::          |
|             ::::::::::::::::::::::::                     ::::::::::::::::::::::::            |
|                :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::               |
|                    :::::::::::::::::::::::::::::::::::::::::::::::::::::::                   |
|                          :::::::::::::::::::::::::::::::::::::::::::                         |
|                                   :::::::::::::::::::::::::                                  |
|                                                                                              |
| ##  red: 1 (10%)                                                                             |
| ;;  blue: 2 (20%)                                                                            |
| oo  orange: 3 (30%)                                                                          |
| ::  white: 4 (40%)                                                                           |
+----------------------------------------------------------------------------------------------+

As you might imagine, the resulting query is a little long and complex. I won’t reproduce it here but Shlomi’s post provides the code if you want to try it for yourself.

It’s very clever. And utterly mad.

Have you seen any other examples of bizarre SQL queries? Post your links below…

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.dangrossman.info Dan Grossman

    I absolutely love it! Genius!

  • http://www.anthonysterling.com/ SilverBulletUK

    I fear Rudy’s response! :-)

  • cts91

    What a BEAST! I’m in awe.

  • http://www.fitlife.cz Houdas

    Madness?

    This… is… SQLPARTA!

  • http://www.sky-web.net/ Dr John

    and just the sort of query we can regularly make use of as well.
    ;)

  • Mike

    Here is a simpler bar chart: http://sqlfool.com/2009/07/poor-womans-graph/

  • Shonkyboy

    Great – but utterly pointless and a huge waste of time – pretty much like the 7 hours fishing i did today :)

  • hallodom

    Am staying with Flash!

  • Shlomi Noach

    Hey,

    Thanks for the mention!
    Just in response to Mike’s bar chart example, why don’t you also have a look at my more elaborate graphs:
    http://code.openark.org/blog/mysql/auto-scaling-scaled-sql-graphs-concluded

    Cheers,
    Shlomi