如何用dplyr处理多表列选择？Shiny反应式场景下求助

Hey there! Let's break down your problem and fix this step by step — first clearing up a common misconception about dplyr, then giving you working solutions for both dplyr and sqldf approaches:

1. Dplyr absolutely supports 3+ table joins #

You might have overlooked that dplyr lets you chain join operations seamlessly. For your exact SQL query, here's how to translate it to dplyr while properly handling your reactive tables (remember to add () to call your reactive objects!):

ratios_135_final <- ratios() %>%
  inner_join(capital_final(), by = "REGN") %>%
  inner_join(rwa_final(), by = "REGN") %>%
  inner_join(names(), by = "REGN") %>%
  inner_join(buffer_bank(), by = "REGN") %>%
  mutate(
    n1.0_after_stress = tot_cap_after_stress * 100 / rwa_0_after_stress,
    n1.2_after_stress = osn_cap_after_stress * 100 / rwa_2_after_stress,
    n1.1_after_stress = bas_cap_after_stress * 100 / rwa_1_after_stress
  ) %>%
  select(n1.0_after_stress, n1.2_after_stress, n1.1_after_stress, REGN, NAME, date, buff)

If you prefer more explicit syntax for complex joins, you can replace each by = "REGN" with join_by(REGN) — it works identically for this use case.

2. Using sqldf with reactive tables #

If you want to stick with sqldf, the workaround is to first capture your reactive tables into regular variables within your reactive context, then reference those variables in your SQL string. Sqldf looks for objects in the current environment, so this avoids the issue of not being able to use ratios() directly in the query:

# First, capture reactive tables into temporary variables
ratios_temp <- ratios()
capital_temp <- capital_final()
rwa_temp <- rwa_final()
names_temp <- names()
buffer_temp <- buffer_bank()

# Now use the temp variables in your sqldf query
ratios_135_final <- sqldf("
  select 
    b.tot_cap_after_stress*100/c.rwa_0_after_stress as 'n1.0_after_stress',
    b.osn_cap_after_stress*100/c.rwa_2_after_stress as 'n1.2_after_stress',
    b.bas_cap_after_stress*100/c.rwa_1_after_stress as 'n1.1_after_stress',
    a.'REGN', d.'NAME', a.date, f.buff 
  from ratios_temp a 
  inner join capital_temp b on (a.'REGN' = b.'REGN') 
  inner join rwa_temp c on (a.'REGN' = c.'REGN') 
  inner join names_temp d on (a.'REGN' = d.'REGN') 
  inner join buffer_temp f on (a.'REGN' = f.'REGN') 
")

Just make sure these temporary variables are defined in the same reactive scope (like inside an observe() or reactive() block) where you run the sqldf query.

Both approaches will work smoothly with your Shiny reactive setup — pick whichever syntax feels more intuitive for you!

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