oracle下的group,oracle中分组取最大group by和over partition by的使用及比较

首先有数据：

数据表projects(项目) ，字段：id,project_id(项目编号),version(版本)，project_id和version都是有索引的，数据大致是一个项目(相同的project_id)会有多个不同的版本(version)，现需找出每个项目的最高版本的id, 编号以及版本.

1.group by

select project_id,max(version) version from projects group by project_id 找到每个项目最大的版本再join一下查到需要的那条数据：

select p.* from projects p

inner join (select project_id,max(version) version from projects group by project_id) pp on pp.project_id=p.project_id and p.version=pp.version

如果有projects的条件最好写在分组里面，因为如果projects表很大的时候group by所有数据的执行效率会慢

2. over partition by

select * from ( select  rank() over (partition by p.project_id order by p.version desc) rk ,p.* from projects p ) where rk=1

rank找出每条记录在结果中通过project_id分组version的排名，限制条件越多，结果越少的时候执行效率越高，在连接其他表进行复杂查询的时候表现比group by的表现优秀一点

做了一个explain比较，由于我们的projects表比较大，并且字段比较多，在select *的时候group by的cost为 9w多，而rank…为17w多，相比之下就是rank查询记录的时候会给所有符合要求的version的记录都临时存放起来再筛选rank=1的，字段越多，那么首次筛选的记录就越多，造成耗费越大；而group by在开始的时候只筛选出了project_id和version，然后1对1join，减少了很多耗费。那么继续测试select p.id-->1:36510 , 2:30974  |  p.id,p.name --> 1:36157 , 2:42137 分析一下，由于group by用的join 两边会产生一点笛卡尔积，而rank没有，在选择较少字段的时候rank的效率会高那么一点；选择字段多的时候rank中间步骤产生的cost较多，浪费资源~，此时group by好用一点。

其实单纯group by 和 rank 的执行效率都差不多，只是要看在什么样的环境，什么样的需求里使用，在不同场景里需要多分析，多思考一下查询的原理，explain以及最终效果。