ADADADADAD

Mysql似oracle分析函数sum over的实现方法是什么[ mysql数据库 ]

mysql数据库时间：2024-11-26 22:07:54

作者：文/会员上传

如何提高mysql查询效率

简介：

先看oracle怎么实现的select deptno,ename,sal,sum(sal) over(order by ename) from emp; --姓名排序连续求和select deptno,ename,sal,sum(sal) over(order by deptno) fro

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

先看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;

结果

原数据

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 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

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)

第二步：计算一下与标示的差值（如果是连续的，那么差值一样）
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的实现方法是什么.docx

将本文的Word文档下载到电脑

下载

热门标签: mysqloracle

精品

申请https需要什么条件

Mysql似oracle分析函数sum over的实现方法是什么[ mysql数据库 ]

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

精品

热门推荐

大家都在看