在 T-SQL 中运行乘法 [英] Running Multiplication in T-SQL
本文介绍了在 T-SQL 中运行乘法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
CCP months QUART YEARS GTS
---- ------ ----- ----- ---
CCP1 1 1 2015 5
CCP1 2 1 2015 6
CCP1 3 1 2015 7
CCP1 4 2 2015 4
CCP1 5 2 2015 2
CCP1 6 2 2015 2
CCP1 7 3 2015 3
CCP1 8 3 2015 2
CCP1 9 3 2015 1
CCP1 10 4 2015 2
CCP1 11 4 2015 3
CCP1 12 4 2015 4
CCP1 1 1 2016 8
CCP1 2 1 2016 1
CCP1 3 1 2016 3
基线表
CCP BASELINE YEARS QUART
---- -------- ----- -----
CCP1 5 2015 1
预期结果
CCP months QUART YEARS GTS result
---- ------ ----- ----- --- ------
CCP1 1 1 2015 5 25 -- 5 * 5 (here 5 is the baseline)
CCP1 2 1 2015 6 30 -- 6 * 5 (here 5 is the baseline)
CCP1 3 1 2015 7 35 -- 7 * 5 (here 5 is the baseline)
CCP1 4 2 2015 4 360 -- 90 * 4(25+30+35 = 90 is the basline)
CCP1 5 2 2015 2 180 -- 90 * 2(25+30+35 = 90 is the basline)
CCP1 6 2 2015 2 180 -- 90 * 2(25+30+35 = 90 is the basline)
CCP1 7 3 2015 3 2160.00 -- 720.00 * 3(360+180+180 = 720)
CCP1 8 3 2015 2 1440.00 -- 720.00 * 2(360+180+180 = 720)
CCP1 9 3 2015 1 720.00 -- 720.00 * 1(360+180+180 = 720)
CCP1 10 4 2015 2 8640.00 -- 4320.00
CCP1 11 4 2015 3 12960.00 -- 4320.00
CCP1 12 4 2015 4 17280.00 -- 4320.00
CCP1 1 1 2016 8 311040.00 -- 38880.00
CCP1 2 1 2016 1 77760.00 -- 38880.00
CCP1 3 1 2016 3 116640.00 -- 38880.00
解释
基线表对每个 CCP 都有一个基线值.
Baseline table has single baseline value for each CCP.
基线值应应用于每个 CCP 的第一季度，下一个季度的前一季度总和值将是基线值.
The baseline value should be applied to first quarter of each CCP and for the next quarters previous quarter sum value will be the basleine.
这是 Sql Server 2008
;WITH CTE AS
( SELECT b.CCP,
Baseline = CAST(b.Baseline AS DECIMAL(15,2)),
b.Years,
b.Quart,
g.Months,
g.GTS,
Result = CAST(b.Baseline * g.GTS AS DECIMAL(15,2)),
NextBaseline = SUM(CAST(b.Baseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
FROM #GTS AS g
INNER JOIN #Base AS b
ON B.CCP = g.CCP
AND b.QUART = g.QUART
AND b.YEARS = g.YEARS
UNION ALL
SELECT b.CCP,
CAST(b.NextBaseline AS DECIMAL(15, 2)),
b.Years,
b.Quart + 1,
g.Months,
g.GTS,
Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,2)),
NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
FROM #GTS AS g
INNER JOIN CTE AS b
ON B.CCP = g.CCP
AND b.Quart + 1 = g.QUART
AND b.YEARS = g.YEARS
AND b.RowNumber = 1
)
SELECT CCP, Months, Quart, Years, GTS, Result, Baseline
FROM CTE;
更新:
工作一年以上
;WITH order_cte
AS (SELECT Dense_rank() OVER(partition BY ccp ORDER BY years, quart) d_rn,*
FROM #gts),
CTE
AS (SELECT b.CCP,
Baseline = Cast(b.Baseline AS DECIMAL(15, 2)),
g.Years,
g.Quart,
g.Months,
g.GTS,
d_rn,
Result = Cast(b.Baseline * g.GTS AS DECIMAL(15, 2)),
NextBaseline = Sum(Cast(b.Baseline * g.GTS AS DECIMAL(15, 2)))
OVER(
PARTITION BY g.CCP, g.years, g.quart),
RowNumber = Row_number()
OVER(
PARTITION BY g.CCP, g.years, g.quart
ORDER BY g.Months)
FROM order_cte AS g
INNER JOIN #Baseline AS b
ON B.CCP = g.CCP
AND b.QUART = g.QUART
AND b.YEARS = g.YEARS
UNION ALL
SELECT b.CCP,
Cast(b.NextBaseline AS DECIMAL(15, 2)),
g.Years,
g.Quart,
g.Months,
g.GTS,
g.d_rn,
Result = Cast(b.NextBaseline * g.GTS AS DECIMAL(15, 2)),
NextBaseline = Sum(Cast(b.NextBaseline * g.GTS AS DECIMAL(15, 2)))
OVER(
PARTITION BY g.CCP, g.years, g.quart),
RowNumber = Row_number()
OVER(
PARTITION BY g.CCP, g.years, g.quart
ORDER BY g.Months)
FROM order_cte AS g
INNER JOIN CTE AS b
ON B.CCP = g.CCP
AND b.d_rn + 1 = g.d_rn
AND b.RowNumber = 1)
SELECT CCP,
Months,
Quart,
Years,
GTS,
Result,
Baseline
FROM CTE;
现在我正在Sql Server 2012+ 中寻找一个解决方案，它将利用SUM OVER(ORDER BY) 功能或任何更好的方法
Now am looking for a solution in Sql Server 2012+ which will utilize SUM OVER(ORDER BY) functionality or any better way
尝试过这样的事情
EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))
但是没有锻炼
推荐答案
以下解决方案假设每季度总是有 3 行(只有最后一个季度可能是部分的)，单个 SELECT，没有递归 :-)
Following solution assumes there are always 3 rows per quarter (only the last quarter might be partial), single SELECT, no recursion :-)
WITH sumQuart AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() -- for the 1st month in a quarter
OVER (PARTITION BY CCP, Years, Quart
ORDER BY months) = 1
-- return the sum of all GTS of this quarter
THEN SUM(GTS) OVER (PARTITION BY CCP, Years, Quart)
ELSE NULL -- other months
END AS sumGTS
FROM gts
)
,cte AS
(
SELECT
sq.*,
COALESCE(b.Baseline, -- 1st quarter
-- product of all previous quarters
CASE
WHEN MIN(ABS(sumGTS)) -- any zeros?
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) = 0
THEN 0
ELSE -- product
EXP(SUM(LOG(NULLIF(ABS(COALESCE(b.Baseline,1) * sumGTS),0)))
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING)) -- product
-- odd number of negative values -> negative result
* CASE WHEN COUNT(CASE WHEN sumGTS < 0 THEN 1 END)
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) % 2 = 0 THEN 1 ELSE -1 END
END) AS newBaseline
FROM sumQuart AS sq
LEFT JOIN BASELINE AS b
ON B.CCP = sq.CCP
AND b.Quart = sq.Quart
AND b.Years = sq.Years
)
SELECT
CCP, months, Quart, Years, GTS,
round(newBaseline * GTS,2),
round(newBaseline,2)
FROM cte
参见小提琴
添加了处理值的逻辑 <= 0 Fiddle
Added logic to handle values <= 0 Fiddle
这篇关于在 T-SQL 中运行乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
