Hi, I'm interested in writing a rule to push certain aggregate rexes to a lower subqueries to be able to remove not needed group by statements. In SQL this would look by transforming:
select t2.id, sum(case when product = 'foo' then sum_units else 0 end) from ( select id, product, sum(units) AS sum_units from orders group by id, product ) t2 group by t2.id Into: select t2.id, sum(sum_units) from ( select id, sum(case when product = 'foo' then sum_units else 0 end) as sum_units from orders group by id ) t2 group by t2.id I've been able to implement a rule for this specific example so I know in theory it's possible to make the rule generic. You can see the rule here: https://pastebin.com/G3x9CdAW My question is if there's a better way to solve this? Are there any existing calcite tools that could be used? I tried looking at PushProjector class but it doesn't seem to be for this purpose? Is it OK to traverse the tree like I'm doing it by calling the getInput method and casting nodes to HepRelVertex or is there a better way to traverse the tree? How would you suggest implementing such a rule? Thanks
