--数据库版本
SEAN@sean> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL 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:41
A0 6 2017-07-25 14:13:41
A0 9 2017-07-25 17:13:41 --结果行1
A1 1 2017-07-25 09:13:41
A1 4 2017-07-25 12:13:41
A1 7 2017-07-25 15:13:41 --结果行2
A2 2 2017-07-25 10:13:41
A2 5 2017-07-25 13:13:41
A2 8 2017-07-25 16:13:41 --结果行3
9 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:41
A1 7 2017-07-25 15:13:41
A2 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:41
A1 7 2017-07-25 15:13:41
A2 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:41
A1 7 2017-07-25 15:13:41
A2 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:41
A0 9 2017-07-25 17:13:41 9 2017-07-25 17:13:41
A0 6 2017-07-25 14:13:41 9 2017-07-25 17:13:41
A1 1 2017-07-25 09:13:41 7 2017-07-25 15:13:41
A1 7 2017-07-25 15:13:41 7 2017-07-25 15:13:41
A1 4 2017-07-25 12:13:41 7 2017-07-25 15:13:41
A2 8 2017-07-25 16:13:41 8 2017-07-25 16:13:41
A2 2 2017-07-25 10:13:41 8 2017-07-25 16:13:41
A2 5 2017-07-25 13:13:41 8 2017-07-25 16:13:41
9 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;
总结:使用内置函数避免一些中间视图的构造和表的自连接
Oracle SQL 分析函数 first、last、first_value、last_value、nth_value
最新推荐文章于 2023-07-24 16:37:18 发布