Oracle 中總計和小計使用的函數

@[TOC](總計和小計、逐行累計求和)### 一、總計和小計使用函數#### 1、rollupgroup by 我們都知道，是一個分組函數，用於針對某一列做分組操作。但是當它搭配其他的函數一起使用的時候，就像一對男女孩，撞出不一樣的火花。==rollup(欄位1，欄位2，.....) rollup ...

@[TOC](總計和小計、逐行累計求和)
### 一、總計和小計使用函數
#### 1、rollup
group by 我們都知道，是一個分組函數，用於針對某一列做分組操作。
但是當它搭配其他的函數一起使用的時候，就像一對男女孩，撞出不一樣的火花。
==rollup(欄位1，欄位2，.....)
rollup和group by一起使用，可以針對每一個分組返回一個小計行，以及為所有的分組返回一個總計行（一個欄位就是返回總計行，多個欄位就是返回每一個分組的一個小計行和一個總計行）==
實踐是檢驗真理的唯一標準，那我們來實踐一下，我們先來快速創建一個表。有多快，很快很快的那種。

```handlebars
CREATE TABLE EMP (
"EMPNO" NUMBER(4) NOT NULL ,
"ENAME" VARCHAR2(10 BYTE) ,
"JOB" VARCHAR2(9 BYTE) ,
"SAL" NUMBER(7,2) ,
"DEPTNO" NUMBER(2)
)
INSERT INTO "SCOTT"."EMP" VALUES ('1', '張三', '開發', '10000', '10');
INSERT INTO "SCOTT"."EMP" VALUES ('2', '李四', '運維', '6000', '20');
INSERT INTO "SCOTT"."EMP" VALUES ('3', '王五', '測試', '6000', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('4', '麻子', '開發', '12000', '10');
```
==當我們的rollup裡面只有一個欄位的時候，就返回一個總計行==

```handlebars
select deptno,sum(sal) from emp
group by rollup(deptno);
查詢結果：
10 22000
20 6000
30 6000
34000
```
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523100648667.png)
==當我們傳遞兩個列欄位的時候，就會按照第一個欄位進行分組，返回一個小計行，最後返回一個總計行==

```handlebars
select ENAME,DEPTNO,sum(sal) from emp group by rollup(ENAME,DEPTNO);
李四 20 6000
李四 6000
麻子 10 12000
麻子 12000
王五 30 6000
王五 6000
張三 10 10000
張三 10000
34000

select ENAME,DEPTNO,sum(sal) from emp group by rollup(DEPTNO,ENAME);
麻子 10 12000
張三 10 10000
10 22000
李四 20 6000
20 6000
王五 30 6000
30 6000
34000
```
根據rollup()傳入的第一個欄位不同，返回的結果是不一樣的。我的理解是，rollup()傳遞多欄位的時候，會==先對多欄位進行分組，然後對第一個欄位進行分組==，比如上面的ename是第一個欄位,多欄位分組後就有四種，然後對第一欄位分組後還剩四種，並且返回一個小計行，最後返回一個總計行
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/2020052310123379.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523101249222.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
#### 2、cube
cube()傳遞一個欄位的時候，和rollup是一樣的結果

```handlebars
select DEPTNO,sum(sal) from emp group by cube(DEPTNO) order by deptno;
10 22000
20 6000
30 6000
34000
```
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523103405378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
但是cube()傳遞多個欄位的時候，就和rollup()不一樣了，cube()對多欄位的處理是先對所有的多欄位進行分組，然後==對第一個欄位進行分組，再對第二個欄位進行分組，意思就是兩種分組合起來了==，從下麵的結果我們就可以看出來，比如第二個欄位是deptno，分組後會把相同的10做為一組，所以可以看到根據ename分組會產生一個小計行，再根據deptno分組後產生了一個小計行，最後返回一個總計行。就等於在rollup()的基礎上，我們多了幾行分組的結果

```handlebars
select ENAME,DEPTNO,sum(sal) from emp group by cube(ENAME,DEPTNO);
34000
10 22000
20 6000
30 6000
李四 6000
李四 20 6000
麻子 12000
麻子 10 12000
王五 6000
王五 30 6000
張三 10000
張三 10 10000

select ENAME,DEPTNO,sum(sal) from emp group by cube(DEPTNO,ENAME);
34000
李四 6000
麻子 12000
王五 6000
張三 10000
10 22000
麻子 10 12000
張三 10 10000
20 6000
李四 20 6000
30 6000
王五 30 6000
```

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523103747346.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523104220398.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
#### 3、grouping
GROUPING函數可以接受一列，返回0或者1。==如果列值為空，那麼GROUPING()返回1；如果列值非空，那麼返回0==。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。我的理解就是==用來填充使用rollup()和cube()產生的null值==

```handlebars
select grouping(deptno),DEPTNO,sum(sal) from emp group by rollup(DEPTNO) ;
0 10 22000
0 20 6000
0 30 6000
1 34000
```
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523105005605.png)
我們使用==decode函數或case when==來填充為null的值。
==DECODE(value,if 條件1，then 值1，if 條件2，then 值2，...，else 其他值)==

```handlebars
select decode(grouping(deptno),1,'總計',DEPTNO) deptno,sum(sal) from emp group by rollup(DEPTNO) ;
10 22000
20 6000
30 6000
總計 34000
```
!![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523105431244.png)
可以看到我們把第一次查詢結果中的null填充了一個“總計”，這裡使用的是rollup()配合，使用cube()也是一樣的。
#### 4、grouping sets
grouping sets()傳遞多欄位就是分別對欄位進行分組了，產生的結果就是多個欄位分別分組後合起來的行。

```handlebars
select ENAME,DEPTNO,sum(sal) from emp group by grouping sets(ENAME,DEPTNO);
王五 6000
李四 6000
張三 10000
麻子 12000
30 6000
20 6000
10 22000
```
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523110031412.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
從結果我們可以看出，分別針對ename分組產生四行結果和對deptno分組產生三行結果。
#### 5、grouping_id
grouping_id()配合grouping()函數使用，會根據grouping()的值來決定，比如==grouping(欄位1)為0，grouping(欄位2)為1，則grouping_id(欄位1，欄位2)的結果就是1，如果grouping(欄位1)為1，grouping(欄位2)為0，則grouping_id(欄位1，欄位2)的結果就是2，如果grouping(欄位1)為1，grouping(欄位2)為1，則grouping_id(欄位1，欄位2)的結果就是3，==

```handlebars
select ename,deptno,sum(sal),
grouping(ename),
grouping(deptno),
grouping_id(ename,deptno)
from emp group by rollup(ename,deptno) ;

李四 20 6000 0 0 0
李四 6000 0 1 1
麻子 10 12000 0 0 0
麻子 12000 0 1 1
王五 30 6000 0 0 0
王五 6000 0 1 1
張三 10 10000 0 0 0
張三 10000 0 1 1
34000 1 1 3
```
![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20200523111437863.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dYQjUyMTEyMTgxMzE0,size_16,color_FFFFFF,t_70)
### 二、逐行累計求和方法（OVER函數）
#### 1、sum(欄位) over(partition by 欄位1 order by 欄位2....)
==over(partition by 欄位1 order by 欄位2....)
按欄位1指定的欄位進行分組排序，或者說按欄位欄位2的值進行分組排序==

```handlebars
select deptno,sal,
sum(sal) over (partition by deptno order by deptno) 逐行累計求和
from emp;
10 10000 22000
10 12000 22000
20 6000 6000
30 6000 6000

select deptno,sal,
sum(sal) over ( order by deptno) 逐行累計求和
from emp
10 10000 22000
10 12000 22000
20 6000 28000
30 6000 34000
```
從上面的結果我們可以看出，加了==partition by deptno==就會根據指定欄位分組逐行累加求和，否則全部逐行累加求和。