Oracle over窗口函数

oracle over函数

select t1.*, (t1.score - t3.avgs) as gaps
  from test_student_score t1,
       (select t2.subject_id, avg(t2.score) as avgs
          from test_student_score t2
         group by t2.subject_id) t3
 where t1.subject_id = t3.subject_id;

如果是使用over 窗口函数,这个sql就变成了一行代码,如下

select t.*,
       (t.score-avg(t.score) over( partition by t.subject_id)) as gaps 
   from test_student_score t

删除重复记录sql

-- sql是对的，但是执行计划有问题
delete test where rowid not in (select max(rowid) from test group by object_id)

delete test where rowid in(
    select row_number() over(partition by object_id order by )
)