SQL Server 查询 - 分组乘法 [英] SQL Server Query - groupwise multiplication
本文介绍了SQL Server 查询 - 分组乘法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我们有一个这样的表:
If we have a table like this:
Grp Value
Grp1 2
Grp1 5
Grp1 3
Grp2 3
Grp2 -5
Grp2 -2
Grp3 4
Grp3 0
Grp3 1
Grp4 -2
Grp4 -4
Grp5 7
Grp5 NULL
Grp6 NULL
Grp6 NULL
Grp7 -1
Grp7 10
我们如何分组/乘以得到这个?
How can we group/multiply to get this?
GrpID Value
Grp1 30
Grp2 30
Grp3 0
Grp4 8
Grp5 7
Grp6 NULL
Grp7 -10
推荐答案
行值相乘与行值的对数相加相同
Multiplying row values is the same as adding logarithms of row values
诀窍是处理零和空值.
好的,现在检查
DECLARE @foo TABLE (GrpID varchar(10), Value float)
INSERt @foo (GrpID, Value)
SELECT 'Grp1', 2
UNION ALL SELECT 'Grp1', 5
UNION ALL SELECT 'Grp1', 3
UNION ALL SELECT 'Grp2', 3
UNION ALL SELECT 'Grp2', -5
UNION ALL SELECT 'Grp2', -2
UNION ALL SELECT 'Grp3', 4
UNION ALL SELECT 'Grp3', 0
UNION ALL SELECT 'Grp3', 1
UNION ALL SELECT 'Grp4', -2
UNION ALL SELECT 'Grp4', -4
UNION ALL SELECT 'Grp5', 7
UNION ALL SELECT 'Grp5', NULL
UNION ALL SELECT 'Grp6', NULL
UNION ALL SELECT 'Grp6', NULL
UNION ALL SELECT 'Grp7', -1
UNION ALL SELECT 'Grp7', 10
SELECT
GrpID,
CASE
WHEN MinVal = 0 THEN 0
WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
ELSE EXP(ABSMult)
END
FROM
(
SELECT
GrpID,
--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
@foo
GROUP BY
GrpID
) foo
这篇关于SQL Server 查询 - 分组乘法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文