代码之家  ›  专栏  ›  技术社区  ›  SidC

使用dplyr和paste0汇总和比较结果后,没有返回任何记录

  •  0
  • SidC  · 技术社区  · 6 年前

    Declare @sStudy varchar(50)
    Set @sStudy = Convert(Varchar(50),@StudyID)
    Declare @inquery nvarchar(max) = N'Select
            c.StudyID, c.RespID, c.ProductNumber, c.ProductSequence, c.BottomScaleValue, 
            c.BottomScaleAnchor, c.TopScaleValue, c.TopScaleAnchor, c.StudyDate,
            c.DayOfWeek, c.A, c.B, c.C, c.D, c.E, c.F,
            c.DependentVarYN, c.VariableAttributeID, c.VarAttributeName, c.[1] as c1, 
            c.[2] as c2, c.[3] as c3, c.[4] as c4, c.[5] as c5, c.[6] as c6, c.[7] as c7, c.[8] as c8
            from ClosedStudyResponses c
            --Sensory Value Attributes only for mean and standard deviation analytics.
            where VariableAttributeID = 1
            and c.StudyID =' +@sStudy ;
    
    BEGIN TRY
            --Insert into CodeMeans
            exec sp_execute_external_script
            @language = N'R',
            @script = N'
            library(dplyr)
            codemeans <- function(StudyID){
                res <- InputDataSet %>%
                    group_by (StudyID, ProductNumber) %>%
                    summarise_all(.funs=c(mean, sd)) %>%
                    setNames(c("StudyID","ProductNumber",
                    paste0("c",1:8, "_mean"),
                    paste0("c",1:8, "_sd")))
                df <- data.frame(res)
                }
                ',
    @input_data_1 = @inquery,
    @output_data_1_name = N'df',
    @params = N'@StudyID int',
    @StudyID = @StudyID
    

    当我通过提供有效的studyID来执行存储过程时,查询结束但不包含任何数据,尽管源表在某些(如果不是大多数)列中包含数据。

    1 回复  |  直到 6 年前
        1
  •  0
  •   notiv    6 年前

    我也认为总结的语法应该是不同的(不是。funs而是funs)。

    EXEC sp_execute_external_script
        @language = N'R',
        @script = N'
        library(dplyr)
    
        df <- InputDataSet %>%
                group_by (StudyID, ProductNumber) %>%
                summarise_all(funs(mean, sd)) %>%
                setNames(c("StudyID","ProductNumber",
                paste0("c",1:8, "_mean"),
                paste0("c",1:8, "_sd")))
            ',
        @input_data_1 = @inquery,
        @output_data_1_name = N'df',
        @params = N'@StudyID int',
        @StudyID = @StudyID
        WITH RESULT SETS(("StudyID" int, "ProductNumber" int, "c1_mean" int, 
        "c2_mean" int, "c3_mean" int, 
        "c4_mean" int, "c5_mean" int, "c6_mean" int, "c7_mean" int, "c8_mean" int, 
        "c1_sd" int, "c2_sd" int,
        "c3_sd" int, "c4_sd" int, "c5_sd" int, "c6_sd" int, "c7_sd" int, "c8_sd" int
        ));