Mysql,Oracle使用rollup函数完成队列统计
Mysql,Oracle使用rollup函数完成行列统计 ??? 昨天突然在 一篇博客中看到了Mysql也有rollup函数,原博文使用了rollup进行行列统计,原博文链接如下: ??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html ??? 本博文主要是记录下mysql和oracl
Mysql,Oracle使用rollup函数完成行列统计??? 昨天突然在一篇博客中看到了Mysql也有rollup函数,原博文使用了rollup进行行列统计,原博文链接如下:
??? http://www.cnblogs.com/lhj588/archive/2012/06/15/2550392.html
??? 本博文主要是记录下mysql和oracle使用rollup函数进行行列统计,内容比较简单。
??? 首先是mysql,建表测试:
???
CREATE TABLE `tmysql_test_hanglietongji` ( `id` int(11) NOT NULL, `c1` char(2) COLLATE utf8_bin DEFAULT NULL, `c2` char(2) COLLATE utf8_bin DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
???
INSERT INTO `tmysql_test_hanglietongji` VALUES (1, 'A1', 'B1', 9); INSERT INTO `tmysql_test_hanglietongji` VALUES (2, 'A2', 'B1', 7); INSERT INTO `tmysql_test_hanglietongji` VALUES (3, 'A3', 'B1', 4); INSERT INTO `tmysql_test_hanglietongji` VALUES (4, 'A4', 'B1', 2); INSERT INTO `tmysql_test_hanglietongji` VALUES (5, 'A1', 'B2', 2); INSERT INTO `tmysql_test_hanglietongji` VALUES (6, 'A2', 'B2', 9); INSERT INTO `tmysql_test_hanglietongji` VALUES (7, 'A3', 'B2', 8); INSERT INTO `tmysql_test_hanglietongji` VALUES (8, 'A4', 'B2', 5); INSERT INTO `tmysql_test_hanglietongji` VALUES (9, 'A1', 'B3', 1); INSERT INTO `tmysql_test_hanglietongji` VALUES (10, 'A2', 'B3', 8); INSERT INTO `tmysql_test_hanglietongji` VALUES (11, 'A3', 'B3', 8); INSERT INTO `tmysql_test_hanglietongji` VALUES (12, 'A4', 'B3', 6); INSERT INTO `tmysql_test_hanglietongji` VALUES (13, 'A1', 'B4', 8); INSERT INTO `tmysql_test_hanglietongji` VALUES (14, 'A2', 'B4', 2); INSERT INTO `tmysql_test_hanglietongji` VALUES (15, 'A3', 'B4', 6); INSERT INTO `tmysql_test_hanglietongji` VALUES (16, 'A4', 'B4', 9); INSERT INTO `tmysql_test_hanglietongji` VALUES (17, 'A1', 'B4', 3); INSERT INTO `tmysql_test_hanglietongji` VALUES (18, 'A2', 'B4', 5); INSERT INTO `tmysql_test_hanglietongji` VALUES (19, 'A3', 'B4', 2); INSERT INTO `tmysql_test_hanglietongji` VALUES (20, 'A4', 'B4', 5);
?? 要完成的效果如下:
???
????? 最简单的是使用union,如下:
?????
select ifnull(c1, 'total') as 'total', sum(if(c2 = 'B1', C3, 0)) AS B1, sum(if(c2 = 'B2', C3, 0)) AS B2, sum(if(c2 = 'B3', C3, 0)) AS B3, sum(if(c2 = 'B4', C3, 0)) AS B4, SUM(C3) AS TOTAL from tmysql_test_hanglietongji group by C1 union select 'total' as 'total', sum(if(c2 = 'B1', C3, 0)) AS B1, sum(if(c2 = 'B2', C3, 0)) AS B2, sum(if(c2 = 'B3', C3, 0)) AS B3, sum(if(c2 = 'B4', C3, 0)) AS B4, SUM(C3) AS TOTAL from tmysql_test_hanglietongji order by 1
??? 也可以使用with rollup函数。注意当使用 rollup时, 你不能同时使用 order by子句进行结果排序
???
select ifnull(c1, 'total') 'total', sum(if(c2 = 'B1', C3, 0)) AS B1, sum(if(c2 = 'B2', C3, 0)) AS B2, sum(if(c2 = 'B3', C3, 0)) AS B3, sum(if(c2 = 'B4', C3, 0)) AS B4, SUM(C3) AS TOTAL from tmysql_test_hanglietongji group by C1 with rollup;
?? with rollup其实是第一个的简化。
?? 也可以这样写:
???
SELECT IFNULL(c1, 'total') AS total, SUM(IF(c2 = 'B1', c3, 0)) AS B1, SUM(IF(c2 = 'B2', c3, 0)) AS B2, SUM(IF(c2 = 'B3', c3, 0)) AS B3, SUM(IF(c2 = 'B4', c3, 0)) AS B4, SUM(IF(c2 = 'total', c3, 0)) AS total FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 WITH ROLLUP HAVING c1 IS NOT NULL) AS A GROUP BY c1 WITH ROLLUP;
??? HAVING c1 IS NOT NULL条件主要是过滤掉对整个tmysql_test_hanglietongji 表求和的那一行,以上面的子查询为例:
???
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 WITH ROLLUP
??? 结果是:
???
?? 相当于:
??
SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 union ALL SELECT c1, 'total' AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1 union ALL SELECT NULL, 'total' AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji
??? 结果是:
???
??? 可以看出group by c1,c2 with rollup相当于group by c1,c2 union group by c1(c2替换为NULL) union?(c1,c2全部替换为NULL)。
?? 这里的替换规则参考了链接
?? http://blog.itpub.net/519536/viewspace-610995
?? 原文是替换Oracle的rollup,在Mysql中也适用。
?? 使用普通sql写法是:
??
SELECT IFNULL(c1, 'total') AS total, SUM(IF(c2 = 'B1', c3, 0)) AS B1, SUM(IF(c2 = 'B2', c3, 0)) AS B2, SUM(IF(c2 = 'B3', c3, 0)) AS B3, SUM(IF(c2 = 'B4', c3, 0)) AS B4, SUM(IF(c2 = 'total', c3, 0)) AS total FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union SELECT c1, 'total' as c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji group by c1) A group by c1 UNION SELECT 'total' as total, SUM(IF(c2 = 'B1', c3, 0)) AS B1, SUM(IF(c2 = 'B2', c3, 0)) AS B2, SUM(IF(c2 = 'B3', c3, 0)) AS B3, SUM(IF(c2 = 'B4', c3, 0)) AS B4, SUM(IF(c2 = 'total', c3, 0)) AS total FROM (SELECT c1, IFNULL(c2, 'total') AS c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union SELECT c1, 'total' as c2, SUM(c3) AS c3 FROM tmysql_test_hanglietongji group by c1) A
?? 少了一个是因为上面的having要求c1 is not null,所以替换c1为NULL就没有了。
?
?? 下面看下oracle中怎么写,想要的效果如图:
???
?? 首先建表。
??
create table TSQL_TEST_HANGLIETONGJI ( ID NUMBER(4) not null, C1 VARCHAR2(2), C2 VARCHAR2(2), C3 NUMBER(4) ) ; alter table TSQL_TEST_HANGLIETONGJI add primary key (ID); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (1, 'A1', 'B1', 9); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (2, 'A2', 'B1', 7); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (3, 'A3', 'B1', 4); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (4, 'A4', 'B1', 2); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (5, 'A1', 'B2', 2); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (6, 'A2', 'B2', 9); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (7, 'A3', 'B2', 8); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (8, 'A4', 'B2', 5); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (9, 'A1', 'B3', 1); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (10, 'A2', 'B3', 8); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (11, 'A3', 'B3', 8); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (12, 'A4', 'B3', 6); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (13, 'A1', 'B4', 8); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (14, 'A2', 'B4', 2); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (15, 'A3', 'B4', 6); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (16, 'A4', 'B4', 9); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (17, 'A1', 'B4', 3); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (18, 'A2', 'B4', 5); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (19, 'A3', 'B4', 2); insert into TSQL_TEST_HANGLIETONGJI (ID, C1, C2, C3) values (20, 'A4', 'B4', 5);
?? 最简单的写法是:
???
select c1, sum(decode(c2,'B1', C3, 0)) AS B1, sum(decode(c2 ,'B2', C3, 0)) AS B2, sum(decode(c2 ,'B3', C3, 0)) AS B3, sum(decode(c2 ,'B4', C3, 0)) AS B4, SUM(C3) AS TOTAL from tsql_test_hanglietongji group by C1 UNION SELECT 'TOTAL', sum(decode(c2 ,'B1', C3, 0)) AS B1, sum(decode(c2 ,'B2', C3, 0)) AS B2, sum(decode(c2 ,'B3', C3, 0)) AS B3, sum(decode(c2 ,'B4', C3, 0)) AS B4, SUM(C3) FROM tsql_test_hanglietongji
?? 然后使用rollup函数简化。
???
SELECT nvl(c1, 'total') AS total, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, sum(c3) AS total FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1)
???也可以这么写:
??
SELECT nvl(c1, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1, c2) HAVING c1 IS NOT NULL) A GROUP BY ROLLUP(c1);
? rollup和普通sql替换上面也说了,举个例子:
??
SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY ROLLUP(c1, c2)
? 效果是:
??
?? 普通sql写法是:
??
SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 union all SELECT NULL, 'total' AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji order by 1, 2
??? 细心的朋友也许注意到了,第二个union all带了order by 1,2而上面的mysql没有带order by,这和mysql和oracle对NULL的默认排序规则有关。
??? 使用普通sql重写rollup为:
???
SELECT nvl(c1, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 HAVING c1 IS NOT NULL) A GROUP BY c1 union all SELECT nvl(null, 'total') AS total_c, SUM(decode(c2, 'B1', c3, 0)) AS B1, SUM(decode(c2, 'B2', c3, 0)) AS B2, SUM(decode(c2, 'B3', c3, 0)) AS B3, SUM(decode(c2, 'B4', c3, 0)) AS B4, SUM(decode(c2, 'total', c3, 0)) AS total_r FROM (SELECT c1, nvl(c2, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1, c2 HAVING c1 IS NOT NULL union all SELECT c1, nvl(null, 'total') AS c2, SUM(c3) AS c3 FROM tsql_test_hanglietongji GROUP BY c1 HAVING c1 IS NOT NULL) A order by 1
?? 这里也排除了c1 is null的情况。
??? 通过上面的对比,发现oracle和mysql的rollup非常相似,对rollup函数感兴趣的朋友请仔细搜索rollup学习。
??? 到这里该结束了,有任何意见请留言,如文中sql有错误也请指出,谢谢。
??? 全文完。
?
?
??
?
?
?
?
?
???
?

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

The retention period of Oracle database logs depends on the log type and configuration, including: Redo logs: determined by the maximum size configured with the "LOG_ARCHIVE_DEST" parameter. Archived redo logs: Determined by the maximum size configured by the "DB_RECOVERY_FILE_DEST_SIZE" parameter. Online redo logs: not archived, lost when the database is restarted, and the retention period is consistent with the instance running time. Audit log: Configured by the "AUDIT_TRAIL" parameter, retained for 30 days by default.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

Oracle database server hardware configuration requirements: Processor: multi-core, with a main frequency of at least 2.5 GHz. For large databases, 32 cores or more are recommended. Memory: At least 8GB for small databases, 16-64GB for medium sizes, up to 512GB or more for large databases or heavy workloads. Storage: SSD or NVMe disks, RAID arrays for redundancy and performance. Network: High-speed network (10GbE or higher), dedicated network card, low-latency network. Others: Stable power supply, redundant components, compatible operating system and software, heat dissipation and cooling system.

The amount of memory required by Oracle depends on database size, activity level, and required performance level: for storing data buffers, index buffers, executing SQL statements, and managing the data dictionary cache. The exact amount is affected by database size, activity level, and required performance level. Best practices include setting the appropriate SGA size, sizing SGA components, using AMM, and monitoring memory usage.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.
