SQL pie chart

August 12, 2009

My other half says I'm losing it. But I think that as an enthusiast kernel developer she doesn't have the right to criticize people. ("I like user space better!" - she exclaims upon reading this).

Shown below is a (single query) SQL-generated pie chart. I will walk through the steps towards making this happen, and conclude with what, I hope you'll agree, are real-world, useful usage samples.

+----------------------------------------------------------------------+
| pie_chart                                                            |
+----------------------------------------------------------------------+
|                                                                      |
|                         ;;;;;;;;;;;;;;;;;;;;;                        |
|                  oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;                 |
|             oooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;            |
|          ooooooooooooooooo                 ;;;;;;;;;;;;#####         |
|        oooooooooooooo                           ;#############       |
|       oooooooooooo                                 ############      |
|      oooooooooooo                                   ############     |
|      ooooooooooo                                     ###########     |
|      oooooooooooo                                   ::::::::::::     |
|       oooooooooooo                                 ::::::::::::      |
|        ooooooooo:::::                           ::::::::::::::       |
|          o::::::::::::::::                 :::::::::::::::::         |
|             :::::::::::::::::::::::::::::::::::::::::::::            |
|                  :::::::::::::::::::::::::::::::::::                 |
|                         :::::::::::::::::::::                        |
|                                                                      |
| ##  red: 1 (10%)                                                     |
| ;;  blue: 2 (20%)                                                    |
| oo  orange: 3 (30%)                                                  |
| ::  white: 4 (40%)                                                   |
+----------------------------------------------------------------------+

Requirements

We need a generic query, which returns at least these two columns: name_column and value_column. For example, the following query will do:

SELECT name AS name_column, value AS value_column FROM sample_values2;
+-------------+--------------+
| name_column | value_column |
+-------------+--------------+
| red         |            1 |
| blue        |            2 |
| orange      |            3 |
| white       |            4 |
+-------------+--------------+

Find sample data in pie_data.sql.

Part 1: expanding the original query

We're going to need to take the above query's results and expand them: how much is the ratio from total, per value? As first step, accumulate values:

SELECT
  name_column,
  value_column,
  @accumulating_value := @accumulating_value+value_column AS accumulating_value
FROM (
  SELECT name AS name_column, value AS value_column FROM sample_values2
  ) select_values,
  (SELECT @accumulating_value := 0) select_accumulating_value
;
+-------------+--------------+--------------------+
| name_column | value_column | accumulating_value |
+-------------+--------------+--------------------+
| red         |            1 |                  1 |
| blue        |            2 |                  3 |
| orange      |            3 |                  6 |
| white       |            4 |                 10 |
+-------------+--------------+--------------------+

Next, we calculate ratio of accumulating value, and present it both in [0..1] range, as well as in [0..2*PI] (radians):

SELECT
  name_order,
  name_column,
  value_column,
  accumulating_value,
  accumulating_value/@accumulating_value AS accumulating_value_ratio,
  2*PI()*accumulating_value/@accumulating_value AS accumulating_value_radians
FROM (
  SELECT
    name_column,
    value_column,
    @name_order := @name_order+1 AS name_order,
    @accumulating_value := @accumulating_value+value_column AS accumulating_value,
    @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column
  FROM (
    SELECT name AS name_column, value AS value_column FROM sample_values2
    ) select_values,
    (SELECT @name_order := 0) select_name_order,
    (SELECT @accumulating_value := 0) select_accumulating_value,
    (SELECT @aggregated_name_column := '') select_aggregated_name_column
  ) select_accumulating_values
;
+------------+-------------+--------------+--------------------+--------------------------+----------------------------+
| name_order | name_column | value_column | accumulating_value | accumulating_value_ratio | accumulating_value_radians |
+------------+-------------+--------------+--------------------+--------------------------+----------------------------+
|          1 | red         |            1 |                  1 |                      0.1 |           0.62831853071796 |
|          2 | blue        |            2 |                  3 |                      0.3 |            1.8849555921539 |
|          3 | orange      |            3 |                  6 |                      0.6 |            3.7699111843078 |
|          4 | white       |            4 |                 10 |                        1 |            6.2831853071796 |
+------------+-------------+--------------+--------------------+--------------------------+----------------------------+

The radians value will help us decide where in the pie chart lies each value.

Part 2: behind the scenes of the pie chart

We now explain how the pie chart works. Later on we combine with Part 1, to produce the complete chart.

We first generate a coordinates system (see SQL graphics):

SELECT
  GROUP_CONCAT(CONCAT(t2.value,'.',t1.value) order by t1.value separator ' ') as circle
FROM
  tinyint_asc t1,
  tinyint_asc t2,
  (select @size := 10) sel_size,
  (select @radius := (@size/2 - 1)) sel_radius
WHERE
  t1.value < @size
  AND t2.value < @size
GROUP BY t2.value
;
+-----------------------------------------+
| circle                                  |
+-----------------------------------------+
| 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 |
| 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 |
| 2.0 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 |
| 3.0 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 |
| 4.0 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 |
| 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 |
| 6.0 6.1 6.2 6.3 6.4 6.5 6.6 6.7 6.8 6.9 |
| 7.0 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 |
| 8.0 8.1 8.2 8.3 8.4 8.5 8.6 8.7 8.8 8.9 |
| 9.0 9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 |
+-----------------------------------------+

Taking a slightly big step further, we calculate the angle per coordinate, in relation to center of coordinate system. Calculation is in radians, but presented in degrees, since it's more readable. Also, we note in which quarter of the graph each point lies.

SELECT
  group_concat(
    round(radians*180/PI())
    order by col_number separator ' ') as circle
FROM (
  SELECT
    t1.value AS col_number,
    t2.value AS row_number,
    @dx := (t1.value - (@size-1)/2) AS dx,
    @dy := ((@size-1)/2 - t2.value) AS dy,
    @abs_radians := IF(@dx = 0, PI()/2, (atan(abs(@dy/@dx)))) AS abs_radians,
    CASE
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians
    END AS radians
  FROM
    tinyint_asc t1,
    tinyint_asc t2,
    (select @size := 15) sel_size,
    (select @radius := (@size/2 - 1)) sel_radius
  WHERE
    t1.value < @size
    AND t2.value < @size) select_combinations
  GROUP BY row_number
;
+-------------------------------------------------------------+
| circle                                                      |
+-------------------------------------------------------------+
| 135 131 126 120 113 106 98 90 82 74 67 60 54 49 45          |
| 139 135 130 124 117 108 99 90 81 72 63 56 50 45 41          |
| 144 140 135 129 121 112 101 90 79 68 59 51 45 40 36         |
| 150 146 141 135 127 117 104 90 76 63 53 45 39 34 30         |
| 157 153 149 143 135 124 108 90 72 56 45 37 31 27 23         |
| 164 162 158 153 146 135 117 90 63 45 34 27 22 18 16         |
| 172 171 169 166 162 153 135 90 45 27 18 14 11 9 8           |
| 180 180 180 180 180 180 180 90 0 0 0 0 0 0 0                |
| 188 189 191 194 198 207 225 270 315 333 342 346 349 351 352 |
| 196 198 202 207 214 225 243 270 297 315 326 333 338 342 344 |
| 203 207 211 217 225 236 252 270 288 304 315 323 329 333 337 |
| 210 214 219 225 233 243 256 270 284 297 307 315 321 326 330 |
| 216 220 225 231 239 248 259 270 281 292 301 309 315 320 324 |
| 221 225 230 236 243 252 261 270 279 288 297 304 310 315 319 |
| 225 229 234 240 247 254 262 270 278 286 293 300 306 311 315 |
+-------------------------------------------------------------+

The above needs some formattign to present well, but that's not the purpose; I'm only showing the above to explain the steps taken.

Part 3: combining the two

Next step is probably the most significant one: we're going to present a rough, square, weird looking pie chart using the original values:

SELECT
  group_concat(
    (SELECT name_order FROM
      (
      SELECT
        name_order,
        name_column,
        value_column,
        accumulating_value,
        accumulating_value/@accumulating_value AS accumulating_value_ratio,
        2*PI()*accumulating_value/@accumulating_value AS accumulating_value_radians
      FROM (
        SELECT
          name_column,
          value_column,
          @name_order := @name_order+1 AS name_order,
          @accumulating_value := @accumulating_value+value_column AS accumulating_value,
          @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column
        FROM (
          SELECT name AS name_column, value AS value_column FROM sample_values2
          ) select_values,
          (SELECT @name_order := 0) select_name_order,
          (SELECT @accumulating_value := 0) select_accumulating_value,
          (SELECT @aggregated_name_column := '') select_aggregated_name_column
        ) select_accumulating_values
      ) select_for_radians
    WHERE accumulating_value_radians >= radians LIMIT 1
    )
    order by col_number separator ' ') as circle
FROM (
  SELECT
    t1.value AS col_number,
    t2.value AS row_number,
    @dx := (t1.value - (@size-1)/2) AS dx,
    @dy := ((@size-1)/2 - t2.value) AS dy,
    @abs_radians := IF(@dx = 0, PI()/2, (atan(abs(@dy/@dx)))) AS abs_radians,
    CASE
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians
    END AS radians
  FROM
    tinyint_asc t1,
    tinyint_asc t2,
    (select @size := 21) sel_size,
    (select @radius := (@size/2 - 1)) sel_radius
  WHERE
    t1.value < @size
    AND t2.value < @size) select_combinations
  GROUP BY row_number
;
+-------------------------------------------+
| circle                                    |
+-------------------------------------------+
| 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 2 |
| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 |
| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 |
| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 1 |
| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 1 1 |
| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 1 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 3 2 2 2 2 2 1 1 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 3 2 2 2 1 1 1 1 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 3 2 2 1 1 1 1 1 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 3 2 1 1 1 1 1 1 1 1 1 1 |
| 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |
+-------------------------------------------+

The interesting SQL trick is that everything goes within the GROUP_CONCAT clause. Instead of presenting the coordinate, we check on the enhanced values table, looking for the first row which has a greater angle (in radians) than the current pixel has. We then display 1, 2, etc. to denote the value.

The next step is actually very simple: instead of drawing the full square, limit to a circle!

SELECT
  group_concat(
    IF(round(sqrt(pow(col_number-(@size-1)/2, 2) + pow(row_number-(@size-1)/2, 2))) BETWEEN @radius/2 AND @radius,
    (SELECT name_order FROM
      (
      SELECT
        name_order,
        name_column,
        value_column,
        accumulating_value,
        accumulating_value/@accumulating_value AS accumulating_value_ratio,
        2*PI()*accumulating_value/@accumulating_value AS accumulating_value_radians
      FROM (
        SELECT
          name_column,
          value_column,
          @name_order := @name_order+1 AS name_order,
          @accumulating_value := @accumulating_value+value_column AS accumulating_value,
          @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column
        FROM (
          SELECT name AS name_column, value AS value_column FROM sample_values2
          ) select_values,
          (SELECT @name_order := 0) select_name_order,
          (SELECT @accumulating_value := 0) select_accumulating_value,
          (SELECT @aggregated_name_column := '') select_aggregated_name_column
        ) select_accumulating_values
      ) select_for_radians
    WHERE accumulating_value_radians >= radians LIMIT 1
    ), '-')
    order by col_number separator ' ') as circle
FROM (
  SELECT
    t1.value AS col_number,
    t2.value AS row_number,
    @dx := (t1.value - (@size-1)/2) AS dx,
    @dy := ((@size-1)/2 - t2.value) AS dy,
    @abs_radians := IF(@dx = 0, PI()/2, (atan(abs(@dy/@dx)))) AS abs_radians,
    CASE
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians
    END AS radians
  FROM
    tinyint_asc t1,
    tinyint_asc t2,
    (select @size := 21) sel_size,
    (select @radius := 7) sel_radius
  WHERE
    t1.value < @size
    AND t2.value < @size) select_combinations
  GROUP BY row_number
;
+-------------------------------------------+
| circle                                    |
+-------------------------------------------+
| - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - 2 2 2 2 2 - - - - - - - - |
| - - - - - - 3 3 3 2 2 2 2 2 2 - - - - - - |
| - - - - - 3 3 3 3 2 2 2 2 2 2 2 - - - - - |
| - - - - 3 3 3 3 3 2 2 2 2 2 2 2 1 - - - - |
| - - - - 3 3 3 3 3 - - - 2 2 2 1 1 - - - - |
| - - - 3 3 3 3 3 - - - - - 1 1 1 1 1 - - - |
| - - - 3 3 3 3 - - - - - - - 1 1 1 1 - - - |
| - - - 3 3 3 3 - - - - - - - 1 1 1 1 - - - |
| - - - 3 3 3 3 - - - - - - - 4 4 4 4 - - - |
| - - - 3 3 3 3 3 - - - - - 4 4 4 4 4 - - - |
| - - - - 3 3 4 4 4 - - - 4 4 4 4 4 - - - - |
| - - - - 3 4 4 4 4 4 4 4 4 4 4 4 4 - - - - |
| - - - - - 4 4 4 4 4 4 4 4 4 4 4 - - - - - |
| - - - - - - 4 4 4 4 4 4 4 4 4 - - - - - - |
| - - - - - - - - 4 4 4 4 4 - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - |
| - - - - - - - - - - - - - - - - - - - - - |
+-------------------------------------------+

That looks a lot more like a pie chart.

Part 4: doing the fancy work

We will now add (in one big step):

  • Stretching along the X-axis.
  • Condensing the spaces.
  • Coloring for the pie parts.
  • A legend.

The text in bold is the original query, and is the only thing you need to change in order to create your own pie charts.

SELECT
  group_concat(
    IF(round(sqrt(pow(col_number/@stretch-0.5-(@size-1)/2, 2) + pow(row_number-(@size-1)/2, 2))) BETWEEN @radius*2/3 AND @radius,
    (SELECT SUBSTRING(@colors, name_order, 1) FROM
      (
      SELECT
        name_order,
        name_column,
        value_column,
        accumulating_value,
        accumulating_value/@accumulating_value AS accumulating_value_ratio,
        @aggregated_data := CONCAT(@aggregated_data, name_column, ': ', value_column, ' (', ROUND(100*value_column/@accumulating_value), '%)', '|') AS aggregated_name_column,
        2*PI()*accumulating_value/@accumulating_value AS accumulating_value_radians
      FROM (
        SELECT
          name_column,
          value_column,
          @name_order := @name_order+1 AS name_order,
          @accumulating_value := @accumulating_value+value_column AS accumulating_value
        FROM (
          SELECT name AS name_column, value AS value_column FROM sample_values2 LIMIT 4
          ) select_values,
          (SELECT @name_order := 0) select_name_order,
          (SELECT @accumulating_value := 0) select_accumulating_value,
          (SELECT @aggregated_data := '') select_aggregated_name_column
        ) select_accumulating_values
      ) select_for_radians
    WHERE accumulating_value_radians >= radians LIMIT 1
    ), ' ')
    order by col_number separator '') as pie
FROM (
  SELECT
    t1.value AS col_number,
    t2.value AS row_number,
    @dx := (t1.value/@stretch - (@size-1)/2) AS dx,
    @dy := ((@size-1)/2 - t2.value) AS dy,
    @abs_radians := IF(@dx = 0, PI()/2, (atan(abs(@dy/@dx)))) AS abs_radians,
    CASE
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians
      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians
      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians
    END AS radians
  FROM
    tinyint_asc t1,
    tinyint_asc t2,
    (select @size := 23) sel_size,
    (select @radius := (@size/2 - 1)) sel_radius,
    (select @stretch := 4) sel_stretch,
    (select @colors := '#;o:X"@+-=123456789abcdef') sel_colors
  WHERE
    t1.value < @size*@stretch
    AND t2.value < @size) select_combinations
  GROUP BY row_number
UNION ALL
  SELECT
    CONCAT(
      REPEAT(SUBSTRING(@colors, value, 1), 2),
      '  ',
      SUBSTRING_INDEX(SUBSTRING_INDEX(@aggregated_data, '|', value), '|', -1)
    )
  FROM
    tinyint_asc
  WHERE
    value BETWEEN 1 AND @name_order
;
+----------------------------------------------------------------------------------------------+
| pie                                                                                          |
+----------------------------------------------------------------------------------------------+
|                                                                                              |
|                                   ;;;;;;;;;;;;;;;;;;;;;;;;;                                  |
|                          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%)                                                                           |
+----------------------------------------------------------------------------------------------+

Making the legend is by itself an interesting hack: aggregating an unknown number of rows using a session user variable, then splitting it again. Of course, this works well if we only have a small number of rows (values), as we expect in our case.

Showcase

OK. Why? Why?

Here are three charts I hope will convince the skeptic reader:

Given the sakila database, list the 8 largest tables (size in KB):

+------------------------------------------------------------------------------+
| pie_chart                                                                    |
+------------------------------------------------------------------------------+
|                                                                              |
|                            #######################                           |
|                    #######################################                   |
|               #################################################              |
|            ##################                   ##################           |
|         ###############                               ###############        |
|        ;;###########                                     #############       |
|      ;;;;;;;;;;;;;                                         #############     |
|      ;;;;;;;;;;;;                                           ############     |
|     ;;;;;;;;;;;;;                                           #############    |
|      ;;;;;;;;;;;;                                           @@@@@@@@@@@@     |
|      ;;;;;;;;;;;;;                                         """""""""""""     |
|        ;;;;;;;;;;;;;                                     XX"""""""""""       |
|         ;;;;;;;;;;;;;;;                               XXXXXXXXXXXXX""        |
|            ;;;;;;;;;;;;;;;;;;                   ::::::::::XXXXXXXX           |
|               ;;;;;;;;;;;;;;;;;;;;;;;;;;oooooooooo::::::::::::X              |
|                    ;;;;;;;;;;;;;;;;;;;;;;ooooooooooo::::::                   |
|                            ;;;;;;;;;;;;;;ooooooooo                           |
|                                                                              |
| ##  rental: 2850816 (43%)                                                    |
| ;;  payment: 2228224 (34%)                                                   |
| oo  inventory: 376832 (6%)                                                   |
| ::  film_text: 325440 (5%)                                                   |
| XX  film: 278528 (4%)                                                        |
| ""  film_actor: 278528 (4%)                                                  |
| @@  customer: 131072 (2%)                                                    |
| ++  staff: 98304 (1%)                                                        |
+------------------------------------------------------------------------------+

How much disk space does each storage engine consume (sum table size per engine)?

+------------------------------------------------------------------------------+
| pie_chart                                                                    |
+------------------------------------------------------------------------------+
|                                                                              |
|                            #######################                           |
|                    #######################################                   |
|               #################################################              |
|            ##################                   ##################           |
|         ###############                               ###############        |
|        #############                                     #############       |
|      #############                                         #############     |
|      ############                                           ############     |
|     #############                                           #############    |
|      ############                                           oooooooooooo     |
|      #############                                         ;;;;;;;;;;;oo     |
|        #############                                     ;;;;;;;;;;;;;       |
|         ###############                               ;;;;;;;;;;;;;;;        |
|            ##################                   #;;;;;;;;;;;;;;;;;           |
|               #####################################;;;;;;;;;;;;              |
|                    ###################################;;;;                   |
|                            #######################                           |
|                                                                              |
| ##  InnoDB: 1908732 (84%)                                                    |
| ;;  MyISAM: 284074 (12%)                                                     |
| oo  ARCHIVE: 84276 (4%)                                                      |
+------------------------------------------------------------------------------+

What were the most popular DMLs during the last 10 seconds?

+------------------------------------------------------------------------------+
| pie_chart                                                                    |
+------------------------------------------------------------------------------+
|                                                                              |
|                            #######################                           |
|                    #######################################                   |
|               #################################################              |
|            ##################                   ##################           |
|         ###############                               ###############        |
|        #############                                     #############       |
|      #############                                         #############     |
|      ############                                           ############     |
|     #############                                           #############    |
|      ############                                           oooo::::::::     |
|      #############                                         ooooooooooooo     |
|        #############                                     ooooooooooooo       |
|         ###############                               ooooooooooooooo        |
|            #################;                   ;;;;;;;;;ooooooooo           |
|               #############;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;oo              |
|                    ######;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                   |
|                            ;;;;;;;;;;;;;;;;;;;;;;;                           |
|                                                                              |
| ##  com_select: 1876 (69%)                                                   |
| ;;  com_insert: 514 (19%)                                                    |
| oo  com_delete: 277 (10%)                                                    |
| ::  com_update: 63 (2%)                                                      |
+------------------------------------------------------------------------------+

Conclusion

ASCII graphics always look funny; some would say the same about top, wgetcal, etc. (should I even mention lynx?)

I think it is possible to do most common charting with SQL: I've already shown how to do horizontal graphs and pie charts. Multi-column bar charts can also be worked out. These are not meant as a permanent solution; but it's good to be able to visualize some values without having to install Nagios (along with apache, php, drivers, etc.), or otherwise exporting table, copying to desktop machines, loading into OpenOffice impress, generating graphs.

Sometimes you just need an immediate overlook. This is where I find SQL charting to be useful.

Sure, there are Perl and Python solutions for that; that's easily achieved as well. But doing it from with the MySQL client gives, in my opinion, a level of confidence: you'll always be able to produce the graph; perl-DBD-MySQL or no perl-DBD-MySQL; Linux or Windows.

Besides, it was fun doing it.

tags: ,
posted in MySQL by shlomi

« | »

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

78 Comments to "SQL pie chart"

  1. Mark Leith wrote:

    This is awesome.

    You're crazy of course, but this is awesome none the less! :P

  2. Domas wrote:

    Riiiight! :-) HTML5 canvas is not needed, when there's

  3. Domas wrote:

    ... there's <PRE>

  4. George Entenman (ge) 's status on Wednesday, 12-Aug-09 11:48:18 UTC - Identi.ca wrote:

    [...] human creativity never ceases to amaze: http://code.openark.org/blog/mysql/sql-pie-chart [...]

  5. shlomi wrote:

    @Mark,
    Thank you - the doctors released me this very morning. Said I wasn't dangerous.

    @Domas,
    I'm sorry, I completely lost you; I'm not sure if you mean you like it or hate it :D

  6. robo47 wrote:

    Really awesome ...
    Nice idea and implementation.

    Never thought that this would be possible :)

  7. Jan Kneschke wrote:

    Please, oh, please: implemented something like http://aa-project.sourceforge.net/bb/ on top of this.

    You have the 2D-rendering, now just loop it at 25frames/s and add a some extra transformations :)

  8. Daniel E. Renfer (duck1123) 's status on Wednesday, 12-Aug-09 14:38:09 UTC - Identi.ca wrote:

    [...] art pie charts in #SQL. http://code.openark.org/blog/mysql/sql-pie-chart So crazy its [...]

  9. Wes Hardaker wrote:

    Ha! That makes everything I've ever done look paltry. Thanks for that. I think.

  10. Gord J wrote:

    Bent. Brilliant, but bent.

  11. shlomi wrote:

    @Jan:
    Seriously??

    @Wes:
    thank you too, I guess ;)

    @Gord:
    Guilty as charged!

    Seems like crazy posts deserve crazy comments :D

  12. familiar wrote:

    What does your family say ;)
    i think of them ;)
    Seriously, what do you eat?

  13. Jay Pipes wrote:

    Ha! Awesomeness.

  14. SQL pie chart | code.openark.org « Netcrema - creme de la social news via digg + delicious + stumpleupon + reddit wrote:

    [...] SQL pie chart | code.openark.orgcode.openark.org [...]

  15. Ben wrote:

    What version of mysql do u use? I'm having trouble on 5.0.27

  16. sss wrote:

    You are crazy or unemployed, but Everyone likes it

  17. math0ne wrote:

    Very impressive!

    But yeah, your crazy.

  18. ASCII_Man wrote:

    Great success!

  19. Dave wrote:

    Man!

    Would the term "doughnut chart" be more appropriate?

    Thanks!

  20. Ronald Bradford wrote:

    I need plenty of help in real practical pursuits, like improved MySQL monitoring and instrumentation and better benchmarking of real-life systems.

    When you have more free time.

  21. shlomi wrote:

    @Ben:
    5.0. If you describe exactly what problems you are experiencing, it would help.

    @Everyone:
    I get the message. I'll just say goodbye to my loving family and commit myself to a good hospital (I'm eating well).

    @Ronald:
    After all these comments, I can;t say for sure if you mean it or if you're joking...?

  22. Håvard Eide (eide) 's status on Thursday, 13-Aug-09 06:02:32 UTC - Identi.ca wrote:

    [...] why use fancy graphics when you can have pie charts in sql: http://code.openark.org/blog/mysql/sql-pie-chart [...]

  23. Nils wrote:

    mhmmm.... pie.

  24. Wikidkaka wrote:

    I really did not read through the query of yours. (Dont believe many people would have done). But you kick ass!!

  25. Pie Charts with MySQL at chris ramsay wrote:

    [...] Look no further than here… Shlomi Noach is clearly a genius! [...]

  26. Raja Sekharan wrote:

    This is the most wicked thing I've ever seen anyone do with SQL! awesome!

  27. Chuck wrote:

    You have too much time on your hands. :)

    I am somewhat new to MySQL and learned a lot about it other than just how to create pie charts using pure sql from this article.

  28. Chuck wrote:

    BTW if I wanted a pie chart from data store in mysql, I think I'd still probably just use a spreadsheet program like excel or OOo.

  29. shlomi wrote:

    @Chuck
    And you would be right in doing so. I'm very glad you learned something - that would be the best thing anyone can tell me!

    @All
    Hmmm... Right... this actually isn't a pie but a donut... Guess I'll need to start all over again!

  30. Rafu wrote:

    http://rafudb.blogspot.com/2009/08/sql-donut.html [...] Oracle version [...]

  31. Maurizio Napolitano (napo) 's status on Friday, 14-Aug-09 09:59:50 UTC - Identi.ca wrote:

    [...] http://code.openark.org/blog/mysql/sql-pie-chart [...]

  32. Log Buffer #158: a Carnival of the Vanities for DBAs | Pythian Group Blog wrote:

    [...] don’t want to shard, but you might want a slice of SQL pie—Shlomi Noach’s SQL-generated pie chart, that [...]

  33. Doug wrote:

    This is the greatest thing next to the wheel! Period

  34. 15-Aug-2009 | MohanArun.com wrote:

    [...] SQL Pie Chart – Interesting – how would you create a Pie chart using SQL alone? (with suitable data of course) [...]

  35. tech: Four short links: 14 August 2009 | tech3bite wrote:

    [...] SQL Pie Chart — an ASCII pie chart, drawn by SQL code. Horrifying and yet inspiring. Compare to PostgreSQL code to produce ASCII Mandelbrot set. (via jdub on Twitter and Simon Willison) [...]

  36. Gráficas circulares SQL « /var/log/jynus wrote:

    [...] es el segundo post de creación de gráficos con SQL de Shlomi Noach. Puedes leer también el original en inglés en su blog y mi post [...]

  37. Gerg wrote:

    As an experiment I translated this into Postgres (not sure how well this will come out in the comments, I can send you the file)

    \set width  80
    \set height 25
    \set radius 1.0
    \set colours '''#;o:X"@+-=123456789abcdef'''
    
    
    with slices as (
     select  cast(row_number() over () as integer) as slice,
             name, 
    	 value,
    	 100.0 * value / sum(value) over () as percentage,
    	 2*PI() * sum(value) over (rows unbounded preceding) / sum(value) over () as radians
      from (values ('red',1),('blue',2),('orange',3),('white',4)) as data(name,value))
    (select array_to_string(array_agg(c),'') from (
    select x, y,
           case when not (sqrt(pow(x, 2) + pow(y, 2)) BETWEEN  :radius*1/10 AND :radius)
                then ' '
                else substring(:colours,
                               (select min(slice) from slices where radians >= PI() + atan2(y,-x)),
                               1)
                end as c
      from (select 2.0*generate_series(0,:width)/:width-1.0) as x(x),
           (select 2.0*generate_series(0,:height)/:height-1.0) as y(y)
     order by y,x
    ) as xy
     group by y
     order by y)
    union all 
    select repeat(substring(:colours,slice,1), 2) || '  ' || 
             name || ': ' || 
             value || '  (' || round(percentage,0) || '%)' 
      from slices;
    
  38. Gerg wrote:

    I think all that stuff with abs() and then handling each quadrants as separate cases is unnecessary. You can just use -x if you want to flip it around the way you did. Why did you want to flip it around anyways?

  39. shlomi wrote:

    @Gerg,

    Ha! I wasn't even aware of ATAN2() which gets 2 arguments... I did the workaround based on the one argument ATAN()...

    Thanks,
    Shlomi

    PS have formatted your code in comment

  40. Dagbok för 15 August 2009 | En sur karamell wrote:

    [...] Delade SQL pie chart | code.openark.org [...]

  41. links for 2009-08-16 | burningCat wrote:

    [...] SQL pie chart My other half says I’m losing it. But I think that as an enthusiast kernel developer she doesn’t have the right to criticize people. (”I like user space better!” – she exclaims upon reading this). [...]

  42. Houďasovo » Blog Archive » Madness? This… is… SQLPARTA! wrote:

    [...] http://code.openark.org/blog/mysql/sql-pie-chart [...]

  43. Gerg wrote:

    Do you mind if I put this on our gallery of sql snippets for Postgres?

  44. Thibauld Favre (thibauld) 's status on Sunday, 16-Aug-09 17:10:04 UTC - Identi.ca wrote:

    [...] http://code.openark.org/blog/mysql/sql-pie-chart [...]

  45. Vinay Srivastava wrote:

    This is the most wicked thing I’ve ever seen anyone do with SQL! awesome!

  46. Andrii V. Mishkovskyi (mishok13) 's status on Monday, 17-Aug-09 09:44:51 UTC - Identi.ca wrote:

    [...] crazy stuff to say the least http://code.openark.org/blog/mysql/sql-pie-chart [...]

  47. meneame.net wrote:

    Generar gráficas tipo tarta en SQL [eng]...

    Se pueden crear gráficas tipo tarta (pie chart) con los datos de una base de datos. Todo hecho únicamente con sentencias SQL y desde consola. via: ocio.barrapunto.com/article.pl?sid=09/08/17/0712203...

  48. TheVoo wrote:

    Hi,

    very nice hack! Still, I found the ASCII representation a bit hard to read and wondered if it was possible to add color to the pie chart? And indeed it is, thanks to ANSI Terminal Control Escape Sequences. Below is a changed version that will output the pie in color to your ANSI terminal. Only drawback: The column headers will be messed up, but perhaps someone find a nice solution to fix that :)

    Only a few lines needed to change: Lines 4, 29, 50, 58, 59.

    @shlomi: keep up with this creative use of the mysql cli!

    SELECT
      group_concat(
        IF(round(sqrt(pow(col_number/@stretch-0.5-(@size-1)/2, 2) + pow(row_number-(@size-1)/2, 2))) BETWEEN @radius*2/3 AND @radius,
        (SELECT SUBSTRING(@colors, name_order*6+1, 6) FROM
          (
          SELECT
            name_order,
            name_column,
            value_column,
            accumulating_value,
            accumulating_value/@accumulating_value AS accumulating_value_ratio,
            @aggregated_data := CONCAT(@aggregated_data, name_column, ': ', value_column, ' (', ROUND(100*value_column/@accumulating_value), '%)', '|') AS aggregated_name_column,
            2*PI()*accumulating_value/@accumulating_value AS accumulating_value_radians
          FROM (
            SELECT
              name_column,
              value_column,
              @name_order := @name_order+1 AS name_order,
              @accumulating_value := @accumulating_value+value_column AS accumulating_value
            FROM (
              SELECT name AS name_column, value AS value_column FROM sample_values2 LIMIT 4
              ) select_values,
              (SELECT @name_order := 0) select_name_order,
              (SELECT @accumulating_value := 0) select_accumulating_value,
              (SELECT @aggregated_data := '') select_aggregated_name_column
            ) select_accumulating_values
          ) select_for_radians
        WHERE accumulating_value_radians >= radians LIMIT 1
        ), concat( 0x1b, '[37m ') )
        order by col_number separator '') as pie
    FROM (
      SELECT
        t1.value AS col_number,
        t2.value AS row_number,
        @dx := (t1.value/@stretch - (@size-1)/2) AS dx,
        @dy := ((@size-1)/2 - t2.value) AS dy,
        @abs_radians := IF(@dx = 0, PI()/2, (atan(abs(@dy/@dx)))) AS abs_radians,
        CASE
          WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians
          WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians
          WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians
          WHEN SIGN(@dy) = 0 THEN 2*PI()-@abs_radians
        END AS radians
      FROM
        tinyint_asc t1,
        tinyint_asc t2,
        (select @size := 23) sel_size,
        (select @radius := (@size/2 - 1)) sel_radius,
        (select @stretch := 4) sel_stretch,
        (select @colors := concat(0x1b,'[31m*',0x1b,'[32m*',0x1b,'[33m*',0x1b,'[34m*',0x1b,'[35m*',0x1b,'[36m*') ) sel_colors
      WHERE
        t1.value < @size*@stretch
        AND t2.value < @size) select_combinations
      GROUP BY row_number
    UNION ALL
      SELECT
        CONCAT(
          REPEAT(SUBSTRING(@colors, value*6+1, 6), 2),
          concat( 0x1b, '[0m  '),
          SUBSTRING_INDEX(SUBSTRING_INDEX(@aggregated_data, '|', value), '|', -1)
        )
      FROM
        tinyint_asc
      WHERE
        value BETWEEN 1 AND @name_order
    ;
    
  49. Martijn Lafeber wrote:

    I have to agree that you're completely crazy, but not dangerous.

    Meanwhile, I'm working hard to create a 3D version of this.

  50. Willie wrote:

    Amazing is an understatment - it's epic dude. Seriously epic!

  51. shlomi wrote:

    @Gerg:
    Please do put it on postgres snippets. I would appreciate source/author credit.

    Regards

  52. shlomi wrote:

    @TheVoo (#48)

    Wonderful! Colors are magical :)

    Thanks for the tips,

    Shlomi

  53. Roland Bouman wrote:

    Hi Shlomi!

    I take a 2 week holiday, and you do what again...?!

    :)

    Seriously, I don't think it's a practical solution, but I'd like to credit you with more than being just crazy and having too much time on your hands. I contemplated creating MySQL pie charts after reading this post:

    http://technology.amis.nl/blog/398/pie-charts-in-sql-how-pathetic-can-you-get

    but I broke my head over it and deemed it impossible. So I stand corrected and feel humbled by your creativity and achievement. It's great, it really is :)

  54. shlomi wrote:

    Hi Roland,

    Nice of you to have dropped by! (Are you reading 2 weeks worth of planet mysql now?)

    I've also seen this post, but only just before I was ready to publish mine. It uses an altogether different method to create the charts.

    It's the common belief that there's nothing practical about SQL pie charts. I got so many funny comments (outside this post) like: "Make useless pie charts even more so with SQL"; and the majority think I'm crazy or evil-geek.

    I'm willing to accept that it's not as useful as online ALTER TABLE (which I've also implemented). I'm also willing to accept I'm a geek (knew that since 2nd grade :D).

    The techniques which served me here -- I still can't be certain where they can serve me again, but they strike me as very useful.

    It was actually great fun to work this out (another funny comment: "This guy get more fun from SQL than he should"), and I confess I enjoy the buzz this made; some people liked, some didn't, but many wanted to share their opinion.

    Regards,
    Shlomi

  55. links for 2009-08-24 | Chris F. Waigl wrote:

    [...] SQL pie chart | code.openark.org OK, this is just silly. But an interesting SQL walkthrough. Maybe. (tags: sql mysql visualization coding funny) [...]

  56. Chris wrote:

    You are a bad ass!!!!!

  57. wow..!!! a pie chart using SQL… http:… « tweets lead to blog wrote:

    [...] August 28, 2009 Permalink | Reply Tags: pie-chart, SQL wow..!!! a pie chart using SQL… http://code.openark.org/blog/mysql/sql-pie-chart [...]

  58. Marcus wrote:

    I'm pretty sure linking this page to any employer of your liking will get you hired in a heartbeat. This, my friend, is _FREAKING_ awesome, not to mention sick. 100% bad-ass overload. I couldn't start comprehend how to build such queries. This is an absolute display of mastering the SQL database. You rock.

  59. Jared wrote:

    Just because you can, doesn't mean you should.

  60. MySAR, a Sidekick for Other Monitoring Tools | Pythian Group Blog wrote:

    [...] was to create a graphic to visualize the results. Taking the ideas from Shlomi Noach’s blog, SQL pie chart, I decided to try and create the charts using the Google Charts [...]

  61. fabijoe wrote:

    I wrote a sql procedure to create svg piecharts, based on a pichert routine learned from the web.
    Procedure uses the piedata varray to store data
    initialized for the demo in the main program (you may, of course, initialize it from a database table, too).
    Chart is generated row-by-row into a table named text with one field named text (about 500 caracter long varchar2 field).
    Then you have to spool it's contents into a file with .svg extension, your browser with an svg plugin will draw it.
    Procedure piechart maybe can be created as a stored procedure, too, to use it from within other plsql programs.

    set linesize 500
    set pagesize 0
    declare
        type piedata_varray is varray(11) of number;
        type colors_varray is varray(11) of varchar2(10);
        piedata  piedata_varray:=piedata_varray(0,0,0,0,0,0,0,0,0,0,0);
        colours colors_varray:=colors_varray('red','orange','yellow','green','blue',
                                             'red','orange','yellow','green','blue','white');
        sliceno number;     
        width number:=400; --canvas size
        height number:=400; --canvas size
        centerx number:=200; --pie center x koord
        centery number:=200; --pie center y koor
        radius number:=190; --pie radius
    
        procedure piechart(piedata in piedata_varray, sliceno in number,cx in number, cy in number, radius in number)
        is
            chartelem varchar2(500):= '';
            i number;
            sumdata number:=0;
            deg     number:=0; -- one degree
            jung    number;  -- necessary to test for arc type, whether it is longer then 180 deg
            dx      number;
            dy      number;
            ax      number;
            adx     number;
            ay      number;
            ady     number;
            oldangle  number;  -- meddig jutottunk el a szögekkel.
            angle     number;  --mekkora lesz a következő szelet szöge
            x   number;
            y    number;
            colour varchar2(10);
            laf number;
        begin
            delete from text;
            insert into text values (0, '');
            insert into text values (0, '');
            insert into vb.text values (0, 'Pie chart');
            insert into text values (0, 'Picture of a pie chart');
            i:=1;
            loop
               exit when i>sliceno;
               sumdata:=sumdata+piedata(i);
               i:=i+1;
            end loop;
            deg := sumdata/360; -- one degree
    	    jung := sumdata/2; 
    	
    	/* Data for grid, circle, and slices */ 
    	
    	     dx := radius; -- Starting point: 
    	     dy := 0; -- first slice starts in the East
    	     oldangle := 0;
    	/* Loop through the slices */
             i:=1;
             loop
                    exit when i>sliceno;
    		        angle := oldangle + piedata(i)/deg; -- cumulative angle
    		        x := cos(0.0174532925 *angle) * radius; -- x of arc's end point
    		        y := sin(0.0174532925 *angle) * radius; -- y of arc's end point --0.0174532925
    		        colour := colours(i);
    		        if piedata(i) > jung then
    			    -- arc spans more than 180 degrees
    			        laf := 1;
    		        else 
    			        laf := 0;
                    end if;    
    		        ax := cx + x; -- absolute x
    		        ay := cy + y; -- absolute y
    		        adx := cx + dx; -- absolute dx
    		        ady := cy + dy; -- absolute dy
    		        chartelem := '';
                     insert into vb.text values(0,chartelem);
                    dx := x; --old end points become new starting point
    		        dy := y; --id.
    		        oldangle := angle;
                    i:=i+1;
    	    end loop;
            insert into text values (0,'');
            commit;       
        end;
    begin
        /*sample values*/
         sliceno:=5;
         piedata(1):=18;
         piedata(2):=26;
         piedata(3):=50;
         piedata(4):=43;
         piedata(5):=35;
         /* Draw and output the SVG file. */
        piechart(piedata,sliceno,centerx,centery,radius);
        commit;
    END;
    /
    spool c:\work\svg\piechart.svg
    select text from text;
    spool off
    
  62. links for 2009-11-03 | Glorified Monkey wrote:

    [...] SQL pie chart | code.openark.org (tags: sql database visualization mysql programming piechart) [...]

  63. Eric Hynds wrote:

    Very impressive!

  64. R M wrote:

    An awesome implementation of SQL

    Yep - U R nutz - but brilliant

  65. Code Classics | Hackers' Library wrote:

    [...] Model clause Solving a Sudoku using Recursive Subquery Factoring of Oracle Database 11g Release 2 SQL-generated pie chart. (Single [...]

  66. himadri wrote:

    which planet do you belong to ..

    this is insanely brilliant .... very very kool .. great stuff ..

    if only I could have half the intelligence you have ..

  67. Geek overload – SQL Ascii Pie Chart – ht… « Geek Notes wrote:

    [...] overload – SQL Ascii Pie Chart – http://code.openark.org/blog/mysql/sql-pie-chart var addthis_pub = ''; var addthis_language = 'en';var addthis_options = 'email, favorites, digg, [...]

  68. JSolutions.se » SQL for yummies wrote:

    [...] eller en dåligt AA-kodad film. Nöjer man sig med stillbilder men inte vill ha random brus kanske cirkeldiagram (pie chart) i SQL-prompten vore [...]

  69. Darren Dignam wrote:

    Thanks. I liked this a lot.
    I have mentioned this work over at my own blog, and also a method for colouring the graph using javascript. I tweaked the SQL to generate non-breaking spaces (otherwise a browser ruins the shape), and filled in the middle of the donut!

    http://blocsoft.com/blog/graph.asp

  70. shlomi wrote:

    @Darren,

    Thanks; nice blog!

  71. Dave wrote:

    Really nice idea and implementation. Thanks for sharing.

  72. Animal wrote:

    Feckin' hell! You're clearly insane! Hugely talented, but quite mad!

  73. Oracle Fusion hybrid BI signals end of Disco era | Dataweave Blog wrote:

    [...] thick pile of paper, covered in uniformly spaced alphanumerics. Charts and graphics were rare, and ascii pie charts were a programming miracle (and possibly still are). Analytics involved someone in operations or [...]

  74. A Big Bag of Epic Awesomeness | Mark Leith wrote:

    [...] So I had to find another way to represent this nicely, otherwise it’s just not that obvious how freaking awesome of an advancement in MySQL statement tracing this is.. Yet I love making SQL do things for me without having to wrap a program around it, so I thought I’d do another take on Shlomi Noach’s graphs with SQL. [...]

  75. dinesh wrote:

    Fantastic ...

  76. SQL « Let's Explore Programming Languages wrote:

    [...] Cleverer people may be able to bastardize SQL further; I am not a clever man. For a real crime against SQLmanity, check out this neato pie chart (including legend) done entirely in SQL. [...]

  77. Four short links: 14 August 2009 - O'Reilly Radar wrote:

    [...] SQL Pie Chart — an ASCII pie chart, drawn by SQL code. Horrifying and yet inspiring. Compare to PostgreSQL code to produce ASCII Mandelbrot set. (via jdub on Twitter and Simon Willison) [...]

  78. John wrote:

    Very nice hack! Can we get some colour? :)

    I decided to have a go at charting the same data using sqlchart:
    http://www.sqldashboards.com/b/sql-pie-chart-mysql/

    it doesn't take changing the mysql commands much. Not as awesome as your hack but it works.

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org