Construct SQL expressions for a design matrix

Description

Expands formula terms into SQL SELECT expressions, handling factor one-hot encoding and interaction terms. Only the right-hand side of the formula is processed; the response variable (LHS) is ignored.

Usage

sql_model_matrix(
  formula,
  conn,
  table,
  expand = c("all", "interactions"),
  sep = "_x_",
  fe_vars = character(),
  intercept = TRUE
)

Arguments

formula A formula (or Formula) object. Only RHS terms are expanded; the LHS (response variable) is ignored.
conn Database connection
table Table name or FROM clause
expand Character: ‘"all"’ expands factors and interactions, ‘"interactions"’ only expands interaction terms (factors in main effects kept as-is for grouping)
sep Character separator for interaction term names. Default is ‘"x"’. Use ‘":"’ for standard R naming convention.
fe_vars Character vector of fixed effect variable names. These are treated as "in the model" for determining whether to drop reference levels in interactions.
intercept Logical. Whether the model has an intercept. As in [stats::model.matrix()], without an intercept the first factor main effect keeps all of its levels (no reference level is dropped).

Details

Terms can also use ‘fixest’-style ‘i()’ syntax: ‘i(f)’ expands ‘f’ like a factor (numeric ‘f’ included), and ‘i(f, x)’ interacts each level of ‘f’ with a numeric or logical ‘x’. ‘ref’ drops the given level(s) and ‘keep’ keeps only the given levels. Columns are named as in ‘fixest’, e.g. ‘period::5:treat’. One difference: in a model without an intercept, a plain ‘i(f)’ drops its first level if another factor comes first, whereas ‘fixest’ drops its last level (via collinearity) instead.

Value

List with: - ‘select_exprs’: character vector of SQL expressions - ‘col_names’: corresponding column names - ‘aliases’: SQL-safe versions of ‘col_names’ (identical unless, e.g., factor levels contain spaces or other special characters) - ‘level_aliases’: data frame mapping each factor dummy ‘alias’ to its ‘var’ and ‘level’ - ‘factor_levels’: list of factor levels by variable (for reference) - ‘i_columns’: data frame mapping each ‘i()’ column ‘alias’ to its factor ‘var’, ‘level’ and interacted variable ‘by’ (‘NA’ if none)

Examples

library("dbreg")

library(DBI)
library(duckdb)
con = dbConnect(duckdb())
duckdb_register(con, "test", data.frame(x1 = 1:3, x2 = c("a", "b", "c")))
sql_model_matrix(~ x1 + x2, con, "test")
$select_exprs
[1] "x1"                                      
[2] "CASE WHEN x2 = 'b' THEN 1.0 ELSE 0.0 END"
[3] "CASE WHEN x2 = 'c' THEN 1.0 ELSE 0.0 END"

$col_names
[1] "x1"  "x2b" "x2c"

$aliases
[1] "x1"  "x2b" "x2c"

$factor_levels
$factor_levels$x2
[1] "a" "b" "c"


$level_aliases
           alias var     level
x2.1         x2a  x2         a
x2.2         x2b  x2         b
x2.3         x2c  x2         c
x2.4 x2_constant  x2 _constant

$i_columns
[1] alias var   level by   
<0 rows> (or 0-length row.names)
sql_model_matrix(~ x1:x2, con, "test")
$select_exprs
[1] "(x1) * (CASE WHEN x2 = 'a' THEN 1.0 ELSE 0.0 END)"
[2] "(x1) * (CASE WHEN x2 = 'b' THEN 1.0 ELSE 0.0 END)"
[3] "(x1) * (CASE WHEN x2 = 'c' THEN 1.0 ELSE 0.0 END)"

$col_names
[1] "x1_x_x2a" "x1_x_x2b" "x1_x_x2c"

$aliases
[1] "x1_x_x2a" "x1_x_x2b" "x1_x_x2c"

$factor_levels
$factor_levels$x2
[1] "a" "b" "c"


$level_aliases
           alias var     level
x2.1         x2a  x2         a
x2.2         x2b  x2         b
x2.3         x2c  x2         c
x2.4 x2_constant  x2 _constant

$i_columns
[1] alias var   level by   
<0 rows> (or 0-length row.names)
sql_model_matrix(~ x1:x2, con, "test", sep = ":")
$select_exprs
[1] "(x1) * (CASE WHEN x2 = 'a' THEN 1.0 ELSE 0.0 END)"
[2] "(x1) * (CASE WHEN x2 = 'b' THEN 1.0 ELSE 0.0 END)"
[3] "(x1) * (CASE WHEN x2 = 'c' THEN 1.0 ELSE 0.0 END)"

$col_names
[1] "x1:x2a" "x1:x2b" "x1:x2c"

$aliases
[1] "x1_x_x2a" "x1_x_x2b" "x1_x_x2c"

$factor_levels
$factor_levels$x2
[1] "a" "b" "c"


$level_aliases
           alias var     level
x2.1         x2a  x2         a
x2.2         x2b  x2         b
x2.3         x2c  x2         c
x2.4 x2_constant  x2 _constant

$i_columns
[1] alias var   level by   
<0 rows> (or 0-length row.names)
sql_model_matrix(~ i(x2, x1, ref = "a"), con, "test")
$select_exprs
[1] "CASE WHEN x2 = 'b' THEN x1 ELSE 0 END"
[2] "CASE WHEN x2 = 'c' THEN x1 ELSE 0 END"

$col_names
[1] "x2::b:x1" "x2::c:x1"

$aliases
[1] "dbreg_i1_1" "dbreg_i1_2"

$factor_levels
$factor_levels$x2
[1] "a" "b" "c"


$level_aliases
           alias var     level
x2.1         x2a  x2         a
x2.2         x2b  x2         b
x2.3         x2c  x2         c
x2.4 x2_constant  x2 _constant

$i_columns
       alias var level by
1 dbreg_i1_1  x2     b x1
2 dbreg_i1_2  x2     c x1
dbDisconnect(con)