网站首页 文章专栏 oracle 高级查询分析函数
oracle 高级查询分析函数
编辑时间:2019-11-08 10:15:16.0 作者:hcy 浏览量:164

惭愧!!!!

常用分析函数如下

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

业务场景:以某一行group by, 但group by 后的某列需要依据其中某列的排序取对应的另一个字段。

select rlbatch, max(qm), max(zm)
  from (select r.*,
               first_value(rlscode) over(partition by rlbatch order by rlrdate) qm,
               last_value(rlscode) over(partition by rlbatch order by rlrdate rows between unbounded preceding and unbounded following) zm
          from reclist r
         where rlmonth = '2019.07')
 group by rlbatch;

注意事项:

first_value() over(partition by ... order by ...) 可正常查询
last_value() over(partition by ... order by ...) 使用它的时候就失效了。

相关链接

https://www.cnblogs.com/dongyj/p/5992083.html

https://www.cnblogs.com/cjm123/p/8033892.html

https://blog.csdn.net/guobinhui/article/details/80965191

 

来说两句吧
最新评论