sql(oracle)資料處理實用總結開窗函式(over partition)使用

1.開窗函式over partition

開窗函式使用

    開窗函式使用於取出多列分組，取一列分組下另一組前幾名，先利用開窗函式對其分組排名，開窗函式排名函式較多使用row_number()，還有rank()等，生成排名列之後將結果集篩選其排名前幾<=2或者你想要的前幾名。

上述例子sql：

select test_name,test_id,n_rank,test_salary_sum from ( select t_alias.test_name,t_alias.test_id, sum(t_alias.test_salary) test_salary_sum, row_number() over(partition by t_alias.test_name order by sum(t_alias.test_salary) desc) n_rank from (select 1 test_id, 'name_1' test_name, 100 test_salary from dual union all select 2 test_id, 'name_1' test_name, 200 test_salary from dual union all select 2 test_id, 'name_1' test_name, 300 test_salary from dual union all select 3 test_id, 'name_1' test_name, 300 test_salary from dual union all select 4 test_id, 'name_1' test_name, 400 test_salary from dual union all select 1 test_id, 'name_2' test_name, 200 test_salary from dual union all select 2 test_id, 'name_2' test_name, 200 test_salary from dual union all select 2 test_id, 'name_2' test_name, 200 test_salary from dual union all select 3 test_id, 'name_2' test_name, 400 test_salary from dual union all select 4 test_id, 'name_2' test_name, 400 test_salary from dual) t_alias group by t_alias.test_name,t_alias.test_id) where n_rank<=2 order by test_name,n_rank;

上述執行結果：

開窗函式例項執行結果

    獲得每個test_name欄位下test_id的test_salary的前2名。

開窗函式例項：

開船函式例項

另有開窗函式替代寫法,可以瞭解：

開窗函式替代寫法