使用MySQL存储过程创建动态交叉表(1)
来源:互联网 发布:linux sftp命令下载 编辑:程序博客网 时间:2024/06/06 07:46
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
- 使用MySQL存储过程创建动态交叉表(1)
- 使用MySQL存储过程创建动态交叉表(2)
- 使用MySQL存储过程创建动态交叉表(3)
- mysql存储过程(递归,交叉表)
- MySql存储过程动态创建表并插入数据
- 动态创建表存储过程
- 交叉表的简单实现1:使用存储过程
- MYSQL存储过程使用动态SQL 建多表
- MySQL存储过程中使用动态行转列
- MySQL存储过程中使用动态行转列
- MySQL存储过程中使用动态行转列
- mysql 存储过程中使用动态sql
- 使用Navicat for MySQL创建存储过程
- MySQL使用存储过程创建用户
- 使用Navicat for MySQL创建存储过程
- MySQL 创建存储过程(MySQL 5.0)
- MySQL 创建存储过程(MySQL 5.0)
- mysql创建存储过程
- 使用架构(XSD)验证XML文件
- linux下文件结构收藏
- 百万数据查询优化技巧三十则
- IE下的javascript调试工具
- domino相关文档就写在domino里
- 使用MySQL存储过程创建动态交叉表(1)
- 300-500内的质数(素数)个数和前10个最大的质数之和算法
- [转载]STL算法:prev_permutation和next_permutation的使用
- 截断显示文章标题时,出现乱码或者“?”问号的问题
- 发现IE下的优秀JavaScript调试工具Companion.JS
- 使用MySQL存储过程创建动态交叉表(2)
- [OpenGL]贪吃蛇游戏源码
- socket编程学习系列(一)
- ssh框架整合实例子1-配置文件