row_number()over(partition by column_1，column_2)函数

1、案列一、使用该函数去重

天庭要给各路神仙发放中秋节礼物，统计了地址，但是有的需要神仙填写了多次数据，现在要进行去重，真实地址按照最后一次提交的时间为准

源数据：

解决办法：

select *
from (select t.*, row_number() over (partition by t.S_NAME order by t.CREATE_DATE_TIME desc) rn
      from worker_informastion t)
where rn = 1

结果：

如果要将不符合要求的数据删除

delete
from WORKER_INFORMASTION
where rowid in
      (select rd
       from (select t.*, row_number() over (partition by t.S_NAME order by t.CREATE_DATE_TIME desc) rn, rowid as rd
             from worker_informastion t)
       where rn > 1)

我们看下执行后的结果：

2、案列二：计算效益

超市需要根据上一年度的商品营业额来决定这一年度商品进货比例，需要统计2021-2022年度不用商品的营业额增长/降低情况

源数据：

统计sql:

select SALES.P_CATEGORY, SALES.P_SALES - SALES2021.P_SALES as sa
from (select t.P_CATEGORY, t.P_SALES, row_number() over (partition by t.P_CATEGORY order by t.P_CATEGORY,t.P_YEAR) as rn
      from Product t) SALES,
     (select t.P_CATEGORY, t.P_SALES, row_number() over (partition by t.P_CATEGORY order by t.P_CATEGORY,t.P_YEAR) as rn
      from Product t
      where t.P_YEAR = '2021') SALES2021
where SALES.P_CATEGORY = SALES2021.P_CATEGORY(+)
  and SALES.rn = SALES2021.rn + 1

结果