分析函式系列之sum(col1) over(partition by col2 order by col3):實現分組彙總或遞增彙總

OkidoGreen發表於2020-04-05
函式

https://blog.csdn.net/rfb0204421/article/details/7672207

https://blog.csdn.net/wawmg/article/details/40840093

語法:sum(col1) over(partition by col2 order by col3 )

 準備資料:

   DEPT_ID    ENAME          SAL
1 1000            A                     2500
2 1000            B                    3500
3 1000            C                    1500
4 1000            D                    2000
5 2000            E                    2500
6 2000            F                    2000
7 2000           G                    3500

主要有四種情況:

sum(sal) over (partition by deptno order by ename) 按部門“連續”求總和
sum(sal) over (partition by deptno) 按部門求總和
sum(sal) over (order by deptno，ename) 不按部門“連續”求總和
sum(sal) over () 不按部門，求所有員工總和，效果等同於sum(sal)。

 

1.有partition by有order by :   在partition by分組下,按照不同的order by col3實現遞增彙總..    

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id order by ENAME) AS TOTAL  from  dept_sal  

結果:按照部門分組,按名字排序實現遞增彙總.       

DEPT_ID ENAME    SAL  TOTAL

1 1000       A           3500   3500
2 1000       B           3500  7000
3 1000       C          1500   8500
4 1000       D          2000  10500
5 2000       E          2500   2500
6 2000       F          2000   4500
7 2000       G         3500    8000       

如果col3重複會只加總一次(當然在本例中這種寫法毫無意義):

SQL>> select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id order by SAL) AS TOTAL from dept_sal     

DEPT_ID ENAME SAL TOTAL

11000 C 1500 1500
2 1000 D 2000 3500
3 1000 A 3500 10500
4 1000 B 3500 10500
5 2000 F 2000 2000
6 2000 E 2500 4500
7 2000 G 3500 8000

 

2.有partition by無order by:  實現分組內所有資料的彙總

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(partition by dept_id) AS TOTAL from dept_sal     

DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 10500
2 1000 B 3500 10500
3 1000 C 1500 10500
4 1000 D 2000 10500
5 2000 E 2500 8000
6 2000 F 2000 8000
7 2000 G 3500 8000

 

 3.無partition by有order by : 直接按order by 欄位實現遞增彙總

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(order by ENAME) AS TOTAL from dept_sal

    DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 3500
2 1000 B 3500 7000
3 1000 C 1500 8500
4 1000 D 2000 10500
5 2000 E 2500 13000
6 2000 F 2000 15000
7 2000 G 3500 18500

如果order by 的值相同,會進行彙總,但彙總後顯示的值會是一樣的,如下:

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over(order by DEPT_ID) AS TOTAL from dept_sal

    DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 10500
2 1000 B 3500 10500
3 1000 C 1500 10500
4 1000 D 2000 10500
5 2000 E 2500 18500
6 2000 F 2000 18500
7 2000 G 3500 18500

 

 4.無partition by無order by:  所有資料相加.

SQL>>select DEPT_ID,ENAME,SAL,sum(SAL) over() AS TOTAL from  dept_sal

    DEPT_ID ENAME SAL TOTAL
1 1000 A 3500 18500
2 1000 B 3500 18500
3 1000 C 1500 18500
4 1000 D 2000 18500
5 2000 E 2500 18500
6 2000 F 2000 18500
7 2000 G 3500 18500
--------------------- 

0、select * from wmg_test;     ---測試資料

 

1、select v1,v2,sum(v2) over(order by v2) as sum     --按照 v2排序，累計n+n-1+....+1

from wmg_test;

2、select v1,v2,sum(v2) over(partition by v1 order by v2) as sum     --先分組，組內在進行 1 中的操作

from wmg_test;

 

3、select v1,v2,sum(v2) over(partition by v1 order by v1) as sum   ---穩定排序

from wmg_test;

 

4、select v1,v2,sum(v2) over(partition by v1) as sum    --相同key的進行回填處理

from wmg_test;

 

5、select distinct v1,sum_01                                        --取一條

from (

select v1,sum(v2) over(partition by v1) as sum_01

from wmg_test

) a;

 

6、當然也可以逆序累加，只需order by desc 即可

總結區別：group by 和partition by的區別

group 單純分組

partition 也能分組，但還具備累計的功能
--------------------- 

 

相關文章