Oracle SQL 分析函数 first、last、first_value、last_value、nth_value
来源:互联网 发布:大学生分期借贷软件 编辑:程序博客网 时间:2024/05/17 20:25
--数据库版本SEAN@sean> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production--建表SEAN@sean> create table t as select 'A' || mod(level, 3) grp, level val, sysdate + level / 24 crt_date from dual connect by level <= 9;Table created.SEAN@sean> col grp for a10;SEAN@sean> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';Session altered.SEAN@sean> select * from t order by grp,crt_date;GRP VAL CRT_DATE---------- ---------- -------------------A0 3 2017-07-25 11:13:41A0 6 2017-07-25 14:13:41A0 9 2017-07-25 17:13:41 --结果行1A1 1 2017-07-25 09:13:41A1 4 2017-07-25 12:13:41A1 7 2017-07-25 15:13:41 --结果行2A2 2 2017-07-25 10:13:41A2 5 2017-07-25 13:13:41A2 8 2017-07-25 16:13:41 --结果行39 rows selected.SEAN@sean> exec dbms_stats.gather_table_stats(user,'T');PL/SQL procedure successfully completed.--需求求每组时间(crt_date)最大的那个值(val),结果字段命令为 result_val 和 result_crt_date--如果 order by t.crt_date 不能唯一确定排序,那么结果可能不正确。这时可以加入 rowid 辅助排序 order by t.crt_date,rowid,本例假设 order by t.crt_date 能唯一确定排序--方法1 row_number()SEAN@sean> select grp, val result_val, crt_date result_crt_date from (select t.grp, t.val, t.crt_date, row_number() over(partition by t.grp order by crt_date desc) rn from t) where rn = 1;GRP RESULT_VAL RESULT_CRT_DATE---------- ---------- -------------------A0 9 2017-07-25 17:13:41A1 7 2017-07-25 15:13:41A2 8 2017-07-25 16:13:41--方法2 (last + order by asc)SEAN@sean> select t.grp, max(t.val) keep(dense_rank last order by t.crt_date asc) result_val, max(t.crt_date) keep(dense_rank last order by t.crt_date asc) result_crt_date from t group by t.grp;GRP RESULT_VAL RESULT_CRT_DATE---------- ---------- -------------------A0 9 2017-07-25 17:13:41A1 7 2017-07-25 15:13:41A2 8 2017-07-25 16:13:41--方法3 (first + order by desc)SEAN@sean> select t.grp, max(t.val) keep(dense_rank first order by t.crt_date desc) result_val, max(t.crt_date) keep(dense_rank first order by t.crt_date desc) result_crt_date from t group by t.grp;GRP RESULT_VAL RESULT_CRT_DATE---------- ---------- -------------------A0 9 2017-07-25 17:13:41A1 7 2017-07-25 15:13:41A2 8 2017-07-25 16:13:41--方法3.1 (first + order by desc) 的分析函数写法,每组返回多行数据SEAN@sean> select t.grp,t.val,t.crt_date, max(t.val) keep(dense_rank first order by t.crt_date desc) over(partition by t.grp) result_val, max(t.crt_date) keep(dense_rank first order by t.crt_date desc) over(partition by t.grp) result_crt_date from t;GRP VAL CRT_DATE RESULT_VAL RESULT_CRT_DATE---------- ---------- ------------------- ---------- -------------------A0 3 2017-07-25 11:13:41 9 2017-07-25 17:13:41A0 9 2017-07-25 17:13:41 9 2017-07-25 17:13:41A0 6 2017-07-25 14:13:41 9 2017-07-25 17:13:41A1 1 2017-07-25 09:13:41 7 2017-07-25 15:13:41A1 7 2017-07-25 15:13:41 7 2017-07-25 15:13:41A1 4 2017-07-25 12:13:41 7 2017-07-25 15:13:41A2 8 2017-07-25 16:13:41 8 2017-07-25 16:13:41A2 2 2017-07-25 10:13:41 8 2017-07-25 16:13:41A2 5 2017-07-25 13:13:41 8 2017-07-25 16:13:419 rows selected.--If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.--对于分析函数,省略窗口子句,默认值是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,也就是第一行到当前行--方法4 (last_value)select t.grp,t.val,t.crt_date, last_value(t.val) over(partition by t.grp order by t.crt_date asc range between unbounded preceding and unbounded following) result_val, last_value(t.crt_date) over(partition by t.grp order by t.crt_date asc range between unbounded preceding and unbounded following) result_crt_date from t;--方法5 (first_value)select t.grp,t.val,t.crt_date, first_value(t.val) ignore nulls over(partition by t.grp order by t.crt_date desc range between unbounded preceding and unbounded following) result_val, first_value(t.crt_date) ignore nulls over(partition by t.grp order by t.crt_date desc range between unbounded preceding and unbounded following) result_crt_date from t;--方法6 (nth_value)select t.grp,t.val,t.crt_date, nth_value(t.val, 1) from last ignore nulls over(partition by t.grp order by t.crt_date asc rows between unbounded preceding and unbounded following) result_val, nth_value(t.crt_date, 1) from last ignore nulls over(partition by t.grp order by t.crt_date asc rows between unbounded preceding and unbounded following) result_crt_date from t;总结:使用内置函数避免一些中间视图的构造和表的自连接
阅读全文
0 0
- Oracle SQL 分析函数 first、last、first_value、last_value、nth_value
- Oracle分析函数总结(2) - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,lead
- oracle 分析函数 FIRST_VALUE、LAST_VALUE
- Oracle 分析函数 Last_value和First_Value
- Oracle分析函数之first_value和last_value 分析函数详解
- 分析函数first_value()与last_value()
- 分析函数first_value()与last_value()
- 分析函数:first_value,last_value用法
- 分析函数——first_value()与last_value()
- 分析函数——FIRST_VALUE()和LAST_VALUE()
- 分析函数--FIRST_VALUE,LAST_VALUE,LAG,LEAD,ROW_NUMBER
- oracle分析函数:三、first,first_value,lag,lead函数
- HIVE分析窗口函数: LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数之LAG,LEAD,FIRST_VALUE和LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- 自制平衡小车:从入门到放弃——基于stm32的平衡小车系列(三)
- 网易招聘笔试(升级之路)
- 图像处理7:滑动条调色板
- spark LDA(原理+源码+示例)(推荐)
- Spark Streaming java实现简单例子(一)
- Oracle SQL 分析函数 first、last、first_value、last_value、nth_value
- 教你如何一步步将项目部署到Github
- MyBatis基础_01
- 初窥Linux 之 我最常用的20条命令
- typeof,instanceof,Object.prototype.toString.call(),js内置对象
- HDOJ 1003 MaxSum(动态规划 求最大子序列和)
- [书单 makefile视频]
- 时间同步
- PTA甲 1003. Emergency (25)