I understand yours note about datatype-specific operations, so I need to think more generic about it.
About yours additional note, I think that it is not possible to get easy the same result with appropriate use of window framing options,
because "exclude ties" will not exclude "current row" itself, only peers of it. So, that is the only difference and reason of DISTINCT aggregate.
Maybe, if we can specify at the same time to "exclude ties" and "exclude current row" itself, there will not be need of DISTINCT, but right now
I think that nor "exclude ties" nor "exclude groups" or "exclude current row", can specify it, because they can't be nested or used at the same time.
Krasiyan Andreev <krasiyan@gmail.com> writes:
> I want to propose to you an old patch for Postgres 11, off-site developed
> by Oliver Ford,
> but I have permission from him to publish it and to continue it's
> development,
> that allow distinct aggregates, like select sum(distinct nums) within a
> window function.
I started to respond by asking whether that's well-defined, but
reading down further I see that that's not actually what the feature
is: what it is is attaching DISTINCT to a window function itself.
I'd still ask whether it's well-defined though, or even minimally
sensible. Window functions are generally supposed to produce one
row per input row --- how does that square with the implicit row
merging of DISTINCT? They're also typically row-order-sensitive
--- how does that work with DISTINCT? Also, to the extent that
this is sensible, can't you get the same results already today
with appropriate use of window framing options?
> It's a WIP, because it doesn't have tests yet (I will add them later) and
> also, it works for a int, float, and numeric types,
As a rule of thumb, operations like this should not be coded to be
datatype-specific. We threw out some features in the original window
function patch until they could be rewritten to not be limited to a
hard-coded set of data types (cf commit 0a459cec9), and I don't see
why we'd apply a lesser standard here. Certainly DISTINCT for
aggregates has no such limitation.
regards, tom lane