Sql Server 中的乘法聚合 [英] Multpilcation Aggregate in Sql Server

问题描述

我正在尝试从下面的 sql 代码创建乘法聚合函数.

I am trying to create Multplication aggregate function from the below sql code.

declare @Floats as table (id int,value float)

insert into @Floats values (1,1)
insert into @Floats values (2,3)
insert into @Floats values (3,6)

SELECT *
FROM   @Floats a
       CROSS apply (SELECT CASE
                             WHEN MinVal = 0 THEN 0
                             WHEN Neg % 2 = 1 THEN -1 * Exp(ABSMult)
                             ELSE Exp(ABSMult)
                           END AS mul_value
                    FROM   (SELECT
                           Sum(Log(Abs(NULLIF(Value, 0)))) AS ABSMult,
                           Sum(Sign(CASE
                                      WHEN Value < 0 THEN 1
                                      ELSE 0
                                    END))                  AS Neg,
                           Min(Abs(Value))                 AS MinVal
                            FROM   @Floats b
                            WHERE  a.id >= b.id) foo) cs 

结果

id  value   mul_value
--  -----   ---------
1   1       1
2   3       3
3   6       18

这是功能的想法.由于我们需要表类型将表作为输入传递给函数，因此我们在下面创建一个

Here is the idea for function. Since we we need table type to pass table as input to function am creating one below

用户定义的表类型

CREATE TYPE [dbo].[UDT_TEST] AS TABLE( value float ) 

功能代码

CREATE FUNCTION udf_Mul(@values dbo.[UDT_TEST] readonly)
RETURNS FLOAT
AS
  BEGIN
      DECLARE @mul_value FLOAT

      SELECT @mul_value=CASE
               WHEN MinVal = 0 THEN 0
               WHEN Neg % 2 = 1 THEN -1 * Exp(ABSMult)
               ELSE Exp(ABSMult)
             END 
      FROM   (SELECT
             --log of +ve row values
             Sum(Log(Abs(NULLIF(Value, 0)))) AS ABSMult,
             --count of -ve values. Even = +ve result.
             Sum(Sign(CASE
                        WHEN Value < 0 THEN 1
                        ELSE 0
                      END))                  AS Neg,
             --anything * zero = zero
             Min(Abs(Value))                 AS MinVal
              FROM   @values) foo

      RETURN @mul_value
  END 

使用这样的功能.

SELECT *
FROM   @Floats a
       CROSS apply (SELECT dbo.udf_mul(value) ast
                            FROM   @Floats b
                            WHERE  a.id >= b.id)  cs 

这里的问题是输入 dbo.udf_mul 期望参数是 [dbo].[UDT_TEST] 类型但值列是 Float代码>类型.

Here the problem is the input dbo.udf_mul expects parameter to be of [dbo].[UDT_TEST] type but value column is of Float type.

错误:

Msg 206, Level 16, State 2, Line 7 操作数类型冲突:float is与 UDT_TEST 不兼容有什么办法可以实现吗?

Msg 206, Level 16, State 2, Line 7 Operand type clash: float is incompatible with UDT_TEST Is there any way to achieve this?

推荐答案

我不会将标量函数用于此类操作.您最终的性能很差，因为查询优化器需要在没有任何优化的情况下row-by-row 运行它.更多信息:SQL Server函数:基础.

I would not use scalar function for this type of operation. You end up with poor performance because query optimizer need to run it row-by-row without any optimalization. More info: SQL Server Functions: The Basics.

第二个使用浮点数/十进制数和 LOG/EXP 你会得到近似误差.

Second with float/decimal and LOG/EXP you will get approximation errors.

第三个将数据传递给函数，你可以使用 XML 像:

Third to pass data to function you can use XML like:

CREATE FUNCTION dbo.udf_Mul(@value xml)
RETURNS FLOAT
AS
  BEGIN
      DECLARE @mul_value FLOAT;
      DECLARE @values AS TABLE ([value] float);

       INSERT INTO @values([value])
       SELECT [value] = t.c.value('(value)[1]', 'float')
      FROM @value.nodes('//row') AS t(c);


      SELECT @mul_value=CASE
               WHEN MinVal = 0 THEN 0
               WHEN Neg % 2 = 1 THEN -1 * Exp(ABSMult)
               ELSE Exp(ABSMult)
             END 
      FROM   (SELECT
             Sum(Log(Abs(NULLIF(Value, 0)))) AS ABSMult,
             Sum(Sign(CASE
                        WHEN Value < 0 THEN 1
                        ELSE 0
                      END))                  AS Neg,
             Min(Abs(Value))                 AS MinVal
              FROM   @values) foo

      RETURN @mul_value
  END 

并调用它:

SELECT *
FROM Floats a
CROSS APPLY (SELECT dbo.udf_mul((SELECT [value]
                                 FROM Floats b
                                 WHERE a.id >= b.id
                                 FOR XML PATH, ROOT('root')))
             AS r) as cs(r);

SqlFiddleDemo

输出:

╔═════╦════════╦════════════════════╗
║ id  ║ value  ║         r          ║
╠═════╬════════╬════════════════════╣
║  1  ║     1  ║ 1                  ║
║  2  ║     3  ║ 3.0000000000000004 ║
║  3  ║     6  ║ 17.999999999999996 ║
║  4  ║     2  ║ 36                 ║
╚═════╩════════╩════════════════════╝

这篇关于Sql Server 中的乘法聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！