GTS表
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
Explantion
基线表具有每个CCP的单个基线值.
基线值应适用于每个CCP的第一季度,并且对于下一季度,上一季度的总和值将是basleine.
;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,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.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,b.Quart + 1,Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15,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,g.Years,g.Quart,d_rn,Result = Cast(b.Baseline * g.GTS AS DECIMAL(15,NextBaseline = Sum(Cast(b.Baseline * g.GTS AS DECIMAL(15,2)))
OVER(
PARTITION BY g.CCP,RowNumber = Row_number()
OVER(
PARTITION BY g.CCP,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,g.d_rn,Result = Cast(b.NextBaseline * g.GTS AS DECIMAL(15,NextBaseline = Sum(Cast(b.NextBaseline * g.GTS AS DECIMAL(15,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,Baseline
FROM CTE;
现在我正在寻找sql Server 2012中的解决方案,它将利用SUM OVER(ORDER BY)功能或任何更好的方式
试过这样的事情
EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))
但没锻炼
解决方法
以下解决方案假设每季度总共有3行(只有最后一个季度可能是部分),单个SELECT,没有递归:-)
WITH sumQuart AS
(
SELECT *,CASE
WHEN ROW_NUMBER() -- for the 1st month in a quarter
OVER (PARTITION BY CCP,Quart
ORDER BY months) = 1
-- return the sum of all GTS of this quarter
THEN SUM(GTS) OVER (PARTITION BY CCP,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.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.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,round(newBaseline * GTS,2),round(newBaseline,2)
FROM cte
