Programming

Who Needs Graphics? Create Charts in SQL

    Craig Buckler
    Share

    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…