Динамическое использование фильтра sqldf и группировки в r

Вы можете построить предложение where так же, как и select.

library(sqldf)

table_a <- data.frame(id =c(1,1,1,2,2,2,3,4,4),
                  state = c("Texas","Texas","Texas","Alaska","Alaska","Alaska","Nevada","Texas","Texas"),
                  gender = c("M","M","M","F","F","F","F","F","F"),
                  channel = c("Y","Y","H","Y","D","D","H","Y","Y"),
                  spend = c(10,10,5,20,20,5,7,1,2))



# user selects variable and their values  
# channel is not selected by user here
input_states <- 'Texas'
input_genders <- 'M'
input_channels <- NULL

a <- c('state','gender','channel')[c(!is.null(input_states),!is.null(input_genders),!is.null(input_channels))]
b <- paste(a, collapse = ",")

# Create the where conditions
where_conditions <- c(
    sprintf("state in ('%s')", paste(input_states, collapse = "', '")),
    sprintf("gender in ('%s')", paste(input_genders, collapse = "', '")),
    sprintf("channel in ('%s')", paste(input_channels, collapse = "', '"))
)[c(!is.null(input_states),!is.null(input_genders),!is.null(input_channels))]

# combine into where clause
where_clause <- paste(where_conditions, collapse = " and ")

sqldf(sprintf("select %s,sum(spend) as tot_spend from table_a where %s group by %s", b, where_clause, b))
#>   state gender tot_spend
#> 1 Texas      M        25

что, я думаю, является вашим требованием?

Затем вы можете управлять выводом, изменяя переменные input_....

# Try with different combinations of input_...
input_states <- c('Texas', 'Nevada')
input_genders <- NULL
input_channels <- 'H'

a <- c('state','gender','channel')[c(!is.null(input_states),!is.null(input_genders),!is.null(input_channels))]
b <- paste(a, collapse = ",")

# Create the where conditions
where_conditions <- c(
    sprintf("state in ('%s')", paste(input_states, collapse = "', '")),
    sprintf("gender in ('%s')", paste(input_genders, collapse = "', '")),
    sprintf("channel in ('%s')", paste(input_channels, collapse = "', '"))
)[c(!is.null(input_states),!is.null(input_genders),!is.null(input_channels))]

# combine into where clause
where_clause <- paste(where_conditions, collapse = " and ")

sqldf(sprintf("select %s,sum(spend) as tot_spend from table_a where %s group by %s", b, where_clause, b))
#>    state channel tot_spend
#> 1 Nevada       H         7
#> 2  Texas       H         5

^{Created on 2024-07-01 with reprex v2.1.0}

Что-то вроде этого?
Первый пример — это пример вопроса. Во втором примере есть предложение where с фильтром input_gender.

Обратите внимание на одинарные кавычки в s2. Они необходимы, поскольку input_gender — это строка символов.

library(sqldf)
#> Loading required package: gsubfn
#> Loading required package: proto
#> Loading required package: RSQLite

table_a <- data.frame(id =c(1,1,1,2,2,2,3,4,4),
                      state = c("Texas","Texas","Texas","Alaska","Alaska","Alaska","Nevada","Texas","Texas"),
                      gender = c("M","M","M","F","F","F","F","F","F"),
                      channel = c("Y","Y","H","Y","D","D","H","Y","Y"),
                      spend = c(10,10,5,20,20,5,7,1,2))



# user selects variable and their values  
# channel is not selected by user here
input_states <- 'Texas'
input_genders <- 'M'
input_channels <- NULL

a <- c('state','gender','channel')[c(!is.null(input_states),!is.null(input_genders),!is.null(input_channels))]
b <- paste(a, collapse = ",")

s1 <- "select %s,
              sum(spend) as tot_spend
              from table_a
              group by %s"
query1 <- sprintf(s1, b, b)
sqldf(query1)
#>    state gender tot_spend
#> 1 Alaska      F        45
#> 2 Nevada      F         7
#> 3  Texas      F         3
#> 4  Texas      M        25


s2 <- "select %s,
              sum(spend) as tot_spend
              from table_a
              where gender = '%s'
              group by %s"
query2 <- sprintf(s2, b, input_genders, b)
sqldf(query2)
#>   state gender tot_spend
#> 1 Texas      M        25

^{Created on 2024-07-01 with reprex v2.1.0}

В третьем примере условия where построены из input_states и input_gender.

filter1 <- sprintf("state = '%s'", input_states)
filter2 <- sprintf("gender = '%s'", input_genders)
s3 <- "select %s, 
              sum(spend) as tot_spend 
              from table_a 
              where %s and %s
              group by %s"
query3 <- sprintf(s3, b, filter1, filter2, b)
sqldf(query3)
#>   state gender tot_spend
#> 1 Texas      M        25

^{Created on 2024-07-01 with reprex v2.1.0}

Создайте функцию cond, которая генерирует текст одного условия, и создайте список L имен и значений, удалив те записи со значениями NULL.

Используя это, создайте предложениеwhere where и сгруппируйте переменные groupby.

Наконец, используйте fn$, чтобы включить замену текста (где знак доллара интерполирует строки) и запустите результат.

if в конце предназначен для обработки ситуации, когда все переменные списка имеют значение NULL.

Укажите verbose=TRUE в качестве аргумента f, чтобы увидеть окончательный сгенерированный оператор SQL.

library(sqldf)

# name is a character string, values is a character vector
cond <- function(name, values) {
  sprintf("%s in (%s)", name, toString(shQuote(values, "sh")))
}

# data is data frame, L is named list or NULL, verbose is logical
f <- function(data, L = NULL, verbose = FALSE) {

  L <- Filter(length, L) 
  sql <- if (length(L)) {
    where <- paste(Map(cond, names(L), L), collapse = ' and\n') 
    groupby <- toString(names(L))
    "select $groupby, sum(spend) as tot_spend 
      from data
      where $where
      group by $groupby"
  } else "select sum(spend) as tot_spend from data"
  fn$sqldf(sql, verbose = verbose)      
}

# tests
L <- list(state = input_states,
          gender = input_genders,
          channel =  input_channels)
f(table_a, L)
##   state gender tot_spend
## 1 Texas      M        25

f(table_a)
##   tot_spend
## 1        80