row_number() over()計算每組前幾個和前百分之幾

1. row_number() over()

文法

ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 [desc])           

根據column1分組，根據column2排序

2.具體事例

如下，我們通過具體執行個體進行了解分析。

有如下一張表：

create table LJB_TEST
(
  dep    NUMBER,
  name   VARCHAR2(50),
  salary NUMBER
);

insert all
into ljb_test values('1','start','5000')
into ljb_test values('1','ha','5000')
into ljb_test values('1','end','4000')
into ljb_test values('1','begin','4500')
into ljb_test values('2','ri','4000')
into ljb_test values('2','zhou','6000')
into ljb_test values('2','yue','3000')
into ljb_test values('3','kettle','4000')
into ljb_test values('3','oracle','4000')
into ljb_test values('3','hive','4000')
into ljb_test values('3','mysql','4000')
into ljb_test values('3','plsql','5000')
select 1 from dual
;
commit;           

我們有如下需求：

2.1.計算每個部門薪水值前二的的人員。

自然的，我們根據部門分組，根據薪水降序，取前兩個。

我們通過SQL實作上述想法。

select * from
(
select 
    t.*,
    row_number() over(partition by dep order by salary desc) rn 
from ljb_test t 
)t
where t.rn<=2
;           

不過，我們很自然的發現，很多人的薪水是相同的，因而，實際上薪水值前二的員工并不代表是兩個員工。也就是說，我們要查的是薪水>=某個值的記錄。

是以，我們将自己的想法通過SQL實作。

with tmp
as
(
    select dep,salary from
    (
        select 
            dep,salary,
            row_number() over(partition by dep order by salary desc) rn 
        from ljb_test t 
    )t
    where t.rn=2
)
select
    t.*
from ljb_test t
inner join
tmp
on t.dep = tmp.dep
and t.salary>=tmp.salary
order by t.dep,t.salary desc           

2.2.計算前百分之幾。

如下，我們需要計算每個部門薪水前50%的員工資訊，且如果不滿兩人，取第一個人薪水。

這個問題的核心就是找出每組的50%的分水嶺（前10%的邏輯一樣）。

如下：

with tmp as
(
    select 
        dep,salary,
        row_number() over(partition by dep order by salary desc) rn 
    from ljb_test t 
)
select tmp.dep,tmp.salary
from tmp
inner join
(
    select tmp.dep,tmp.salary
    from tmp
    --2.關聯獲得50%的薪水值
    inner join
    (
        --1.算出前50%的每組的row_number()
        select t.dep, round(case when count(*)*0.5<1 then 1 else count(*)*0.5 end,0) ct
        from ljb_test t
        group by t.dep
    )tt
    on tmp.dep = tt.dep and tmp.rn = tt.ct
)ttt
on tmp.dep = ttt.dep and tmp.salary >= ttt.salary