Add support for OVER (ORDER BY) for CLR Aggregate functions
Currently, there is no way to specify aggregate function processing order - and, indeed, the IsInvariantToOrder property of the SqlUserDefinedAggregate attribute has no effect.
This can be important for string concatenation aggregates, as the strings may have to be aggregated in a specific order. The ability to use a clause such as:
SELECT dbo.StringConcatenate(StringValue) OVER (ORDER BY ID) FROM dbo.MyTable
This would mean that you could support better forms of aggregation, and that other aggregates could be implemented in a more succinct way (for example, a median aggregate could benefit from knowing that input was ordered).
Upvotes: 36<=-=Oct 8 2010 11:58AM=-=>
Thanks for your feedback. We will consider it for a future version of SQL Server. Please note however that the syntax you are proposing doesn’t achieve what you are looking for. The ORDER BY within OVER clause controls the ordering of the window and not the order of rows to the aggregate. We support OVER clause (excluding ORDER BY clause since we don’t have it) already for CLR aggregates. So if we extend OVER clause to support ORDER BY it will work with existing CLR aggregates also.
ANSI SQL:2008 and earlier has a feature called ordered set functions that use a WITHIN GROUP specification. One such aggregate is PERCENTILE_CONT which looks like:
PERCENTILE_CONT( ) WITHIN GROUP (ORDER BY )Once we support the above syntax for ordered set function, we will consider extending the CLR Aggregate contract to do the same. This will provide the functionality you are looking for i.e., performing aggregate operation in a specific order.
Umachandar, SQL Programmability Team
I had to go to the PostgreSQL documentation http://www.postgresql.org/docs/8.4/interactive/tutorial-window.html to understand what UC is talking about here. Hopefully, it is clear what Matt is asking for here, regardless of what syntax ought to be used to provide it.
The limited support for window functions in SQL Server is entirely to blame for the misconceptions we have acquired around the OVER clause.<=-=Nov 19 2013 9:58AM=-=>
“The ORDER BY within OVER clause controls the ordering of the window and not the order of rows to the aggregate.”
This is not true. According to MSDN:
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window functioncalculation is performed.<=-=Jan 18 2015 1:18AM=-=>
‘over clause’ s contains now ‘ROWS | RANGE’ argument ,that can use for UDA aggregates ,<=-=Apr 4 2015 1:40PM=-=>
What is the status of supporting the WITHIN GROUP syntax, and afterwards allowing this in CLRs?
I’m also interested in doing:
SELECT dbo.StringConcatenate(StringValue) WITHIN GROUP (ORDER BY ID) FROM dbo.MyTable
The posted feature request appear to be Closed, won’t fix, without stating why?