nested aggr - aggr within aggr

[Anonymous]

Is there any pitfalls using aggr within aggr like that:

avg(total <practice>

  aggr(

       avg(

            aggr(

                      avg(score)

                      ,survey,section

                 )

       )

  ,survey

  )

)

I know of performance implications but I am wondering if anyone else actually did that successfully. I cannot find any examples but I tested it quickly and looks like aggr nesting works fine.

[#2]

If the data is correct ...

For some rolling periods, we can do sth like that:

sum( aggr( rangesum( above( sum(VALUE),0,3) ),TIME_KEY))

As yoi said, performance is not as good as a simple sum() or avr()

Fabrice

[hic · Accepted solution]

This should work. But why do you want to do it? What's the use-case?

One single Aggr means a two-step nested aggregation.

Two nested Aggr's would mean a three-step nested aggregation.

HIC

[Anonymous]

Hi Henric,

I was surprised it would work but it does work fine!

my use case was exactly that - three level aggregation. I was working with some survey data (questions/answers) and the requirement was to calculate overall organization's score as a mean of means of all survey sections answers (group of questions). Then in turn, means of sections answers are calculated as means of each individual question scores in each section. So it is a mean of means of means. I considered doing this in a script but the requirement was to calculate the final score on the fly and respect selections.

I did not have a lot of data (a couple million rows with each survey's answer score) and the performance was actually very good - granted we have quite a powerful server.

[hic]

Well, if a three-step dynamic aggregation is what you need - then that is the way to solve it.

There is (to my knowledge) no limit to how many nested aggr:s you can have. But every nested aggregation will hit the performance, so you should avoid it if possible.

HIC