R dbplyr连接SQL Server时百分比计算错误问题排查

问题背景 #

通过ODBC连接Microsoft SQL Server数据库，使用dbplyr生成聚合汇总表：每行对应一个serovar（血清型），每列对应reportyear（报告年份），单元格为该血清型对应年份的住院病例百分比。本地data.frame运行代码可得到预期结果，但数据库实时连接时，百分比列仅返回0或100。

示例数据 #

# 创建示例数据：
dt <- data.frame(
  pid = c(1,2,3,4,5,6,7,8), 
  serovar = c("x", "x", "x", "x", "y", "y", "y", "y"), 
  reportyear = c(2020, 2020, 2020, 2021, 2020, 2020, 2021, 2021), 
  hospitalised = c("Y", "Y", "N", "N", NA, "Y", NA, "N"))

# 输出：
> dt
  pid serovar reportyear hospitalised
1   1       x       2020            Y
2   2       x       2020            Y
3   3       x       2020            N
4   4       x       2021            N
5   5       y       2020         <NA>
6   6       y       2020            Y
7   7       y       2021         <NA>
8   8       y       2021            N

R代码实现 #

# 创建数据库延迟连接：
db <- tbl(con, 
          category = params$category, 
          schema = "clean", 
          table = viewname)

summarytab <- db %>% 
  
  # 选择所需列：
  select(serovar, reportyear, hospitalised) %>% 
  
  # 重新编码住院状态：
  mutate(hospitalised_tf = case_when(
    hospitalised == "Y" ~ TRUE,
    hospitalised == "N" ~ FALSE, 
    .default = NA
  )) %>%
  
  # 按年份筛选行：
  filter(between(reportyear, 2020, 2024)
         & !is.na(serovar) 
         & !is.na(reportyear)
         & !is.na(hospitalised_tf)) %>%
  
  # 按血清型和年份分组：
  group_by(serovar, reportyear) %>% 
  
  # 计算各血清型、年份的总病例数和住院病例数：
  summarise(total_cases = n(),
            hosp_sum = sum(hospitalised_tf, na.rm = TRUE)) %>%
  
  # 计算住院百分比：
  mutate(hosp_pct = (hosp_sum/total_cases)*100) %>%
  
  # 按血清型和年份排序：
  arrange(serovar, reportyear) %>%
  
  # 拉取结果到本地查看：
  collect()

本地运行结果 #

> summarytab
# A tibble: 4 × 5
# Groups:   serovar [2]
  serovar reportyear total_cases hosp_sum hosp_pct
  <chr>        <dbl>       <int>    <int>    <dbl>
1 x             2020           3        2     66.7
2 x             2021           1        0      0  
3 y             2020           1        1    100  
4 y             2021           1        0      0

生成的SQL查询 #

show_query(summarytab)
<SQL>
SELECT "q01".*, ("hosp_sum" / "total_cases") * 100.0 AS "hosp_pct"
FROM (
  SELECT
    "serovar",
    "reportyear",
    COUNT(*) AS "total_cases",
    SUM("hospitalised_tf") AS "hosp_sum"
  FROM (
    SELECT "q01".*
    FROM (
      SELECT
        "q01".*,
        CASE
WHEN ("hospitalised" = 'Y') THEN 1
WHEN ("hospitalised" = 'N') THEN 0
ELSE NULL
END AS "hospitalised_tf"
      FROM (
        SELECT
          "Serotype" AS "serovar",
          "DateUsedForStatisticsYear" AS "reportyear",
          "Hospitalisation" AS "hospitalised"
        FROM "FWD"."clean"."SALM_Case"
      ) "q01"
    ) "q01"
    WHERE ("reportyear" BETWEEN 2020.0 AND 2024.0 AND NOT(("serovar" IS NULL)) AND NOT(("reportyear" IS NULL)) AND NOT(("hospitalised_tf" IS NULL)))
  ) "q01"
  GROUP BY "serovar", "reportyear"
) "q01"
ORDER BY "serovar", "reportyear"
> 

原因分析 #

核心问题是SQL Server的整数除法规则：当两个整数类型的数值相除时，结果会自动取整为整数，而非浮点数。

在生成的SQL中：

• hosp_sum是SUM(hospitalised_tf)的结果，为整数类型
• total_cases是COUNT(*)的结果，也为整数类型

因此hosp_sum / total_cases的计算只会得到0（分子小于分母时）或1（分子等于分母时），再乘以100后自然只能得到0或100。而本地R运行时，R会自动将整数除法转换为浮点数除法，因此能得到正确的百分比值。

解决方法 #

需要强制SQL Server执行浮点数除法，只需将其中一个操作数转换为浮点数即可，两种常见实现方式：

方法1：用as.double()转换数值类型 #

修改百分比计算的代码：

mutate(hosp_pct = (as.double(hosp_sum)/total_cases)*100)

对应的SQL会自动转为：

SELECT "q01".*, (CAST("hosp_sum" AS FLOAT) / "total_cases") * 100.0 AS "hosp_pct"

方法2：乘以1.0强制转换为浮点数 #

修改百分比计算的代码：

mutate(hosp_pct = (hosp_sum * 1.0 / total_cases)*100)

对应的SQL会自动转为：

SELECT "q01".*, (("hosp_sum" * 1.0) / "total_cases") * 100.0 AS "hosp_pct"

两种方法都能让SQL Server执行浮点数除法，得到正确的百分比结果。

内容的提问来源于stack exchange，提问作者Amy M