Oracle over partition by
假設一個公司的薪資資料如下.
DEPT NAME SALARY
-------------------------
RD1ANDY120
RD1KEN140
RD1KUO180
HR1BEN180
RD2YANG130
RD2RAY180
1. 統計每個人佔部門總薪資的比例
select dept,name,salary, salary/sum(salary)over(partition by dept) as ratio from salary
HR1BEN1801
RD1ANDY1200.27
RD1KEN1400.31
RD1KUO1800.40
RD2YANG1300.41
RD2RAY1800.58
2. 統計每個人佔整公司的比例
select dept,name,salary, salary/sum(salary)over(partition by null) as ratio from salary
RD1ANDY1200.12
RD1KEN1400.15
RD1KUO1800.19
HR1BEN1800.19
RD2YANG1300.13
RD2RAY1800.19
3. 排序每個部門薪資由高至低
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary
HR1BEN1801
RD1KUO1801
RD1KEN1402
RD1ANDY1203
RD2RAY1801
RD2YANG1302
4. 排序整公司薪資由高至低 rank()
select dept,name,salary, rank() over(partition by null order by salary desc) as rank from salary
RD1KUO1801
HR1BEN1801
RD2RAY1801
RD1KEN1404
RD2YANG1305
RD1ANDY1206
4. 排序整公司薪資由高至低 dense_rank()
select dept,name,salary, dense_rank() over(partition by null order by salary desc) as rank from salary
RD1KUO1801
HR1BEN1801
RD2RAY1801
RD1KEN1402
RD2YANG1303
RD1ANDY1204
5. 最後要提一個觀念就是 partition by 的欄位值與 order by 是無關的.
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary order by name
RD1ANDY1203
HR1BEN1801
RD1KEN1402
RD1KUO1801
RD2RAY1801
RD2YANG1302
假設一個公司的薪資資料如下.
DEPT NAME SALARY
-------------------------
RD1ANDY120
RD1KEN140
RD1KUO180
HR1BEN180
RD2YANG130
RD2RAY180
1. 統計每個人佔部門總薪資的比例
select dept,name,salary, salary/sum(salary)over(partition by dept) as ratio from salary
HR1BEN1801
RD1ANDY1200.27
RD1KEN1400.31
RD1KUO1800.40
RD2YANG1300.41
RD2RAY1800.58
2. 統計每個人佔整公司的比例
select dept,name,salary, salary/sum(salary)over(partition by null) as ratio from salary
RD1ANDY1200.12
RD1KEN1400.15
RD1KUO1800.19
HR1BEN1800.19
RD2YANG1300.13
RD2RAY1800.19
3. 排序每個部門薪資由高至低
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary
HR1BEN1801
RD1KUO1801
RD1KEN1402
RD1ANDY1203
RD2RAY1801
RD2YANG1302
4. 排序整公司薪資由高至低 rank()
select dept,name,salary, rank() over(partition by null order by salary desc) as rank from salary
RD1KUO1801
HR1BEN1801
RD2RAY1801
RD1KEN1404
RD2YANG1305
RD1ANDY1206
4. 排序整公司薪資由高至低 dense_rank()
select dept,name,salary, dense_rank() over(partition by null order by salary desc) as rank from salary
RD1KUO1801
HR1BEN1801
RD2RAY1801
RD1KEN1402
RD2YANG1303
RD1ANDY1204
5. 最後要提一個觀念就是 partition by 的欄位值與 order by 是無關的.
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary order by name
RD1ANDY1203
HR1BEN1801
RD1KEN1402
RD1KUO1801
RD2RAY1801
RD2YANG1302
文章標籤
全站熱搜
