ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分组的同时取符合条件的数据

分组的同时取符合条件的数据

原创 Linux操作系统 作者:yujj_cn 时间:2012-03-19 23:08:51 0 删除 编辑

一、问题描述

  给某项目的报表写SQL。需要根据pk_plan(计划主键)取投资组合的最新净值,在xxxx_fund(投资组合)中没有为组合记录当前净值,需要从xxxx_fund_net(投资组合净值)中取数。

 

二、问题分析

  这类需求在开发系统的过程中存在普遍性,如取在一定的时间范围内,员工(或企业等)的期初(或期末)资产信息等。接上例,一般都这样实现:

select

  fn.pk_fund,

  fn.net_date,

  fn.net_value

from xxxx_fund_net fn

where  fn.billstatus = 1 and (fn.pk_fund, fn.net_date) in (

  select

    f.pk_fund,

    max(fn.net_date) net_date

  from xxxx_fund_net fn

  inner join xxxx_fund f on f.pk_fund = fn.pk_fund

  where f.pk_plan = '0001AA10000000014R7K' and fn.billstatus = 1

  group by f.pk_fund

);

  这种实现方式,能够满足要求,但要对基础表进行两次扫描,第1次扫描,取组合及组合最大净值日期;第2次扫描,根据第1次扫描的结果,到基础表中取数。并且同时还要分组排序。基础表小还则罢了,如果基础表大,效率一定是不高的。

  印象中有同事曾经解决过类似的问题,但今天我又碰上了,顺便再研究一下,并形成文档。

 

三、使用窗口函数,取各分组中,满足条件的记录

  SQL如下:

select * from (

  select

    row_number() over(partition by f.pk_fund order by fn.net_date desc nulls last) lev,

    fn.pk_fund,

    fn.net_date,

    fn.net_value

  from xxxx_fund_net fn

  inner join xxxx_fund f on fn.pk_fund = f.pk_fund

  where f.pk_plan = '0001AA10000000014R7K' and fn.billstatus = 1

) where lev = 1;

  关键在红色部分。根据pk_fund开窗(分组),按net_date降序排列,空值放在最后,编号。

  然后取每个分组的第1条记录,就是我们需要的结果集。

 

三、进一步研究

  在上述SQL中,内部的select会取出很多不需要的数据,分组排序并取各分组第1条记录,感觉不爽。使用以下SQL

select

  f.pk_fund,

  min(fn.net_date) keep(dense_rank first order by fn.net_date desc nulls last) net_date,

  min(fn.net_value) keep(dense_rank first order by fn.net_date desc nulls last) net_value

from xxxx_fund_net fn

inner join xxxx_fund f on fn.pk_fund = f.pk_fund

where f.pk_plan = '0001AA10000000014R7K' and fn.billstatus = 1

group by f.pk_fund;

  关键在红色部分。根据pk_fund分组,根据net_date降序排列,空值放在最后,取最大的一条值对应的记录,如果有多条,取min值。当net_date相同时,取任一条(最大、最小等)都是有效的,net_value这样取值有问题,能这样写是基于以下业务逻辑:一个工作日内,一个组合只能有一条有效的净值信息。

  DENSE_RANK:返回一个唯一值,当遇到相同数据时,它们的排列序号是一样的。

 

四、结论

  对于每天(或满足排序条件的最小单位)只能有一条有效数据的结果集,推荐使用第3种方案,否则推荐使用第2种方案。

 

  *题外话:在进行降序排序时,建议根据情况加上nulls last,因为在进行排序时,null值是最大的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26802046/viewspace-719067/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-03-19

  • 博文量
    8
  • 访问量
    37787