Wednesday, 11 November 2009

MySQL bar chart from the command line

Whilst working a fairly tricky bug involving the powerful GROUP_CONCAT function of MySQL, I also played with the CONCAT function and though a bit grass root, I managed to build a quick bar chart directly in MySQL.

Here is a quick example of what you can do.

First we need a test table:

CREATE TABLE `source_data` (
  `id` int(11) NOT NULL auto_increment,
  `name_of_variable` varchar(10) NOT NULL,
  `var_value` decimal(10,0) NOT NULL,
  `period` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then some test data:

insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '1', '0', '2009-01-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '2', '0', '2009-01-07');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '12', '0', '2009-02-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '5', '0', '2009-02-06');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '6', '0', '2009-03-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '11', '0', '2009-03-10');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '3', '0', '2009-03-23');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '6', '0', '2009-04-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '21', '0', '2009-05-01');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '10', '0', '2009-05-21');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '1', '0', '2009-06-29');
insert into `source_data` ( `name_of_variable`, `var_value`, `id`, `period`) values ( 'sales', '7', '0', '2009-06-30');

And now we make a basic bar chart, directly in MySQL console:
SELECT 
  date_format(period, '%Y-%m') as monthly_period,
  name_of_variable as 'metric',
  CONCAT(REPEAT('*', var_value)) as 'bar chart'
FROM 
  source_data
GROUP BY 
  name_of_variable , 
  date_format(period, '%Y-%m') 
ORDER BY
  period;

And the result is :

+----------------+--------+-----------------------+
| monthly_period | metric | bar chart             |
+----------------+--------+-----------------------+
| 2009-01        | sales  | *                     |
| 2009-02        | sales  | ************          |
| 2009-03        | sales  | ******                |
| 2009-04        | sales  | ******                |
| 2009-05        | sales  | ********************* |
| 2009-06        | sales  | *                     |
+----------------+--------+-----------------------+
6 rows in set (0.01 sec)

Et voila, you can have some fun with MySQL console... sort of !