千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2025-01-21 作者：千家信息网编辑
千家信息网最后更新 2025年01月21日，这篇文章主要介绍"Mysql似oracle分析函数sum over的实现方法是什么"，在日常操作中，相信很多人在Mysql似oracle分析函数sum over的实现方法是什么问题上存在疑惑，小编查阅
千家信息网最后更新 2025年01月21日Mysql似oracle分析函数sum over的实现方法是什么

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

很赞哦！
部门 总和 收益 备用 方法 函数 分析 学习 差值 时间 薪水 剩余 相同 份额 借款人 区间 数据 更多 本金 帮助 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 宝山区微型软件开发工艺 医院管理软件开发 go语言的数据库实现 软件开发票税率一般多少 苹果如何信任软件开发者 上传文件服务器 网络安全操作系统维护 怀旧服魔兽服务器选哪个 财务软件开发流程word 游戏服务器分布式数据库 创建网站连接数据库失败 深圳联兴软件开发公司 服务器如何远程到桌面 第八届中国网络安全大会8月 眼镜行业软件开发哪个好一些 奇怪君我的世界服务器 车辆国几排放的数据库 网络安全性分析 网络安全防护儿歌歌 数据库三员 中控智能服务器 西安智牛网络技术有限公司 服务器带外口显示不安全连接 数据库的开关应该交给哪种类 江门自主可控软件开发维修电话 使用国产cpu 的服务器 吴忠软件开发解决方案 数据库监测系统 服务器存储技术培训 湖北通用软件开发费用

相关文章

0