On 04/12/22 02:27, David Rowley wrote:
To make this work when rows can exit the window frame seems
significantly harder. Likely a hash table would be a better data
structure to remove records from, but then how are you going to spill
the hash table to disk when it reaches work_mem? As David J mentions,
it seems like you'd need a hash table with a counter to track how many
times a given value appears and only remove it from the table once
that counter reaches 0. Unsure how you're going to constrain that to
not use more than work_mem though.
Interesting problem, Hashtables created in normal aggregates (AGG_HASHED
mode) may provide some reference as they have hashagg_spill_tuple but I
am not sure of any prior implementation of hashtable with counter and
spill. Major concern is, if we go through tuplesort route (without order
by case), would we get handicapped in future if we want order by or more
features?
Are there any other databases which support DISTINCT window aggregate
with an ORDER BY in the window clause?
Oracle db support distinct window aggregates albeit without order by
clause. Rest of databases which I've tried (mysql/sqlserver express)
don't even support distinct in window aggregates so those gets ruled out
as well.
If you were to limit this to only working with the query you mentioned
in [1], i.e PARTITION BY without an ORDER BY, then you only need to
aggregate once per partition per aggregate and you only need to do
that once all of the tuples for the partition are in the tuplestore.
It seems to me like you could add all the records to a tuplesort and
then sort by the DISTINCT column then aggregate everything except for
consecutive duplicates. You can then aggregate any other aggregates
which share the same DISTINCT column, otherwise, you just destroy the
tuplesort and rinse and repeat for the next aggregate.
This looks like way to go that would ensure main use case of portability
from Oracle.
If you were to limit this to only working with the query you mentioned
in [1], i.e PARTITION BY without an ORDER BY,
I need to dig deeper into order by case.
--
Regards,
Ankit Kumar Pandey
