RFC: selective aggregates

[likg227]

Background

This is a useful feature according to some articles. This feature is supported by PostgreSQL and we can use it to implement distinct aggregation.

Design

Syntax: AGG(<expression>) FILTER(WHERE <condition>), e.g. sum(v1) FILTER(WHERE v1 < 5).

The FILTER clause extends aggregate functions (sum, avg, count, …) by an additional WHERE clause. The result of the aggregate is built from only the rows that satisfy the additional WHERE clause too.

In frontend, we can add Condition for PlanAggCall . As for the backend, is it easy to implement this feature? cc @StrikeW

Discussions

An alternative approach is using CASE .. WHEN .. to wrap aggregate's argument. Generally, we just need to use CASE .. WHEN .. to wrap the arguments and compute it in LogicalProject below LogicalAgg. However, we have to insert an extra LogicalProject when we want use this alternative approach to implement distinct aggregation, which might bring more performance cost.

In conclusion, selective aggregates are clearer and more convenient.

Reference

PostgreSQL document

Modern SQL

[fuyufjh]

+1 for this.

By the way,

An alternative approach is using CASE .. WHEN .. to wrap aggregate's argument.

The "alternative approach" doesn't seem to work perfectly. CASE WHEN does not provide the filtering semantic exactly. Assume a Project with CASE WHEN was added under Agg operator, it must still put something (i.e. generated by the then branch) to the aggregation function. The best choice here must be a NULL because almost all agg functions simply ignore NULLs in their implementation, that is: AGG({..., NULLs}) == AGG({...}), but this property may be broken for some corner cases or UDAF.