Mysql似oracle分析函数sumover的实现方法是什么

这篇文章主要介绍“MySQL似oracle分析函数sum over的实现方法是什么”，在日常操作中，相信很多人在Mysql似oracle分析函数sum over的实现方法是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql似oracle分析函数sum over的实现方法是什么”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：国际域名空间、网络空间、营销软件、网站建设、那曲网站维护、网站推广。

先看oracle怎么实现的

1. select deptno,ename,sal,sum(sal) over(order by ename) from emp; --姓名排序连续求和

2. select deptno,ename,sal,sum(sal) over(order by deptno) from emp; --所有部们排序连续求和

3. select deptno,ename,sal,sum(sal) over(partition by deptno) from emp; ---各个部门的总和

4. select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) from emp; ---各个部门之间连续求和

5. select deptno,ename,sal,sum(sal) over(order by deptno,ename) from emp;

8. select deptno,ename,sal,

9.       sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和

10.       sum(sal) over (partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变

11.       100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",

12.       sum(sal) over (order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和

13.       sum(sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和

14.      100*round(sal/sum(sal) over (),4) "总份额(%)"

15.      from emp

mysql的实现

1. 如下：

2. SELECT a.id,a.user_id,a.borrow_id, a.repayment_money,
   (SELECT SUM(repayment_money) FROM rb_repayment_period WHERE id<=a.id) "累加和",

3. (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_idHAVING  borrow_id=a.`borrow_id` ) "每组累加和"    
   FROM rb_repayment_period a;
   

结果


原数据

1. sql：

2. 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的实现

1. 环境：

2. mysql> show create table tbl\G;
   *************************** 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
   

3. mysql> insert into tbl values (1,26),(2,46),(3,35),(4,68),(5,93),(6,92);

4. 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)
   

7. 实现一：

8. 
   
   
   
   实现二：解决重复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+------+------+
   | 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 一下

1. 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;

2. +-----------+---------+--------+

3. | start_pos | end_pos | tel |

4. +-----------+---------+--------+

5. | 1 | 2 | 187163 |

6. | 5 | 6 | 187163 |

7. | 8 | 8 | 187163 |

8. | 9 | 9 | 19999 |

9. +-----------+---------+--------+

10. 4 rows in set (0.00 sec)

12. 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;

13. +-----------+---------+--------+

14. | start_pos | end_pos | tel |

15. +-----------+---------+--------+

16. | 3 | 4 | 187164 |

17. | 7 | 7 | 187164 |

18. +-----------+---------+--------+

19. 2 rows in set (0.00 sec)

20. MySQL [interface_hd_com]> select * from testtab;

21. +------+--------+

22. | id | tel |

23. +------+--------+

24. | 1 | 187163 |

25. | 2 | 187163 |

26. | 3 | 187164 |

27. | 4 | 187164 |

28. | 5 | 187163 |

29. | 6 | 187163 |

30. | 7 | 187164 |

31. | 8 | 187163 |

32. | 9 | 19999 |

33. +------+--------+

34. 9 rows in set (0.00 sec)

35. 第一步：标示

36. 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)
    

37. 第一步求出个数

38. MySQL [interface_hd_com]> select a.col,COUNT(*) as count,( select count(*) from testtt b where b.col

39. +------+-------+--------+

40. | col | count | rownum |

41. +------+-------+--------+

42. | 26 | 1 | 0 |

43. | 35 | 2 | 1 |

44. | 46 | 2 | 3 |

45. | 68 | 2 | 5 |

46. | 92 | 2 | 7 |

47. | 93 | 2 | 9 |

48. +------+-------+--------+

49. 6 rows in set (0.00 sec)

50. 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)

51. 瑕疵：当有重复的数据时就有bug了

52. 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的实现方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！