sql.dt: SQL query to data.table query mapping

In jangorecki/dwtools: Data Warehouse related functions.

Description

Push data.table arguments into sql friendly way, see examples.

Usage

sql.dt(select, from, where, group, order, .SDcols)

Arguments

select	list or variables or single variable to select or update.
from	data.table.
where	expression which should return logical when executed in [i=...].
group	list of variables/functions to group by.
order	expression custom order, usually order(colA).
.SDcols	character vector, to be used when using it in select, e.g. in lapply.

Details

This function is just a simple wrapper of SQL argument sequence and name to data.table argument sequence and name. All language syntax in each args must be valid R statement e.g. list(cust_code, curr_code) which will be forwarded to data.table arguments.

Examples

suppressPackageStartupMessages(library(dwtools))

dt <- dw.populate(scenario="fact")
print(dt)

sql.dt(select = geog_code, from = dt, where = value %between% c(100,180))
sql.dt(geog_code, dt, value %between% c(100,180))

sql.dt(select = list(geog_code, time_code, value), from = dt, where = value < 100)
sql.dt(list(geog_code, time_code, value), dt, value < 100)

sql.dt(, dt, value < 100) # select * / .SD

sql.dt(
  select = list(sum_value=sum(value)),
  from = dt,
  group = list(geog_code),
  order = order(-sum_value)
)
sql.dt(list(sum_value=sum(value)), dt, , list(geog_code), order(-sum_value)) # the same

# update by reference
dt <- dw.populate(scenario="fact")
print(dt)
sql.dt(
  select = c("cust_curr_amount","cust_curr_value") := lapply(.SD,sum),
  from = dt,
  group = list(cust_code, curr_code),
  .SDcols=c("amount","value")
)

# the same but non update
dt <- dw.populate(scenario="fact")
sql.dt(
  select = c("cust_curr_amount","cust_curr_value") := lapply(list(amount,value),sum),
  from = copy(dt),
  group = list(cust_code, curr_code)
)

jangorecki/dwtools documentation built on May 18, 2019, 12:24 p.m.