[PHOENIX-2794] Flatten nested aggregate queries when possible

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: James R. Taylor
• Created: 24/Mar/16 01:26
• Updated: 12/May/16 20:48

Description

The following query:

SELECT TRUNC(ts,'HOUR'), AVG(avg_val)
FROM (SELECT AVG(val),ts FROM T GROUP BY ts)
GROUP BY TRUNC(ts,'HOUR');

will run much more efficiently if flattened so that the hourly bucketing is done on the server-side like this:

SELECT TRUNC(ts,'HOUR'), AVG(val)
FROM T
GROUP BY TRUNC(ts,'HOUR');

We should flatten when possible. Not sure what the general rule is, but perhaps if the inner and outer aggregate function matches, you can always do this? Maybe only for some aggregate functions like SUM, MIN, MAX, AVG?

This comes up in time series queries in particular.

Issue Links

• depends upon: CALCITE-1172 Add rule to flatten two Aggregate operators into one