Class AggregateExpandWithinDistinctRule


@Enclosing public class AggregateExpandWithinDistinctRule extends RelRule<AggregateExpandWithinDistinctRule.Config>
Planner rule that rewrites an Aggregate that contains WITHIN DISTINCT aggregate functions.

For example,

SELECT o.paymentType,
     COUNT(*) as "count",
     COUNT(*) WITHIN DISTINCT (o.orderId) AS orderCount,
     SUM(o.shipping) WITHIN DISTINCT (o.orderId) as sumShipping,
     SUM(i.units) as sumUnits
   FROM Orders AS o
   JOIN OrderItems AS i USING (orderId)
   GROUP BY o.paymentType

becomes

     SELECT paymentType,
       COUNT(*) as "count",
       COUNT(*) FILTER (WHERE g = 0) AS orderCount,
       SUM(minShipping) FILTER (WHERE g = 0) AS sumShipping,
       SUM(sumUnits) FILTER (WHERE g = 1) as sumUnits
    FROM (
      SELECT o.paymentType,
        GROUPING(o.orderId) AS g,
        SUM(o.shipping) AS sumShipping,
        MIN(o.shipping) AS minShipping,
        SUM(i.units) AS sumUnits
        FROM Orders AS o
        JOIN OrderItems ON o.orderId = i.orderId
        GROUP BY GROUPING SETS ((o.paymentType), (o.paymentType, o.orderId)))
     GROUP BY o.paymentType

By the way, note that COUNT(*) WITHIN DISTINCT (o.orderId) is identical to COUNT(DISTINCT o.orderId). WITHIN DISTINCT is a generalization of aggregate(DISTINCT). So, it is perhaps not surprising that the rewrite to GROUPING SETS is similar.

If there are multiple arguments (e.g. SUM(a) WITHIN DISTINCT (x), SUM(a) WITHIN DISTINCT (y)) the rule creates separate GROUPING SETs.