Just out of curiosity, is the second level aggregation using AVG in a window context? It the frame is the whole table and it aggregates over it?
On Thu, Feb 10, 2022 at 3:12 PM Justin Swanhart <[email protected]> wrote: > That is really neat about Oracle. > > The alternative in general is to use a subquery: > SELECT avg(avg(sal)) FROM emp GROUP BY deptno; > becomes > select avg(the_avg) > from (select avg(sal) from emp group b deptno) an_alias; > > or > > with the_cte as (select avg(sal) x from emp group by deptno) > select avg(x) from the_cte; > > On Thu, Feb 10, 2022 at 3:03 PM Julian Hyde <[email protected]> > wrote: > >> Some databases, e.g. Oracle, allow TWO levels of nesting: >> >> SELECT avg(sal) FROM emp GROUP BY deptno; >> >> AVG(SAL) >> ======== >> 1,566.67 >> 2,175.00 >> 2,916.65 >> >> SELECT avg(avg(sal)) FROM emp GROUP BY deptno; >> >> AVG(SUM(SAL)) >> ============= >> 9,675 >> >> The first level aggregates by department (returning 3 records), and the >> second level computes the grand total (returning 1 record). But that is an >> exceptional case. >> >> Generally, any expression in the SELECT or HAVING clause of an aggregate >> query is either ‘before’ or ‘after’ aggregation. Consider >> >> SELECT t.x + 1 AS a, 2 + SUM(t.y + 3) AS b >> FROM t >> GROUP BY t.x >> >> The expressions “t.y” and “t.y + 3” occur before aggregation; “t.x”, “t.x >> + 1”, “SUM(t.y + 3)” and “2 + SUM(t.y + 3)” occur after aggregation. SQL >> semantics rely heavily on this stratification. Allowing an extra level of >> aggregation would mess it all up. >> >> Julian >> >> >> >> > On Feb 10, 2022, at 9:45 AM, Justin Swanhart <[email protected]> >> wrote: >> > >> > This is a SQL limitation. >> > >> > mysql> select sum(1); >> > +--------+ >> > | sum(1) | >> > +--------+ >> > | 1 | >> > +--------+ >> > 1 row in set (0.00 sec) >> > >> > mysql> select sum(sum(1)); >> > ERROR 1111 (HY000): Invalid use of group function >> > >> > On Thu, Feb 10, 2022 at 12:39 PM Gavin Ray <[email protected]> >> wrote: >> > >> >> Went to test this query out and found that it can't be performed: >> >> >> >> SELECT >> >> JSON_OBJECT( >> >> KEY 'users' >> >> VALUE JSON_ARRAYAGG( >> >> JSON_OBJECT( >> >> KEY 'name' VALUE "users"."name", >> >> KEY 'todos' VALUE JSON_ARRAYAGG( >> >> JSON_OBJECT( >> >> KEY 'description' VALUE "todos"."description" >> >> ) >> >> ) >> >> ) >> >> ) >> >> ) >> >> FROM >> >> "users" >> >> LEFT OUTER JOIN >> >> "todos" ON "users"."id" = "todos"."user_id"; >> >> >> >> Checking the source, seems this is a blanket policy, not a >> >> datasource-specific thing. >> >> From a functional perspective, it doesn't feel like it's much different >> >> from JOINs >> >> But I don't understand relational theory or DB functionality in the >> least, >> >> so I'm not fit to judge. >> >> >> >> Just curious why Calcite doesn't allow this >> >> >> >>
