{"id":1093,"date":"2009-08-12T12:49:41","date_gmt":"2009-08-12T10:49:41","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1093"},"modified":"2009-08-12T12:49:41","modified_gmt":"2009-08-12T10:49:41","slug":"sql-pie-chart","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-pie-chart","title":{"rendered":"SQL pie chart"},"content":{"rendered":"

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

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

\n
+----------------------------------------------------------------------+\r\n| pie_chart                                                            |\r\n+----------------------------------------------------------------------+\r\n|                                                                      |\r\n|                         ;;;;;;;;;;;;;;;;;;;;;                        |\r\n|                  oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;                 |\r\n|             oooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;            |\r\n|          ooooooooooooooooo                 ;;;;;;;;;;;;#####         |\r\n|        oooooooooooooo                           ;#############       |\r\n|       oooooooooooo                                 ############      |\r\n|      oooooooooooo                                   ############     |\r\n|      ooooooooooo                                     ###########     |\r\n|      oooooooooooo                                   ::::::::::::     |\r\n|       oooooooooooo                                 ::::::::::::      |\r\n|        ooooooooo:::::                           ::::::::::::::       |\r\n|          o::::::::::::::::                 :::::::::::::::::         |\r\n|             :::::::::::::::::::::::::::::::::::::::::::::            |\r\n|                  :::::::::::::::::::::::::::::::::::                 |\r\n|                         :::::::::::::::::::::                        |\r\n|                                                                      |\r\n| ##  red: 1 (10%)                                                     |\r\n| ;;  blue: 2 (20%)                                                    |\r\n| oo  orange: 3 (30%)                                                  |\r\n| ::  white: 4 (40%)                                                   |\r\n+----------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Requirements<\/h4>\n
We need a generic query, which returns at least these two columns: name_column<\/strong> and value_column<\/strong>. For example, the following query will do:<\/p>\n
\n
SELECT name AS name_column, value AS value_column FROM sample_values2;\r\n+-------------+--------------+\r\n| name_column | value_column |\r\n+-------------+--------------+\r\n| red         |            1 |\r\n| blue        |            2 |\r\n| orange      |            3 |\r\n| white       |            4 |\r\n+-------------+--------------+<\/pre>\n<\/blockquote>\n
Find sample data in pie_data.sql<\/a>.<\/p>\n
Part 1: expanding the original query<\/h4>\n
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:<\/p>\n
\n
SELECT\r\n  name_column,\r\n  value_column,\r\n  @accumulating_value := @accumulating_value+value_column AS accumulating_value\r\nFROM (\r\n  SELECT name AS name_column, value AS value_column FROM sample_values2\r\n  ) select_values,\r\n  (SELECT @accumulating_value := 0) select_accumulating_value\r\n;\r\n+-------------+--------------+--------------------+\r\n| name_column | value_column | accumulating_value |\r\n+-------------+--------------+--------------------+\r\n| red         |            1 |                  1 |\r\n| blue        |            2 |                  3 |\r\n| orange      |            3 |                  6 |\r\n| white       |            4 |                 10 |\r\n+-------------+--------------+--------------------+<\/pre>\n<\/blockquote>\n
Next, we calculate ratio of accumulating value, and present it both in [0..1] range, as well as in [0..2*PI] (radians):<\/p>\n
\n
SELECT\r\n  name_order,\r\n  name_column,\r\n  value_column,\r\n  accumulating_value,\r\n  accumulating_value\/@accumulating_value AS accumulating_value_ratio,\r\n  2*PI()*accumulating_value\/@accumulating_value AS accumulating_value_radians\r\nFROM (\r\n  SELECT\r\n    name_column,\r\n    value_column,\r\n    @name_order := @name_order+1 AS name_order,\r\n    @accumulating_value := @accumulating_value+value_column AS accumulating_value,\r\n    @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column\r\n  FROM (\r\n    SELECT name AS name_column, value AS value_column FROM sample_values2\r\n    ) select_values,\r\n    (SELECT @name_order := 0) select_name_order,\r\n    (SELECT @accumulating_value := 0) select_accumulating_value,\r\n    (SELECT @aggregated_name_column := '') select_aggregated_name_column\r\n  ) select_accumulating_values\r\n;\r\n+------------+-------------+--------------+--------------------+--------------------------+----------------------------+\r\n| name_order | name_column | value_column | accumulating_value | accumulating_value_ratio | accumulating_value_radians |\r\n+------------+-------------+--------------+--------------------+--------------------------+----------------------------+\r\n|          1 | red         |            1 |                  1 |                      0.1 |           0.62831853071796 |\r\n|          2 | blue        |            2 |                  3 |                      0.3 |            1.8849555921539 |\r\n|          3 | orange      |            3 |                  6 |                      0.6 |            3.7699111843078 |\r\n|          4 | white       |            4 |                 10 |                        1 |            6.2831853071796 |\r\n+------------+-------------+--------------+--------------------+--------------------------+----------------------------+<\/pre>\n<\/blockquote>\n
The radians value will help us decide where in the pie chart lies each value.<\/p>\n
Part 2: behind the scenes of the pie chart<\/h4>\n
We now explain how the pie chart works. Later on we combine with Part 1<\/strong>, to produce the complete chart.<\/p>\n
We first generate a coordinates system (see SQL graphics<\/a>):<\/p>\n
\n
SELECT\r\n  GROUP_CONCAT(CONCAT(t2.value,'.',t1.value) order by t1.value separator ' ') as circle\r\nFROM\r\n  tinyint_asc t1,\r\n  tinyint_asc t2,\r\n  (select @size := 10) sel_size,\r\n  (select @radius := (@size\/2 - 1)) sel_radius\r\nWHERE\r\n  t1.value < @size\r\n  AND t2.value < @size\r\nGROUP BY t2.value\r\n;\r\n+-----------------------------------------+\r\n| circle                                  |\r\n+-----------------------------------------+\r\n| 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 |\r\n| 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 |\r\n| 2.0 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 |\r\n| 3.0 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 |\r\n| 4.0 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 |\r\n| 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 |\r\n| 6.0 6.1 6.2 6.3 6.4 6.5 6.6 6.7 6.8 6.9 |\r\n| 7.0 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.9 |\r\n| 8.0 8.1 8.2 8.3 8.4 8.5 8.6 8.7 8.8 8.9 |\r\n| 9.0 9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 |\r\n+-----------------------------------------+<\/pre>\n<\/blockquote>\n
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.<\/p>\n
\n
SELECT\r\n  group_concat(\r\n    round(radians*180\/PI())\r\n    order by col_number separator ' ') as circle\r\nFROM (\r\n  SELECT\r\n    t1.value AS col_number,\r\n    t2.value AS row_number,\r\n    @dx := (t1.value - (@size-1)\/2) AS dx,\r\n    @dy := ((@size-1)\/2 - t2.value) AS dy,\r\n    @abs_radians := IF(@dx = 0, PI()\/2, (atan(abs(@dy\/@dx)))) AS abs_radians,\r\n    CASE\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians\r\n    END AS radians\r\n  FROM\r\n    tinyint_asc t1,\r\n    tinyint_asc t2,\r\n    (select @size := 15) sel_size,\r\n    (select @radius := (@size\/2 - 1)) sel_radius\r\n  WHERE\r\n    t1.value < @size\r\n    AND t2.value < @size) select_combinations\r\n  GROUP BY row_number\r\n;\r\n+-------------------------------------------------------------+\r\n| circle                                                      |\r\n+-------------------------------------------------------------+\r\n| 135 131 126 120 113 106 98 90 82 74 67 60 54 49 45          |\r\n| 139 135 130 124 117 108 99 90 81 72 63 56 50 45 41          |\r\n| 144 140 135 129 121 112 101 90 79 68 59 51 45 40 36         |\r\n| 150 146 141 135 127 117 104 90 76 63 53 45 39 34 30         |\r\n| 157 153 149 143 135 124 108 90 72 56 45 37 31 27 23         |\r\n| 164 162 158 153 146 135 117 90 63 45 34 27 22 18 16         |\r\n| 172 171 169 166 162 153 135 90 45 27 18 14 11 9 8           |\r\n| 180 180 180 180 180 180 180 90 0 0 0 0 0 0 0                |\r\n| 188 189 191 194 198 207 225 270 315 333 342 346 349 351 352 |\r\n| 196 198 202 207 214 225 243 270 297 315 326 333 338 342 344 |\r\n| 203 207 211 217 225 236 252 270 288 304 315 323 329 333 337 |\r\n| 210 214 219 225 233 243 256 270 284 297 307 315 321 326 330 |\r\n| 216 220 225 231 239 248 259 270 281 292 301 309 315 320 324 |\r\n| 221 225 230 236 243 252 261 270 279 288 297 304 310 315 319 |\r\n| 225 229 234 240 247 254 262 270 278 286 293 300 306 311 315 |\r\n+-------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
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.<\/p>\n
Part 3: combining the two<\/h4>\n
Next step is probably the most significant one: we’re going to present a rough, square, weird looking pie chart using the original values:<\/p>\n
\n
SELECT\r\n  group_concat(\r\n    (SELECT name_order FROM\r\n      (\r\n      SELECT\r\n        name_order,\r\n        name_column,\r\n        value_column,\r\n        accumulating_value,\r\n        accumulating_value\/@accumulating_value AS accumulating_value_ratio,\r\n        2*PI()*accumulating_value\/@accumulating_value AS accumulating_value_radians\r\n      FROM (\r\n        SELECT\r\n          name_column,\r\n          value_column,\r\n          @name_order := @name_order+1 AS name_order,\r\n          @accumulating_value := @accumulating_value+value_column AS accumulating_value,\r\n          @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column\r\n        FROM (\r\n          SELECT name AS name_column, value AS value_column FROM sample_values2\r\n          ) select_values,\r\n          (SELECT @name_order := 0) select_name_order,\r\n          (SELECT @accumulating_value := 0) select_accumulating_value,\r\n          (SELECT @aggregated_name_column := '') select_aggregated_name_column\r\n        ) select_accumulating_values\r\n      ) select_for_radians\r\n    WHERE accumulating_value_radians >= radians LIMIT 1\r\n    )\r\n    order by col_number separator ' ') as circle\r\nFROM (\r\n  SELECT\r\n    t1.value AS col_number,\r\n    t2.value AS row_number,\r\n    @dx := (t1.value - (@size-1)\/2) AS dx,\r\n    @dy := ((@size-1)\/2 - t2.value) AS dy,\r\n    @abs_radians := IF(@dx = 0, PI()\/2, (atan(abs(@dy\/@dx)))) AS abs_radians,\r\n    CASE\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians\r\n    END AS radians\r\n  FROM\r\n    tinyint_asc t1,\r\n    tinyint_asc t2,\r\n    (select @size := 21) sel_size,\r\n    (select @radius := (@size\/2 - 1)) sel_radius\r\n  WHERE\r\n    t1.value < @size\r\n    AND t2.value < @size) select_combinations\r\n  GROUP BY row_number\r\n;\r\n+-------------------------------------------+\r\n| circle                                    |\r\n+-------------------------------------------+\r\n| 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 2 |\r\n| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 |\r\n| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 2 |\r\n| 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 2 2 1 |\r\n| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 2 2 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 2 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 2 2 2 2 2 2 2 1 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 3 2 2 2 2 2 1 1 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 3 2 2 2 1 1 1 1 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 3 2 2 1 1 1 1 1 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 3 2 1 1 1 1 1 1 1 1 1 1 |\r\n| 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n| 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 |\r\n+-------------------------------------------+<\/pre>\n<\/blockquote>\n
The interesting SQL trick is that everything goes within the GROUP_CONCAT clause<\/em>. 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<\/strong>, 2<\/strong>, etc. to denote the value.<\/p>\n
The next step is actually very simple: instead of drawing the full square, limit to a circle!<\/p>\n
\n
SELECT\r\n  group_concat(\r\n    IF(round(sqrt(pow(col_number-(@size-1)\/2, 2) + pow(row_number-(@size-1)\/2, 2))) BETWEEN @radius\/2 AND @radius,\r\n    (SELECT name_order FROM\r\n      (\r\n      SELECT\r\n        name_order,\r\n        name_column,\r\n        value_column,\r\n        accumulating_value,\r\n        accumulating_value\/@accumulating_value AS accumulating_value_ratio,\r\n        2*PI()*accumulating_value\/@accumulating_value AS accumulating_value_radians\r\n      FROM (\r\n        SELECT\r\n          name_column,\r\n          value_column,\r\n          @name_order := @name_order+1 AS name_order,\r\n          @accumulating_value := @accumulating_value+value_column AS accumulating_value,\r\n          @aggregated_name_column := CONCAT(@aggregated_name_column, name_column, ',') AS aggregated_name_column\r\n        FROM (\r\n          SELECT name AS name_column, value AS value_column FROM sample_values2\r\n          ) select_values,\r\n          (SELECT @name_order := 0) select_name_order,\r\n          (SELECT @accumulating_value := 0) select_accumulating_value,\r\n          (SELECT @aggregated_name_column := '') select_aggregated_name_column\r\n        ) select_accumulating_values\r\n      ) select_for_radians\r\n    WHERE accumulating_value_radians >= radians LIMIT 1\r\n    ), '-')\r\n    order by col_number separator ' ') as circle\r\nFROM (\r\n  SELECT\r\n    t1.value AS col_number,\r\n    t2.value AS row_number,\r\n    @dx := (t1.value - (@size-1)\/2) AS dx,\r\n    @dy := ((@size-1)\/2 - t2.value) AS dy,\r\n    @abs_radians := IF(@dx = 0, PI()\/2, (atan(abs(@dy\/@dx)))) AS abs_radians,\r\n    CASE\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians\r\n    END AS radians\r\n  FROM\r\n    tinyint_asc t1,\r\n    tinyint_asc t2,\r\n    (select @size := 21) sel_size,\r\n    (select @radius := 7) sel_radius\r\n  WHERE\r\n    t1.value < @size\r\n    AND t2.value < @size) select_combinations\r\n  GROUP BY row_number\r\n;\r\n+-------------------------------------------+\r\n| circle                                    |\r\n+-------------------------------------------+\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n| - - - - - - - - 2 2 2 2 2 - - - - - - - - |\r\n| - - - - - - 3 3 3 2 2 2 2 2 2 - - - - - - |\r\n| - - - - - 3 3 3 3 2 2 2 2 2 2 2 - - - - - |\r\n| - - - - 3 3 3 3 3 2 2 2 2 2 2 2 1 - - - - |\r\n| - - - - 3 3 3 3 3 - - - 2 2 2 1 1 - - - - |\r\n| - - - 3 3 3 3 3 - - - - - 1 1 1 1 1 - - - |\r\n| - - - 3 3 3 3 - - - - - - - 1 1 1 1 - - - |\r\n| - - - 3 3 3 3 - - - - - - - 1 1 1 1 - - - |\r\n| - - - 3 3 3 3 - - - - - - - 4 4 4 4 - - - |\r\n| - - - 3 3 3 3 3 - - - - - 4 4 4 4 4 - - - |\r\n| - - - - 3 3 4 4 4 - - - 4 4 4 4 4 - - - - |\r\n| - - - - 3 4 4 4 4 4 4 4 4 4 4 4 4 - - - - |\r\n| - - - - - 4 4 4 4 4 4 4 4 4 4 4 - - - - - |\r\n| - - - - - - 4 4 4 4 4 4 4 4 4 - - - - - - |\r\n| - - - - - - - - 4 4 4 4 4 - - - - - - - - |\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n| - - - - - - - - - - - - - - - - - - - - - |\r\n+-------------------------------------------+<\/pre>\n<\/blockquote>\n
That looks a lot more like a pie chart.<\/p>\n
Part 4: doing the fancy work<\/h4>\n
We will now add (in one big step):<\/p>\n
    \n
  • Stretching along the X-axis.<\/li>\n
  • Condensing the spaces.<\/li>\n
  • Coloring for the pie parts.<\/li>\n
  • A legend.<\/li>\n<\/ul>\n
    The text in bold<\/strong> is the original query, and is the only thing you need to change in order to create your own pie charts.<\/p>\n
    \n
    SELECT\r\n  group_concat(\r\n    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,\r\n    (SELECT SUBSTRING(@colors, name_order, 1) FROM\r\n      (\r\n      SELECT\r\n        name_order,\r\n        name_column,\r\n        value_column,\r\n        accumulating_value,\r\n        accumulating_value\/@accumulating_value AS accumulating_value_ratio,\r\n        @aggregated_data := CONCAT(@aggregated_data, name_column, ': ', value_column, ' (', ROUND(100*value_column\/@accumulating_value), '%)', '|') AS aggregated_name_column,\r\n        2*PI()*accumulating_value\/@accumulating_value AS accumulating_value_radians\r\n      FROM (\r\n        SELECT\r\n          name_column,\r\n          value_column,\r\n          @name_order := @name_order+1 AS name_order,\r\n          @accumulating_value := @accumulating_value+value_column AS accumulating_value\r\n        FROM (\r\n          SELECT name AS name_column, value AS value_column FROM sample_values2 LIMIT 4<\/strong>\r\n          ) select_values,\r\n          (SELECT @name_order := 0) select_name_order,\r\n          (SELECT @accumulating_value := 0) select_accumulating_value,\r\n          (SELECT @aggregated_data := '') select_aggregated_name_column\r\n        ) select_accumulating_values\r\n      ) select_for_radians\r\n    WHERE accumulating_value_radians >= radians LIMIT 1\r\n    ), ' ')\r\n    order by col_number separator '') as pie\r\nFROM (\r\n  SELECT\r\n    t1.value AS col_number,\r\n    t2.value AS row_number,\r\n    @dx := (t1.value\/@stretch - (@size-1)\/2) AS dx,\r\n    @dy := ((@size-1)\/2 - t2.value) AS dy,\r\n    @abs_radians := IF(@dx = 0, PI()\/2, (atan(abs(@dy\/@dx)))) AS abs_radians,\r\n    CASE\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) >= 0 THEN @abs_radians\r\n      WHEN SIGN(@dy) >= 0 AND SIGN(@dx) <= 0 THEN PI()-@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) <= 0 THEN PI()+@abs_radians\r\n      WHEN SIGN(@dy) <= 0 AND SIGN(@dx) >= 0 THEN 2*PI()-@abs_radians\r\n    END AS radians\r\n  FROM\r\n    tinyint_asc t1,\r\n    tinyint_asc t2,\r\n    (select @size := 23) sel_size,\r\n    (select @radius := (@size\/2 - 1)) sel_radius,\r\n    (select @stretch := 4) sel_stretch,\r\n    (select @colors := '#;o:X\"@+-=123456789abcdef') sel_colors\r\n  WHERE\r\n    t1.value < @size*@stretch\r\n    AND t2.value < @size) select_combinations\r\n  GROUP BY row_number\r\nUNION ALL\r\n  SELECT\r\n    CONCAT(\r\n      REPEAT(SUBSTRING(@colors, value, 1), 2),\r\n      '  ',\r\n      SUBSTRING_INDEX(SUBSTRING_INDEX(@aggregated_data, '|', value), '|', -1)\r\n    )\r\n  FROM\r\n    tinyint_asc\r\n  WHERE\r\n    value BETWEEN 1 AND @name_order\r\n;\r\n+----------------------------------------------------------------------------------------------+\r\n| pie                                                                                          |\r\n+----------------------------------------------------------------------------------------------+\r\n|                                                                                              |\r\n|                                   ;;;;;;;;;;;;;;;;;;;;;;;;;                                  |\r\n|                          oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                         |\r\n|                    ooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                   |\r\n|                oooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;               |\r\n|             oooooooooooooooooooooooo                     ;;;;;;;;;;;;;;;;;;;;;###            |\r\n|           oooooooooooooooooooo                                 ;;;;;;;;;###########          |\r\n|         oooooooooooooooooo                                         ##################        |\r\n|       ooooooooooooooooo                                               #################      |\r\n|      ooooooooooooooooo                                                 #################     |\r\n|      oooooooooooooooo                                                   ################     |\r\n|     oooooooooooooooo                                                     ################    |\r\n|      oooooooooooooooo                                                   ::::::::::::::::     |\r\n|      ooooooooooooooooo                                                 :::::::::::::::::     |\r\n|       ooooooooooooooooo                                               :::::::::::::::::      |\r\n|         oooooooooooooo::::                                         ::::::::::::::::::        |\r\n|           ooooooo:::::::::::::                                 ::::::::::::::::::::          |\r\n|             ::::::::::::::::::::::::                     ::::::::::::::::::::::::            |\r\n|                :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::               |\r\n|                    :::::::::::::::::::::::::::::::::::::::::::::::::::::::                   |\r\n|                          :::::::::::::::::::::::::::::::::::::::::::                         |\r\n|                                   :::::::::::::::::::::::::                                  |\r\n|                                                                                              |\r\n| ##  red: 1 (10%)                                                                             |\r\n| ;;  blue: 2 (20%)                                                                            |\r\n| oo  orange: 3 (30%)                                                                          |\r\n| ::  white: 4 (40%)                                                                           |\r\n+----------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
    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.<\/p>\n
    Showcase<\/h4>\n
    OK. Why? Why?<\/em><\/p>\n
    Here are three charts I hope will convince the skeptic reader:<\/p>\n
    Given the sakila<\/strong> database, list the 8 largest tables (size in KB):<\/em><\/p>\n
    \n
    +------------------------------------------------------------------------------+\r\n| pie_chart                                                                    |\r\n+------------------------------------------------------------------------------+\r\n|                                                                              |\r\n|                            #######################                           |\r\n|                    #######################################                   |\r\n|               #################################################              |\r\n|            ##################                   ##################           |\r\n|         ###############                               ###############        |\r\n|        ;;###########                                     #############       |\r\n|      ;;;;;;;;;;;;;                                         #############     |\r\n|      ;;;;;;;;;;;;                                           ############     |\r\n|     ;;;;;;;;;;;;;                                           #############    |\r\n|      ;;;;;;;;;;;;                                           @@@@@@@@@@@@     |\r\n|      ;;;;;;;;;;;;;                                         \"\"\"\"\"\"\"\"\"\"\"\"\"     |\r\n|        ;;;;;;;;;;;;;                                     XX\"\"\"\"\"\"\"\"\"\"\"       |\r\n|         ;;;;;;;;;;;;;;;                               XXXXXXXXXXXXX\"\"        |\r\n|            ;;;;;;;;;;;;;;;;;;                   ::::::::::XXXXXXXX           |\r\n|               ;;;;;;;;;;;;;;;;;;;;;;;;;;oooooooooo::::::::::::X              |\r\n|                    ;;;;;;;;;;;;;;;;;;;;;;ooooooooooo::::::                   |\r\n|                            ;;;;;;;;;;;;;;ooooooooo                           |\r\n|                                                                              |\r\n| ##  rental: 2850816 (43%)                                                    |\r\n| ;;  payment: 2228224 (34%)                                                   |\r\n| oo  inventory: 376832 (6%)                                                   |\r\n| ::  film_text: 325440 (5%)                                                   |\r\n| XX  film: 278528 (4%)                                                        |\r\n| \"\"  film_actor: 278528 (4%)                                                  |\r\n| @@  customer: 131072 (2%)                                                    |\r\n| ++  staff: 98304 (1%)                                                        |\r\n+------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
    How much disk space does each storage engine consume (sum table size per engine)?<\/em><\/p>\n
    \n
    +------------------------------------------------------------------------------+\r\n| pie_chart                                                                    |\r\n+------------------------------------------------------------------------------+\r\n|                                                                              |\r\n|                            #######################                           |\r\n|                    #######################################                   |\r\n|               #################################################              |\r\n|            ##################                   ##################           |\r\n|         ###############                               ###############        |\r\n|        #############                                     #############       |\r\n|      #############                                         #############     |\r\n|      ############                                           ############     |\r\n|     #############                                           #############    |\r\n|      ############                                           oooooooooooo     |\r\n|      #############                                         ;;;;;;;;;;;oo     |\r\n|        #############                                     ;;;;;;;;;;;;;       |\r\n|         ###############                               ;;;;;;;;;;;;;;;        |\r\n|            ##################                   #;;;;;;;;;;;;;;;;;           |\r\n|               #####################################;;;;;;;;;;;;              |\r\n|                    ###################################;;;;                   |\r\n|                            #######################                           |\r\n|                                                                              |\r\n| ##  InnoDB: 1908732 (84%)                                                    |\r\n| ;;  MyISAM: 284074 (12%)                                                     |\r\n| oo  ARCHIVE: 84276 (4%)                                                      |\r\n+------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
    What were the most popular DMLs during the last 10 seconds?<\/em><\/p>\n
    \n
    +------------------------------------------------------------------------------+\r\n| pie_chart                                                                    |\r\n+------------------------------------------------------------------------------+\r\n|                                                                              |\r\n|                            #######################                           |\r\n|                    #######################################                   |\r\n|               #################################################              |\r\n|            ##################                   ##################           |\r\n|         ###############                               ###############        |\r\n|        #############                                     #############       |\r\n|      #############                                         #############     |\r\n|      ############                                           ############     |\r\n|     #############                                           #############    |\r\n|      ############                                           oooo::::::::     |\r\n|      #############                                         ooooooooooooo     |\r\n|        #############                                     ooooooooooooo       |\r\n|         ###############                               ooooooooooooooo        |\r\n|            #################;                   ;;;;;;;;;ooooooooo           |\r\n|               #############;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;oo              |\r\n|                    ######;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;                   |\r\n|                            ;;;;;;;;;;;;;;;;;;;;;;;                           |\r\n|                                                                              |\r\n| ##  com_select: 1876 (69%)                                                   |\r\n| ;;  com_insert: 514 (19%)                                                    |\r\n| oo  com_delete: 277 (10%)                                                    |\r\n| ::  com_update: 63 (2%)                                                      |\r\n+------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
    Conclusion<\/h4>\n
    ASCII graphics always look funny; some would say the same about top<\/em>, wget<\/em>,\u00a0 cal<\/em>, etc. (should I even mention lynx<\/em>?)<\/p>\n
    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<\/em>, php<\/em>, drivers, etc.), or otherwise exporting table, copying to desktop machines, loading into OpenOffice impress, generating graphs.<\/p>\n
    Sometimes you just need an immediate overlook. This is where I find SQL charting to be useful.<\/p>\n
    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<\/em> or no perl-DBD-MySQL<\/em>; Linux or Windows.<\/p>\n
    Besides, it was fun doing it.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[41,21],"class_list":["post-1093","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-hD","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1093","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1093"}],"version-history":[{"count":39,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1093\/revisions"}],"predecessor-version":[{"id":1185,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1093\/revisions\/1185"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1093"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1093"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1093"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}