为什么方法 1 中的值不准确以及如何解决它？ 我尝试将数据类型更改为
我正在尝试执行累积乘法。我正在尝试两种方法来执行此操作
示例数据：
DECLARE @TEST TABLE
(
PAR_COLUMN INT,
PERIOD INT,
VALUE NUMERIC(22, 6)
)
INSERT INTO @TEST VALUES
(1,601,10 ),
(1,602,20 ),
(1,603,30 ),
(1,604,40 ),
(1,605,50 ),
(1,606,60 ),
(2,601,100),
(2,602,200),
(2,603,300),
(2,604,400),
(2,605,500),
(2,606,600)
备注：value列中的数据永远不会是整数，值将包含小数部分。为了显示近似问题，我将示例值保留为整数。
方法 1： EXP + LOG + SUM(( Over(排序方式(
在这种方法中，我使用EXP + LOG + SUM() Over(Order by)技术来查找累积乘法。在此方法中，值不准确;结果中存在一些舍入和近似问题。
SELECT *,
Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
OVER(
PARTITION BY PAR_COLUMN
ORDER BY PERIOD)) AS CUM_MUL
FROM @TEST;
结果：
PAR_COLUMN PERIOD VALUE CUM_MUL
---------- ------ --------- ----------------
1 601 10.000000 10
1 602 20.000000 200 -- 10 * 20 = 200(correct)
1 603 30.000000 6000.00000000001 -- 200 * 30 = 6000.000000000 (not 6000.00000000001) incorrect
1 604 40.000000 240000
1 605 50.000000 12000000
1 606 60.000000 720000000.000001 -- 12000000 * 60 = 720000000.000000 (not 720000000.000001) incorrect
2 601 100.000000 100
2 602 200.000000 20000
2 603 300.000000 5999999.99999999 -- 20000.000000 *300.000000 = 6000000.000000 (not 5999999.99999999) incorrect
2 604 400.000000 2399999999.99999
2 605 500.000000 1199999999999.99
2 606 600.000000 719999999999998
方法 2：轨迹乘法(递归 CTE(
此方法完美运行，没有任何舍入或近似问题。
;WITH CTE
AS (SELECT TOP 1 WITH TIES PAR_COLUMN,
PERIOD,
VALUE,
CUM_MUL = VALUE
FROM @TEST
ORDER BY PERIOD
UNION ALL
SELECT T.PAR_COLUMN,
T.PERIOD,
T.VALUE,
Cast(T.VALUE * C.CUM_MUL AS NUMERIC(22, 6))
FROM CTE C
INNER JOIN @TEST T
ON C.PAR_COLUMN = T.PAR_COLUMN
AND T.PERIOD = C.PERIOD + 1)
SELECT *
FROM CTE
ORDER BY PAR_COLUMN,PERIOD
结果
PAR_COLUMN PERIOD VALUE CUM_MUL
---------- ------ --------- ----------------
1 601 10.000000 10.000000
1 602 20.000000 200.000000
1 603 30.000000 6000.000000
1 604 40.000000 240000.000000
1 605 50.000000 12000000.000000
1 606 60.000000 720000000.000000
2 601 100.000000 100.000000
2 602 200.000000 20000.000000
2 603 300.000000 6000000.000000
2 604 400.000000 2400000000.000000
2 605 500.000000 1200000000000.000000
2 606 600.000000 720000000000000.000000
谁能告诉我
Float并增加numeric scale但没有使用。
我真的很想使用比方法 1 快得多的方法 2。
编辑：现在我知道近似的原因了。任何人都可以找到解决此问题的方法吗？
在纯 T-SQL LOG 和 EXP 中使用 float 类型(8 字节(进行操作，该类型只有 15-17 个有效数字。如果对足够大的值求和，即使是最后 15 位数字也会变得不准确。您的数据是numeric(22,6)的，因此 15 位有效数字是不够的。
POWER可以以更高的精度返回numeric类型，但它对我们来说用处不大，因为无论如何，LOG和LOG10都只能返回float。
为了演示问题，我将示例中的类型更改为numeric(15,0)并使用POWER而不是EXP：
DECLARE @TEST TABLE
(
PAR_COLUMN INT,
PERIOD INT,
VALUE NUMERIC(15, 0)
);
INSERT INTO @TEST VALUES
(1,601,10 ),
(1,602,20 ),
(1,603,30 ),
(1,604,40 ),
(1,605,50 ),
(1,606,60 ),
(2,601,100),
(2,602,200),
(2,603,300),
(2,604,400),
(2,605,500),
(2,606,600);
SELECT *,
POWER(CAST(10 AS numeric(15,0)),
Sum(LOG10(
Abs(NULLIF(VALUE, 0))
))
OVER(PARTITION BY PAR_COLUMN ORDER BY PERIOD)) AS Mul
FROM @TEST;
结果
+------------+--------+-------+-----------------+
| PAR_COLUMN | PERIOD | VALUE | Mul |
+------------+--------+-------+-----------------+
| 1 | 601 | 10 | 10 |
| 1 | 602 | 20 | 200 |
| 1 | 603 | 30 | 6000 |
| 1 | 604 | 40 | 240000 |
| 1 | 605 | 50 | 12000000 |
| 1 | 606 | 60 | 720000000 |
| 2 | 601 | 100 | 100 |
| 2 | 602 | 200 | 20000 |
| 2 | 603 | 300 | 6000000 |
| 2 | 604 | 400 | 2400000000 |
| 2 | 605 | 500 | 1200000000000 |
| 2 | 606 | 600 | 720000000000001 |
+------------+--------+-------+-----------------+
这里的每一步都会失去精度。计算 LOG 会损失精度，SUM 会丢失精度，EXP/POWER 会丢失精度。有了这些内置函数，我认为你对此无能为力。
所以，答案是 - 将 CLR 与 C# decimal 类型(不是 double (一起使用，它支持更高的精度(28-29 位有效数字(。您的原始 SQL 类型numeric(22,6)适合它。而且您不需要LOG/EXP的技巧.
哎呀。我尝试制作一个计算产品的 CLR 聚合。它在我的测试中有效，但仅作为一个简单的聚合，即
这有效：
SELECT T.PAR_COLUMN, [dbo].[Product](T.VALUE) AS P
FROM @TEST AS T
GROUP BY T.PAR_COLUMN;
甚至OVER (PARTITION BY)有效：
SELECT *,
[dbo].[Product](T.VALUE)
OVER (PARTITION BY PAR_COLUMN) AS P
FROM @TEST AS T;
但是，使用 OVER (PARTITION BY ... ORDER BY ...) 运行产品不起作用(使用 SQL Server 2014 Express 12.0.2000.8 检查(：
SELECT *,
[dbo].[Product](T.VALUE)
OVER (PARTITION BY T.PAR_COLUMN ORDER BY T.PERIOD
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_MUL
FROM @TEST AS T;
关键字"ORDER"附近的语法不正确。
搜索找到了此连接项目，该项目已关闭为"无法修复"和此问题。
C# 代码：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
using System.Text;
namespace RunningProduct
{
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
MaxByteSize = 17,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
IsNullIfEmpty = true)]
public struct Product : IBinarySerialize
{
private bool m_bIsNull; // 1 byte storage
private decimal m_Product; // 16 bytes storage
public void Init()
{
this.m_bIsNull = true;
this.m_Product = 1;
}
public void Accumulate(
[SqlFacet(Precision = 22, Scale = 6)] SqlDecimal ParamValue)
{
if (ParamValue.IsNull) return;
this.m_bIsNull = false;
this.m_Product *= ParamValue.Value;
}
public void Merge(Product other)
{
SqlDecimal otherValue = other.Terminate();
this.Accumulate(otherValue);
}
[return: SqlFacet(Precision = 22, Scale = 6)]
public SqlDecimal Terminate()
{
if (m_bIsNull)
{
return SqlDecimal.Null;
}
else
{
return m_Product;
}
}
public void Read(BinaryReader r)
{
this.m_bIsNull = r.ReadBoolean();
this.m_Product = r.ReadDecimal();
}
public void Write(BinaryWriter w)
{
w.Write(this.m_bIsNull);
w.Write(this.m_Product);
}
}
}
安装 CLR 程序集：
-- Turn advanced options on
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
-- Enable CLR
EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
CREATE ASSEMBLY [RunningProduct]
AUTHORIZATION [dbo]
FROM 'C:RunningProductRunningProduct.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE [dbo].[Product](@ParamValue numeric(22,6))
RETURNS numeric(22,6)
EXTERNAL NAME [RunningProduct].[RunningProduct.Product];
GO
这个问题非常详细地讨论了运行 SUM 的计算，Paul White 在他的回答中展示了如何编写一个 CLR 函数来有效地计算正在运行的 SUM。这将是编写计算正在运行的产品的函数的良好开端。
请注意，他使用不同的方法。Paul 没有创建自定义聚合函数，而是创建了一个返回表的函数。该函数将原始数据读入内存并执行所有必需的计算。
通过使用您选择的编程语言在客户端实现这些计算，可能更容易实现所需的效果。只需阅读整个表并计算客户端上的运行产品。如果在服务器上计算的正在运行的产品是将进一步聚合数据的更复杂计算中的中间步骤，则创建 CLR 函数是有意义的。
另一个想法浮现在脑海中。
查找提供高精度Log和Exp函数的第三方 .NET 数学库。创建这些标量函数的 CLR 版本。然后使用EXP + LOG + SUM() Over (Order by)方法，其中SUM是内置的T-SQL函数，它支持Over (Order by)和Exp，Log是自定义CLR函数，返回的不是float，而是高精度decimal。
请注意，高精度计算也可能很慢。在查询中使用 CLR 标量函数也可能使其变慢。
LOG()和EXP()将参数隐式转换为float数据类型，即近似值。
对于您的数据，您可以舍入到大倍数：
--720000000000000 must be multiple of 600
select
round( 719999999999998/600, 0 ) * 600
--result: 720000000000000
在 SQLFiddle 上测试它
create TABLE T
(
PAR_COLUMN INT,
PERIOD INT,
VALUE NUMERIC(22, 6)
)
INSERT INTO T VALUES
(1,601,10.1 ), --<--- I put decimals just to test!
(1,602,20 ),
(1,603,30 ),
(1,604,40 ),
(1,605,50 ),
(1,606,60 ),
(2,601,100),
(2,602,200),
(2,603,300),
(2,604,400),
(2,605,500),
(2,606,600)
查询 1：
with T1 as (
SELECT *,
Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
OVER(
PARTITION BY PAR_COLUMN
ORDER BY PERIOD)) AS CUM_MUL,
VALUE AS CUM_MAX1,
LAG( VALUE , 1, 1.)
OVER(
PARTITION BY PAR_COLUMN
ORDER BY PERIOD ) AS CUM_MAX2,
LAG( VALUE , 2, 1.)
OVER(
PARTITION BY PAR_COLUMN
ORDER BY PERIOD ) AS CUM_MAX3
FROM T )
select PAR_COLUMN, PERIOD, VALUE,
( round( ( CUM_MUL / ( CUM_MAX1 * CUM_MAX2 * CUM_MAX3) ) ,6)
*
cast( ( 1000000 * CUM_MAX1 * CUM_MAX2 * CUM_MAX3) as bigint )
) / 1000000.
as CUM_MUL
FROM T1
结果：
| PAR_COLUMN | PERIOD | VALUE | CUM_MUL |
|------------|--------|-------|-----------------|
| 1 | 601 | 10.1 | 10.1 | --ok! because my data
| 1 | 602 | 20 | 202 |
| 1 | 603 | 30 | 6060 |
| 1 | 604 | 40 | 242400 |
| 1 | 605 | 50 | 12120000 |
| 1 | 606 | 60 | 727200000 |
| 2 | 601 | 100 | 100 |
| 2 | 602 | 200 | 20000 |
| 2 | 603 | 300 | 6000000 |
| 2 | 604 | 400 | 2400000000 |
| 2 | 605 | 500 | 1200000000000 |
| 2 | 606 | 600 | 720000000000000 |
注意 I x1000000 在没有小数的情况下工作