Server中的自定义聚合函数

Server中的自定义聚合函数

本文介绍了MS SQL Server中的自定义聚合函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何在MS SQL Server中创建自定义聚合函数?

How can I create a custom aggregate function in MS SQL Server? An example would help a lot.

推荐答案

SQL Server 2000并不正式支持自定义聚合函数。但是,最近我也需要该功能,并且发现这篇文章很有启发性:

SQL Server 2000 doesn't officially support custom aggregate functions. However, I recently needed that functionality as well, and I found this article enlightening:
http://weblogs.sqlteam.com/jeffs/articles/1490.aspx

虽然有点黑,但是:需要访问 sp_OA ___ 扩展过程。

It's a bit hack-ish, though: it requires access to the sp_OA___ extended procedures.

摘要是您可以模拟具有一系列四个包装函数的聚合函数,每个包装函数均执行以下任务之一:

The summary is that you can simulate an aggregate function with a series of four wrapper functions, each of which performs one of the following tasks:


  1. 创建可以保存ActiveX对象查询中的状态。在运行查询之前,先调用


    • 使用ActiveX对象执行实际的聚合。

    • 清除GROUP BY边界上的ActiveX对象状态

    • 销毁对象。在运行查询后以及在错误处理过程中,称为

  1. Create an ActiveX object that can hold state within the query. Call this before running the query.
    • Do the actual aggregation using the ActiveX object.
    • Clear the ActiveX object state on GROUP BY boundries
    • Destroy the object. Call this after running the query and during error handling.

然后在查询的选择列表中包括项目2 3,并且项目2还必须包装在现有的无效集合函数(如MAX()或MIN())中。您还可以将这种技术用于累积函数来执行行号之类的操作。

You then include items 2 and 3 in the select list for your query, and item 2 must also be wrapped in an existing no-effect aggregate function like MAX() or MIN(). You can also use this technique for cumulative functions to do things like row numbers.

一些评论表明,优化器可能会尝试通过优化优化来消除聚合效应。在某些情况下会打电话,尽管我希望那确实是非常罕见的情况。但是,我发现了这个问题,因为我认真对待了这些警告,以继续寻找更好的东西。

Some of the comments suggest that the optimizer may try to negate the aggregation effects by optimizing away the calls in some circumstances, though I expect that would be a very rare case indeed. However, I found this question because I took those warnings seriously enough to continue searching for something better.

这篇关于MS SQL Server中的自定义聚合函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-01 12:00