sql – 创建一个以整列作为输入和输出的函数

发布时间：2020-12-12 07:05:22 所属栏目：MsSql教程来源：网络整理

导读：我有几个用R编写的程序,现在我需要在T-SQL中进行翻译以将它们传递给客户端.我是T-SQL的新手,我在翻译所有R函数方面遇到了一些困难. 一个例子是数值导数函数,对于两个输入列(值和时间),它将返回具有计算导数的另一列(具有相同长度). 我目前的理解是：我不能使

我有几个用R编写的程序,现在我需要在T-SQL中进行翻译以将它们传递给客户端.我是T-SQL的新手,我在翻译所有R函数方面遇到了一些困难.

一个例子是数值导数函数,对于两个输入列(值和时间),它将返回具有计算导数的另一列(具有相同长度).

我目前的理解是：

>我不能使用SP,因为我需要使用这个函数内联
select语句,如：
从Customer_Detail中选择Customer_ID,日期,金额,衍生物(金额,日期)
>我不能使用UDF,因为它们可以作为输入参数仅采用标量.由于速度的原因,我需要矢量化函数,因为对于某些函数,如上所述,逐行运行没有意义(对于下一个和前一个需要的每个值)
> UDA采用整列,但正如名称所示……,他们会像sum或avg那样聚合列.

如果以上是正确的,哪些其他技术可以让我创建我需要的功能类型？类似于我所遵循的SQL内置函数的一个例子是square(),它(显然)采用一列并返回自己^ 2.我的目标是创建一个函数库,其行为类似于square,power等.但是在内部它会有所不同导致square take并返回每个标量通过行读取.我想知道是否有可能使用累积方法(如UDA)的User Defied能够对导入结束时的所有数据进行操作,然后返回相同长度的列？

注意：目前我正在使用SQL-Server 2005,但我们很快就会切换到2012年(或者可能在几个月内切换到2014年),所以基于任何2005版本的SQL-Server的答案都可以.

编辑：为R开发人员添加了R标签,希望他们已经面临这样的困难.

EDIT2：添加了CLR标记：我经历了Pro t-sql 2005程序员指南中定义的CLR用户定义聚合.我上面已经说过,这种功能不适合我的需要,但值得研究. UDA所需的4种方法是：Init,Accumulate,Merge和Terminate.我的请求需要由UDA的同一个实例一起分析整个数据.因此,包括将多核处理的部分结果组合在一起的合并方法的选项将不起作用.

解决方法

我想你可能会考虑改变一下你的想法.在处理数据集时,SQL语言非常好,尤其是现代RDBMS实现(如SQL Server 2012),但您必须在集合中思考,而不是在行或列中思考.虽然我不知道你确切的任务,但是让我们看一下 – SQL Server 2012有一套很好的 window functions ranking functions analytic functions common table expressions,所以你几乎可以编写任何内联查询.您可以使用 common table expression链来以您想要的任何方式转换数据,计算运行总计,计算平均值或窗口上的其他聚合等等.

实际上,我一直很喜欢SQL,当我学习函数式语言(ML和Scala)时,我的想法是我的SQL方法与函数式语言范式非常相似 – 只需切片和切块数据而不将任何内容保存到变量中,直到你有结果集你的需要.

举个简单的例子,这是来自SO-How to get average of the ‘middle’ values in a group?的问题.目标是获得每组中间3个值的平均值：

TEST_ID TEST_VALUE  GROUP_ID
1       5           1       -+
2       10          1        +- these values for group_id = 1
3       15          1       -+
4       25          2       -+
5       35          2        +- these values for group_id = 2
6       5           2       -+
7       15          2       
8       25          3
9       45          3       -+
10      55          3        +- these values for group_id = 3
11      15          3       -+
12      5           3
13      25          3
14      45          4       +- this value for group_id = 4

对我来说,在R中执行它并不是一件容易的事,但在SQL中它可能是一个非常简单的查询,如下所示：

with cte as (
    select
        *,row_number() over(partition by group_id order by test_value) as rn,count(*) over(partition by group_id) as cnt
    from test
)
select
    group_id,avg(test_value)
from cte
where
    cnt <= 3 or
    (rn >= cnt / 2 - 1 and rn <= cnt / 2 + 1)
group by group_id

您还可以轻松扩展此查询以获得中间的5个值.

仔细看看analytical functions,尝试重新考虑你在窗口函数方面的计算,可能在简单的SQL中重写你的R程序并不困难.

希望能帮助到你.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!