R脚本在T-SQL的sp_execute_external_script中结果错误舍入问题

我之前碰到过类似的情况，这通常是R与SQL之间的数据类型映射默认设置或者R的数值输出精度限制搞的鬼。下面是几个针对性的解决方法，亲测有效：

1. 在R代码中强制设置输出精度并确保数值类型 #

默认情况下，R的options(digits)参数值是7，这会导致数值输出时被截断到7位有效数字。你可以在R脚本开头设置更高的精度，同时将summary()的结果显式转换为double类型，确保完整的数值被传递给SQL：

# 先设置R的全局输出精度，比如保留12位有效数字
options(digits = 12)

mysummary <- cbind.data.frame( 
  S = c("Minimum", "1st Quartal", "Median", "Mean", "3rd Quarter", "Maximum") , 
  do.call( cbind , lapply(costs_q_active, function(x) {
    # 将summary结果转换为double类型，避免格式丢失
    as.double(summary(x))
  }) )
)

2. 在SQL端显式定义结果集的数据类型 #

SQL的sp_execute_external_script在默认情况下会自动推断返回列的数据类型，这可能会把R的高精度数值映射到低精度的SQL类型（比如FLOAT或者DECIMAL(10,2)），从而导致舍入。你可以用WITH RESULT SETS子句强制指定每一列的高精度类型：

EXEC sp_execute_external_script
  @language = N'R',
  @script = N'
    options(digits = 12)
    mysummary <- cbind.data.frame( 
      S = c("Minimum", "1st Quartal", "Median", "Mean", "3rd Quarter", "Maximum") , 
      do.call( cbind , lapply(costs_q_active, function(x) as.double(summary(x))) )
    )
  ',
  @input_data_1 = N'-- 替换成你的输入数据查询
                    SELECT cost_col1, cost_col2, cost_col3 FROM your_cost_table;',
  @input_data_1_name = N'costs_q_active',
  -- 显式定义结果集的列和数据类型，这里用DECIMAL(18,8)保证足够精度
  WITH RESULT SETS (
    (
      Statistic_Type VARCHAR(50) NOT NULL,
      Cost_Column1 DECIMAL(18,8),
      Cost_Column2 DECIMAL(18,8),
      Cost_Column3 DECIMAL(18,8)
      -- 其他成本列依次添加
    )
  );

3. 兜底方案：用字符串传递全精度数值 #

如果前两种方法还不行，可以在R脚本中先把数值转换为保留全精度的字符串，然后在SQL端再转换回数值类型：

# 将数值转为带全精度的字符串
mysummary <- cbind.data.frame( 
  S = c("Minimum", "1st Quartal", "Median", "Mean", "3rd Quarter", "Maximum") , 
  do.call( cbind , lapply(costs_q_active, function(x) {
    format(summary(x), digits = 15)
  }) )
)

然后在SQL中先接收字符串，再转换为高精度数值：

SELECT 
  Statistic_Type,
  CAST(Cost_Column1 AS DECIMAL(18,8)) AS Cost_Column1,
  CAST(Cost_Column2 AS DECIMAL(18,8)) AS Cost_Column2
FROM (
  EXEC sp_execute_external_script
    @language = N'R',
    @script = N'
      options(digits = 15)
      mysummary <- cbind.data.frame( 
        S = c("Minimum", "1st Quartal", "Median", "Mean", "3rd Quarter", "Maximum") , 
        do.call( cbind , lapply(costs_q_active, function(x) format(summary(x), digits = 15)) )
      )
    ',
    @input_data_1 = N'SELECT cost_col1, cost_col2 FROM your_cost_table;',
    @input_data_1_name = N'costs_q_active',
    WITH RESULT SETS (
      (
        Statistic_Type VARCHAR(50),
        Cost_Column1 VARCHAR(50),
        Cost_Column2 VARCHAR(50)
      )
    )
) AS temp;

核心原因总结 #

• R默认的digits设置会截断数值输出，导致传递给SQL的是不完整的数值；
• SQL自动推断返回列类型时，可能选择了精度不足的数值类型，从而触发舍入。

通过在R端强制保留精度，同时在SQL端显式指定高精度数据类型，就能解决这个精度丢失的问题。

内容的提问来源于stack exchange，提问作者Miška