Mysql似oracle分析函數sum over的實現方法是什么

166次閱讀

共計 10378 個字符，預計需要花費 26 分鐘才能閱讀完成。

這篇文章主要介紹“Mysql 似 oracle 分析函數 sum over 的實現方法是什么”，在日常操作中，相信很多人在 Mysql 似 oracle 分析函數 sum over 的實現方法是什么問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql 似 oracle 分析函數 sum over 的實現方法是什么”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

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

連續區間的實現（求連續 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 的實現方法是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完