MULTIARG¶
Introduction¶
MULTIARG
is a shorthand for specifying multiple functions in SELECT
and Flow’s GROUP BY
. For example, you might have a TQL query that looks like:
SELECT SUM(a) AS a_sum,
SUM(b) AS b_sum,
SUM(c) AS c_sum,
SUM(d) AS d_sum,
SUM(e) AS e_sum
FROM table
GROUP BY id
Using MULTIARG
, you can express the same query with
SELECT SUM(
MULTIARG(a, b, c, d, e) AS arg
) AS &arg_sum
FROM table
GROUP BY id
So in this case, a SUM
will be calculated for each argument specified in MULTIARG
, and an appropriate alias will be generated.
MULTIARG
takes a list of columns, followed by an alias for that MULTIARG
. The primary alias for any expression that contains a MULTIARG
must use a special template format: it must contain the MULTIARG
alias with an &
prefix.
In the example above, the MULTIARG
alias is arg
, so anywhere where &arg
appears in the primary alias (&arg_sum
), it will be replaced with the respective column name (a
, b
, c
, d
, e
).
So
SELECT PUSH(
MULTIARG(column_a, column_b) AS arg
) AS list_&arg_&arg
FROM TABLE
GROUP BY id
would be equivalent to
SELECT PUSH(column_a) AS list_column_a_column_a,
PUSH(column_b) AS list_column_b_column_b
FROM TABLE
GROUP BY id
One thing to note is that columns inside documents cannot be used inside MULTIARG
. So MULTIARG(a.b, a.c)
is not valid, unless a
is the FROM table alias.
You can also specify MULTIARG
for multiple parameters, and the result will be every permutation of those MULTIAiRGs
.
For example
SELECT DIVIDE(
MULTIARG(a, b, c) AS numerator,
MULTIARG(c, d, e) AS denominator
) AS &numerator_&denominator
would be equivalent to
SELECT DIVIDE(a, c) AS a_c,
DIVIDE(a, d) AS a_d,
DIVIDE(a, e) AS a_e,
DIVIDE(b, c) AS b_c,
DIVIDE(b, d) AS b_d,
DIVIDE(b, e) AS b_e,
DIVIDE(c, c) AS c_c,
DIVIDE(c, d) AS c_d,
DIVIDE(c, e) AS c_e
and
SELECT ADD(
MULTIARG(a, b) AS arg1,
TOINT(
MULTIARG(c, d) AS arg2
)
) AS &arg1_&arg2
would be equivalent to
SELECT ADD(a, TOINT(c)) AS a_c,
ADD(a, TOINT(d)) AS a_d,
ADD(b, TOINT(c)) AS b_c,
ADD(b, TOINT(d)) AS b_d
You can also use MULTIARG
in list and document literals, as well as predicates:
SELECT [a, MULTIARG(b, c) AS list_arg, d] AS list_with_&list_arg,
{
'key1': MULTIARG(a, b) AS key1_value,
'key2': 'value'
} AS doc_with_&key1_value,
(amount GT MULTIARG(amt1, amt2) AS amt) AS gt_&amt
Which would be equivalent to
SELECT [a, b, d] AS list_with_b,
[a, c, d] AS list_with_d,
{
'key1': a,
'key2': 'value'
} AS doc_with_a,
{
'key1': b,
'key2': 'value'
} AS doc_with_b,
(amount GT amt1) AS gt_amt1,
(amount GT amt2) AS gt_amt2