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
