大数据——Redshift通过UDF函数实现金融业XIRR函数

函数简介：
https://support.microsoft.com/zh-cn/office/xirr-%e5%87%bd%e6%95%b0-de1242ec-6477-445b-b11b-a303ad9adc9d?ui=zh-cn&rs=zh-cn&ad=cn

编写python UDF函数

CREATE OR REPLACE FUNCTION xirr(dates VARCHAR ,moneys VARCHAR)
RETURNS numeric(10,6) IMMUTABLE
AS $$

    from scipy import optimize
    from datetime import datetime
    def xnpv(rate, moneys, dates):
        return sum([float(money) / ((1 + rate) ** (dts / 365.0)) for (money, dts) in zip(moneys, dates)])

    def xirr(dates,moneys,guess=0.1):
        moneys_tmp = []
        for value in moneys.split(','):
            moneys_tmp.append(value)
        moneys = moneys_tmp
        dates_data = []
        for t in dates.split(','):
            t_tmp = (datetime.strptime(t.split(' ')[0], '%Y-%m-%d').date() - datetime.strptime(
                dates.split(',')[0].split(' ')[0], '%Y-%m-%d').date()).days
            da = t_tmp
            dates_data.append(int(da))
        dates = dates_data
        xirr_data = optimize.newton(lambda r: xnpv(r, moneys, dates), guess)
        return xirr_data
    return xirr(dates,moneys)
$$ LANGUAGE plpythonu;

导入官方数据
测试使用：

select
  name,xirr(dates,moneys)
  from (
  select id,name, date, money,
    listagg(date, ',' )  WITHIN GROUP (ORDER BY date) OVER ( PARTITION BY name ) as dates , 
    listagg(money, ',' )  WITHIN GROUP (ORDER BY date) OVER ( PARTITION BY name ) as moneys 
  from check_info.xirrtest
    ) a 
    group by name,dates,moneys