Mysql似oracle分析函数sum over的达成方法是什么
发布时间：2021-12-17 10:05:25 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍Mysql似oracle分析函数sum over的实现方法是什么，在日常操作中，相信很多人在Mysql似oracle分析函数sum over的实现方法是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答Mysql似oracle分析函数sum ove
|
这篇文章主要介绍“Mysql似oracle分析函数sum over的实现方法是什么”，在日常操作中，相信很多人在Mysql似oracle分析函数sum over的实现方法是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql似oracle分析函数sum over的实现方法是什么”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 先看oracle怎么实现的 select deptno,ename,sal,sum(sal) over(order by ename) from emp; --姓名排序连续求和 select deptno,ename,sal,sum(sal) over(order by deptno) from emp; --所有部们排序连续求和 select deptno,ename,sal,sum(sal) over(partition by deptno) from emp; ---各个部门的总和 select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) from emp; ---各个部门之间连续求和 select deptno,ename,sal,sum(sal) over(order by deptno,ename) from emp; select deptno,ename,sal, sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和 sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变 100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)", sum(sal) over (order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和 sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和 100*round(sal/sum(sal) over (),4) "总份额(%)" from emp mysql的实现 如下： SELECT a.id,a.user_id,a.borrow_id, a.repayment_money, (SELECT SUM(repayment_money) FROM rb_repayment_period WHERE id<=a.id) "累加和", (SELECT AVG(repayment_money) FROM rb_repayment_period WHERE id<=a.id) "平均值" , (SELECT SUM(repayment_money) FROM rb_repayment_period WHERE borrow_id=a.borrow_id GROUP BY borrow_id) "每组和", (SELECT SUM(repayment_money) FROM rb_repayment_period) "全部和", (SELECT SUM(repayment_money) FROM rb_repayment_period WHERE id<=a.id GROUP BY borrow_id HAVING borrow_id=a.`borrow_id` ) "每组累加和" FROM rb_repayment_period a; 结果 Mysql似oracle分析函数sum over的实现方法是什么 原数据 sql： CREATE TABLE `rb_repayment_period` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `borrow_id` int(11) DEFAULT '0' COMMENT '标的id', `user_id` int(11) DEFAULT '0' COMMENT '借款人id', `repayment_money` decimal(20,6) DEFAULT '0.000000' COMMENT '本次还款金额', `capital_money` decimal(20,6) DEFAULT '0.000000' COMMENT '本金', `expect_money` decimal(20,6) DEFAULT '0.000000' COMMENT '预期收益', `exceed_money` decimal(20,6) DEFAULT '0.000000' COMMENT '超额收益', `actual_rate` decimal(20,6) DEFAULT '0.000000' COMMENT '实际收益率', `third_company_money` decimal(20,6) DEFAULT '0.000000' COMMENT '第三方公司收益', `load_money` decimal(20,6) DEFAULT '0.000000' COMMENT '借款人利益', `repayment_time` int(3) DEFAULT '0' COMMENT '还款次数', `repayment_stage` int(3) DEFAULT '0' COMMENT '当前还款的阶段', `playform_money` decimal(20,6) DEFAULT '0.000000' COMMENT '平台收益', `add_datetime` timestamp NOT NULL DEFAULT '2016-04-24 03:49:30' COMMENT '操作时间', `memo_id_first` int(11) DEFAULT '0' COMMENT '备用id', `memo_dec_first` decimal(20,6) DEFAULT '0.000000' COMMENT '备用dec', `memo_str_first` varchar(500) DEFAULT NULL COMMENT '备用str1', `memo_str_second` varchar(500) DEFAULT NULL COMMENT '备用str2', `memo_date_first` timestamp NULL DEFAULT '2016-04-24 03:49:30' COMMENT '备用时间1', `memo_date_second` timestamp NULL DEFAULT '2016-04-24 03:49:30' COMMENT '备用时间2', `total_repay_money` decimal(20,6) DEFAULT '0.000000' COMMENT '累计还款总额', `repay_type` int(3) DEFAULT '0' COMMENT '还款类型', `left_capital_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余本金', `left_expect_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余收益', `left_money` decimal(20,6) DEFAULT '0.000000' COMMENT '剩余留用', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `rb_repayment_period` -- LOCK TABLES `rb_repayment_period` WRITE; /*!40000 ALTER TABLE `rb_repayment_period` DISABLE KEYS */; INSERT INTO `rb_repayment_period` VALUES (26,160,188,1000.000000,1000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,2,0.0000 00,'2016-04-24 07:43:38',0,0.000000,NULL,NULL,'2016-04-24 03:49:30','2016-04-24 03:49:30',0.000000,0,0.000000,0.000000,0.000000),(27 ,160,188,100.000000,0.000000,100.000000,0.000000,0.000000,0.000000,0.000000,2,2,0.000000,'2016-04-24 07:45:26',0,0.000000,NULL,NULL, '2016-04-24 03:49:30','2016-04-24 03:49:30',0.000000,0,0.000000,0.000000,0.000000),(30,160,188,1000.000000,0.000000,87.500000,11.250 000,0.000000,11.250000,890.000000,3,4,0.000000,'2016-04-24 08:09:11',0,0.000000,NULL,NULL,'2016-04-24 03:49:30','2016-04-24 03:49:30 ',0.000000,0,0.000000,0.000000,0.000000),(42,163,187,4400.000000,2000.000000,375.000000,0.000000,0.000000,0.000000,2025.000000,1,3,0 .000000,'2016-04-25 07:33:59',0,0.000000,NULL,NULL,'2016-04-25 07:33:59','2016-04-25 07:33:59',0.000000,0,0.000000,0.000000,0.000000 ),(47,172,187,10000.000000,2000.000000,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'2016-04-26 02:48:05',0,0.00 0000,NULL,NULL,'2016-04-26 02:48:05','2016-04-26 02:48:05',0.000000,0,0.000000,0.000000,0.000000),(48,174,187,10000.000000,2000.0000 00,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'2016-04-26 03:23:41',0,0.000000,NULL,NULL,'2016-04-26 03:23:41' ,'2016-04-26 03:23:41',0.000000,0,0.000000,0.000000,0.000000),(49,157,187,3000.000000,1000.000000,120.000000,0.000000,0.000000,0.000 000,1880.000000,1,3,0.000000,'2016-04-26 03:58:56',0,0.000000,NULL,NULL,'2016-04-26 03:58:56','2016-04-26 03:58:56',3000.000000,2,0. 000000,0.000000,0.000000),(50,175,187,10000.000000,2000.000000,375.000000,12.500000,0.000000,12.500000,7600.000000,1,4,0.000000,'201 6-04-26 05:29:48',0,0.000000,NULL,NULL,'2016-04-26 05:29:48','2016-04-26 05:29:48',10000.000000,2,0.000000,0.000000,0.000000),(54,17 7,187,2000.000000,2000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,2,0.000000,'2016-04-27 01:59:35',0,0.000000,NULL,NULL,' 2016-04-27 01:59:35','2016-04-27 01:59:35',2000.000000,1,0.000000,375.000000,0.000000),(55,177,187,4000.000000,0.000000,375.000000,0 .000000,360.000000,0.000000,3625.000000,2,3,0.000000,'2016-04-27 02:01:43',0,0.000000,NULL,NULL,'2016-04-27 02:01:43','2016-04-27 02 :01:43',6000.000000,2,0.000000,0.000000,0.000000),(56,178,187,2100.000000,2000.000000,100.000000,0.000000,0.000000,0.000000,0.000000 ,1,2,0.000000,'2016-04-27 03:43:43',0,0.000000,NULL,NULL,'2016-04-27 03:43:43','2016-04-27 03:43:43',2100.000000,1,0.000000,275.0000 00,0.000000),(57,178,187,3000.000000,0.000000,275.000000,0.000000,378.000000,0.000000,2725.000000,2,3,0.000000,'2016-04-27 07:07:34' ,0,0.000000,NULL,NULL,'2016-04-27 07:07:34','2016-04-27 07:07:34',5100.000000,2,0.000000,0.000000,0.000000),(58,181,187,1000.000000, 1000.000000,0.000000,0.000000,0.000000,0.000000,0.000000,1,1,0.000000,'2016-04-27 07:15:58',0,0.000000,NULL,NULL,'2016-04-27 07:15:5 8','2016-04-27 07:15:58',1000.000000,1,1000.000000,375.000000,0.000000),(59,181,187,500.000000,500.000000,0.000000,0.000000,180.0000 00,0.000000,0.000000,2,1,0.000000,'2016-04-27 07:26:34',0,0.000000,NULL,NULL,'2016-04-27 07:26:34','2016-04-27 07:26:34',1500.000000 ,1,500.000000,375.000000,0.000000); rownum的实现 环境： mysql> show create table tblG; *************************** 1. row *************************** Table: tbl Create Table: CREATE TABLE `tbl` ( `id` int(11) NOT NULL, `col` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> insert into tbl values (1,26),(2,46),(3,35),(4,68),(5,93),(6,92); mysql> select * from tbl -> ; +----+------+ | id | col | +----+------+ | 1 | 26 | | 2 | 46 | | 3 | 35 | | 4 | 68 | | 5 | 93 | | 6 | 92 | +----+------+ 6 rows in set (0.00 sec) 实现一： 实现二：解决重复bug（先建立一张数字表Nums(a int) 插入1-100即可） 第二步： MySQL [interface_hd_com]> select Nums.a+c.rownum as rank ,col from (select a.col,COUNT(*) as count,( select count(*) from testtt b where b.col<a.col) as rownum from testtt a group by a.col) c,Nums where Nums.a<=count order by col; +------+------+ | rank | col | +------+------+ | 1 | 26 | | 2 | 35 | | 3 | 35 | | 4 | 46 | | 5 | 46 | | 6 | 68 | | 7 | 68 | | 8 | 92 | | 9 | 92 | | 10 | 93 | | 11 | 93 | +------+------+ 11 rows in set (0.01 sec) 连续区间的实现（求连续id区间） 第二步：计算一下与标示的差值（如果是连续的，那么差值一样） mysql> SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1 AS alias1 FROM tbl,(SELECT @id:=0) AS id) b; +----+--------+------+ | id | alias1 | diff | +----+--------+------+ | 11 | 1 | 10 | | 12 | 2 | 10 | | 13 | 3 | 10 | | 14 | 4 | 10 | | 15 | 5 | 10 | | 16 | 6 | 10 | | 18 | 7 | 11 | | 19 | 8 | 11 | +----+--------+------+ 8 rows in set (0.00 sec) 第三步：根据差值分组找出最大最小即可 mysql> SELECT MIN(id) start_pos,MAX(id) end_pos -> FROM -> (SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1 AS alias1 FROM tbl,(SELECT @id:=0) AS id) b) -> AS c -> GROUP BY diff; +-----------+---------+ | start_pos | end_pos | +-----------+---------+ | 11 | 16 | | 18 | 19 | +-----------+---------+ 2 rows in set (0.00 sec) 实验：求tel相同的连续段 按照上面的思路求得 MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id) b) as c GROUP BY diff,tel order by tel desc; +-----------+---------+--------+ | start_pos | end_pos | tel | +-----------+---------+--------+ | 3 | 7 | 187164 | | 1 | 8 | 187163 | | 9 | 9 | 19999 | +-----------+---------+--------+ ---这样是有bug的 发现这样是不行的，因为id是连续的，所以同一个tel的diff是相同的，但其实中间隔着别的tel 解决办法：分两次求在合并 union 一下 MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (SELECT distinct(tel) from testtab where tel<>187164)) b) as c GROUP BY diff,tel order by tel desc; +-----------+---------+--------+ | start_pos | end_pos | tel | +-----------+---------+--------+ | 1 | 2 | 187163 | | 5 | 6 | 187163 | | 8 | 8 | 187163 | | 9 | 9 | 19999 | +-----------+---------+--------+ 4 rows in set (0.00 sec) MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (187164)) b) as c GROUP BY diff,tel order by tel desc; +-----------+---------+--------+ | start_pos | end_pos | tel | +-----------+---------+--------+ | 3 | 4 | 187164 | | 7 | 7 | 187164 | +-----------+---------+--------+ 2 rows in set (0.00 sec) MySQL [interface_hd_com]> select * from testtab; +------+--------+ | id | tel | +------+--------+ | 1 | 187163 | | 2 | 187163 | | 3 | 187164 | | 4 | 187164 | | 5 | 187163 | | 6 | 187163 | | 7 | 187164 | | 8 | 187163 | | 9 | 19999 | +------+--------+ 9 rows in set (0.00 sec) 第一步：标示 mysql> SELECT id,@id:=@id+1 AS alias1 FROM tbl,(SELECT @id:=0) AS id; +----+--------+ | id | alias1 | +----+--------+ | 11 | 1 | | 12 | 2 | | 13 | 3 | | 14 | 4 | | 15 | 5 | | 16 | 6 | | 18 | 7 | | 19 | 8 | +----+--------+ 8 rows in set (0.00 sec) 第一步求出个数 MySQL [interface_hd_com]> select a.col,COUNT(*) as count,( select count(*) from testtt b where b.col<a.col) as rownum from testtt a group by a.col; +------+-------+--------+ | col | count | rownum | +------+-------+--------+ | 26 | 1 | 0 | | 35 | 2 | 1 | | 46 | 2 | 3 | | 68 | 2 | 5 | | 92 | 2 | 7 | | 93 | 2 | 9 | +------+-------+--------+ 6 rows in set (0.00 sec) mysql> select id,a.col,( select count(*) from tbl b where b.col<=a.col) as rank from tbl a order by rank; +----+------+------+ | id | col | rank | +----+------+------+ | 1 | 26 | 1 | | 3 | 35 | 2 | | 2 | 46 | 3 | | 4 | 68 | 4 | | 6 | 92 | 5 | | 5 | 93 | 6 | +----+------+------+ 6 rows in set (0.00 sec) 瑕疵：当有重复的数据时就有bug了 mysql> select id,a.col,(select count(*) from tbl b where b.col<=a.col ) as rank from tbl a order by rank; +----+------+------+ | id | col | rank | +----+------+------+ | 1 | 26 | 2 | | 9 | 26 | 2 | | 3 | 35 | 4 | | 8 | 35 | 4 | | 2 | 46 | 5 | | 4 | 68 | 6 | | 6 | 92 | 7 | | 5 | 93 | 8 | +----+------+------+ 8 rows in set (0.00 sec) 到此，关于“Mysql似oracle分析函数sum over的实现方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！ （编辑：源码网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
