A genuine use for a SQL CLR Aggregate

When SQL CLR functions were added to SQL Server 2005 my interest was piqued by the possibility of introducing new aggregate functions. I quickly realised that most of the aggregate activity I wanted to do was better handled by T-SQL and/or extremely dangerous within a CLR function. For example, if we wanted to use an aggregate to work out the median (middle value) from a set you could write a CLR aggregate however on a large data set you would run the risk of consuming a huge amount of memory.
Judging by the examples (the lack of) on the Internet it seems that most people reached the same conclusion. In fact most examples seem entirely academic exercises or face the same problem as my Median example. The CLR aggregate examples I have seen are:
  • Concatenate strings (lots of plagiarism for this one)
  • A baker's dozen example (when would I ever use it?)
  • A weighted average calculation (interesting, but of limited use)