是否可以将用户定义的聚合(clr)与窗口函数(over)一起使用? [英] Is it possible to use user defined aggregates (clr) with window functions (over)?
问题描述
是否可以将用户定义的聚合(clr)与窗口函数(over)一起使用?
Is it possible to use user defined aggregates (clr) with window functions (over) ?
在文档中找不到答案:http://technet.microsoft.com/en-us/library/ms190678.aspx
推荐答案
您说得对,在文档中查找任何内容都很棘手.但是搜索 Connect 网站,我设法找到了这个 gem:
You're right that it's tricky to find anything in the documentation. But searching the Connect website, I managed to find this gem:
今天,您可以像常规聚合函数一样使用带有 OVER 子句和 PARTITION BY 的 CLR 聚合.一旦我们支持窗口函数...
Today, you can use CLR aggregates with OVER clause and PARTITION BY just like regular aggregate functions. Once we have support for window functions...
这是微软的回应.
但是,在我等待旧机器创建新数据库项目并创建此聚合时,我在 Connect 站点上进行了搜索:
However, searching on the Connect site was what I did whilst I was waiting for my aged machine to create a new database project and create this aggregate:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize = 2000)]
public struct SqlAggregate1 : IBinarySerialize
{
private SqlString last;
public void Init()
{
// Ignore
}
public void Accumulate(SqlString Value)
{
last = Value;
}
public void Merge (SqlAggregate1 Group)
{
// Ignore
}
public SqlString Terminate ()
{
// Put your code here
return last;
}
public void Read(BinaryReader r)
{
last = new SqlString(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(last.ToString());
}
}
然后运行这个脚本:
select dbo.SqlAggregate1(Column2) OVER (PARTITION BY Column1)
from (select 1,'abc' union all select 1,'def' union all
select 2,'ghi' union all select 2,'jkl') as t(Column1,Column2)
产生:
------------
abc
abc
ghi
ghi
说起来还很长——只要尝试,您就可以很容易地为自己找到答案.
Which is a long way to say - you could have easily discovered the answer for yourself just by trying it.
这篇关于是否可以将用户定义的聚合(clr)与窗口函数(over)一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!