使用MySQL存储过程创建动态交叉表（1）

The Wizard revisited: Dynamic Crosstabs using MySQL Stored Procedures

     

Since I read the excellent article MySQL Wizardry by Giuseppe Maxia some years ago I wanted to implement crosstabs in pure MySQL - and had to wait a long time until 5.0 made it possible. And then of course I didn't find the time. But after Roland Bouman blogged on this same topic yesterday, it was definitely the right moment to try out the real thing: Fully dynamic crosstabs in a pure MySQL stored procedure.

     

If you're new to crosstabs make sure you read the article mentioned above first. It's likewise instructive and witty. What I do here in pure SQL is actually just a translated and slightly simplified version of Giuseppe Maxia's Perl code.

     

He was so lucky to know one of those real geeks (just called "The Wizard") who led him through all the tedious steps involved in generating crosstabs. Despite the title, I couldn't revisit the Wizard. Knowing that he possibly only exists in our dreams, I had to work through this all alone... But let's just have a look at the code first, being only a quick suggestion, ready for your improvements.

     

CREATE PROCEDURE xtab(`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'
BEGIN
    DECLARE `xtab_col_name`  VARCHAR(32)    DEFAULT '';
    DECLARE `xtab_col_alias` VARCHAR(32)    DEFAULT '';
    DECLARE `xtab_query`     VARCHAR(4096)  DEFAULT '';
    DECLARE `done`           BIT(1)         DEFAULT 0;
   
    DECLARE `column_cursor` CURSOR FOR
        SELECT `temp_col_name`, `temp_col_alias` FROM `xtab_columns`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;
   
    -- We have to use a temporary table here as MySQL doesn't
    -- allow us to declare a cursor in prepared statements
    DROP TABLE IF EXISTS `xtab_columns`;
    SET @column_query := CONCAT('CREATE TEMPORARY TABLE `xtab_columns` ',
                                'SELECT DISTINCT ',
                                '`', `col_name`, '` AS `temp_col_name`, ',
                                '`', `col_alias`, '` AS `temp_col_alias` ',
                                `col_from`);
   
    PREPARE `column_query` FROM @column_query;
    EXECUTE `column_query`;
    DEALLOCATE PREPARE `column_query`;
   
    OPEN `column_cursor`;
    column_loop: LOOP
        FETCH `column_cursor` INTO `xtab_col_name`, `xtab_col_alias`;
        IF `done` THEN LEAVE column_loop; END IF;
        SET `xtab_query` = CONCAT(`xtab_query`,
                                  '/tSUM(IF(`', `col_name`, '` = /'',
                                  `xtab_col_name`, '/', ',
                                  `col_value`, ', 0)) AS `',
                                  `xtab_col_alias`, '`,/n');
    END LOOP column_loop;
    CLOSE `column_cursor`;
    DROP TABLE IF EXISTS `xtab_columns`;
   
    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
    SET @xtab_query = `xtab_query`;
    PREPARE `xtab` FROM @xtab_query;
    EXECUTE `xtab`;
    DEALLOCATE PREPARE `xtab`;
END


     

The procedure uses dynamic SQL via PREPARE/EXECUTE twice: First to build the expressions defining the columns in the crosstab and finally to execute the dynamic crosstab query. It takes six parameters: The first three control the building of the crosstab columns: col_name is the original column on which the crosstab should be done, col_alias could be a different column to be used for the names displayed in the column headers of the crosstab (if you use a JOIN in the next parameter), col_from is the query part starting with FROM to be used to retrieve the crosstab values. The next three parameters control the actual crosstab query: col_value can be set to 1 if you simply want to count in your crosstab, but it can be set to a column name as well to sum up an arbitrary value. row_name is the main crosstab query starting again with FROM, it can as well contain JOINs, WHERE conditions, ORDER BY clauses and usually contains a GROUP BY statement for the column mentioned in row_name.

     

There were two issues with prepared statements to work around (as of MySQL 5.0.15) that didn't contribute to a cleaner code structure:

     


    *         MySQL doesn't yet accept local variables in the prepared statement syntax (see my feature request Bug #13572). So we have to pollute the global variable namespace whenever we use dynamic SQL inside a stored routine.

    *         I didn't find a way to define a cursor inside a prepared statement: MySQL yields an error on a CREATE PROCEDURE statement if it contains an OPEN statement for a cursor that doesn't have an explicit corresponding DECLARE before it.

           


     

To work around the second point it was necessary to use a temporary table. The user invoking the procedure therefore needs the CREATE TEMPORARY TABLE privilege. You could of course change the SQL SECURITY to DEFINER, but I didn't want to do that, as it could give the user the possibility to circumvent access restrictions on tables by just accessing them via a crosstab.

     

But now let's finally create a tiny test database to try out the xtab stored procedure.

     

CREATE TABLE employees (
    id INT auto_increment PRIMARY KEY,
    shop_id INT,
    gender ENUM('m', 'f'),
    name VARCHAR(32),
    salary INT
);

CREATE TABLE shops (
    shop_id INT auto_increment PRIMARY KEY,
    shop VARCHAR(32)
);

INSERT INTO shops (shop)
VALUES ('Zurich'), ('New York'), ('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser', 4700),
(2, 'f', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);


     

The two examples show that the procedure works for counting as well as for summing up values.

     

mysql> CALL xtab('gender', 'gender', 'FROM employees', 1, 'shop',
'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');
+----------+------+------+-------+
| shop     | m    | f    | total |
+----------+------+------+-------+
| London   | 2    | 0    |     2 |
| New York | 0    | 1    |     1 |
| Zurich   | 1    | 1    |     2 |
+----------+------+------+-------+
3 rows in set (0.03 sec)

mysql> CALL xtab('gender', 'gender', 'FROM employees', 'salary', 'shop',
'FROM employees INNER JOIN shops USING (shop_id) GROUP BY shop');
+----------+-------+------+-------+
| shop     | m     | f    | total |
+----------+-------+------+-------+
| London   | 10300 | 0    | 10300 |
| New York | 0     | 5600 | 5600  |
| Zurich   | 4500  | 4700 | 9200  |
+----------+-------+------+-------+
3 rows in set (0.01 sec)

 

 

原文地址：http://www.futhark.ch/mysql/106.html