The INLINE keyword allows inlining a call to an arbitrary aggregate query function with a variable number of arguments. INLINE converts any aggregate into a simple function call that operates on however many arguments are provided, without any need for imposed groups. See the SQL Functions topic for a complete list of aggregate functions.
INLINE <aggregate-function>(...)
If the aggregate function takes more than one argument, the number of arguments passed to the inline call must be a multiple of the number of arguments the aggregate function takes. We can illustrate that requirement with simple examples using constant numbers, instead of field values as normally used:
The Max(<value>) aggregate function takes one argument, such as a constant or a field. We can execute:
? INLINE Max(3, 4, 5, 8, 7, 6)
...to get a value of 8. In contrast, the Covar(<value>, <value>) takes two arguments, comparing the two values to compute sample covariance. We can execute:
? INLINE Covar(1, 1, 2, 5, 3, 6, 4, 1)
...to get a value of 0.166... The numbers in the list of Covar arguments above are considered pairwise as indicated by the text colors (added as an illustration for this topic). Since Covar takes two arguments, we must provide an even number of arguments in the arguments list when using INLINE to compute covariance on an arbitrary number of arguments.
If the aggregate function takes just one argument, then the sequence of arguments when using INLINE is a simple list. If the aggregate takes more than one argument, the sequence is a list of lists, or in other words a serialized array. For example, if the aggregate function takes 3 arguments, we can enter just 3 arguments, or we can enter 3 arguments followed by a comma and then another 3 arguments, or any multiple of 3 arguments in that pattern, all in the same line of arguments.
Given a table called States that reports the population for each state:
SELECT Max([pop2010]) FROM [States];
The ordinary use of Max, without INLINE, goes straight down the pop2010 column, from state to state, to produce a single number that is the maximum population found:
If we like, we can use the INLINE keyword to make the Max aggregate take all its values from the argument list. Using INLINE allows us to find the population maximum considering several columns at once:
SELECT INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) FROM [States];
The query with INLINE goes down the table from state to state, and for each state, takes its population in 2010, 2014, 2016 and 2018, and then computes the maximum of these four values.
The result is a table with a list of population numbers for each state, giving the peak population that occurred in those years for that state. We can create a prettier version of the query, that adds the state name to each result row:
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [maxpop]
FROM [States]
ORDER BY [maxpop] DESC;
To produce:
In the examples above, the use of INLINE with four arguments is equivalent to creating function such as:
FUNCTION inlinemax(@a INT32, @b INT32, @c INT32, @d INT32) INT32 AS (
SELECT Max(result) FROM (VALUES (@a), (@b), (@c), (@d))
) END;
The results of using the new inlinemax function above or INLINE Max with four arguments are the same (reporting Log results):
> ? inlinemax(2, 5, 3, 8)
int32: 8
> ? INLINE Max(2, 5, 3, 8)
float64: 8
The advantage of INLINE is that we do not have to write a new, custom function for each new data type, or aggregate, or number of arguments. We can simply use INLINE with whatever aggregate function we like with as many arguments as we like. An additional advantage of INLINE is the system can optimize queries using INLINE better than the use of custom functions.
Credits - Tim Baigent, SQL master and expert Manifold user, contributed explanations of INLINE to the georeference.org forum, from which this topic borrows. Thanks, Tim!
Will INLINE operate on a user-defined aggregate? Yes. The implementation of INLINE is generic at the core. There are various optimization pins that advanced aggregates can use, but they are optional. When user-defined aggregate functions are allowed in Manifold, they quite likely will be supported by INLINE automatically without any additional requirements.
How does INLINE integrate with GROUP BY? INLINE converts an aggregate to a normal function call: INLINE Max(...) is semantically the same as Sin(...) with no group semantics. Use of INLINE is also like Coalesce(...), because, like an INLINE aggregate, Coalesce also takes a variable number of arguments.
Clarifying aliases - Consider the use of an alias:
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [maxpop]
FROM [States]
ORDER BY [maxpop] DESC;
That is the casual approach used in the example above. It works because the alias field name, maxpop, does not appear other than as an alias. A safer, more rigorous style would be to write:
SELECT * FROM (
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [maxpop]
FROM [States])
ORDER BY [maxpop] DESC;
Why? Because if the alias repeats a name within the function list it can be ambiguous what the ORDER BY is intended to use. Suppose we write:
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [pop2010]
FROM [States]
ORDER BY [pop2010] DESC;
That is legal, but it is ambiguous if the ORDER BY should order by the contents of the original pop2010 field, or if it should order by the alias that uses the result of the INLINE Max function. We can eliminate the ambiguity by using the sub-query construction:
SELECT * FROM (
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [pop2010]
FROM [States])
ORDER BY [pop2010] DESC;
In the above, ORDER BY will use the aliased pop2010, that is, the result of the INLINE Max function.
Brackets can help avoid errors - In the example:
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS [maxpop]
FROM [States]
ORDER BY [maxpop] DESC;
We wrapped maxpop with square [ ] brackets. We could have written:
SELECT [State], INLINE Max([pop2010], [pop2012], [pop2016], [pop2018]) AS maxpop
FROM [States]
ORDER BY maxpop DESC;
... since the alias has no spaces. However, using square brackets for field names and aliases can prevent inadvertent errors if the desired alias is the same as a keyword.
The first version of the query above failed because it used the alias pop without square brackets, so the query parser understood it to be a keyword used with PRAGMA, and thus syntactically incorrect following AS. Like any big database system, Manifold has plenty of keywords, which might not always be remembered. To eliminate the possibility of collisions with keywords, we can wrap field names and aliases in square brackets. Although the use of square brackets will allow using a name that would be a keyword without brackets, it is still a good idea to avoid using keywords as field name, square brackets or not.