一、问题描述

　　给某项目的报表写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值是最大的。