oracle 分析函数加order by的影响
create table test (id number(2), name varchar2(10), salary number(6,2));
insert into test values (1,'Tom',120);
insert into test values (2,'Ellen',240);
insert into test values (2,'Joe',80);
insert into test values (3,'Andy',300);
insert into test values (3,'Kary',500);
insert into test values (3,'Erick',1300);
insert into test values (3,'Hou',40);
insert into test values (3,'Mary',200);
insert into test values (3,'Secooler',800);
commit;
select * from test order by ID,name;
ID NAME SALARY
--- ---------- --------
1 Tom 120.00
2 Ellen 240.00
2 Joe 80.00
3 Andy 300.00
3 Erick 1300.00
3 Hou 40.00
3 Kary 500.00
3 Mary 200.00
3 Secooler 800.00
取每个分组最高收入
正确
select id,name, salary, first_value(name) over (partition by id order by salary desc ) as highest_sal_name from test
错误
select id,name, salary, last_value(name) over (partition by id order by salary ) as highest_sal_name from test
正确
select ID, name, salary, LAST_VALUE(name) OVER (partition by ID order by salary asc rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as highest_sal_name from test
执行顺序, 分组 -> 窗口 -> 排序
分析函数包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows)
窗口就是分析函数分析时要处理的数据范围,就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录,因此我们在想得到某个栏位的累计值时,我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样,即整个组的总和。
窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。
1)row 物理的位置windows范围;
2)range 逻辑的相对位置windows范围;
窗口子句中我们经常用到指定第一行,当前行,最后一行这样的三个属性。
当前行之前的所有行, unbounded preceding,
当前行是 current row,
当前行之后的所有行, unbounded following,
窗口子句不能单独出现,必须有order by子句时才能出现,如:
last_value(sal) over(partition by deptno order by sal
rows between unbounded preceding and unbounded following)
以上示例指定窗口为整个分组。而出现order by子句的时候,不一定要有窗口子句,但效果会很不一样,此时的窗口默认是当前组的第一行到当前行!
当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
相关文章
- C++ 操作 Oracle
- oracle分析函数技术详解(配上开窗函数over())
- RDBMS SQL 编辑器 | MySQL、Oracle、MariaDB、SQLsever、SQLite、PostgreSQL
- Oracle中NVL、NVL2、DECODE函数的用法
- Oracle BigFile
- 【Docker】安装Presto连接Hive、mysql、oracle、postgresql、SQL server等7种类型数据库
- ORA-01034: ORACLE not available问题
- 《Oracle SQL疑难解析》——1.1 从表中查询数据
- Mybatis调用Oracle存储过程,Oracle创建存储过程,Mybatis调用procedure
- oracle函数 least(exp1,exp2,exp3,……,expn)
- oracle函数 LENGTHC(c1).LENGTH2(c1).LENGTH4(c1)
- oracle函数 INITCAP(c1)
- oracle函数 CHR(n1)
- oracle函数 ASCII(x1)
- oracle函数 to_single_byte(c1)
- oracle函数 MAX([distinct|all]x)
- oracle函数 lag()和lead()
- [Oracle维护工程师手记]一次升级后运行变慢的分析
- 英语linux+英语firefox+英语Oracle OEM如何设置成显示日语
- ORACLE 索引
- oracle 11 自增
- Mybatis之Oracle增删查改示例--转
- 测试连接oracle数据库耗时
- Oracle 与 Mysql 数据类型差异