修改NHS处方数据API的R脚本SQL查询以匹配药物类别

先排查LIKE语句失效的核心原因 #

• API语法限制：部分数据API会限制SQL语法，可能不支持LIKE通配符。先直接在API的测试环境（如果提供）执行SELECT * FROM presc WHERE bnf_chemical_substance LIKE '0403%'，验证是否能返回预期结果。
• 字段类型不匹配：确认bnf_chemical_substance的存储类型，如果是数值型，LIKE会失效，需要转成字符串后再匹配：CAST(bnf_chemical_substance AS VARCHAR) LIKE '0403%'。
• 脚本参数转义问题：原脚本用sprintf构建SQL时，单个%会被当作转义符，导致最终生成的SQL里没有正确的%通配符。

修改脚本的具体实现 #

原脚本的查询构建逻辑大概率是这样的：

sql_query <- sprintf("SELECT * FROM presc WHERE bnf_chemical_substance = '%s'", drug_code)

要改成支持前缀匹配的写法，需要用%%来输出实际的%：

# 定义目标药物类别前缀
drug_prefix <- "0403"
# 构建带LIKE的查询语句
sql_query <- sprintf("SELECT * FROM presc WHERE bnf_chemical_substance LIKE '%s%%'", drug_prefix)

这样最终生成的SQL就是LIKE '0403%'，能正确匹配所有以0403开头的编码。

备选方案：如果API不支持LIKE #

要是API确实不允许用LIKE，可以分两步走：

1. 先拉取所有唯一的bnf_chemical_substance编码
2. 筛选出前缀匹配的编码，再批量查询

# 1. 获取所有化学物质编码
all_codes_query <- "SELECT DISTINCT bnf_chemical_substance FROM presc"
all_codes <- execute_query(all_codes_query) # 复用原脚本的查询执行函数
# 2. 筛选以0403开头的编码
target_codes <- all_codes$bnf_chemical_substance[grepl("^0403", all_codes$bnf_chemical_substance)]
# 3. 批量查询（注意API的请求限制，数据量大的话要分批次）
results_list <- lapply(target_codes, function(code) {
  sql <- sprintf("SELECT * FROM presc WHERE bnf_chemical_substance = '%s'", code)
  execute_query(sql)
})
# 合并所有结果
final_results <- do.call(rbind, results_list)

额外注意点 #

• 确认API的单次查询行数上限，避免因数据量过大被限流；
• 核对字段名是否正确，比如是否混淆了bnf_chemical_substance和bnf_code这类相似字段。

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