按月取组合净值，并补齐空缺数据

一、问题描述

　　取某组合，某个日期区间内的净值信息，要求如下：

　　1、按月分组，如果该月存在多条净值信息，则显示该月日期最大的净值信息；

　　2、如果该月不存在净值信息，则补齐数据，日期为当月第1天，净值为上月净值。

　　这种需求存在普遍性。

 

二、问题分析

　　1、需要从xxxx_fund_net中取有效净值，并根据月份分组；

　　2、需要补齐月份数据，净值日期为当月第一天；

　　3、需要为补齐的数据填补上月的净值；

　　问题的关键在于：补齐月份数据，并把上月净值作为其净值。

 

三、问题解决

　　SQL如下：

select

  fund_net.pk_fund,

  work_day.work_month,

  -- 处理补齐数据的日期

  case when (net_date is null) then work_month || '-01' else net_date end net_date,

  -- 处理补齐数据的净值

  cast (last_value(net_value ignore nulls) over (partition by pk_fund order by work_month rows between unbounded preceding and current row) as number(10, 6)) new_net_value,

  net_value old_net_value

from (

  -- 取从组合成立到当前工作日的月份

  select

    distinct substr(wd.workday, 1, 7) work_month

  from xxxx_workday wd

  where wd.workday <= (

    select max(net_date) work_day

    from xxxx_fund_net fn

    where fn.pk_fund = '0001AA1000000001KSRK' and fn.billstatus = 1

  ) and wd.workday >= (

    select

      net_date

    from xxxx_fund f

    where f.pk_fund = '0001AA1000000001KSRK'

  )

) work_day

left join (

  -- 按月取有效净值

  select

    f.pk_fund, f.net_date, substr(f.net_date, 1, 7) net_month, f.net_value

  from xxxx_fund_net f where f.pk_fund = '0001AA1000000001KSRK' and f.billstatus = 1 and f.net_date in (

    select

      max(fn.net_date) net_date

    from xxxx_fund_net fn

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

    where fn.pk_fund = '0001AA1000000001KSRK' and fn.billstatus = 1 and fn.net_date >= f.net_date

    group by substr(fn.net_date, 1, 7)

  )

) fund_net

-- 补齐数据的条件

on work_day.work_month = fund_net.net_month

order by work_day.work_month;

　　关键点解释如下：

　　1、补齐数据的条件

　　月份相等。

　　2、补齐数据的日期
　　如果为空，则用月份+'01'作为净值日期

　　3、补齐数据的净值

　　根据pk_fund分组，按work_month(驱动表，总是有数据的)排序，进行开窗，对于每1条记录，如果net_value为空，则在分组窗口的第1行到当前行的范围内，找上一条非空的net_value，作为当前记录的net_value值。

 

以下是全部合法的ROWS窗口范围:

1)Rows between unbounded preceding and unbounded following

窗口开始于分组第一行，结束于分组最后一行。

 

2)Rows [between] unbounded preceding [and current row]

窗口开始于分组第一行，结束于当前行。

 

3)Rows between unbounded preceding and value_expr preceding

窗口开始于分组第一行，结束于当前行前value_expr行。

 

4)Rows between unbounded preceding and value_expr following

窗口开始于分组第一行，结束于当前行后value_expr行。

 

5)Rows between current row and unbounded following

窗口开始于当前行，结束于分组最后一行。

 

6)Rows [between current row and] current row

窗口开始于当前行，结束于当前行。

 

7)Rows between current row and value_expr following

窗口开始于当前行，结束于当前行后value_expr行。

 

8)Rows between value_expr preceding and unbounded following

窗口开始于当前行前value_expr行，结束于分组最后一行。

 

9)Rows [between value_expr] preceding [and current row]

窗口开始于当前行前value_expr行，结束于当前行。

 

10)Rows between value_expr1 preceding and value_expr2 preceding

窗口开始于当前行前value_expr1行，结束于当前行前value_expr2行。这里一定要满足value_expr1>=value_expr2。

 

11)Rows between value_expr1 preceding and value_expr2 following

窗口开始于当前行前value_expr1行，结束于当前行后value_expr2行。

 

12)Rows between value_expr following and unbounded following

窗口开始于当前行后value_expr行，结束于分组最后一行。

 

13)Rows between value_expr1 following and value_expr2 following

窗口开始于当前行后value_expr1行，结束于当前行后value_expr2行。这里一定要满足value_expr1<=value_expr2

 

14)Rows unbounded preceding

与2等价。

 

15)Rows current row

与6等价。

 

16)Rows value_expr preceding

与9等价。

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

转载于:http://blog.itpub.net/26802046/viewspace-719070/