Question / request for Aggregate operation

[mbasmanova]

We'd like to be able to specify masks for individual aggregations and a boolean ignoreNullKeys for a grouping set.

Masks are input columns of type boolean which allow to mask out rows for individual aggregations, e.g. SELECT count(1) filter (where a > 10) FROM t.

ignoreNullKeys boolean flag allows to avoid unnecessary processing when an aggregation is followed by an inner join on the grouping keys. In this case, rows with nulls in grouping keys cannot possible match the join condition and therefore we'd like to skip aggregations for such groups.

CC: @jacques-n

[mbasmanova]

Also, would it make sense to add a "step" enum to aggregate operation: partial, final, intermediate or single? This would allow us to specify the type of input and result of the operation, e.g.

(from https://facebookincubator.github.io/velox/develop/aggregate-functions.html)

[jacques-n]

Totally agree for the concept of phase exposed. I've already outlined in the context of the aggregate functions but need to do the same with regards to bindings within aggregate. I'll review the phases you've shared here and evaluate how they encompass those currently outlined in our proposal for aggregate functions and post a patch that includes at least an initial sketch of the common aggregations shortly. Thanks!

Masks are input columns of type boolean which allow to mask out rows for individual aggregations, e.g. SELECT count(1) filter (where a > 10) FROM t.

If I understand the request correctly, this feels like syntactic-sugared variation of COUNT(CASE WHEN a > 10 then null else 1 end). Is that correct/fair? If that's the case why the need for a customization like this?

Another random thought is that one could define an extension function CONDITIONAL_COUNT(1, a > 10) or similar to provides this functionality. I'm just trying to figure out the specifics to why this should be expressed separately. Also, are you looking to add this to a particular physical aggregation (e.g. hash aggregation) or to the logical aggregation operation?

ignoreNullKeys boolean flag
This also sounds more like something you'd want to apply to a specific physical aggregation (e.g. hash aggregation), is that correct. Is your request across all grouping sets, per grouping set or per field per grouping set? For example, imagine if I have the grouping sets: [[city, state, country],[state]], what variations do you want to express (for example, if I get a null for state, I would figure I would exclude the record for the second grouping set. Would I exclude for the first when there are other grouping fields that are still populated?

[jacques-n]

Also, would it make sense to add a "step" enum to aggregate operation: partial, final, intermediate or single?

@mbasmanova , in my most recent PR I've also proposed the following AggregationPhases:

substrait/binary/expression.proto

Line 88 in 1ca0bfb

enum AggregationPhase {

[mbasmanova]

@jacques-n Thank you. I'll check these out.

[jacques-n]

Hey there, we've added support in aggregate measures for a masking filter per measure as part of #88. I believe that covers the majority of this ticket. For now, we propose using either a separate pre-filter for ignoreNulls or adding an AdvancedExtension that exposes that property in AggregateRel.

Closing this issue. Feel free to reopen!