dwtools (version 0.8.3.9)

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

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)
)