使用MySQL預存程序建立動態交叉表（2）

A mail from the Wizard himself

     

What an honour: Only hours after publishing my blog entry on dynamic crosstabs in MySQL I got an email from Giuseppe Maxia, the Wizard himself.

     

But not only did the Wizard send me a "Nice shot!" in his mail, Roland Bouman also said "Thank you guys, I learnt a lot from those!" commenting on our articles. That's actually exactly what I'd have to say to both of them myself.

     

Giuseppe Maxia, being the real Wizard, pointed out in his mail "that you don't need temporary tables to get the column values, but everything can be achieved with normal SQL (albeit not simple)." According to him there are "at least two methods to overcome the restriction on cursors with prepared queries" which forced me to use a temporary table in my example. Although he didn't go into any further details, I'm sure he'll give us some nice insights when he speaks at the Opensource Database Conference next Tuesday in Frankfurt (be sure to attend if you can, I unfortunately can't) and hopefully at the MySQL Users Conference 2006.

     

One solution I thought of when writing the stored procedure this Monday would of course be to use GROUP_CONCAT to avoid a temporary table in the first of the two dynamic queries. I chose the cursor loop over GROUP_CONCAT as a loop would give us more flexibility to enhance the procedure with more functionality. But after rethinking this I found that you could possibly achieve about 90% of what you could ever think of with nested control flow functions inside the GROUP_CONCAT itself.

     

CREATE PROCEDURE xtab2(`col_name` VARCHAR(32), `col_alias` VARCHAR(32),
                       `col_from` VARCHAR(256), `col_value` VARCHAR(32),
                       `row_name` VARCHAR(32), `row_from` VARCHAR(256))
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Generate dynamic crosstabs - variant with GROUP_CONCAT'
BEGIN

    -- Some heavy double quoting (quoting of already quoted strings) involved here
    -- to build the query that builds the list of columns for the crosstab
    SET @column_query := CONCAT('SELECT CONCAT(GROUP_CONCAT(DISTINCT ',
                                '/'//tSUM(IF(`', `col_name`, '` = ///'/', `',
                                `col_name`, '`, /'///', ', `col_value`,
                                ', 0)) AS `/', `', `col_alias`, '`, /'`/' ',
                                'SEPARATOR /',//n/'), /',//n/') INTO @xtab_query ',
                                `col_from`);

    -- Uncomment the following line if you want to see the
    -- generated query to assemble the columns
    -- SELECT @column_query;
   
    PREPARE `column_query` FROM @column_query;
    EXECUTE `column_query`;
    DEALLOCATE PREPARE `column_query`;

    SET @xtab_query = CONCAT('SELECT `', `row_name`, '`,/n',
                             @xtab_query, '/t',
                             IF(`col_value` = '1',
                                'COUNT(*)',
                                CONCAT('SUM(`', `col_value`, '`)')
                             ),
                             ' AS `total`/n',
                             `row_from`);
   
    -- Uncomment the following line if you want to see the
    -- generated crosstab query for debugging purposes
    -- SELECT @xtab_query;
   
    -- Execute crosstab
    PREPARE `xtab` FROM @xtab_query;
    EXECUTE `xtab`;
    DEALLOCATE PREPARE `xtab`;
END

     

This version is shorter than the first one and definitely more in the style of how you should normally work with a database: Don't loop over rows when you can achieve the same result with a simple query. We'll possibly loose some clarity as soon as we start to add further functionality to the GROUP_CONCAT, but we sure gain speed compared to a loop. It's just that the first of the two dynamic queries now looks like a real mess from all the quoting.