inst/doc/generating-aggregation-code.R

In DBmaps: A Metadata-Driven Framework for Streamlining Database Joins

## ----setup, include = FALSE---------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
library(data.table)
library(DBmaps)

## ----transactions-example, echo = TRUE----------------------------------------
# Define metadata for the transactions table
transactions_info <- table_info(
  table_name = "transactions",
  source_identifier = "transactions.csv",
  identifier_columns = c("customer_id", "product_id", "time"),
  key_outcome_specs = list(
    list(OutcomeName = "Revenue", ValueExpression = quote(price * quantity), AggregationMethods = list(
      list(AggregatedName = "RevenueByCustomer", AggregationFunction = "sum", GroupingVariables = "customer_id"),
      list(AggregatedName = "RevenueByProduct", AggregationFunction = "sum", GroupingVariables = "product_id")
    )),
    list(OutcomeName = "UnitsSold", ValueExpression = quote(quantity), AggregationMethods = list(
      list(AggregatedName = "TotalUnitsByCustomer", AggregationFunction = "sum", GroupingVariables = "customer_id")
    ))
  )
)

# For this example, our master metadata only contains one table's info
master_metadata_dt <- transactions_info

cat("Master metadata table created successfully.\n")

## ----aggregation-code-generation, echo=TRUE-----------------------------------
generated_code <- generate_aggregation_code(
  table_name_filter = "transactions",
  metadata_dt = master_metadata_dt
)

# Let's see the code strings that were generated
print(generated_code)

## ----runnable-code, echo=TRUE-------------------------------------------------
# 1. Create a sample 'transactions' data.table
transactions <- data.table(
  customer_id = c("C001", "C002", "C001", "C003", "C002"),
  product_id = c("P01", "P02", "P02", "P01", "P02"),
  price = c(10, 20, 22, 11, 21),
  quantity = c(1, 2, 1, 3, 2)
)

# 2. Select the code that aggregates by customer_id
code_to_run <- generated_code["customer_id"]
cat("Code to be executed:\n", code_to_run, "\n\n")

# 3. Parse and evaluate the code string
aggregated_by_customer <- eval(parse(text = code_to_run))

# 4. View the result
cat("Result of execution:\n")
print(aggregated_by_customer)