On 12/11/62 18:50, Merijn Verstraaten wrote:
I already asked this question without an answer, but as it hidden somewhere
nested deeply in another thread I think it may have simply gone under the
radar. Apologies for the duplication if you already saw it!
How is the behaviour of (custom) window functions defined? Specifically, in the
presence of an ORDER BY on the window.
The functionality of row_number/rank/dense_rank seems to require that xStep and
xInverse are called on rows in the order specified by ORDER BY. And, indeed,
the implementation of row_number() in the sqlite source seems to rely on being
called in the same order as ORDER BY, but at the same time the documentation
states:
"the built-in window functions, however, require special-case handling in the query
planner and hence new window functions that exhibit the exceptional properties found in
the built-in window functions cannot be added by the application."
So does this indeed mean that these builtin ones are handled specially and
other windows functions have to accept/deal with having their window arguments
added/removed in an arbitrary order?
No, they don't need to handle that. The rows will always be
added/removed in ORDER BY order for all window functions (and will be
removed in the same order in which they were added if the ORDER BY order
is ambiguous).
There is some special handling for the various built-in window functions
though. For example, most of them ignore the window type. You can't
implement percent_rank(), cume_dist() or ntile() without knowing the
number of rows in the partition before returning any values, so that
requires special handling as well. There are probably other things too...
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
