如何在R的dbGetQuery中将SQL查询内的固定日期设为可复用变量

解决方案 #

你可以通过glue_sql搭配R原生字符串语法实现变量复用，同时彻底解决引号转义的问题，完整可运行代码如下：

# 加载依赖包
library(DBI)
library(glue)

# 仅需在此处定义一次日期，全局可复用
cutoff_date <- as.Date("2021-11-01")

# 构建SQL查询
cohort_query <- glue_sql(
  r"{
  select u.unique_id customer_id,
    o.user_id user_hash,
    "min"(o.deliveryconfirmeddate) first_order,
    (
        CASE
            WHEN ("min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '1' YEAR)) THEN 0
            WHEN ("min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '2' YEAR)) THEN 1
            WHEN ("min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '3' YEAR)) THEN 2
            WHEN ("min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '4' YEAR)) THEN 3
            WHEN ("min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '5' YEAR)) THEN 4
            ELSE 5
        END
    ) "cohort_year",
    min(o.id) as first_order_id
  FROM "bj-analytics"."mysql_bj_orders" o
  INNER JOIN "bj-analytics"."mysql_bj_users" u ON (u.user_id = o.user_id)
  WHERE o.connectedorder = false
    AND o.status <> 'cancelled'
    AND o.status <> 'Cancelled'
  GROUP BY 1, 2
  HAVING "min"(o.deliveryconfirmeddate) >= (date({cutoff_date}) - INTERVAL '6' YEAR)
    AND "min"(o.deliveryconfirmeddate) < date({cutoff_date})
  }",
  .con = con
)

# 执行查询
result <- dbGetQuery(con, cohort_query)

关键说明 #

• R 4.0及以上版本支持的r"{...}"是原生字符串语法，内部的单引号、双引号都不需要手动加\转义，直接解决引号匹配混乱的问题
• 所有需要替换日期的位置统一用{cutoff_date}占位，glue_sql会自动根据数据库连接适配转义规则，不需要手动给变量加引号
• 必须传入.con = con参数，这是glue_sql正确适配对应数据库SQL语法的核心要求
• 如果你的R版本低于4.0，把r"{...}"换成反引号包裹整个SQL语句即可，同样不需要手动转义内部引号

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