Add avg of one array to all values of another array


#1

Let A and B be some arrays. I need to compute A + avg(B) but this seems to fail (it doesn’t like the aggregate):

apply(A, p, v1 + aggregate(B, avg(v2)))
...
Error id: scidb::SCIDB_SE_SYNTAX::SCIDB_LE_UNEXPECTED_OPERATOR_IN_EXPRESSION
Error description: Query syntax error. Array operators cannot be used inside scalar expressions.

Is there any way to calculate this?


#2

Hello,

In AFL aggregate() is an operator, all query operators return an array and arrays can’t be used inside an apply or filter expression - but you can easily do this with a join. Something like this:

$ iquery -aq "store(build(<val:double>[x=1:3,3,0,y=1:3,3,0], x), foo)"
{x,y} val
{1,1} 1
{1,2} 1
{1,3} 1
{2,1} 2
{2,2} 2
{2,3} 2
{3,1} 3
{3,2} 3
{3,3} 3

apoliakov@kali:~$ iquery -aq "apply(cross_join(foo, aggregate(foo, avg(val) as m)), val2, val+m)"
{x,y,i} val,m,val2
{1,1,0} 1,2,3
{1,2,0} 1,2,3
{1,3,0} 1,2,3
{2,1,0} 2,2,4
{2,2,0} 2,2,4
{2,3,0} 2,2,4
{3,1,0} 3,2,5
{3,2,0} 3,2,5
{3,3,0} 3,2,5

Note that we’ve picked up extra attributes along the way, which can be removed using project. We also have an extra dimension i which came with the output of aggregate operator. That can be removed using slice if needed.


#3

Thanks, that works! I guess I got confused that aggregate results in an array, it’s a bit non-intuitive.


#4

Consider a grouped aggregate, i.e.

aggregate(foo, avg(val), x)

So the AFL intuition is that a grand-total aggregate is a case where there is a single large “group” - thus a single-celled array is returned.