Support for filtered aggregations

[kblomster]

Problem

SQL:2003 introduced filtered aggregations, e.g. SELECT COUNT(*) FILTER(WHERE foo > 0). They're currently supported in Postgres and SQLite.

If I want to use this syntax in Jet, I have to resort to Raw, which is inconvenient. I'd like to be able to build the FILTER(WHERE ...) clause in the same way I build any other WHERE clause.

To be fair, the ANSI SQL form of the FILTER clause is in many cases essentially syntactic sugar, and it can usually be emulated with a CASE statement, but this is not always true. Taking the CASE approach typically relies on aggregate functions ignoring nulls, but this does not work with functions such as Postgres' json_arrayagg, where nulls may or may not be a desired part of the output. Postgres also extends standard SQL by allowing subqueries like EXISTS() in the FILTER clause, and that can't be emulated with a CASE statement either.

Possible solution

Aggregate functions in dialects that support this syntax could return an extended Expression object that would allow adding a FILTER clause, similar to how the window OVER clause is implemented. Rough sketch:

type AggregateIntegerExpression interface {
    IntegerExpression
    FILTER_WHERE(expression BoolExpression) IntegerExpression
}

// usage:
SELECT(
    COUNT(STAR).
        FILTER_WHERE(SomeCol.IS_NOT_NULL))

[go-jet]

Related comment - #355 (comment).

[go-jet]

With the release v2.12.0 users can now build custom expressions and operators using newly exposed CustomExpression and Token functions. (wiki)

[brettinternet]

Hi, do I have this right?

func FILTER_WHERE(agg Expression, where BoolExpression) Expression {
	return CustomExpression(agg, Token("FILTER (WHERE"), where, Token(")"))
}

SELECT(
	FILTER_WHERE(
		COUNT(DISTINCT(MyTable.ID)),
		MyOtherTable.Name.EQ("John"),
	),
)

Or can the agg type be more narrow than Expression?

[go-jet]

Yeah, it looks good.

You can also make it a bit more SQL like:

type filterExpand struct {
	agg Expression
}

func (f *filterExpand) WHERE(cond BoolExpression) Expression {
	return CustomExpression(f.agg, Token("FILTER (WHERE"), cond, Token(")"))
}

func FILTER(agg Expression) *filterExpand {
	return &filterExpand{agg}
}

then it can be used like:

SELECT(
	FILTER(COUNT(DISTINCT(MyTable.ID))).WHERE(MyOtherTable.Name.EQ("John")),
)

[go-jet]

Hmm, not sure why I closed this issue. It is a valid missing feature.

[brettinternet]

Thank you for your solution, that's very helpful.