Metaprogramming With Functions

(1) Function expr generates metacode from objects, operators, or other metacode.

Syntax: expr(X1, X2, ......) where X's are objects, operators, or other metacode.

expr(6,<,8);
// output
< 6 < 8 >

expr(sum, 1 2 3);
// output
< sum [1,2,3] >

a=6;
expr(a,+,1);
// output
< 6 + 1 >

expr(<a>,+,1);
// output
< a + 1 >

expr(<a>,+,<b>);
// output
< a + b >

expr(a+7,*,8);
// output
< 13 * 8 >

expr(<a+7>,*,8);
// output
< (a + 7) * 8 >

expr(not, < a >);
// output
< ! a >

(2) Function eval evaluates the given metacode.

Syntax: eval(<X>) where X is metacode.

eval(<1+2>);
// output
3

eval(<1+2+3=10>);
// output
0

eval(expr(6,<,8));
// output
1

eval(expr(sum, 1 2 3));
// output
6

a=6; b=9;
eval(expr(<a>,+,<b>));
// output
15

(3) Function sqlCol converts column names into metaexpressions.

Syntax: sqlCol(colNames), where colNames can be one column name or a vector of column names. Each of these column names need to be quoted.

sqlCol(`PRC);
// output
< PRC >

sqlCol(["PRC", "RET", "DATE", "TICKER"]);
// output
(< PRC >,< RET >,< DATE >,< TICKER >)

(4) Function sqlColAlias uses metacode and an optional alias name to define a column. It is often used for calculated columns.

Syntax: sqlColAlias(<metacode>, [aliasName])

sqlColAlias(<x>, `y);
// output
< x as y >

sqlColAlias(<avg(PRC)>, `avgPRC);
// output
< avg(PRC) as avgPRC >

sqlColAlias(<avg(PRC)>);
// output
< avg(PRC) as avg_PRC >

(5) Function sql creates a SQL statement dynamically.

Syntax: sql(select, from, [where], [groupBy], [groupFlag], [csort], [ascSort], [having], [orderBy], [ascOrder], [limit], [hint])

symbol = take(`GE,6) join take(`MSFT,6) join take(`F,6)
date=take(take(2017.01.03,2) join take(2017.01.04,4), 18)
price=31.82 31.69 31.92 31.8  31.75 31.76 63.12 62.58 63.12 62.77 61.86 62.3 12.46 12.59 13.24 13.41 13.36 13.17
volume=2300 3500 3700 2100 1200 4600 1800 3800 6400 4200 2300 6800 4200 5600 8900 2300 6300 9600
t1 = table(symbol, date, price, volume);

t1;

x=5000
whereConditions = [<symbol=`MSFT>,<volume>x>]
havingCondition = <sum(volume)>200>;

sql(sqlCol("*"), t1);
// output
< select * from t1 >

sql(sqlCol("*"), t1, whereConditions);
// output
< select * from t1 where symbol == "MSFT",volume > x >

sql(select=sqlColAlias(<avg(price)>), from=t1, where=whereConditions, groupBy=sqlCol(`date));
// output
< select avg(price) as avg_price from t1 where symbol == "MSFT",volume > x group by date >

sql(select=sqlColAlias(<avg(price)>), from=t1, groupBy=[sqlCol(`date),sqlCol(`symbol)]);
// output
< select avg(price) as avg_price from t1 group by date,symbol >

sql(select=(sqlCol(`symbol),sqlCol(`date),sqlColAlias(<cumsum(volume)>, `cumVol)), from=t1, groupBy=sqlCol(`date`symbol), groupFlag=0);
// output
< select symbol,date,cumsum(volume) as cumVol from t1 context by date,symbol >

sql(select=(sqlCol(`symbol),sqlCol(`date),sqlColAlias(<cumsum(volume)>, `cumVol)), from=t1, where=whereConditions, groupBy=sqlCol(`date), groupFlag=0);
// output
< select symbol,date,cumsum(volume) as cumVol from t1 where symbol == "MSFT",volume > x context by date >

sql(select=(sqlCol(`symbol),sqlCol(`date),sqlColAlias(<cumsum(volume)>, `cumVol)), from=t1, where=whereConditions, groupBy=sqlCol(`date), groupFlag=0, csort=sqlCol(`volume), ascSort=0);
// output
< select symbol,date,cumsum(volume) as cumVol from t1 where symbol == "MSFT",volume > x context by date csort volume desc >

sql(select=(sqlCol(`symbol),sqlCol(`date),sqlColAlias(<cumsum(volume)>, `cumVol)), from=t1, where=whereConditions, groupBy=sqlCol(`date), groupFlag=0, having=havingCondition);
// output
< select symbol,date,cumsum(volume) as cumVol from t1 where symbol == "MSFT",volume > x context by date having sum(volume) > 200 >

sql(select=sqlCol("*"), from=t1, where=whereConditions, orderBy=sqlCol(`date), ascOrder=0);
// output
< select * from t1 where symbol == "MSFT",volume > x order by date desc >

sql(select=sqlCol("*"), from=t1, limit=1);
// output
< select top 1 * from t1 >

sql(select=sqlCol("*"), from=t1, groupBy=sqlCol(`symbol), groupFlag=0, limit=1);
// output
< select top 1 * from t1 context by symbol >

sql(select=(sqlCol(`symbol),sqlCol(`date),sqlColAlias(<cumsum(volume)>, `cumVol)), from=t1, groupBy=sqlCol(`date`symbol), groupFlag=0, hint=HINT_KEEPORDER);
// output
< select [128] symbol,date,cumsum(volume) as cumVol from t1 context by date,symbol >

A convenient and flexible way to generate complicated queries dynamically is to define a function that calls function sql.

def f1(t, sym, x){
   whereConditions=[<symbol=sym>,<volume>x>]
   return sql(sqlCol("*"),t,whereConditions).eval()
};

f1(t1, `MSFT, 5000);

f1(t1, `F, 9000);

def f2(t, sym, colNames, filterColumn, filterValue){
   whereConditions=[<symbol=sym>,expr(sqlCol(filterColumn),>,filterValue)]
   return sql(sqlCol(colNames),t,whereConditions).eval()
};

f2(t1,`GE, `symbol`date`volume, `volume, 3000);

f2(t1,`F, `symbol`date`volume,`price,13.2);

(6) Function partial creates a partial application.

Syntax: partial(func, args...)

partial(add,1)(2);
// output
3

def f(a,b):a pow b
g=partial(f, 2)
g(3);
// output
8

(7) Function makeCall calls a function with the specified parameters to generate a piece of script. The difference between template functions call and makecall is that makecall doesn't execute the script.

In the following example, we create a function generateReport that reports selected columns from a given table with specified format.

def generateReport(tbl, colNames, colFormat): sql(sqlColAlias(each(makeCall{format},sqlCol(colNames),colFormat),colNames), tbl).eval()
t = table(1..100 as id, (1..100 + 2018.01.01) as date, rand(100.0, 100) as price, rand(10000, 100) as qty, rand(`A`B`C`D`E`F`G, 100) as city);
t;

generateReport(t, ["id", "date","price","qty"], ["0", "MM/dd/yyyy", "0.00", "#,###"]);

The equivalent SQL script for the previous execution is:

def generateReportSQL(tbl, colNames, colFormat): sql(sqlColAlias(each(makeCall{format},sqlCol(colNames),colFormat),colNames), tbl)
generateReportSQL(t, ["id", "date","price","qty"], ["0", "MM/dd/yyyy", "0.00", "#,###"]);
// output
< select format(id, "0") as id,format(date, "MM/dd/yyyy") as date,format(price, "0.00") as price,format(qty, "#,###") as qty from t >

3 Ways to construct a function to execute a SQL statement:

Example 1:

def f1(t, sym, x): select * from t where name=sym, vol>x;
f1(t1, `MSFT, 7000);

Alternatively, we can use the function eval with a block of metacode.

Example 2:

def f2(t, sym, x): eval(<select * from t where name=`MSFT, vol>x >);
f2(t1,`MSFT, 7000);

We can also define a function to generate a SQL statement with function sql. The SQL statement will be executed when the function is called. For more complicated query generation, this is much more convenient and flexible.

Example 3

def f3(t, sym, x){
whereConditions=[<name=sym>,<vol>x>]
return sql(sqlCol("*"),t,whereConditions).eval()
};

f3(t1, `MSFT, 7000);

f3(t1, `F, 9000);

Among the 3 methods, function sql is the most flexible. It can be used dynamically to construct SQL statements.

def f4(t, sym, colNames, filterColumn, filterValue){
whereConditions=[<name=sym>,expr(sqlCol(filterColumn),>,filterValue)]
return sql(sqlCol(colNames),t,whereConditions).eval()
};
f4(t1,`MSFT, `name`date`vol, `vol, 7000);

f4(t1,`F, `name`date`vol,`PRC,13.2);

(8) Function binaryExpr generates metacode of binary expression.

Syntax: binaryExpr(X, Y, optr)

t = table(reshape(rand(1.0, 200), 20:10));
n=10;
weights = array(ANY, n).fill!(0..(n-1), 1..n)\n;
names = t.colNames();
tp = binaryExpr(sqlCol(names), weights, *);
tp;
// output
(< col0 * 0.1 >,< col1 * 0.2 >,< col2 * 0.3 >,< col3 * 0.4 >,< col4 * 0.5 >,< col5 * 0.6 >,< col6 * 0.7 >,< col7 * 0.8 >,< col8 * 0.9 >,< col9 * 1 >)

(9) Function unifiedExpr generates metacode of multivariate expression.

Syntax: unifiedExpr(objs, optrs)

The following example generates metacode of SQL select statement and calculates the weighted sum of each column.

selects = sqlColAlias(unifiedExpr(binaryExpr(sqlCol(names), weights, *), take(+, n-1)), "weightedSum");
re = sql(selects, t);
re;
// output
< select (col0 * 0.1) + (col1 * 0.2) + (col2 * 0.3) + (col3 * 0.4) + (col4 * 0.5) + (col5 * 0.6) + (col6 * 0.7) + (col7 * 0.8) + (col8 * 0.9) + (col9 * 1) as weightedSum from tc0ccbd6a00000000 >
re.eval()

(10) Function makeUnifiedCall generates metacode for function call. Different from higher-order function unifiedCall, makeUnifiedCall doesn't execute the metacode.

Syntax: makeUnifiedCall(func, args)

The following example generates metacode equivalent to that of Example 9:

selects = sqlColAlias(makeCall(flatten, makeCall(dot, makeUnifiedCall(matrix, sqlCol(names)), 1..n\n)), "weightedSum");
re = sql(selects, t);
re;
// output
< select flatten(dot(matrix(col0, col1, col2, col3, col4, col5, col6, col7, col8, col9), [0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1])) as weightedSum from tc0ccbd6a00000000 >
re.eval();

We can use function format for reporting. It has 2 arguments: a table column and the corresponding format string. The format string follows number and datetime formatting in Java. We can use 6 symbols (0/#/,/./%/E) to represent the format of a number and 9 symbols (y/M/d/H/h/m/s/S/n) to represent the format of a temporal object. For examples:

The example below generates a report dynamically given a table, a list of column names, and a list of column formats. Metaprogramming functions sqlCol, sqlColAlias, sql, and makeCall are used to generate a SQL statement and then function eval is called to generate a report. makeCall produces a piece of code that makes a function call. The first parameter is a function and the other parameters are required arguments.

def generateReport(tbl, colNames, colFormat){
   colCount = colNames.size()
   colDefs = array(ANY, colCount)
   for(i in 0:colCount){
      if(colFormat[i] == "")
         colDefs[i] = sqlCol(colNames[i])
      else
         colDefs[i] = sqlCol(colNames[i], format{,colFormat[i]})
   }
   return sql(colDefs, tbl).eval()
}

Generate a sample table with 100 rows and 4 columns (id, date, price, and qty) and then call function generateReport to generate a report.

t = table(1..100 as id, (1..100 + 2018.01.01) as date, rand(100.0, 100) as price, rand(10000, 100) as qty);
t;

generateReport(t, ["id", "date","price","qty"], ["0", "MM/dd/yyyy", "0.00", "#,###"]);

We can use metaprogramming to generate a filtering condition (i.e., where clause in a SQL statement).

def generateReportWithFilter(tbl, colNames, colFormat, filter){
   colCount = colNames.size()
   colDefs = array(ANY, colCount)
   for(i in 0:colCount){
      if(colFormat[i] == "")
         colDefs[i] = sqlCol(colNames[i])
      else
         colDefs[i] = sqlCol(colNames[i], format{,colFormat[i]})
   }
   return sql(colDefs, tbl, filter).eval()
}
generateReportWithFilter(t, ["id","date","price","qty"], ["","MM/dd/yyyy", "00.00", "#,###"], < id>10 and price<20 >);