Кумулятивная мера с использованием R, встроенного в SQL
Простите, что я очень новичок в R, и я просто смотрю варианты, которые в настоящее время находятся в нашей среде SQL 2016.
В настоящее время у нас есть требование предоставить кумулятивную прибыль. Примерный набор данных приведен ниже:
FundID Date FundReturn
ABC 1987-10-31 0
ABC 1987-11-30 -9.28669
ABC 1987-12-31 3.08304
ABC 1988-01-31 -3.00125
ABC 1988-02-29 0.61238
ABC 1988-03-31 4.29258
ABC 1988-04-30 0.13697
ABC 1988-05-31 2.57786
ABC 1988-06-30 2.36947
ABC 1988-07-31 0.57114
ABC 1988-08-31 -1.21550
ABC 1988-09-30 7.09027
ABC 1988-10-31 3.45807
ABC 1988-11-30 1.12679
Нам нужно будет взять этот набор данных и применить к нему совокупный показатель возврата производительности, чтобы datset выглядел следующим образом:
FundID Date FundReturn FundReturnCumu100 FundReturnCumu0
ABC 1987-10-31 0 1 0
ABC 1987-11-30 -9.28669 0.9071331 -0.0928669
ABC 1987-12-31 3.08304 0.935100376 -0.064899624
ABC 1988-01-31 -3.00125 0.907035676 -0.092964324
ABC 1988-02-29 0.61238 0.912590181 -0.087409819
ABC 1988-03-31 4.29258 0.951763845 -0.048236155
ABC 1988-04-30 0.13697 0.953067476 -0.046932524
ABC 1988-05-31 2.57786 0.977636221 -0.022363779
ABC 1988-06-30 2.36947 1.000801018 0.000801018
ABC 1988-07-31 0.57114 1.006516993 0.006516993
ABC 1988-08-31 -1.2155 0.994282779 -0.005717221
ABC 1988-09-30 7.09027 1.064780113 0.064780113
ABC 1988-10-31 3.45807 1.101600954 0.101600954
ABC 1988-11-30 1.12679 1.114013684 0.114013684
Я могу создать это в SQL со следующим кодом:
SELECT
FundID
, [Date]
, FundReturn
, ISNULL (
EXP(SUM(LOG(ABS(NULLIF((FundReturn+100)/100, 1))))
OVER(ORDER BY FundID, [Date] ROWS UNBOUNDED PRECEDING))
,1) AS FundReturnCumu100
, ISNULL (
EXP(SUM(LOG(ABS(NULLIF((FundReturn+100)/100, 1))))
OVER(ORDER BY FundID, [Date] ROWS UNBOUNDED PRECEDING))
,1)-1 AS FundReturnCumu0
FROM #Worktable
ORDER BY [Date]
Я также хочу проверить, возможно ли это, используя функцию R, встроенную в хранимую процедуру SQL, чтобы получить тот же результат? Вышеприведенная математика в основном является результатом возврата производительности в период времени, поэтому есть ли функция продукта, которую я мог бы использовать, чтобы создать тот же результат datset?
Изменить: я до сих пор выполнял следующие действия, которые возвращают основу набора данных, используя sp_execute_external_script:
EXEC sp_execute_external_script
@language = N'R'
, @script = N'OutputDataSet<-InputDataSet'
, @input_data_1 = N' SELECT *
FROM [InMemory].[dbo].[CumulativePerformanceTest]
ORDER BY [FundID],[Date]'
WITH RESULT SETS (
(
[FundID] NVARCHAR(50)
, [Date] DATE
, [FundReturn] NVARCHAR(255)
)
);
GO
Что мне нужно сделать, чтобы изменить приведенное выше, чтобы применить вычисления FundReturnCumu100 и FundReturnCumu0 в R?
Спасибо
Ответы
Ответ 1
После долгих размышлений в Google мне удалось это решить. В итоге я придумал следующее:
DECLARE @R_Script NVARCHAR(MAX);
SET @R_Script = N'
OutputDataSet <- InputDataSet;
OutputDataSet[,6] <- exp(cumsum(log(abs((InputDataSet$FundReturn+100)/100))));
OutputDataSet[,7] <- exp(cumsum(log(abs((InputDataSet$BenchmarkReturn+100)/100))));
OutputDataSet[,8] <- exp(cumsum(log(abs((InputDataSet$SectorReturn+100)/100))));
OutputDataSet[,9] <- (exp(cumsum(log(abs((InputDataSet$FundReturn+100)/100)))))-1;
OutputDataSet[,10] <- (exp(cumsum(log(abs((InputDataSet$BenchmarkReturn+100)/100)))))-1;
OutputDataSet[,11] <- (exp(cumsum(log(abs((InputDataSet$SectorReturn+100)/100)))))-1;';
DECLARE @SQL_Script NVARCHAR(MAX)
SET @SQL_Script = N'
SELECT
FundID
, Date
, CONVERT(DECIMAL(38,6), FundReturn) AS FundReturn
, CONVERT(DECIMAL(38,6), BenchmarkReturn) AS BenchmarkReturn
, CONVERT(DECIMAL(38,6), SectorReturn) AS SectorReturn
FROM [InMemory].[dbo].[CumulativePerformanceTest]
WHERE FundID = ''F000002D0V''
ORDER BY FundID,Date;';
EXEC sp_execute_external_script
@language = N'R',
@script = @R_Script,
@input_data_1 = @SQL_Script
WITH RESULT SETS (
(
[FundID] NVARCHAR(50)
, [Date] DATE
, [FundReturn] DECIMAL(38,6)
, [BenchmarkReturn] DECIMAL(38,6)
, [SectorReturn] DECIMAL(38,6)
, [FundReturnCumu100] DECIMAL(38,6)
, [BenchmarkReturnCumu100] DECIMAL(38,6)
, [SectorReturnCumu100] DECIMAL(38,6)
, [FundReturnCumu0] DECIMAL(38,6)
, [BenchmarkReturnCumu0] DECIMAL(38,6)
, [SectorReturnCumu0] DECIMAL(38,6)
)
);
GO
Я знаю, что кодирование потенциально может потребовать немного опрятного, но оно работает :)
Ответ 2
Мы можем преобразовать его в dplyr код с помощью
library(dplyr)
df1 %>%
arrange(FundID, Date) %>%
mutate(FundReturnCumu100 = exp(cumsum(log(abs((FundReturn + 100)/100)))),
FundReturnCumu0 = FundReturnCumu100 - 1)
# FundID Date FundReturn FundReturnCumu100 FundReturnCumu0
#1 ABC 1987-10-31 0.00000 1.0000000 0.0000000000
#2 ABC 1987-11-30 -9.28669 0.9071331 -0.0928669000
#3 ABC 1987-12-31 3.08304 0.9351004 -0.0648996237
#4 ABC 1988-01-31 -3.00125 0.9070357 -0.0929643237
#5 ABC 1988-02-29 0.61238 0.9125902 -0.0874098186
#6 ABC 1988-03-31 4.29258 0.9517638 -0.0482361550
#7 ABC 1988-04-30 0.13697 0.9530675 -0.0469325241
#8 ABC 1988-05-31 2.57786 0.9776362 -0.0223637789
#9 ABC 1988-06-30 2.36947 1.0008010 0.0008010181
#10 ABC 1988-07-31 0.57114 1.0065170 0.0065169930
#11 ABC 1988-08-31 -1.21550 0.9942828 -0.0057172210
#12 ABC 1988-09-30 7.09027 1.0647801 0.0647801126
#13 ABC 1988-10-31 3.45807 1.1016010 0.1016009542
#14 ABC 1988-11-30 1.12679 1.1140137 0.1140136836
