MySQL wizardry - Some help from SQL itself (
Page 3 of 4 )
The Wizard
smiled. "If you like to, you're welcome. However, you could get some help from
the database engine itself, provided that you ask nicely." And while he was
speaking he typed a very cryptic statement:
mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`')
FROM departments;
| CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS
`',dept,'`') |
| , SUM(IF(dept = "Development", 1,0)) AS
`Development` |
| , SUM(IF(dept = "Personnel", 1,0)) AS
`Personnel` |
| , SUM(IF(dept = "Research", 1,0)) AS
`Research` |
| , SUM(IF(dept = "Sales", 1,0)) AS
`Sales` |
| , SUM(IF(dept = "Training", 1,0)) AS
`Training` |
5 rows in set
(0.00 sec) "You know," he went on, "you can also use SQL to
produce SQL code. This is one of the cases. You have in front of you the list of
columns that you should include in your query. Now, with some cut-and-paste, we
could get the result you want. Here."
mysql> SELECT location
, SUM(IF(dept = "Development", 1,0)) AS `Development`
, SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
, SUM(IF(dept = "Research", 1,0)) AS `Research`
, SUM(IF(dept = "Sales", 1,0)) AS `Sales`
, SUM(IF(dept = "Training", 1,0)) AS `Training`
, COUNT(*) AS total
FROM locations INNER JOIN employees USING (loc_code)
INNER JOIN departments USING (dept_code)
GROUP BY location;
| location |
Development |
Personnel |
Research |
Sales |
Training |
total |
| Boston |
2 |
0 |
0 |
1 |
0 |
3 |
| Cagliari |
0 |
0 |
3 |
0 |
0 |
3 |
| London |
0 |
1 |
0 |
0 |
1 |
2 |
| Manchester |
0 |
0 |
0 |
2 |
0 |
2 |
| Marseille |
2 |
0 |
0 |
1 |
0 |
3 |
| Milano |
0 |
0 |
0 |
1 |
1 |
2 |
| New York |
3 |
0 |
0 |
1 |
0 |
4 |
| Paris |
1 |
0 |
0 |
1 |
0 |
2 |
| Roma |
0 |
1 |
0 |
1 |
1 |
3 |
9 rows in set (0.36 sec) The wizard was
now unstoppable. He had reached the stage where he simply couldn't help giving
away his knowledge. "Before we go on," he said, taking possession of my
keyboard, "let's see how this same method can do more than counting. Let's
replace those 1s with a numeric field, and do real summing up."
mysql> SELECT location
, SUM(IF(dept = "Development", salary,0)) AS `Development`
, SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
, SUM(IF(dept = "Research", salary,0)) AS `Research`
, SUM(IF(dept = "Sales", salary,0)) AS `Sales`
, SUM(IF(dept = "Training", salary,0)) AS `Training`
, SUM(salary) AS total
FROM locations INNER JOIN employees USING (loc_code)
INNER JOIN departments USING (dept_code)
GROUP BY location;
| location |
Development |
Personnel |
Research |
Sales |
Training |
total |
| Boston |
11900 |
0 |
0 |
5950 |
0 |
17850 |
| Cagliari |
0 |
0 |
16800 |
0 |
0 |
16800 |
| London |
0 |
5700 |
0 |
0 |
5700 |
11400 |
| Manchester |
0 |
0 |
0 |
11550 |
0 |
11550 |
| Marseille |
11150 |
0 |
0 |
5800 |
0 |
16950 |
| Milano |
0 |
0 |
0 |
5550 |
4900 |
10450 |
| New York |
17850 |
0 |
0 |
6100 |
0 |
23950 |
| Paris |
5700 |
0 |
0 |
5400 |
0 |
11100 |
| Roma |
0 |
5000 |
0 |
5500 |
5100 |
15600 |
9 rows in set (0.00 sec) "Don't forget to
change also the total field. A simple SUM without IF, and your total is ready."
I was looking at the screen, which was showing what seemed to be the
complete solution to my problem, but the wizard was shaking his head. "As a
matter of fact," he was saying, there is something that we can improve here. We
have two queries, in which we are reading the
departments table. So we
are reading it twice twice. The whole process could be improved, by querying for
department code the first time, and omitting the join with departments the
second time."
mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`')
FROM departments;"Here. Let's get the columns once more. Good. And
there it is. This one looks better."
SELECT location
, SUM(IF(dept_code = "1", 1,0)) AS `Personnel`
, SUM(IF(dept_code = "2", 1,0)) AS `Training`
, SUM(IF(dept_code = "3", 1,0)) AS `Research`
, SUM(IF(dept_code = "4", 1,0)) AS `Sales`
, SUM(IF(dept_code = "5", 1,0)) AS `Development`
, COUNT(*) AS total
FROM locations INNER JOIN employees USING (loc_code)
GROUP BY location;
He changed the previous two SQL statements,
executed them, with some cut-and-pasting in the middle, and got exactly the same
result. Now he was explaining me why he did it. "The first query is scanning all
the departments table, and we know that its results will be used to build the
second query containing the employees table, which has already a department
code. Therefore, we can skip the join with departments, since the only purpose
of that join was to get the names of the departments."
"I see" was the only
comment I could offer, Since I was overwhelmed by his continuous insight. I got
the idea, and I couldn't help thinking that he must have done that before. All
those pieces of information were coming just too fast for me. Luckily, all logs
were on, so I knew that I would be able to get all the statements back when he
would leave. Which was not the case yet. The wizard was now ready to give me his
philosophical view of cross tabulating.
{mospagebreak title=The golden
rules} "Rule number one:" -- wizards have always a rule n. 1 for everything --
"
cross-tabulating is a simple algorithm with a complex implementation.
Once you know the principle, you can do everything you need for your statistics.
The difficult part is making the process automatic. You don't know in advance
which values you should use for your columns. Hence, you have to find such
values every time, before creating the second query. The real problem with
cross-tabs is that
there are no simple cases. Even the ones that look as
such, have sticky problems. And you should consider this as rule number two."
I was about to lose heart, but he had more for me. "On the other hand,"
he continued, "This algorithm is so flexible that will let you do things that
your ordinary spreadsheet won't let you. For example, let's suppose that you
want a x-tab with the number of employees by gender and the total of their
salaries as well. In your common graphical tool you can do either of them. With
this system, you can combine both. It's no difficult, just complicated. Now, if
you fetch some more coffee, I'll show you."
And when I came back from the
kitchenette, there was it.
mysql> SELECT location
, SUM(IF(gender='M',1,0)) AS M
, SUM(IF(gender='M',salary,0)) AS salary_M
, SUM(IF(gender='F',1,0)) AS F
, SUM(IF(gender='F',salary,0)) AS salary_F
, COUNT(*) AS empl
, SUM(salary) AS tot_salary
FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;
| location |
M |
salary_M |
F |
salary_F |
empl |
tot_salary |
| Boston |
1 |
5800 |
2 |
12050 |
3 |
17850 |
| Cagliari |
1 |
5600 |
2 |
11200 |
3 |
16800 |
| London |
1 |
5700 |
1 |
5700 |
2 |
11400 |
| Manchester |
1 |
5600 |
1 |
5950 |
2 |
11550 |
| Marseille |
1 |
5550 |
2 |
11400 |
3 |
16950 |
| Milano |
2 |
10450 |
0 |
0 |
2 |
10450 |
| New York |
2 |
11950 |
2 |
12000 |
4 |
23950 |
| Paris |
1 |
5400 |
1 |
5700 |
2 |
11100 |
| Roma |
3 |
15600 |
0 |
0 |
3 |
15600 |
9 rows in set (0.00 sec) He took a sip
from his mug and added, "And of course nobody could prevent you from inserting a
totally unrelated line into your query, like this one:
mysql> SELECT location ,SUM(IF(gender='M',1,0)) AS M
, SUM(IF(gender='M',salary,0)) AS salary_M
, SUM(IF(gender='F',1,0)) AS F
, SUM(IF(gender='F',salary,0)) AS salary_F
, SUM(IF(dept_code = "4", 1,0)) AS `Sales`
, COUNT(*) AS empl
, SUM(salary) AS tot_salary FROM locations
INNER JOIN employees USING (loc_code) GROUP BY location;
| location |
M |
salary_M |
F |
salary_F |
Sales |
empl |
tot_salary |
| Boston |
1 |
5800 |
2 |
12050 |
1 |
3 |
17850 |
| Cagliari |
1 |
5600 |
2 |
11200 |
0 |
3 |
16800 |
| London |
1 |
5700 |
1 |
5700 |
0 |
2 |
11400 |
| Manchester |
1 |
5600 |
1 |
5950 |
2 |
2 |
11550 |
| Marseille |
1 |
5550 |
2 |
11400 |
1 |
3 |
16950 |
| Milano |
2 |
10450 |
0 |
0 |
1 |
2 |
10450 |
| New York |
2 |
11950 |
2 |
12000 |
1 |
4 |
23950 |
| Paris |
1 |
5400 |
1 |
5700 |
1 |
2 |
11100 |
| Roma |
3 |
15600 |
0 |
0 |
1 |
3 |
15600 |
9 rows in set (0.00 sec) "Here we have
results coming from three different sources: the counting of employees by
gender, the sum of their salaries by gender, and the total number of the
employees in the Sales department, regardless of their gender. I know that this
particular example doesn't make much sense, but this is something that people in
the management, for reasons that completely escape my intellectual hold, want to
put together. Don't underestimate such possibility, which could prove to be
useful in many occasions."
I knew by then that his enthusiasm was
growing thinner, and I should have expected him to leave abruptly any moment. I
had to refuel his good disposition and ask some juicy questions. "This is really
wonderful," I said, "but in a real application you would not use SQL alone. How
can I implement this algorithm with a general purpose language?"
Putting the pieces together
The wizard startled, as if awaken from a
troubled sleep and suddenly his eyes were sparkling with renewed interest. "Yes,
of course," he replied. "If we stick to one-level cross-tabulations, this
algorithm will fit nicely into any high level language. We should avoid the
multi-level ones, for the moment, because the general concept is more important
than the thorny details. Let me draw you a flow-chart diagram."
I had
already relinquished my desktop, and I stayed quietly at his side, while he was
skillfully drawing this diagram, which he explained almost as fast as he was
designing.
"The required actions are quite simple. However, you need
some planning before starting. First, you have to identify the source for the
columns. It could be the same source from where you need to count or sum, but in
a well organized and normalized database it should be in a separated table.
Either way, your first action with the database will be to query for distinct
column values. Then you will merge such values within the summary statements
composing your query. And at that point you'll be ready to execute. You can only
omit the initial query if you are 100% sure that your values have not changed.
This could be the case, for example, if your column values are in a read-only
table. But usually this is not the case, or else cross-tabulations wouldn't be
that hard. Well, let's start. Any preferred language?"
This was a false
democratic question, which I knew by experience. I could mention any language
and he would be proficient in it, but he would reject on some ground, until I
would eventually manage to name the language he had in mind. Having gone through
the motions before, I had my answer ready: "I think that Perl would serve the
purpose," I said hastily. He nodded, approving my wisdom, and fired a copy of
vim from a xterm. "Sorry if you are an Emacs guy," but his voice betrayed his
complete lack of sorrow, "Nothing like vim to highlight Perl syntax. I am sure
you can follow me into this, and besides, I have the helm." That, I knew for
sure. My computer was firmly in his possession, and I started doubting that I
would ever have it back.
The wizard, unaware of my anxiety, was already
writing
#!/usr/bin/perl -w
use DBI;
use strict;
Now, you would expect your average wizard to be
adventurous and careless, keen of programming without constraints. Not this one.
My wizard is a wizard because he behaves like one, but, being a database wizard,
he is also very strict in matter of coding. More than once he gave me a speech
on the importance of catching the errors in advance, before they can catch you.
Since I knew that lecture by heart, I did not comment. My only contribution was
to provide the IP address of my server and the location where the DBI could look
for my user-name and password. I suspect that the wizard already knew that much,
but he wanted to make me feel important by letting me provide some tiny
contribution to the script.
my %params;
while (<>) { # gets the configuration from the input pipe
next if /^#/; # skips comments
my ($name,$value) = split '=',$_;
chomp $value;
$params{$name} = $value ;
}
$params{database} or die "no valid input given\n";
"We are going to read the parameters into a hash from a
configuration file. They are too many for the command line, and it's better to
save them to a file, which will be even clearer. In a production case, you would
rather store those parameters into a database table. Four our purposes, we are
going to use a text file. Actually, let's write it down, before we continue. I
think it would be better to start with the simple case, location by gender."
title=location-gender
database=xcompany
row_name=location
row_alias=town
col_name=gender
col_alias=gender
col_value=1
col_from=FROM employees
col_where=
col_order=
row_from=FROM locations INNER JOIN employees USING(loc_code)
row_where=
row_order=ORDER BY loc_sort_order
row_group=group by location
He saved those parameters into a location-gender-count.xtab
and explained: "See, our parameters are the description of the final query. In
order to build the query, we need to set its parts. In this particular case, we
are not going to use a WHERE and a ORDER BY clause for the columns, but you know
that we may need them for the others, so we'll leave them. Our parsing mechanism
can easily take care of the empty strings. In this script I assume that all
these parameters are properly set in the configuration file. I will skip all the
error checking on that, and you can implement it later on."
my $dbh = DBI->connect("DBI:mysql:$params{database};"
."host=172.16.35.1;mysql_read_default_file=$ENV{HOME}/.my.cnf")
or die "can't connect $! \n";
"This is fairly simple. Just the connection to the database.
Now we have a database handle $dbh and with it we can go for the first query."
my $row_statement = "";
my $sth = $dbh->prepare("SELECT DISTINCT $params{col_name}, $params{col_alias} "
. $params{col_from} . " " . $params{col_where} . $params{col_order});
$sth->execute();
while (my ($colname, $colalias) = $sth->fetchrow_array()) {
$row_statement .= ", SUM(IF($params{col_name} = \"${colname}\","
. " $params{col_value}, 0)) AS `${colalias}` \n"
}
$sth->finish();
$dbh->disconnect();
I recognized the proceedings. He was instructing Perl to do
the same thing that he was previously doing manually. I told him that much.
"Yes, exactly," he approved. "And in addition to that, using a high level
language will grant us some more freedom. Since we have already collected the
field list from our configuration file, we are not in a hurry to compose our
second query. Having the parameters in a hash will also give us some amount of
control, since any missing parameter from the configuration file will be duly
reported by the Perl compiler. At this point, the only thing we need to do is to
gather all the pieces together and produce our final query."
# ------------ add total column and row details
$row_statement .= ", " . (($params{col_value} eq "1")? "COUNT(*)"
: "SUM($params{col_value})")
. " AS total \n"
. $params{row_from}
. $params{row_where} ;
print "use $params{database};\n";
$row_statement =~ s/\n\n/\n/g; # remove double EOL
print "SELECT $params{row_name} AS $params{row_alias} \n",
"$row_statement\n$params{row_group} \n $params{row_order} ;\n";
# the cross table
print "SELECT 'TOTAL'\n$row_statement;\n"; # the total line
He continued explaining. "Since you have seen the manual
examples and the flow-chart, what we are doing shouldn't come as a surprise to
you. We add a total line, which will be a real SUM or a COUNT, depending on the
parameter that was entered. Then we add the FROM and WHERE clause, and we don't
care if this last one is empty. The resulting script will include a USE database
statement, followed by a query that should look much like the one we entered
manually with cut-and-paste."
I wanted desperately to enter the discussion,
so I told him what was troubling me. "Why you did not complete the
$row_statement with the GROUP and ORDER-BY clauses? Why did you stop at the
WHERE level?"
Almost annoyed by my lack of insight, he replied "You should
see it by yourself. The $row_statement as I made it is what we have in common
between the normal selection and the total line. To get the grand total you
should not GROUP, and since we are going to get only one line, it doesn't make
much sense to ORDER it. Does it? Now, since you mentioned the total, this
solution is just for a quick demonstration, which I can do with a single line of
Perl. If your tables are those big babies with millions of record that you might
have in your data warehouse, then you'd better send the output of this query to
a temporary table and then get the grand total from there. For tables with less
than one hundred thousand records, MySQL will slurp this kind of cross-tabs in a
blink."
He got up from my chair, and I knew that my lesson was over.
"Wait a minute." I said hastily. " This script doesn't execute the cross-tab
query. How do you use it?"
Still halfway from my chair, he bent to the
keyboard, and typed
$ perl xtab.pl < location-gender-count.xtab | mysql -t
"Just add your password and host, if you need it, and it
will give you the X-tab."
"But? wait! I have a few questions about
multi-level cross-tabulations."
He smiled in a devilish way. "I am sure you
do. But I have a girlfriend who wants to discuss database theory with me," he
looked at his watch and added "in exactly ten minutes at that new Chinese
restaurant downtown, and I shouldn't make her wait. I think you have enough food
for thought to fill your weekend. Give me a call next week, and perhaps, if I
can spare half an hour, I can give you a hand." And while offering his
metaphoric limb, he extended his real one to shake mine, and off he went before
I could say 'son of a wizard!'
