이란 - 파이썬 sql




SQL에 내장 된 R을 사용하는 누적 측정 (2)

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

나는 코딩이 잠재적으로 약간의 깔끔한 것을 필요로 할 수 있다는 것을 안다. 그러나 그것은 작동한다 :)

용서해주십시오. 저는 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

이 데이터 세트를 가져 와서 누적 성능 반환 값을 적용하여 데이터 세트가 다음과 같이 표시되도록해야합니다.

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]

SQL 스토어드 프로 시저에 임베드 된 R 함수를 사용하여 동일한 결과를 얻을 수 있는지 테스트하고 싶습니다. 위의 수학은 기본적으로 timeseries에 대한 성능 반환의 산물이므로 동일한 결과 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

R에서 FundReturnCumu100FundReturnCumu0 계산을 적용하려면 위의 내용을 변경하려면 어떻게해야합니까?

감사


우리는 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




sql-server