Metaprogramming With Macro Variables

For versions 2.00.12/3.00.00 and earlier, metacode could only be generated using built-in functions. Since verison 2.00.12/3.00.00, the use of macro variables is introduced for metaprogramming, allowing to generate metacode in a more intuitive manner through <select statement>.

The declaration of macro variables depends on whether the variable passed into the metacode is defined as a scalar or a vector:

For a scalar, use "_$" (for single-column macro variables). For example, a variable is defined as name="sym". The metacode can be <SELECT _$name FROM t>, which will be parsed as SELECT sym FROM t during execution.
For a vector, use "_$$" (for multi-column macro variables). For example, a variable is defined as names=["sym", "time"]. The metacode can be <SELECT _$$name FROM t>, which will be parsed as SELECT sym, time FROM t during execution.

To make macro variable take effect, the following MUST be noted:

The variable name must be a string and variables must conform to the Naming Rules.
It can only be applied to columns, column aliases, parameters of functions, expressions, etc. Macro variables applied to functions and expressions are treated as a tuple, where each element corresponds to one column.
Currently, only SELECT statement is supported. Macro variables cannot applied to UPDATE and DELETE statements.
It can only be used with clauses WHERE, GROUP BY, PIVOT BY, CONTEXT BY, CSORT, and ORDER BY. Clauses such as CASE WHEN, OVER, and nested queries followed by FROM are not supported yet.
When used with CSORT and ORDER BY clauses, only single-column macro variable (with "_$") is supported.

Examples

Examples for single-column macro variables.

(1) Used to select a single column.

t = table(take(2023.01M+1 2 1 1 2,5) as month, take(`Rome`London`London`Rome,5) as city, take(200 500 100 300 300, 5) as sold1, take(2100 1500 1100 3100 2300,5) as sold2)
selectCity="city"
<select _$selectCity from t>.eval()

Output:


city
Rome
London
London
Rome
Rome

(2) Used as the argument of a unary function to operate on a single column.

selectCity="city"
selectSold1="sold1"
alias="avg_sold1"
<select avg(_$selectSold1) as _$alias from t group by _$selectCity>.eval()

Output:


city	avg_sold1
Rome	266.6667
London	300

Examples for multi-column macro variables.

(1) Used to select multiple columns.

colNames=["city", "sold1"]
<select _$$colNames from t>.eval()

Output:


city	sold1
Rome	200
London	500
London	100
Rome	300
Rome	300

(2) Each of the corresponding columns is passed as an argument to the function.

colNames=["sold1", "sold2"]
alias = "sum_sold"

<select rowSum(_$$colNames) as _$alias from t>.eval()

Output:


sum_sold
2,300
2,000
1,200
3,400
2,600

(3) Used with higher-order functions.

Apply a function to multiple columns by row. The following example calculates the sum for each row of column "sold1" and "sold2". It returns the same result as the example (2) above.
```
colNames=["sold1", "sold2"]
alias = "sum_sold"

<select sum:H(_$$colNames) as _$alias from t>.eval()
```

Apply a function to multiple columns by column. The following example calculates the sum for columns "sold1" and "sold2".

colNames=["sold1", "sold2"]
alias=["sum_sold1", "sum_sold2"]

<select  sum:V(_$$colNames) as _$$alias from t>.eval()

Output:


sum_sold1	sum_sold2
1,400	10,100